求适用于Excel 2016的按前缀分类去重计数公式

核心思路 #

Excel 2016没有原生的UNIQUE函数，所以用数组公式结合SUM、COUNTIF和文本筛选功能，实现按前缀对重复值去重后计数。

具体公式用法 #

假设你要在summary表的B2单元格统计pc前缀的唯一值数量，输入以下公式后，必须按Ctrl+Shift+Enter组合键确认（这是Excel 2016数组公式的要求）：

=SUM(IF(LEFT(data!$A$2:$A$1000,2)="pc",1/COUNTIF(data!$A$2:$A$1000,data!$A$2:$A$1000),0))

公式拆解 #

• LEFT(data!$A$2:$A$1000,2)="pc"：筛选出data表A列中前2个字符是pc的所有单元格
• 1/COUNTIF(data!$A$2:$A$1000,data!$A$2:$A$1000)：对每个重复值，重复n次就生成n个1/n，求和后每个唯一值刚好贡献1
• SUM把所有符合条件的唯一值的贡献加起来，得到最终的去重计数结果

适配其他前缀 #

• 统计nb前缀：把公式里的"pc"改成"nb"即可
• 统计vc前缀：同理替换为"vc"
• 如果前缀长度不是2位（比如前缀是3个字符），调整LEFT函数的第二个参数为对应长度

关键注意事项 #

• 把公式里的data!$A$2:$A$1000替换成你实际的数据范围，尽量覆盖所有可能的行，避免漏掉每周更新的新数据
• 一定要按Ctrl+Shift+Enter输入数组公式，不然只会计算第一个单元格的结果
• 数据中的空单元格会被自动忽略，不影响计数结果

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