求可查找子串并统计唯一值的Excel公式

问题分析 #

你需要统计B列中包含子串“Pink”的唯一值数量，实际数据超38000行，手动统计结果约1094，但现有公式出现#VALUE!错误或结果偏差（比如得到100）。你的原有公式问题出在匹配逻辑和数据范围引用上：

• SEARCH("_Pink_",B:B)硬要求子串前后带有下划线，缩小了匹配范围，导致大量符合条件的数据被遗漏
• SEARCH("*_Pink_*",B:B)多此一举添加通配符，SEARCH本身支持部分匹配，额外的通配符会干扰匹配逻辑
• 整列引用B:B包含大量空单元格，不仅会增加计算负荷，还可能让UNIQUE把空值计入统计，导致结果异常

正确公式推荐 #

以下公式适用于Excel 365/2021及以上版本（支持动态数组）：

基础版（不区分大小写）

=COUNTA(UNIQUE(FILTER(B:B,ISNUMBER(SEARCH("Pink",B:B))*(B:B<>""))))

区分大小写版

如果需要严格区分大小写，将SEARCH替换为FIND：

=COUNTA(UNIQUE(FILTER(B:B,ISNUMBER(FIND("Pink",B:B))*(B:B<>""))))

大数据量优化版

针对38000+行的大数据，建议使用实际数据范围而非整列引用，减少计算压力避免溢出：

=COUNTA(UNIQUE(FILTER(B2:B38000,ISNUMBER(SEARCH("Pink",B2:B38000))*(B2:B38000<>""))))

公式逻辑说明 #

1. SEARCH("Pink", 范围)：在目标单元格中查找“Pink”子串，不区分大小写，找到返回位置，未找到返回错误值
2. ISNUMBER(...)：将查找结果转换为布尔值（找到为TRUE，未找到为FALSE）
3. *(B:B<>"")：排除空单元格，避免空值被纳入筛选范围
4. FILTER：筛选出所有符合条件的单元格
5. UNIQUE：对筛选结果去重，得到唯一值集合
6. COUNTA：统计去重后非空值的数量，即最终所需结果

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