Excel：如何统计列中不在指定列表内的值的数量

针对表单里员工名字拼写错误（不在65人正确列表）的统计需求，这里给你几个实用的Excel操作方法：

方法1：统计所有错误名字的总次数 #

假设：

• 员工填写的名字列是A2:A1000（跳过表头A1）
• 65人的正确员工列表是D2:D66（跳过表头D1）

公式1：用SUMPRODUCT+MATCH组合 #

=SUMPRODUCT(--(ISNA(MATCH(A2:A1000,D2:D66,0))))

• 逻辑：MATCH会查找A列每个名字在正确列表中的位置，找不到（拼写错误）就返回#N/A；ISNA把这些结果转成TRUE/FALSE；--把布尔值转成1（错误）/0（正确）；最后SUMPRODUCT求和得到总错误次数。

公式2：用总数减去正确名字的数量 #

=COUNTA(A2:A1000)-SUMPRODUCT(COUNTIF(A2:A1000,D2:D66))

• 逻辑：COUNTA统计A列所有非空名字的总数；SUMPRODUCT(COUNTIF(...))统计A列中属于正确列表的名字总数量；两者相减就是错误名字的总次数。

方法2：统计每个错误名字的单独出现次数 #

如果需要知道每个拼写错误的名字分别出现了几次，分两种情况操作：

适用于Excel 365/2021（支持动态数组） #

1. 先提取所有不在正确列表的唯一错误名字，比如在F2单元格输入：

=UNIQUE(FILTER(A2:A1000,ISNA(MATCH(A2:A1000,D2:D66,0))))

2. 在旁边G2单元格输入计数公式，下拉填充：

=COUNTIF(A2:A1000,F2)

适用于旧版Excel（不支持动态数组） #

1. 给A列添加表头（比如「填写名字」），选中A列点击「数据」选项卡→「高级」筛选，勾选「将筛选结果复制到其他位置」，列表区域选A列，复制到空白列（比如F列），勾选「选择不重复的记录」，确定后得到所有唯一名字
2. 在G列添加公式=IF(ISNA(MATCH(F2,D:D,0)),COUNTIF(A:A,F2),"")，下拉后仅显示错误名字的计数结果

方法3：用数据透视表快速统计 #

1. 新增辅助列B，表头设为「是否错误」，B2单元格输入公式：

=ISNA(MATCH(A2,D:D,0))

下拉填充后，错误名字对应TRUE，正确名字对应FALSE
2. 选中A列和B列，点击「插入」选项卡→「数据透视表」
3. 在透视表字段设置：

• 把「填写名字」拖到「行」区域
• 把「是否错误」拖到「筛选」区域，筛选出TRUE
• 把「填写名字」拖到「值」区域，设置值汇总方式为「计数」

4. 最终透视表会清晰显示每个错误名字的出现次数

注意事项 #

名字的空格、大小写会影响匹配结果，比如「张三」和「张三 」（末尾有空格）会被判定为不同名字。可以先统一格式：

• 处理填写列：=TRIM(UPPER(A2))（去除空格+转大写），放在C列
• 处理正确列表：=TRIM(UPPER(D2))，放在E列
• 之后的公式都用C列和E列来匹配，避免误判

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