如何基于COUNTIF统计唯一值？特定格式引用列场景

适用场景 #

针对格式为1-XXX-YYY的引用数据（XXX为3位字母，YYY为数字），需统计每个XXX对应的唯一完整引用数量（重复的完整引用仅计1次）。

通用Excel版本公式（兼容所有版本） #

假设目标XXX在单元格D2，数据存储在A:A列，使用以下公式：

=SUMPRODUCT((MID(A:A,3,3)=D2)/COUNTIF(A:A,A:A))

公式原理

• MID(A:A,3,3)=D2：提取A列每个单元格的XXX部分（从第3位开始取3个字符），判断是否与目标XXX匹配，生成逻辑数组
• COUNTIF(A:A,A:A)：计算每个完整引用在A列的出现次数，生成次数数组
• 用逻辑数组除以次数数组：重复N次的引用会被拆分为N个1/N，求和后等价于仅计1次
• SUMPRODUCT对结果求和，得到去重后的统计数

Excel 365/2021 简化公式（动态数组） #

如果使用支持动态数组的Excel版本，可使用更直观的公式：

=COUNTA(UNIQUE(FILTER(A:A,(MID(A:A,3,3)=D2)*(A:A<>""))))

公式原理

• FILTER(A:A,(MID(A:A,3,3)=D2)*(A:A<>""))：筛选出所有XXX匹配且非空的完整引用
• UNIQUE()：去除筛选结果中的重复值
• COUNTA()：统计去重后的引用数量

优化建议 #

• 尽量使用具体数据范围（如A2:A1000）替代全列A:A，提升公式计算效率
• 若需批量统计多个XXX，可将公式下拉应用到对应单元格

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