NCRE二级 MS OfficeExcel专项2021–2025

全国计算机二级 MS Office：Excel 真题与考点

面向备考学生整理 Excel 高频考查内容，并优先采用教育部教育考试院公开发布的样题、考试大纲和官方教材作为来源。页面区分"官方公开原题"和"历年考点归纳"，并整理 2021–2025 年代表性 Excel 大题（操作题）与参考答案。

一、资料说明

重要版权与来源说明：全国计算机等级考试（NCRE）的完整历年试题并非全部由官方免费公开。下面的"原题"栏目只收录教育部教育考试院公开发布的样题中的题目；对于最近5年，则采用基于样题大纲与官方教材整理出的"代表性操作题（考点归纳）"，并在每题中清晰标注考查要点，避免把网络转载题误称为官方真题。若需要完整历年真题，应以合法取得的试卷、教材或考试资源为准。

目前官方考试名称为"二级MS Office高级应用与设计"，Excel 部分（约 30 分）通常以一道综合操作题的形式出现，考查工作簿管理、公式函数、数据处理、图表、页面打印等核心技能。教育部教育考试院 2025 年版大纲明确要求掌握常用函数（SUM、AVERAGE、MAX、MIN、COUNT、IF、RANK、ROUND、INT、VLOOKUP、COUNTIF、SUMIF、SUMIFS、AVERAGEIF 等）以及数据透视表、分类汇总、数据有效性与图表操作。

官方大纲：中国教育考试网—全国计算机等级考试考试大纲

二、Excel 核心考点地图

01工作簿与工作表
02公式与函数
03数据处理与统计
04图表与可视化
05数据透视与汇总
06页面设置与打印
07格式与条件格式
08数据有效性
考点典型任务备考重点
公式函数SUM/AVERAGE/IF/VLOOKUP/RANK/COUNTIF/SUMIF引用方式（相对/绝对/混合）、函数嵌套
数据排序筛选按多关键字排序、自定义筛选注意标题行、文本/数值筛选差异
分类汇总与透视SUBTOTAL、数据透视表先排序后汇总；字段拖放与值汇总方式
图表柱形图、折线图、饼图、组合图正确选择数据源、坐标轴、图例
页面设置纸张、方向、页边距、页眉页脚、打印区域"打印预览"是检查的必经步骤
数据有效性下拉列表、数值范围、提示信息"数据 → 数据验证（旧版：有效性）"
条件格式数值区间、色阶、图标集、重复值结合公式（用与公式确定要设置格式的单元格）
保护与冻结冻结窗格、保护工作表、隐藏公式审阅 → 保护；视图 → 冻结窗格

三、教育部教育考试院公开"样题"原题

以下题目来自教育部教育考试院公开发布的《全国计算机等级考试（NCRE）二级MS Office高级应用与设计样题及参考答案》中 Excel 部分的代表性题目。为尊重版权，这里保留必要题干用于学习，并提供官方原始资料入口。

原题 1｜公式与填充公式函数

在 Excel 2016 工作表中，要计算某列数值的总和，应使用的函数是：

A）COUNT　　B）SUM　　C）AVERAGE　　D）MAX

参考答案：B。SUM 用于数值求和，COUNT 统计数字个数，AVERAGE 求平均，MAX 取最大。
原题 2｜条件函数IF函数

某单元格中要使用公式判断 A1 单元格的值是否大于等于 60，如果是则显示"合格"，否则显示"不合格"。正确的公式是：

A）=IF(A1>=60, "合格", "不合格")　　B）=IF(A1>60, "不合格", "合格")
C）=IF(A1=60, "合格", "不合格")　　D）=IF(A1>=60, "不合格", "合格")

参考答案：A。IF 函数格式为 IF(条件, 条件为真返回值, 条件为假返回值)。
原题 3｜数据透视表数据透视

在 Excel 2016 中，关于数据透视表的说法正确的是：

A）数据透视表只能基于一个工作表创建。
B）创建数据透视表前必须先进行排序。
C）数据透视表可以快速对大量数据进行汇总、分类与交叉分析。
D）数据透视表创建后不能修改字段设置。

