excel数据处理与分析章节测试题与答案

excel数据处理与分析章节测试题与答案一、单项选择题（每题2分，共20分）1.在Excel中，若要统计某列中数值型数据的个数，应使用的函数是（）A.COUNTB.COUNTAC.COUNTBLANKD.COUNTIF答案：A解析：COUNT函数统计数值型数据的个数；COUNTA统计非空单元格个数（包括文本）；COUNTBLANK统计空白单元格；COUNTIF按条件统计。2.以下关于数据透视表的描述，错误的是（）A.数据透视表可动态调整行/列字段B.原始数据修改后，需手动刷新数据透视表才能更新C.值字段默认汇总方式为“求和”D.不能基于多个不连续的数据区域创建数据透视表答案：D解析：Excel2016及以上版本支持通过“数据模型”基于多个不连续区域创建数据透视表。3.若A1单元格内容为“2023-10-5”，B1单元格输入公式“=MONTH(A1)”，则返回结果为（）A.10B.5C.2023D.10-5答案：A解析：MONTH函数提取日期中的月份值，返回1-12的整数。4.在条件格式中，若要将高于平均值的单元格填充为红色，应选择的规则类型是（）A.突出显示单元格规则-大于B.项目选取规则-值最大的10项C.使用公式确定要设置格式的单元格D.数据条答案：A解析：“突出显示单元格规则-大于”可直接输入平均值（或用AVERAGE函数）作为条件值，系统自动计算当前区域平均值。5.执行“数据-排序”操作时，若选中“数据包含标题”，则（）A.标题行不会参与排序B.标题行会作为普通数据参与排序C.排序后标题行自动调整位置D.无法对标题行进行格式设置答案：A解析：勾选“数据包含标题”时，标题行作为字段名标识，不参与数据行的排序。6.若要在Excel中实现“当输入的工号长度不等于6位时提示错误”，应使用（）A.数据验证-设置-文本长度B.条件格式-新建规则-公式C.保护工作表-允许用户编辑区域D.审阅-批注答案：A解析：数据验证的“文本长度”规则可限制输入内容的字符数，配合错误提示信息实现该功能。7.以下关于VLOOKUP函数的描述，正确的是（）A.查找值必须位于查找区域的首列B.第四个参数为0时表示近似匹配C.可以查找水平方向的数据D.若查找区域包含隐藏列，仍能正确返回结果答案：A解析：VLOOKUP要求查找值在查找区域的首列；第四个参数0为精确匹配，1为近似匹配；水平查找需用HLOOKUP；隐藏列会导致VLOOKUP无法访问数据。8.在“数据透视表字段”窗格中，灰色显示的字段表示（）A.该字段已被添加到报表中B.该字段类型不支持汇总（如文本）C.该字段为空白字段D.数据透视表已达到最大字段限制答案：B解析：文本型字段（如“产品名称”）在值区域默认无法汇总（灰色显示），需手动选择“计数”等汇总方式。9.若要将A列中“2023/10/05”格式的日期批量转换为“2023年10月05日”，最快捷的方法是（）A.使用TEXT函数：=TEXT(A1,"yyyy年mm月dd日")B.复制后粘贴为数值，再设置单元格格式C.数据-分列-固定宽度-日期格式D.开始-数字-自定义-类型输入“yyyy年mm月dd日”答案：D解析：直接设置单元格格式为自定义“yyyy年mm月dd日”可批量转换，无需公式或分列操作。10.在Excel中，若要计算“销售额”列中大于5000且小于10000的数值个数，应使用的函数是（）A.COUNTIFS(销售额,">5000",销售额,"<10000")B.COUNTIF(销售额,">5000")-COUNTIF(销售额,">=10000")C.SUM(IF((销售额>5000)*(销售额<10000),1,0))（数组公式）D.以上方法均正确答案：D解析：COUNTIFS可直接多条件统计；通过两个COUNTIF相减可计算区间值；数组公式配合SUM和IF也可实现，三种方法均正确。二、判断题（每题1分，共10分。正确填“√”，错误填“×”）1.绝对引用符号“$”只能出现在列标前，不能出现在行号前。（）答案：×解析：“”可2.数据透视表的值字段可以设置为“最大值”“最小值”“平均值”等多种汇总方式。（）答案：√解析：值字段右键-值字段设置中可选择求和、计数、最大值等20余种汇总方式。3.使用“合并后居中”功能合并多个单元格时，只有左上角单元格的内容会被保留。（）答案：√解析：合并单元格会清空其他单元格内容，仅保留左上角数据。4.在“数据验证”中设置“整数”类型时，若允许负数，需手动勾选“允许负数”选项。（）答案：×解析：“整数”类型默认允许负数，无需额外设置；若要限制正数，需设置“最小值”为0。5.函数“=SUMIF(区域,条件,求和区域)”中，“条件”可以是文本、数值或表达式。（）答案：√解析：条件支持多种形式，如">60"、"苹果"、B1（单元格引用）等。6.对数据进行降序排序时，空单元格会被排列在数据区域的最上方。（）答案：×解析：排序时空单元格默认排在最后（升序/降序均如此）。7.图表中的数据系列可以来源于不同的工作表，但必须为同一工作簿。（）答案：√解析：图表数据源可跨工作表引用（如=Sheet2!A1:B10），但不能跨工作簿（需链接）。8.使用“快速填充”功能（Ctrl+E）时，需至少输入1个示例值才能触发自动填充。（）答案：√解析：快速填充需要用户提供至少一个正确示例，系统通过模式识别完成后续填充。9.在“条件格式”中，使用公式“=A1>100”时，该规则会应用于当前选中区域的所有单元格，其中A1会根据相对位置自动调整。（）答案：√解析：条件格式中的公式默认使用相对引用，应用到B1时会自动变为“=B1>100”。10.保护工作表后，所有单元格的编辑权限都会被锁定，无法进行任何修改。（）答案：×解析：保护工作表时可勾选“允许用户编辑区域”，设置特定区域可编辑（需输入密码）。三、操作题（每题10分，共30分）1.现有“员工信息表”（部分数据如下），要求：（1）将A1:E1单元格合并并居中，设置字体为“微软雅黑”，字号14，加粗；（2）在F列计算“年龄”（当前年份为2023年，出生日期格式为“yyyy-mm-dd”）；（3）将“性别”列中“男”填充为蓝色底纹，“女”填充为粉色底纹。|A（工号）|B（姓名）|C（部门）|D（出生日期）|E（性别）||----------|----------|----------|---------------|----------||001|张三|销售部|1990-05-12|男||002|李四|技术部|1995-08-20|女|答案及解析：（1）选中A1:E1→开始-对齐方式-合并后居中→字体选择“微软雅黑”，字号14，点击加粗按钮；（2）在F2单元格输入公式：=2023-YEAR(D2)→向下填充至数据末尾；（3）选中E列数据区域（E2:E3等）→开始-条件格式-新建规则-使用公式确定格式→输入公式：=E2="男"→设置填充颜色为蓝色→重复操作，输入公式：=E2="女"→设置填充颜色为粉色。常见错误：合并单元格时未选中连续区域；计算年龄时直接用“2023-D2”（需用YEAR函数提取年份）；条件格式公式未使用相对引用（如固定为E2）。2.某公司“2023年季度销售表”包含字段：日期（A列）、产品（B列）、地区（C列）、销售额（D列）、成本（E列）。要求：（1）在F列计算“毛利”（毛利=销售额-成本）；（2）按“地区”和“产品”分类汇总，显示各地区各产品的总销售额；（3）将“日期”列中2023年第二季度（4-6月）的数据筛选出来。答案及解析：（1）F2单元格输入公式：=D2-E2→向下填充；（2）插入数据透视表（选中数据区域→插入-数据透视表-选择放置位置）→将“地区”拖入行区域，“产品”拖入行区域（或列区域），“销售额”拖入值区域（默认求和）；（3）点击“日期”列筛选按钮→日期筛选-介于→起始日期2023-4-1，结束日期2023-6-30→确定。关键步骤：数据透视表需正确拖放字段（行区域放分类字段，值区域放汇总字段）；日期筛选时注意“介于”包含起始和结束日期。3.现有“客户订单表”（数据范围A1:H100），其中H列“交货状态”需根据G列“交货日期”判断：若交货日期≤订单日期（B列）+30天，标记为“按时”；否则标记为“延迟”。要求用函数实现自动标记。答案及解析：在H2单元格输入公式：=IF(G2<=B2+30,"按时","延迟")向下填充至H100。扩展说明：若“订单日期”或“交货日期”为文本格式，需先用DATEVALUE函数转换（如=IF(DATEVALUE(G2)<=DATEVALUE(B2)+30,"按时","延迟")）；若存在空值，可添加容错处理：=IF(ISBLANK(G2),"未交货",IF(G2<=B2+30,"按时","延迟"))。四、综合分析题（40分）背景资料：某电商公司2023年1-6月销售数据（部分如下表），包含字段：月份（A列）、商品类别（B列）、商品名称（C列）、销售数量（D列）、单价（E列）、地区（F列）。|月份|商品类别|商品名称|销售数量|单价|地区||------|----------|----------|----------|------|--------||1|家电|空调|120|3500|华北||1|家电|冰箱|80|2800|华南||2|3C数码|手机|200|4200|华北||...|...|...|...|...|...|任务要求：（1）计算各商品的月销售额（销售额=销售数量×单价），并添加“销售额”列（G列）；，（2）分析“家电”类商品在华北地区的季度销售趋势（1-3月为Q1，4-6月为Q2），要求：①创建数据透视表，行标签为“月份”，列标签为“商品名称”，值为“销售额”求和；②基于数据透视表生成折线图，显示各家电商品在华北地区的月度销售额变化；（3）统计上半年各地区总销售额TOP3的商品类别，输出表格包含“地区”“商品类别”“总销售额”“排名”字段；（4）若7月份华北地区空调预计销售150台，单价保持3500元，在不修改原数据的情况下，预测华北地区7月空调销售额对上半年总销售额的占比（保留2位小数）。答案及详细解析：（1）计算月销售额在G2单元格输入公式：=D2*E2→向下填充至数据末尾。解析：乘法运算直接使用“*”符号，确保“销售数量”和“单价”均为数值格式（非文本），否则需用VALUE函数转换。（2）家电类华北地区季度销售趋势分析①数据透视表创建步骤：筛选数据：在“商品类别”列筛选“家电”，在“地区”列筛选“华北”；插入数据透视表（选中筛选后的数据区域→插入-数据透视表-选择放置位置如Sheet2!A1）；字段设置：将“月份”拖入行区域，“商品名称”拖入列区域，“销售额”拖入值区域（值字段设置为“求和”）；调整布局：右键行标签-组合-起始月份1，结束月份6，步长1（确保显示所有月份）。②生成折线图：选中数据透视表任意单元格→插入-图表-折线图（推荐带数据标记的折线图）；图表标题设置为“华北地区家电类商品月度销售额趋势（2023年1-6月）”；坐标轴设置：X轴为“月份”（1-6月），Y轴为“销售额（元）”，调整刻度间隔；美化：添加数据标签，调整线条颜色区分不同商品。常见问题解决：若数据透视表未显示所有月份（如2月无数据），需在“数据透视表选项-显示-空行”中勾选显示空行，或通过“组合”功能强制显示1-6月。（3）各地区TOP3商品类别统计①数据透视表1（计算地区+类别总销售额）：插入数据透视表（全选原始数据）→行区域拖入“地区”和“商品类别”（“地区”在上，“商品类别”在下），值区域拖入“销售额”（求和）；右键值字段-值显示方式-按某一字段汇总的百分比（可不选），保持默认“求和”。②按地区分组排名：在数据透视表右侧插入辅助列（如H列），标题为“排