计算机二级 Excel 真题解析 :滨海中学物理统考

四季读书网 2026-08-19 23:30:54 3 0

距离 9 月考试还有一个月，很多考生的 Excel 依然停留在 "背步骤" 的阶段。以真题 "滨海中学物理统考" 为例，这道题表面上考查了 11 个知识点，但剥开外衣，它的本质只有三个动作：把明细按条件聚合、把总分按权重汇总、把结果套上漂亮格式。今天我们不背公式，而是从出题人的视角，把这道题完整推演一遍。

Excel 公认是二级里最难、最容易拉开分差的模块，而这道 "滨海中学物理统考" 就是典型代表。综合该题所覆盖的考点广度与操作复杂度，若以10分制作为难度标尺，其综合实战难度大致处于8至9分区间（属于综合应用型）。但难题并不代表难拿分，它的得分点是一块一块分布的，只要按照科学的方法去 "审题、拆解、操作"，拿稳 80% 的分数并不玄乎。

下面我们按照 "审题→拆解→操作步骤→避坑"的闭环逻辑，带大家彻底吃透这套题。

第一步：审题破局—— 明确数据的 "三大流向"

科学做题的第一步，绝不是急着点鼠标，而是先理清题目到底让你干什么。

这道题的核心逻辑可以用三个问题来概括：

1.数据从哪来？

给定了三张表：成绩单（包含学生所在学校、班号及总分）、小题得分情况（各班级各小题的平均得分）、分值表（每道题的分值及属性）。

2.结果填到哪去？

需要算到两张汇总表：按班级汇总、按学校汇总。

3.最终目标是什么？

算出班级级和校级的人数、最高 / 最低 / 平均分。
算出班级级的客观题和主观题均分。
算出校级的加权平均分和每道小题的得分率。

做完这个 "审题" 动作，你的脑子里就会有一张清晰的地图，不会在操作时迷路。

第二步：拆解与思路：把综合大题拆成 "五块得分田"

思路决定效率。这道题千万不要当成一整座山去爬，而是要利用 "数据透视表 + 辅助列" 的通用思路，拆解成五步相对独立的步骤。

1.基础块：解决班级/学校人数、最高分、最低分、平均分。

思路：单条件、双条件统计，直接套用数据透视表，一拖拽全搞定。

2.进阶块：解决跨表提取"客观题、主观题平均分"。

思路：必须认清原始粒度。小题得分情况表里每一行是一个班级，每一格已经是 "该题平均分"。由于两表行顺序一一对应，直接在按班级汇总表中跨表横向求和即可，千万千万不要除人数。

3.硬核块：解决全校"加权平均分"。

思路：班级人数不同，不能直接算平均，必须 "加权"。为了避开难懂的数组公式，我们回到按班级汇总表，建两列 "总分" 辅助列，再用 SUMIFS汇总给学校。

4.难点块：解决"单题得分率"。

思路：极度依赖 "混合绝对引用"。算每个班每道题的总分（平均分 × 人数），再汇总给学校，再除以该题分值。

5.收尾块：解决新表转置、格式规范与预警。

思路：必须利用 "选择性粘贴 - 数值 - 转置"，最后套用具体样式名、精准的条件格式区域。

第三步：操作实战—— 严格对照真题流程一步步上机

【操作 1】透视表打底，别用复杂函数死磕

操作：选中成绩单表数据，点击【插入】→【数据透视表】，位置选 "现有工作表"（位置：成绩单！F1）确定。

关键设置：将 "学校名称" 拖入【行标签】后，在透视表中右键"滨海市第一中学"→移动→移至最前（确保透视表学校顺序与按班级汇总表的预填顺序一致，否则后续复制粘贴会对错行）；再将 "班号" 拖入【行标签】（在 "学校名称" 下方）；将 "物理" 重复拖四次到【值标签】。

调整布局：点击透视表【设计】→【报表布局】选 "以表格形式显示"→【分类汇总】选 "不显示分类汇总"→【报表布局】选 "重复所有项目标签"（这一步不能省：否则学校名称只在每个学校的第一行显示，复制后 A 列出现空值，后续 SUMIFS 按学校匹配会失败）。

修改汇总方式：右键四个 "求和项：物理" 进入【值字段设置】，分别改为：计数、最大值、最小值、平均值。

