Excel单元格逗号分隔混合编号精确计数求和公式求解

业务场景说明 #

• 一列维护食品条目清单，另一列存储对应食品的全部分类标签，分类包含含肉、含鱼、冷食、热食、含水等维度
• 每个分类对应1-15区间内的唯一指定编号
• 单元格内分类编号为无序混合排列，统一采用#,#格式存储，编号与逗号之间无空格
• 需实现两项功能：
  • 统计某指定编号在目标列中出现的总次数
  • 汇总所有指定编号的出现次数，输出总计值

旧公式的匹配误差问题 #

此前使用的统计公式：

=COUNTIF($C$3:$C$10,"*" & G3 & "*")+COUNTIF($C$3:$C$10,G3)

存在逻辑漏洞：统计编号1这类短数字的出现次数时，会将单元格内存储的15、11这类包含数字1的其他编号误判为匹配，最终计数结果偏大失真。

误匹配问题示例：

准确统计公式 #

单个指定编号出现次数统计 #

以统计C3:C10单元格范围内，G3单元格存储的指定编号的出现次数为例，使用以下公式即可实现精确匹配，不会出现子串误判：

=SUMPRODUCT(--ISNUMBER(SEARCH(","&G3&",",","&$C$3:$C$10&",")))

公式逻辑：

• 给目标列每个单元格的编号串前后统一补逗号，把所有编号包裹在两个逗号之间，例如原单元格内容1,11,15会被处理为,1,11,15,
• 搜索时同样给目标编号前后补逗号，例如查找编号1就匹配,1,字符串，不会误匹配到,11,、,15,中包含的数字1
• 最终统计匹配成功的单元格数量，即为该编号的准确出现次数

多编号出现次数总计 #

如果需要统计多个指定编号的累计出现次数，例如统计G3:G7范围内所有编号在C3:C10中的总出现次数，使用以下公式：

=SUMPRODUCT(--ISNUMBER(SEARCH(","&TRANSPOSE(G3:G7)&",",","&$C$3:$C$10&",")))

若使用的Excel版本不支持自动数组溢出，输入公式后按Ctrl+Shift+Enter三键结束，即可触发数组公式计算得到正确结果。

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