Excel 2019匹配指定值时统计对应列唯一ID数量的公式求助

适用场景 #

• 满足C列（Code 2）匹配A列指定单元格时，统计B列（ID Number）唯一值需求
• 完全兼容Microsoft Home and Student 2019版本
• 支持跨工作表运算，适配20万行级数据量

具体公式 #

单工作表场景（以E2单元格匹配A2值为例） #

=SUMPRODUCT(--(C:C=A2),1/COUNTIFS(B:B,B:B,C:C,A2))

跨工作表场景（数据存放在名为「业务数据表」的工作表为例） #

=SUMPRODUCT(--('业务数据表'!C:C=A2),1/COUNTIFS('业务数据表'!B:B,'业务数据表'!B:B,'业务数据表'!C:C,A2))

性能优化建议（针对20万行大数据量） #

• 不要使用整列引用，将公式中的列范围替换为实际数据行范围，例如数据到200000行就写为C2:C200000、B2:B200000，运算效率可提升50%以上
• 若涉及多个工作表统计，优先按单工作表分别计算再求和，避免多表合并的数组运算导致卡顿

公式逻辑说明 #

• --(C:C=A2)：将所有C列等于A2值的行标记为1，其余为0
• 1/COUNTIFS(B:B,B:B,C:C,A2)：给重复出现的同一个ID分配权重，例如某个ID出现3次则单次权重为1/3，求和后刚好计为1个唯一值
• SUMPRODUCT将两部分结果相乘后求和，最终得到匹配Code的唯一ID总数

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