提取班级数据：选中透视表四列数据（不要选中最后一行总计），复制，到按班级汇总表 C2 位置，右键【选择性粘贴】→【数值】。

提取学校数据：复制刚才的透视表到旁边空白处，把新表【行标签】里的 "班号"直接拖拽出去（移除）。此时的表即变为学校级统计。同样复制数值（不选总计行），粘贴到按学校汇总表 B2 处。

【操作 2】提取客观、主观题平均分（不除人数）

操作：在按班级汇总的G2单元格，输入：=SUM(小题得分情况!C2:Q2)(C到Q共15列，正好是1-15题)

操作：在H2单元格，输入：=SUM(小题得分情况!R2:V2)(R到V共5列，是16-20题)

操作：选中 G2、H2，双击右下角十字柄，向下填充。

操作：在按班级汇总的I1输入 "客观题总分"，J1输入 "主观题总分"。I2输入：=G2*C2J2输入：=H2*C2同样双击向下填充。

注意：题目备注明确说明，由于"小题得分情况"表中每题平均分已被系统四舍五入，客观题+主观题平均分之和可能与成绩单的总分平均分有微小差异，这是正常现象，无需调整。

【操作 3】汇总全校加权平均分

操作：在按学校汇总工作表中，F2输入：=SUMIFS(按班级汇总!I:I, 按班级汇总!A:A, 按学校汇总!A2)/B2（向下填充）G2输入：=SUMIFS(按班级汇总!J:J, 按班级汇总!A:A, 按学校汇总!A2)/B2（向下填充）

【操作 4】攻克 "每道小题的得分率"（最难部分）

前置：把按班级汇总中的 "考试学生数"，复制到小题得分情况的W列。

建表头：在小题得分情况的X1至AQ1依次输入题号1到20。

•班级单题总分：在X2输入：=C2*$W2。

重点解析：$W2是列绝对引用，横拉时永远乘以 W 列的人数；下拖时更新为不同班级。

将 X2 向右拖拽至 AQ2，再双击向下填充。

校级单题得分率：回到按学校汇总表，在H2输入：=SUMIFS(小题得分情况!X:X, 小题得分情况!$A:$A, 按学校汇总!$A2)/$B2/分值表!B$3(将H2向右、向下拖拽，覆盖所有小题和学校)

•混合引用解析（核心考点）：公式中三处引用方向各不相同——$A2锁列不锁行（向下拖时学校列固定、行号递增）；$B2锁列不锁行（全校总人数列固定）；分值表!B$3锁行不锁列——向右拖动时列号自动变化（B→C→D…），依次取第 1、2、3… 题的分值，向下拖动时行号固定为第 3 行（不同学校的同一道题满分相同）。锁错任何一个方向，拖拽后数据都会全面出错。

【操作 5】规范格式与转置数据

格式设置（严格对照题目要求，分表设置）：

按学校汇总表：选中B2:D5（人数、最高分、最低分）→【数值】→小数位数0；选中E:G列（平均分、客观题 / 主观题均分）→【数值】→小数位数2；选中H:AA列（20 道题得分率）→【百分比】→小数位数2。

按班级汇总表：选中C:E列（人数、最高分、最低分）→【数值】→小数位数0；选中F:J列（平均分、客观题 / 主观题均分及辅助列）→【数值】→小数位数2。

转置操作：新建工作表 "按学校汇总 2"；选中按学校汇总的数据区域（A1 至最后一列最后一行，不要整表全选），复制；切换到新表 A1 单元格，右键→【选择性粘贴】→同时勾选【数值】和【转置】（切记：不选 "数值" 会把跨表公式一起转置，导致引用方向错乱、计算结果静默出错；不选 "转置" 则无法实现行列互换）。

补全新表百分比格式：由于上述转置只粘贴了数值，需选中新表中的得分率区域B8:E27，右键→【设置单元格格式】→【百分比】→小数位数2，与源表显示保持一致。

套表与预警：选中新表数据区域，点击【开始】→【套用表格格式】→选择"紫色-表样式中等深浅12"。然后选中得分率区域B8:E27，使用【条件格式】→【突出显示单元格规则】→【小于】，输入0.8，设置为 "浅红填充色深红色文本"；再用【介于】，输入80%到90%，设置为 "黄填充色深黄色文本"。

保存文件：全部操作完成后，按Ctrl+S保存文件。