体测汇总熬两晚 → 1 小时：我搭了一条 WPS「数据流水线」（3 个公式直接抄）

上周三下午最后一节课，操场边的沙坑旁。我蹲着给三年级补测坐位体前屈，风把记录表吹跑了两张，追回来的时候顺序已经乱了。

这不是第一次。

作为一个小学体育老师，期末最怕的其实不是上课，是"汇总"。体质健康测试要报的东西摊开来是这样：身高、体重、肺活量、50 米跑、坐位体前屈、一分钟跳绳、一分钟仰卧起坐——7 个项目，6 个班，264 个孩子。

而这些数据，从来不在一张表里。

▍我的数据，散在四个地方

① 纸质记录表：课上随手记的，字迹只有我自己看得懂，风一吹就乱序

② 手机相册：补测、复测拍的照片，37 张，名字和序号对不上

③ 班主任发来的 Excel：身高体重是校医室体检时给的，一个班一个文件

④ 仪器导出的文件：肺活量仪导出 CSV，身高体重仪导出 PDF，学号一会儿是 20230312，一会儿是 312

以前我是这么干的：开四个窗口，复制、粘贴、VLOOKUP，对不上就肉眼找。去年最惨的一次，VLOOKUP 少锁了一个 $，256 行里有 40 行静默错位，第二天上报前才发现，全部推翻重来。两个晚上，干到凌晨一点半。

今年换了个思路，一句话概括：先把"人"对齐，再让数据自己归位。人 = 学号 + 姓名，这是唯一的锚点，其他所有列都只是往这个骨架上挂。

下面是我实际跑的 5 步。

▍第一步：把"纸"变成"表"（会员功能在这里救场）

这一步以前最耗时间，今年基本是白捡的。

· 手机拍的手写记录表照片 → 图片转表格，整张表按原结构还原成可编辑的表格

· 仪器导出的 PDF → PDF 转 Excel，直接拿到结构化数据

· 各班的 Word/Excel 名单 → 统一转成同一套表头

但转完千万别急着合并。先检查三件事，这三件事决定了后面会不会崩：

1. 表头有没有被识别成两行（有合并单元格的纸质表最容易出这问题）

2. 学号有没有变成科学计数法（20230312 会变成 2.02E+07）

3. 数字有没有被当成文本（单元格左上角出现绿色小三角）

学号列先设成文本格式再粘贴。这一步偷懒，后面 XLOOKUP 必定一片 #N/A。

▍第二步：合并——别再复制粘贴了

数据 → 合并表格 → 多个工作表合并成一个工作表。

因为 6 个班用的是同一个模板，这一步能一次全部并进来，不用一个一个复制。

一个坑：如果各表第一行是大标题（"三年级一班体质健康测试记录表"），要先删掉那一行，只留真正的表头，否则第一行数据会被当成表头吃掉——264 人会莫名少 6 个。

▍第三步：把人对齐（整条流水线的核心）

主表 = 学籍名单（学号、姓名、性别、班级），这是骨架。顺序很关键：从名单出发去挂数据，而不是从数据表出发去找人。

然后用 XLOOKUP 把每一项挂上去。这里有两个必须处理的情况：

① 重名。264 人里有 2 对重名。我用 =B2&C2（学号&姓名）做复合键，比单纯用姓名稳得多。

② 转学生和漏测。名单里有、体测表里没有的孩子，直接匹配会返回 #N/A，一填充下拉就变成 0 分——而 0 分会老老实实被算成"不及格"，把班级平均分拉下去。

所以一定要套一层：

=IFERROR(XLOOKUP($A2,肺活量!$A:$A,肺活量!$C:$C),"未测")

"未测"和"0 分"是两件事，混在一起，最后的数据就没法看了。

▍第四步：三个公式，直接抄

① BMI（注意单位）

=ROUND(F2/(E2/100)^2,1)

身高那列是厘米，必须除以 100 换成米。我第一次忘了，全班 BMI 算出来全是 0。

② 等级评定

=IFS(M2>=90,"优秀",M2>=80,"良好",M2>=60,"及格",TRUE,"不及格")

90 及以上优秀、80 及以上良好、60 及以上及格，这是《国家学生体质健康标准》的口径，别自己拍脑袋改。注意 IFS 要按从高到低写，顺序错了全盘皆错。

③ 缺测标记（省掉最后一遍人工核对）

=IF(OR(F2="",H2="",I2=""),"待补测","")

这一列填完，筛一下"待补测"，要补测的名单直接出来了，不用再翻 264 行。

▍第五步：让看表的人 30 秒看懂

数据算对只是及格，让领导一眼看出问题才是加分项。我做了四件事：

· 条件格式：总分 <60 整行浅红、≥90 浅绿；BMI 超出正常范围的标橙。一眼扫过去，哪几个孩子要重点关注，不用看数字

· 冻结窗格：视图 → 冻结窗格，冻结首行 + 前 4 列。264 行往下翻，永远知道自己在看谁

· 数据透视表：行放班级，值放平均分、优良率、不及格人数、未测人数。6 个班横向一比，谁弱一目了然

· 图表：各班平均分柱形图放在汇总页顶部；再挑一个班做单项雷达图，这个班到底是速度弱还是力量弱，图形比表格诚实

打印的时候记得页面布局 → 打印标题 → 顶端标题行选第一行，这样 264 行打出来，每一页都有表头，不会出现"翻到第三页不知道这列是什么"的情况。

▍四个我踩过的坑，帮你省两个晚上

1. 学号变科学计数法——先设文本格式，或者粘贴时选"只保留文本"

2. 数字带单位——仪器导出的常是 1850 ml、9.2秒，用 Ctrl+H 把单位替换成空，再转数值

3. "未测"被当成 0 分——见上面第 ③ 个公式，这是最容易被忽略、也最影响结论的一处

4. 粘贴把格式和公式一起带过去——一律右键"选择性粘贴 → 数值"

▍最后，说结果

· 时间：两个晚上 → 一个半小时（含最后核对）

· 上报：今年一次过，没有被退回重报

· 复用：这张表存成了模板放在云文档里，明年只需要"换数据 → 刷新透视表"，前面的结构一行都不用改

对我来说，这套东西的价值不是省了几个小时，而是把"对数据"这件事从"靠眼睛"变成了"靠结构"。眼睛会累，结构不会。

最后想请教一下同行：你们学校的体测数据是手写记录还是仪器直接导出？我这边肺活量仪导出的学号永远少一位，每年都要手动补一遍，一直没找到彻底解决的办法。评论区聊聊，如果有更好的处理方式，我把它补进模板里，整理好放在评论区置顶。

（模板已整理：学籍名单主表 + 各项数据挂载页 + 透视表 + 条件格式，直接换数据就能用，需要的老师评论区留个言。）