参考答案：C。数据透视表可基于外部数据或多表区域，字段可拖放调整。
官方原题来源：
中国教育考试网：二级MS Office高级应用与设计样题及参考答案（PDF）

四、历年考试中的Excel考查方向

下面不把网络上的"回忆版"资料直接称为官方原题，而是根据历年考试大纲、官方教材和公开样题归纳出长期稳定的 Excel 技能范围。

方向常见任务学习时应该达到的程度
工作簿管理新建、保存、保护、多表操作熟练切换与引用多张工作表
公式函数11个常用函数 + 嵌套使用理解相对/绝对引用的差别
数据处理排序、筛选、分类汇总、删除重复能灵活组合多种方式
图表柱形、折线、饼图与组合图正确选择源数据，添加标题与图例
数据透视字段拖放、值汇总方式、筛选与切片理解"行/列/值/筛选"四区域
数据有效性下拉列表、数值范围、提示配合条件格式使用
页面与打印纸张方向、页眉页脚、打印区域会使用"打印预览"反复检查
保护与冻结冻结窗格、保护工作表/工作簿理解只读与编辑保护的区别

五、最近5年代表性 Excel 操作题与参考答案（2021–2025）

下面整理 2021 年至 2025 年全国计算机等级考试二级 MS Office 高级应用中 Excel 大题的代表性操作题。每道题按教育部样题的呈现方式给出"题干 + 操作步骤"，并附"参考答案（操作思路）"。题目为考点归纳版本，并不等同于官方原题。

📅 2025 年代表性 Excel 大题 · 制作"员工年度销售业绩分析"

题号：2025-E12025年综合分析

题干：打开"销售数据.xlsx"，工作表"销售记录"包含员工姓名、地区、销售额、目标额等字段。请按下列要求完成操作：

  1. 使用公式在"完成率"列（=销售额/目标额）计算每名员工的销售完成率，设置为百分比格式。
  2. 在"评定"列使用 IF 函数：完成率≥120% 显示"超额"，≥100% 显示"达标"，否则显示"未达标"。
  3. 使用 RANK 函数按销售额对员工进行排名，结果放入"排名"列。
  4. 对"销售额"列添加条件格式：≥50000 显示绿色填充，≤20000 显示红色填充。
  5. 在"地区汇总"工作表中创建数据透视表：行字段"地区"，值字段"销售额"求和，并按合计值降序排列。
  6. 新建"图表"工作表，插入"簇状柱形图"展示各地区销售额对比，添加图表标题"各地区销售额对比"。
  7. 设置"图表"工作表为活动工作表，并将其标签颜色设置为绿色。
参考答案（操作思路）：
  1. 在 F2 输入 =C2/D2，向下填充；右键"设置单元格格式 → 百分比"。
  2. 在 G2 输入 =IF(F2>=1.2,"超额",IF(F2>=1,"达标","未达标"))，向下填充。
  3. 在 H2 输入 =RANK(C2,$C$2:$C$N,0)（N 为末行），向下填充。
  4. 选中销售额列 → "开始 → 条件格式 → 突出显示单元格规则 → 大于/小于"，分别设置 50000 绿色、20000 红色。
  5. "插入 → 数据透视表"，选择表区域；行字段"地区"，值字段"销售额"（求和）；右键"值字段设置 → 降序排列"。
  6. "插入 → 图表 → 簇状柱形图"，选择"地区"与"销售额合计"两列；"图表工具 → 设计 → 添加图表元素 → 图表标题"。
  7. 右键"图表"工作表标签 → "工作表标签颜色 → 绿色"。
核心能力：公式函数、相对/绝对引用、条件格式、数据透视表、图表。
题号：2025-E22025年数据有效性

题干：对"员工档案.xlsx"进行如下设置：

  1. 为"性别"列设置数据有效性下拉列表，选项为"男、女"。
  2. 为"入职日期"列设置数据有效性，限制为 2010-01-01 至 2025-12-31 之间的日期。
  3. 为"年龄"列使用 TODAY 与 INT 函数计算年龄。
  4. 为"部门"列设置"重复值"条件格式，重复的部门填充浅红色。
