排考场、录成绩、算达标率、填监考表……教务的日常，几乎一半时间都在和表格较劲。其实有 6 个函数，就能把最费时的几类活儿一键搞定。今天用教务场景把它们讲透：VLOOKUP、XLOOKUP、COUNTIFS、LEFT、RIGHT、MID。

文中的例子都基于下面这张表（表名假设为"成绩"）：

一、查找类：VLOOKUP 与 XLOOKUP

1. VLOOKUP —— 最经典的"照名单取值"

作用：拿着一个值，去另一张表里"对号入座"，把同行某个字段取回来。语法：VLOOKUP(查找值, 查找区域, 返回第几列, 0)

教务场景：竞赛名单上只有学号，需要从成绩表把姓名、数学分数带过来。

姓名：=VLOOKUP($A2, 成绩!$A:$F, 2, 0) 数学：=VLOOKUP($A2, 成绩!$A:$F, 5, 0)

避坑点

• 查找值必须在区域第一列（学号在 A 列，没问题）。
• 第 4 参数一定写 0（或 FALSE）表示精确匹配；省略会变成"近似匹配"，分数会张冠李戴。
• "返回第几列"是在你选的区域里数，不是整张表的列号。
• 区域要用 $ 锁死，否则下拉填充立刻错位。查不到会显示 #N/A，用 IFERROR 包一层更体面：

=IFERROR(VLOOKUP($A2, 成绩!$A:$F, 2, 0), "未参加")

2. XLOOKUP —— 新版"六边形战士"

作用：VLOOKUP 的全面升级版。语法：XLOOKUP(查找值, 查找列, 返回列, "找不到时显示什么")

=XLOOKUP($A2, 成绩!$A:$A, 成绩!$E:$E, "未参加")

它比 VLOOKUP 强在哪？

• 不用数第几列
  ，直接指定"返回哪一列"；
• 可以往左查
  ——比如按姓名反查学号：

=XLOOKUP("张伟", 成绩!$B:$B, 成绩!$A:$A, "查无此人")

• 天生可以设"找不到的默认值"，不用再套 IFERROR。

唯一的坑：XLOOKUP 只在 Microsoft 365 / Excel 2021 及以上、以及较新版本 WPS 里才有。在旧版电脑上打开会变成 #NAME?。发给别人（尤其是其他老师、上级）之前，先确认对方版本；要稳妥，就继续用 VLOOKUP。

二、统计类：COUNTIFS —— 多条件一键计数

作用：按多个条件数个数。语法：COUNTIFS(区域1, 条件1, 区域2, 条件2, …)

教务场景：统计各班各分数段人数、及格率、缺考人数，是教务最常用的统计件。

九(3)班数学及格人数： =COUNTIFS(成绩!$C:$C,"九(3)班", 成绩!$E:$E,">=60")  九(3)班数学优秀(≥90)人数： =COUNTIFS(成绩!$C:$C,"九(3)班", 成绩!$E:$E,">=90")  九(3)班数学 80~89 分人数： =COUNTIFS(成绩!$C:$C,"九(3)班", 成绩!$E:$E,">=80", 成绩!$E:$E,"<90")  缺考人数： =COUNTIFS(成绩!$C:$C,"九(3)班", 成绩!$E:$E,"缺考")

避坑点

• 文本条件要加英文双引号；带比较符号的整串加引号，如 ">=60"。
• 每个条件的区域行数必须一致，否则结果忽大忽小。
• 空格不会被计入；数"非空"用 "<>"。
• 班级写法务必统一：九3班
  、九(3)班、九（3）班 在 Excel 眼里是三个班，一不留神就漏统计。

顺手记：同一家族还有 SUMIFS（多条件求和）、AVERAGEIFS（多条件平均），公式结构完全一样，用来算"各班总分求和""各科平均分"极方便。

三、文本类：LEFT / RIGHT / MID —— 从学号里"拆"出信息

规范编码的学号，本身就是一个信息包。假设学号 20230312 的规则是：前 4 位入学年 + 中间 2 位班级 + 末 2 位班内序号。

教务实战：

一步生成班级名： ="九("&VALUE(MID(A2,5,2))&")班"        → 九(3)班  从身份证号提取出生日期： =MID(B2,7,8)                          → 19900815 =TEXT(MID(B2,7,8),"0-00-00")          → 1990-08-15  提取姓氏（点名、做桌牌）： =LEFT(C2,1)

避坑点

• 学号一定要存成"文本"！
  不然 0305 会被吞成 305，前导 0 直接丢。导入数据时选"文本"，或先把这一列设为文本格式。
• MID 的位置从 1 开始数。
• 区分 LEN 和 LENB：LEN 把每个中文算 1 个字符，LENB 算 2 个——判断姓名长度用 LEN 更符合直觉。
• 复姓（欧阳、司马等）直接用 LEFT(C2,1) 会拆错，需要额外判断。

四、组合技：一个流程走完"并表—统计—拆号"

把三组函数串起来，就是教务做总表的标准动作：

1. 建总表：
   用 XLOOKUP（或 VLOOKUP）把各科老师发来的分数、班主任发来的名单，按学号一一归位；
2. 出统计：
   用 COUNTIFS 一张表算出各班、各科的及格 / 优秀 / 缺考人数；
3. 补信息：
   用 LEFT / MID 从学号里批量生成班级、考场、座号。

三步下来，过去大半天的活，可能十几分钟就完事。

写在最后

这 6 个函数当然不能解决所有问题，但它们覆盖了教务最高频的三件事：查、统、拆。

不过还有一句掏心窝的话：函数只是提速的一半，规范才是根本。学号统一格式、班级统一写法、一列只放一类信息——数据规范了，函数才带得动。与其每次在乱表里救火，不如先把模板立起来。

愿每个教务人，都能少加一会儿班。