如何用ArrayFormula+COUNTIFS实现跨Sheet成绩统计匹配

嘿，这个需求我经常碰到，用ArrayFormula结合COUNTIFS就能完美解决，不用手动下拉或复制公式，一次性搞定所有统计！

先理清楚需求逻辑 #

Sheet1存的是学生的成绩数据，Sheet2是讲师对应的LEC.ID，我们要给每个讲师统计不同成绩等级的学生人数——说白了就是同时匹配LEC.ID和成绩等级两个条件，统计符合条件的学生数量。

分步实现方案 #

1. 先设置成绩等级表头 #

在Sheet2的C1、D1、E1…这些单元格里，输入你要统计的成绩等级，比如"A"、"B"、"C"、"不及格"，确保和Sheet1里的成绩格式完全一致（比如都是文本或都是数值）。

2. 基础版：单列公式自动填充整行 #

在Sheet2的C2单元格（也就是第一个成绩等级列的第一个统计单元格）输入下面的公式：

=ArrayFormula(COUNTIFS(Sheet1!$B:$B, $B:$B, Sheet1!$C:$C, C$1))

输入完成后，这个公式会自动填充整个C列，统计每一行LEC.ID对应的该成绩等级人数。

接着你只需要把这个公式横向拖动到D2、E2…其他成绩等级列，所有列的统计就都搞定了！

公式拆解一下

• Sheet1!$B:$B：锁定Sheet1的LEC.ID列，横向拖动公式时不会乱跑
• $B:$B：锁定Sheet2的LEC.ID列，确保始终用当前行的讲师ID做匹配
• Sheet1!$C:$C：锁定Sheet1的成绩列，保证统计的是正确的成绩数据
• C$1：锁定当前列的成绩等级表头，纵向拖动时始终用表头的等级做条件
• ArrayFormula：让公式自动应用到B列所有有数据的行，不用逐行手动输入

3. 进阶版：一个公式搞定所有列（不用拖动） #

如果你想更高效，只用一个公式生成所有成绩等级的统计，可以用BYCOL函数配合（适合Google Sheets或Excel 365）。假设你的成绩等级表头在C1:E1，直接在C2输入：

=ArrayFormula(BYCOL(C$1:E$1, LAMBDA(grade, COUNTIFS(Sheet1!$B:$B, $B:$B, Sheet1!$C:$C, grade))))

这个公式会自动遍历C1到E1的所有成绩等级，一次性生成所有列的统计数据，连拖动都省了！

避坑提醒 #

• 格式要统一：Sheet1的成绩和Sheet2的等级表头必须是同一种格式，比如都是文本"A"，不能一个是文本一个是数值90，否则会匹配失败
• 避免空白ID：如果Sheet2的B列有空白行，公式会统计Sheet1中所有LEC.ID为空的学生，建议给B列加个数据验证，不让空值出现

内容的提问来源于stack exchange，提问作者papacom