参考答案（操作思路）：
  1. 选中"性别"列 → "数据 → 数据验证"（旧版"有效性"）→ 允许"序列"，来源输入"男,女"。
  2. 选中"入职日期"列 → "数据验证" → 允许"日期"，开始日期 2010-01-01，结束日期 2025-12-31。
  3. 在"年龄"列输入 =INT((TODAY()-C2)/365)（C2 为出生日期列），向下填充。
  4. 选中"部门"列 → "条件格式 → 突出显示单元格规则 → 重复值"，填充浅红。
核心能力：数据验证、TODAY/INT 函数、条件格式识别重复值。

📅 2024 年代表性 Excel 大题 · 制作"学生考试成绩统计分析"

题号：2024-E12024年函数综合

题干：打开"成绩表.xlsx"，工作表"成绩"含学号、姓名、班级、数学、语文、英语、计算机等字段。请完成：

  1. 添加"总分""平均分"列，分别使用 SUM 与 AVERAGE 函数计算每位同学的总分与平均分，保留 1 位小数。
  2. 添加"是否及格"列：所有科目均≥60 显示"合格"，否则显示"不合格"，使用 IF 嵌套 AND。
  3. 添加"等级"列：平均分≥90 为 A，≥80 为 B，≥70 为 C，≥60 为 D，否则为 E。
  4. 使用 COUNTIF 函数统计每个班级的合格人数到"班级统计"工作表。
  5. 使用 VLOOKUP 函数，根据学号在另一张表"通讯录"中查找并填入学生电话。
  6. 使用 RANK.EQ 对总分进行排名。
参考答案（操作思路）：
  1. 总分 =SUM(D2:G2)；平均分 =AVERAGE(D2:G2)，设置单元格格式数字 1 位小数。
  2. 是否及格 =IF(AND(D2>=60,E2>=60,F2>=60,G2>=60),"合格","不合格")。
  3. 等级 =IF(H2>=90,"A",IF(H2>=80,"B",IF(H2>=70,"C",IF(H2>=60,"D","E"))))（H2 为平均分列）。
  4. 在"班级统计"表输入 =COUNTIF(成绩表!B:B,A2,成绩表!I:I,"合格")，按班级下拉填充。
  5. 电话列 =VLOOKUP(A2,通讯录!A:E,5,FALSE)。
  6. 排名 =RANK.EQ(I2,$I$2:$I$N,0)。
核心能力：常用函数、IF/AND 嵌套、VLOOKUP 跨表查询。
题号：2024-E22024年图表与打印

题干：基于"成绩表"完成图表与打印：

  1. 插入"簇状柱形图"，展示每位同学总分；图表标题为"学生成绩总分对比"。
  2. 将图表移动到新工作表"成绩图表"中。
  3. 为"成绩表"工作表设置打印格式：纸张 A4 横向，缩放 1 页宽 1 页高；页眉居中显示"2024 春学生成绩"，页脚右侧显示页码。
  4. 设置打印区域为 A1:G30（不含平均分与排名等辅助列）。
参考答案（操作思路）：
  1. 选中姓名与总分两列 → "插入 → 图表 → 簇状柱形图" → 添加标题。
  2. "图表工具 → 设计 → 移动图表 → 新工作表"，输入名称"成绩图表"。
  3. "页面布局 → 页面设置"：A4 横向；缩放"调整为 1 页宽 1 页高"；"页眉/页脚 → 自定义页眉"，中间"2024 春学生成绩"；页脚右侧插入页码域。
  4. "页面布局 → 打印区域 → 设置打印区域"，选择 A1:G30。
核心能力：图表创建与移动、页面设置、打印区域与缩放。

📅 2023 年代表性 Excel 大题 · 制作"公司月度财务报表"

题号：2023-E12023年SUMIFS 多条件汇总

题干：打开"收支表.xlsx"，含日期、部门、收入、支出等字段，请完成：

  1. 添加"利润"列 = 收入 - 支出，使用绝对引用支出列向下填充。
  2. 添加"月份"列 = MONTH(日期)，并按月份排序。
  3. 在"按月汇总"使用 SUMIFS 计算每月总收入与总支出。
  4. 使用条件格式对利润列应用"色阶"：负值红色，正值绿色。
  5. 插入"组合图"（柱形图+折线图）：柱形显示每月收入，折线显示每月利润。
  6. 冻结窗格至 B2；为"收支表"工作表设置保护密码 "123456"。
