如何在Excel中按单列类别统计多列分数中指定数值的出现次数？

需求：按类别统计指定分数在所有Score列中的总出现次数，例如上述表格中类别4对应的Score列里，分数4共出现4次。此前尝试用=COUNTIFs(A1:A5,"4",B1:E5,"2")，但因COUNTIFS无法直接跨多列匹配，返回#VALUE错误。

解决方案 #

方法1：SUMPRODUCT函数（兼容全版本Excel）

公式格式：=SUMPRODUCT((A$2:A$5=目标类别)*(B$2:E$5=目标分数))
示例：统计类别4中分数4的出现次数，输入：

=SUMPRODUCT((A$2:A$5=4)*(B$2:E$5=4))

原理：两个条件分别生成布尔数组（符合条件为1，否则为0），相乘后仅同时满足两个条件的位置保留1，SUMPRODUCT对结果求和得到总次数。

方法2：COUNTIFS+数组（Excel 365/2021及以上版本）

通过数组拆分多列范围，配合SUM求和：

=SUM(COUNTIFS(A$2:A$5,4,CHOOSE({1,2,3,4},B$2:B$5,C$2:C$5,D$2:D$5,E$2:E$5),4))

动态数组版本直接回车即可，旧版需按Ctrl+Shift+Enter触发数组计算。

方法3：Power Query批量统计

1. 选中数据区域，点击「数据」→「从表格/区域」导入Power Query；
2. 选中所有Score列（B-E），点击「转换」→「逆透视列」→「逆透视其他列」，将多列Score转为「属性」「值」两列；
3. 添加「分组依据」：分组列选「Category」和「值」，操作选「计数行」，新列名设为「出现次数」；
4. 关闭并上载到Excel，即可得到所有类别-分数组合的统计结果。

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