Excel中如何用TEXTSPLIT与COUNTIF统计逗号分隔代码的出现次数？

问题背景 #

我有一份数据，每行记录人名和其使用的逗号分隔代码，示例数据如下：

需要统计指定代码范围内每个代码的出现次数，预期结果如下：

之前在谷歌表格中用这个公式实现：

=IFERROR(SUM(ARRAYFORMULA(IFERROR(IF({SPLIT($E$7:$E$75,",")}=$O9,+1,+0),+0))),0)

现在切换到Excel，不知道怎么实现，求解决方案。

解决方案 #

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

如果你的Excel版本支持TEXTSPLIT、TOCOL函数，用以下公式即可快速实现：

假设代码数据列在B2:B7，待统计的目标代码在D2（如示例中的"A"），公式：

=COUNTIF(TOCOL(TEXTSPLIT(B2:B7, ","), 1), D2)

• TEXTSPLIT(B2:B7, ",")：将每个单元格内的逗号分隔代码拆分为数组
• TOCOL(...,1)：把拆分后的多维数组转为单列，同时忽略空值
• COUNTIF：统计目标代码在该单列数组中的出现次数

若要一次性生成所有代码的统计结果，直接将目标代码列作为COUNTIF的第二个参数，输入后按回车即可自动溢出结果：

=COUNTIF(TOCOL(TEXTSPLIT(B2:B7, ","), 1), D2:D12)

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

如果是不支持动态数组的旧版本，使用以下数组公式（输入后按Ctrl+Shift+Enter确认）：

假设目标代码在D2，数据列是B2:B7：

=SUM(IFERROR(--(TRIM(MID(SUBSTITUTE(B2:B7, ",", REPT(" ", 100)), (ROW(INDIRECT("1:"&LEN(B2:B7)-LEN(SUBSTITUTE(B2:B7, ",", ""))+1))-1)*100+1, 100))=D2), 0))

• SUBSTITUTE(..., REPT(" ", 100))：将逗号替换为长空格，方便按位置截取代码
• MID+TRIM：逐个提取并清理每个代码内容
• --(...)：将代码匹配结果转为1或0的数值
• SUM(IFERROR(...))：对匹配结果求和，同时忽略拆分过程中的错误值

关于未出现的代码 #

两种方案在遇到未在数据中出现的代码时，都会自动返回0，符合预期统计需求。

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