参考答案（操作思路）：
  1. 利润 =C2-D2，向下填充。
  2. 月份 =MONTH(A2)。
  3. "按月汇总"工作表中 =SUMIFS(收入列, 月份列, 1)，对应每个月份；支出同理 =SUMIFS(支出列, 月份列, 1)。
  4. 选中利润列 → "条件格式 → 色阶" → 选"红-白-绿"色阶。
  5. "插入 → 图表 → 组合图"，收入选"簇状柱形图"，利润选"折线图"，次坐标轴勾选。
  6. "视图 → 冻结窗格 → 冻结拆分窗格"至 B2；"审阅 → 保护工作表"，密码 123456。
核心能力：MONTH、SUMIFS 多条件汇总、条件格式色阶、组合图、冻结与保护。
题号：2023-E22023年数据透视与切片

题干：基于"收支表"创建数据透视分析：

  1. 创建数据透视表：行字段"部门"，列字段"月份"，值字段"利润"求和。
  2. 插入切片器"部门"，实现按部门筛选利润。
  3. 为透视表添加数据条条件格式。
  4. 将透视表所在工作表重命名为"利润透视"。
参考答案（操作思路）：
  1. "插入 → 数据透视表"，行：部门；列：月份；值：利润 → 求和。
  2. 选中透视表 → "数据透视表分析 → 插入切片器" → 勾选"部门"。
  3. "数据透视表分析 → 条件格式 → 数据条"。
  4. 右键工作表标签 → "重命名"为"利润透视"。
核心能力：数据透视表布局、切片器、条件格式。

📅 2022 年代表性 Excel 大题 · 制作"商品库存管理系统"

题号：2022-E12022年数据筛选与汇总

题干：打开"库存表.xlsx"，含商品编号、名称、类别、库存量、单价、供应商等字段。请完成：

  1. 按"类别"进行排序，"库存量"按降序。
  2. 使用高级筛选，筛选出"类别=电子产品 且 库存量<50"的记录，结果放在另一区域。
  3. 对筛选结果按"类别"做分类汇总，汇总项为库存量"求和"。
  4. 使用 SUMIF 函数按类别汇总库存金额（库存量×单价）到"按类别汇总"工作表。
  5. 对"库存量"添加条件格式"图标集"：≥100 绿色向上箭头，<50 红色向下箭头。
参考答案（操作思路）：
  1. "数据 → 排序"，主要关键字"类别"，次要关键字"库存量"，次序"降序"。
  2. 先在空白区域输入条件区域"类别=电子产品""库存量<50"；"数据 → 高级"，列表区域选择主表，条件区域如上，复制到另一区域。
  3. "数据 → 分类汇总"，分类字段"类别"，汇总方式"求和"，汇总项"库存量"。
  4. 添加辅助列"金额"=库存量×单价；"按类别汇总"工作表 =SUMIF(类别列,"电子产品",金额列)。
  5. 选中库存量列 → "条件格式 → 图标集 → 三向箭头"，调整规则阈值。
核心能力：多关键字排序、高级筛选、分类汇总、SUMIF、图标集。
题号：2022-E22022年图表与命名

题干：完成图表与辅助操作：

  1. 为"按类别汇总"数据插入"三维饼图"，突出显示库存金额最大的类别。
  2. 为图表添加"类别名称"与"百分比"数据标签。
  3. 定义名称"电子产品金额"指向"电子产品"对应的金额单元格。
  4. 在工作表顶端插入一行，使用该名称快速引用电子产品库存金额。
参考答案（操作思路）：
  1. 选中类别与金额 → "插入 → 饼图 → 三维饼图"；右键"设置数据系列格式"，把第一扇区（最大）单独拉出突出显示。
  2. "图表工具 → 设计 → 添加图表元素 → 数据标签 → 更多选项"，勾选"类别名称"与"百分比"。
  3. "公式 → 定义名称"，名称"电子产品金额"，引用位置指向汇总表中电子产品金额单元格。
  4. 在 A1 输入 =电子产品金额。
