2025年计算机二级考试电子表格操作考试题及答案

2025年计算机二级考试电子表格操作考试题及答案一、基础数据整理打开考生文件夹下的“销售数据.xlsx”工作簿，其中“原始数据”工作表包含某公司2024年第四季度销售记录，字段包括：序号（A列）、销售日期（B列）、部门（C列）、产品ID（D列）、产品类别（E列）、销售人员（F列）、销售额（G列）、成本（H列）。请按以下要求完成操作：1.在B列右侧插入一列（新B列），列标题为“月份”，利用函数提取“销售日期”中的月份（如“2024-10-05”提取为“10”），结果为数值型。2.将A列“序号”设置为文本格式（以避免首位0丢失），调整C列“部门”列宽为12，G列“销售额”和H列“成本”列宽为15。3.将A1:I1单元格区域合并后居中，设置字体为“微软雅黑”、字号14、加粗；其余标题行（A2:I2）设置为浅蓝填充色（主题颜色第5行第1列）、居中对齐。答案：1.选中B列（原销售日期列），右键选择“插入”；在B2单元格输入公式“=MONTH(A2)”（注：插入列后原B列变为C列，此处假设原数据从A2开始），向下填充至数据末尾；选中B列，右键“设置单元格格式”-“数字”-“数值”-“小数位数0”。2.选中A列，右键“设置单元格格式”-“数字”-“文本”；选中C列，右键“列宽”-输入“12”；选中G、H列，右键“列宽”-输入“15”。3.选中A1:I1，点击“开始”-“合并后居中”；设置字体“微软雅黑”、字号14、加粗；选中A2:I2，点击“填充颜色”-选择主题颜色第5行第1列（浅蓝），点击“居中”对齐。二、公式与函数应用1.在I列插入“利润”列（标题为“利润”），计算每笔销售的利润（利润=销售额-成本），结果保留2位小数。2.在J列插入“利润率”列（标题为“利润率”），计算利润率（利润率=利润/销售额），结果以百分比格式显示（保留2位小数），若销售额为0则显示“无数据”（需处理错误值）。3.利用VLOOKUP函数，在K列提供“产品名称”（标题为“产品名称”），产品ID与名称对应关系存储在“产品对照表”工作表的A1:B10区域（A列为产品ID，B列为产品名称）。4.在L列计算“季度累计销售额”，要求按“销售人员”统计其10月至12月的累计销售额（例如：张三10月所有销售记录的销售额之和，加上11月、12月的销售额之和）。答案：1.在I2单元格输入公式“=G2-H2”，向下填充；选中I列，右键“设置单元格格式”-“数值”-“小数位数2”。2.在J2单元格输入公式“=IF(G2=0,"无数据",I2/G2)”，向下填充；选中J列，右键“设置单元格格式”-“百分比”-“小数位数2”。3.在K2单元格输入公式“=VLOOKUP(D2,产品对照表!$A$1:$B$10,2,0)”，向下填充（注意锁定区域为绝对引用）。4.在L2单元格输入公式“=SUMIFS(G:G,F:F,F2)”，向下填充（SUMIFS函数按F列销售人员筛选，求和G列销售额）。三、数据排序与筛选1.对“原始数据”工作表，按以下优先级排序：主要关键字“部门”（升序），次要关键字“利润率”（降序），第三关键字“销售日期”（升序）。2.筛选出“部门”为“家电部”或“数码部”、“月份”为11月且“利润率”高于18%的记录，隐藏其他行。3.对筛选结果中的“销售额”列添加数据条条件格式（颜色为橙色，最小值和最大值类型均为“百分比”，值为0和100）。答案：1.选中数据区域（A2:L1001，假设共1000条记录），点击“数据”-“排序”；主要关键字选“部门”、升序；添加条件，次要关键字选“利润率”、降序；添加条件，第三关键字选“销售日期”、升序，点击“确定”。2.点击“数据”-“筛选”；点击“部门”筛选按钮，勾选“家电部”和“数码部”；点击“月份”筛选按钮，勾选“11”；点击“利润率”筛选按钮，选择“数字筛选”-“大于”-输入“18%”，点击“确定”。3.选中筛选后的“销售额”列（如G3:G500），点击“开始”-“条件格式”-“数据条”-“其他规则”；在“格式样式”中选择“数据条”，颜色选橙色；最小值类型选“百分比”、值0；最大值类型选“百分比”、值100，点击“确定”。四、数据透视表与图表1.在“分析”工作表中，基于“原始数据”工作表创建数据透视表，要求：行标签为“部门”和“产品类别”，列标签为“月份”，值字段为“销售额”（求和）和“利润率”（平均值），显示行总计和列总计。2.在数据透视表下方插入一张簇状柱形图，数据源为数据透视表中“家电部”各产品类别10-12月的销售额数据（需筛选家电部），图表标题为“家电部各产品类别月度销售额对比”，设置分类轴标题为“产品类别”，数值轴标题为“销售额（万元）”，数据标签显示值（保留2位小数）。答案：1.点击“插入”-“数据透视表”，选择数据源为“原始数据!$A$1:$L$1001”，放置位置为“分析!A1”；在“数据透视表字段”中，将“部门”和“产品类别”拖至“行”区域，“月份”拖至“列”区域；将“销售额”拖至“值”区域（默认求和），“利润率”拖至“值”区域并右键“值字段设置”-“平均值”；勾选“数据透视表工具-设计”-“总计”-“行和列总计”。2.点击数据透视表中的“部门”筛选按钮，选择“家电部”；选中筛选后的行标签（产品类别）和列标签（10月、11月、12月）及对应销售额数据（如B3:D5），点击“插入”-“柱形图”-“簇状柱形图”；右键图表标题，输入“家电部各产品类别月度销售额对比”；右键分类轴（X轴），选择“设置坐标轴格式”-“坐标轴选项”-“标题”-“坐标轴下方标题”，输入“产品类别”；右键数值轴（Y轴），设置标题为“销售额（万元）”；右键图表数据系列，选择“添加数据标签”，右键数据标签-“设置数据标签格式”-“数字”-“数值”-“小数位数2”。五、高级操作与优化1.在“原始数据”工作表F列“销售人员”中，设置数据有效性：允许“序列”，来源为“张三,李四,王五,赵六”（逗号分隔），输入信息为“请从下拉列表中选择销售人员”，出错警告标题为“错误”，信息为“必须选择指定销售人员”。2.使用INDEX+MATCH组合函数，在M列“最高销售额”中，查找每个销售人员的最高销售额（例如：张三的所有销售记录中销售额最大值）。3.设置“原始数据”工作表页面布局：纸张大小为A4，上下边距2厘米，左右边距1.5厘米，水平居中对齐；页眉内容为“2024年第四季度销售数据统计”（居中），页脚左侧插入“制表人：财务部”，右侧插入页码（格式为“第X页，共Y页”）。答案：1.选中F列（F2:F1001），点击“数据”-“数据有效性”-“数据验证”；“设置”选项卡中，允许选“序列”，来源输入“张三,李四,王五,赵六”；“输入信息”选项卡中，标题输入“提示”，输入信息输入“请从下拉列表中选择销售人员”；“出错警告”选项卡中，样式选“停止”，标题输入“错误”，信息输入“必须选择指定销售人员”，点击“确定”。2.在M2单元格输入公式“=INDEX(G:G,MATCH(F2,F:F,0)+MATCH(MAX(IF(F:F=F2,G:G)),G:G,0)-1)”（注：需按Ctrl+Shift+Enter输入数组公式），或更简洁的“=MAXIFS(G:G,F:F,F2)”（若Excel版本支持MAXIFS），向下填充。3.点击“页面布局”-“纸张大小”-“A4”；点击“页边距”-“自定义边距”，设置上、下边距2厘米，左、右边距1.5厘米；勾选“水平居中”；点击“页眉/页脚”-“自定义页眉”，中间位置输入“2024年第四季度销售数据统计”；点击“自定义页脚”，左侧输入“制表人：财务部”，右侧输入“&P/&N”（表示第X页，共Y页）。六