2025年全国计算机等级考试二级真题测试(答案)三、电子表格题

2025年全国计算机等级考试二级练习题测试(答案)三、电子表格题（一）基础数据整理与格式设置打开考生文件夹下的“销售数据.xlsx”工作簿，其中包含“原始数据”“产品信息”“区域目标”3张工作表。要求完成以下操作：1.在“原始数据”工作表的A列前插入一列，列标题为“订单ID”，并按“D列（客户ID）+E列（日期，格式为YYYYMMDD）+流水号”规则提供唯一订单ID。其中流水号为当日同客户的第N笔订单，例如客户ID为C001的客户20240305有3笔订单，则订单ID分别为C00120240305001、C00120240305002、C00120240305003。操作步骤：（1）选中A列，右键选择“插入”，在A1单元格输入“订单ID”；（2）在B2单元格输入公式：=D2&TEXT(E2,"YYYYMMDD")&TEXT(COUNTIFS(D$2:D2,D2,E$2:E2,E2),"000")；（3）双击B2单元格填充柄向下填充至数据末尾；（4）选中B列，右键选择“复制”，再右键选择“选择性粘贴数值”，移除公式依赖。2.将“原始数据”工作表的“日期”列（E列）格式设置为“2024年3月5日”（自定义格式：yyyy"年"m"月"d"日"），“销售额”列（H列）设置为带千位分隔符的数值格式，保留2位小数；将“利润”列（I列）设置为百分比格式，保留1位小数（如5.2%）。操作步骤：（1）选中E列，右键选择“设置单元格格式”，在“数字”选项卡中选择“自定义”，类型输入“yyyy"年"m"月"d"日"”，点击“确定”；（2）选中H列，右键选择“设置单元格格式”，“分类”选择“数值”，勾选“使用千位分隔符”，小数位数设为2，点击“确定”；（3）选中I列，同样进入“设置单元格格式”，“分类”选择“百分比”，小数位数设为1，点击“确定”。（二）函数应用与数据计算1.在“原始数据”工作表J列插入“产品类别”列，根据“产品ID”（G列）从“产品信息”工作表中匹配获取对应类别（“产品信息”工作表A列为产品ID，B列为类别）。操作步骤：（1）在J1单元格输入“产品类别”；（2）在J2单元格输入公式：=VLOOKUP(G2,产品信息!A:B,2,0)；（3）向下填充至数据末尾，检查是否存在N/A错误（若有，需核对“产品信息”工作表是否包含所有产品ID）。2.在“原始数据”工作表K列插入“是否达标”列，若该订单“销售额”（H列）≥对应区域当月目标（“区域目标”工作表A列为区域，B列为月份，C列为目标销售额），则标记“达标”，否则“未达标”。其中“区域”对应“原始数据”C列，“月份”为E列的月份（如3月）。操作步骤：（1）在K1单元格输入“是否达标”；（2）在K2单元格输入公式：=IF(H2>=VLOOKUP(C2&TEXT(E2,"m月"),区域目标!A:C,3,0),"达标","未达标")；（3）公式说明：通过TEXT函数将日期转为“m月”格式（如3月），与区域（C列）拼接成查找值（如“华北3月”），在“区域目标”工作表中A列需预先将区域与月份拼接（如A2=“华北”&B2），或调整VLOOKUP的查找范围为区域目标!A:C，并确保查找值与A列内容格式一致；（4）向下填充公式，验证逻辑（例如华北区3月目标为5000元，某订单销售额为5500元则显示“达标”）。3.在“原始数据”工作表L列插入“销售额排名”列，按“产品类别”（J列）分组，计算该订单销售额在同类别中的降序排名（相同销售额排名相同，后续排名不跳号）。操作步骤：（1）在L1单元格输入“销售额排名”；（2）在L2单元格输入公式：=RANK.EQ(H2,IF(J$2:J$1000=J2,H$2:H$1000),0)；（3）按下Ctrl+Shift+Enter组合键输入数组公式（Excel2019及以上版本可直接输入，旧版本需确认）；（4）向下填充公式，检查排名是否按类别分组（如“电子产品”类别内的销售额单独排名）。（三）数据排序、筛选与高级操作1.对“原始数据”工作表数据按以下规则排序：主要关键字为“区域”（C列）升序，次要关键字为“产品类别”（J列）升序，第三关键字为“销售额”（H列）降序。操作步骤：（1）选中数据区域（A1:L1000，假设数据至1000行）；（2）点击“数据”选项卡“排序”按钮，打开“排序”对话框；（3）“主要关键字”选择“区域”，排序依据“数值”，次序“升序”；（4）点击“添加条件”，“次要关键字”选择“产品类别”，次序“升序”；（5）再次“添加条件”，“第三关键字”选择“销售额”，次序“降序”；（6）勾选“数据包含标题”，点击“确定”完成排序。2.在“原始数据”工作表中筛选出“区域”为“华东”或“华南”、“产品类别”为“家电”或“日用品”、“销售额”≥8000元且“利润”≥6.0%的订单，要求使用“高级筛选”功能，条件区域放置在A13:L15（自行设计条件区域）。操作步骤：（1）在A13单元格输入“区域”，B13输入“产品类别”，C13输入“销售额”，D13输入“利润”；（2）在A14输入“华东”，B14输入“家电”，C14输入“>=8000”，D14输入“>=6%”；（3）在A15输入“华南”，B15输入“日用品”，C15输入“>=8000”，D15输入“>=6%”；（4）点击“数据”选项卡“高级”按钮，打开“高级筛选”对话框；（5）“列表区域”选择原始数据区域（A1:L1000），“条件区域”选择A13:D15；（6）选择“在原有区域显示筛选结果”或“将筛选结果复制到其他位置”（本题默认原有区域），点击“确定”。（四）条件格式与图表制作1.在“原始数据”工作表中，对“利润”列（I列）应用条件格式：若利润≥8.0%，填充色为绿色（标准色）；若利润≤4.0%，填充色为红色（标准色）；其他情况无格式。操作步骤：（1）选中I2:I1000（数据区域）；（2）点击“开始”选项卡“条件格式”“新建规则”；（3）选择“使用公式确定要设置格式的单元格”；（4）在“为符合此公式的值设置格式”中输入：=I2>=8%，点击“格式”，填充选择绿色，点击“确定”；（5）再次点击“条件格式”“新建规则”，输入公式：=I2<=4%，格式设置为红色填充，点击“确定”；（6）调整条件顺序（通过“管理规则”将“>=8%”规则置于“<=4%”之上，避免覆盖）。2.在“分析”工作表（新建）中，基于“原始数据”工作表数据创建组合图表：分类轴为“月份”（提取E列月份，如1月、2月…12月）；柱形图系列为“各月总销售额”（H列求和）；折线图系列为“各月平均利润”（I列求平均）；图表标题为“2024年各月销售与利润趋势图”；数据标签显示柱形图的具体销售额（保留0位小数）。操作步骤：（1）在“分析”工作表A1输入“月份”，B1输入“总销售额”，C1输入“平均利润”；（2）在A2输入“1月”，向下填充至A13为“12月”；（3）在B2输入公式：=SUMIF(原始数据!E:E,">=202411",原始数据!H:H)SUMIF(原始数据!E:E,">=202421",原始数据!H:H)（逐月计算，或使用SUMIFS按月份筛选）；（4）在C2输入公式：=AVERAGEIF(原始数据!E:E,">=202411",原始数据!I:I)AVERAGEIF(原始数据!E:E,">=202421",原始数据!I:I)；（5）选中A1:C13数据区域，点击“插入”选项卡“组合图表”“自定义组合图”；（6）设置“总销售额”为“簇状柱形图”，“平均利润”为“折线图”，勾选“次坐标轴”（可选，根据数据范围调整）；（7）添加图表标题“2024年各月销售与利润趋势图”；（8）右键柱形图系列，选择“添加数据标签”，设置标签格式为“值”，小数位数0；（9）调整坐标轴刻度、图例位置，美化图表。（五）数据透视表与数据透视图1.在“分析”工作表中创建数据透视表，放置于A16单元格，要求：行标签：区域、产品类别；列标签：季度（将“日期”字段分组为13月=Q1，46月=Q2，79月=Q3，1012月=Q4）；值字段：销售额（求和）、利润（平均值，显示为百分比，保留1位小数）；按“销售额”降序排列行标签。操作步骤：（1）点击“插入”选项卡“数据透视表”，选择“原始数据”为数据源，放置位置选择“分析!A16”；（2）在“数据透视表字段”任务窗格中，将“区域”“产品类别”拖至“行”区域（“区域”在上，“产品类别”在下）；（3）将“日期”拖至“列”区域，右键“日期”字段“组合”，起始于2024/1/1，结束于2024/12/31，步长选择“季度”，点击“确定”（列标签显示为Q1、Q2等）；（4）将“销售额”拖至“值”区域（默认求和），将“利润”拖至“值”区域，右键“利润”值字段“值字段设置”，选择“平均值”，点击“数字格式”设置为百分比（1位小数）；（5）右键行标签区域任意单元格“排序”“其他排序选项”，主要关键字选择“求和项:销售额”，降序排列；（6）调整列宽、行高，设置数据区域边框。2.基于上述数据透视表创建数据透视图，类型为“簇状柱形图”，显示各区域、产品类别在不同季度的销售额对比，图表放置于“分析”工作表F16位置。操作步骤：（1）选中数据透视表任意单元格，点击“分析”选项卡“数据透视图”；（2）选择“簇状柱形图”，点击“确定”；（3）拖动图表至F16位置，调整大小；（4）删除冗余图例（若有），设置图表标题为“区域产品类别季度销售额对比”；（5）右键坐标轴调整刻度，确保数据显示清晰。（六）工作表保护与打印设置1.保护“原始数据”工作表，允许用户编辑“备注”列（M列，需自行插入），其他列不可编辑，密码设置为“123456”（区分大小写）。操作步骤：（1）在“原始数据”工作表M1输入“备注”；（2）点击“审阅”选项卡“允许用户编辑区域”；（3）点击“新建”，“区域名称”设为“备注列”，“引用单元格”选择M1:M1000，点击“确定”；（4）点击“保护工作表”，勾选“允许此工作表的所有用户进行：选定锁定单元格”“选定未锁定单元格”；（5）输入密码“123456”，再次确认密码，点击“确定”；（6）验证：尝试修改其他列（如A列）应提示“单元格或图表项受保护，无法修改”，修改M列可正常输入。2.设置“分析”工作表打印