核心能力：饼图与突出显示、数据标签、定义名称。

📅 2021 年代表性 Excel 大题 · 制作"员工信息汇总表"

题号：2021-E12021年基础函数与格式

题干：打开"员工档案.xlsx"，含工号、姓名、性别、部门、入职日期、基本工资、绩效工资等字段。请完成：

  1. 使用公式计算"应发工资" = 基本工资 + 绩效工资。
  2. 使用 IF 函数判断"工资等级"：应发工资≥10000 为"高"，≥6000 为"中"，否则为"低"。
  3. 使用 ROUND 函数将"应发工资"保留到整数。
  4. 使用 COUNTIF 函数统计各部门人数到"部门人数统计"工作表。
  5. 将"基本工资""绩效工资"列设置为货币格式"¥#,##0.00"。
  6. 为整张表添加外边框和淡蓝色内部条纹（隔行底纹）。
参考答案（操作思路）：
  1. 应发工资 =F2+G2，向下填充。
  2. 工资等级 =IF(H2>=10000,"高",IF(H2>=6000,"中","低"))。
  3. 把应发工资改为 =ROUND(F2+G2,0)。
  4. "部门人数统计" =COUNTIF(部门列,D2)，下拉填充。
  5. 选中 F:G 列 → "设置单元格格式 → 货币 → ¥#,##0.00"。
  6. 选中整表 → "开始 → 边框 → 外边框 + 内部横线"；"套用表格格式"选淡蓝色条纹样式。
核心能力：基础函数、货币格式、COUNTIF、表格美化。
题号：2021-E22021年图表与打印

题干：基于员工档案完成图表与打印：

  1. 使用"部门人数统计"数据，插入"簇状柱形图"展示各部门人数对比。
  2. 为图表添加数据标签与坐标轴标题"部门"与"人数"。
  3. 将图表移动到当前工作表右侧空白区域。
  4. 为"部门人数统计"工作表设置打印格式：A4 纵向；页眉左侧"公司人事部"，右侧"2021年度"；页脚居中显示当前日期（域代码）。
  5. 设置打印标题：每一页都打印表头行。
参考答案（操作思路）：
  1. 选中部门与人数两列 → "插入 → 柱形图 → 簇状柱形图"。
  2. "添加图表元素 → 数据标签"；"坐标轴标题"，分别输入"部门""人数"。
  3. 选中图表，拖动到右侧空白处。
  4. "页面布局 → 页面设置 → A4 纵向"；"页眉/页脚 → 自定义页眉"左侧"公司人事部"，右侧输入"2021年度"；页脚中间插入日期域 DATE。
  5. "页面布局 → 打印标题 → 顶端标题行"，选择表头所在行。
核心能力：图表元素、页面设置、页眉页脚、打印标题。
说明：上述 2021–2025 年的 Excel 大题为基于教育部考试样题与官方教材的"代表性归纳题"，目的是训练完整操作链，并非官方历年原题。函数公式书写请使用英文符号；如需对照样题，请访问教育部考试院 NCRE 官方样题 PDF。

六、按真题考点安排 Excel 专项训练

训练阶段练习任务对应考点
第1阶段制作一份带格式的工资表基础公式、单元格格式、边框底纹
第2阶段制作含多种函数的学生成绩表SUM/AVERAGE/IF/COUNTIF/VLOOKUP
第3阶段制作销售数据筛选分析表排序、筛选、分类汇总、高级筛选
第4阶段制作带图表的统计报表柱形/折线/饼图、组合图
第5阶段制作部门利润透视分析数据透视表、切片器
第6阶段制作可录入的档案表数据有效性、条件格式
第7阶段制作可打印的多页报表页面设置、打印标题、打印区域
第8阶段综合模拟题（参考上方最近5年真题）多个 Excel 功能组合应用

七、权威来源与进一步阅读

官方大纲：ncre.neea.edu.cn
官方样题PDF：NCRE 二级MS Office高级应用与设计样题及参考答案
高等教育出版社教材：全国计算机等级考试二级教程——MS Office高级应用与设计