EXCEL: 查找符合多个条件，并且不重复的数据的个数的3种方法：公式，数据透视表，数组公式

1 目标问题：想筛选出(在a列月份为5)且不重复的b列数据有几个

有2个条件

• 查找第1列月份为5月的
• 并且第2列不重复的
• 数据个数

方法1：用加辅助列+简单公式的办法

其实逻辑是更清晰的，就是显得步骤繁琐

• 第1个辅助列1，查找日期中的月份，
• 具体用month() 等解决
• 第2个辅助列，查找B列不重复的数据，
• 具体用=IF(COUNTIF(B$5:B5,B5)=1,1,0) 来解决，如果重复就标记为0，不重复则标准为1，
• 这个技巧就是主义，让范围是逐步从前1个---变为前2个---变为前3个---等等

• 记住： 不是从全列查询，这样重复的就1次都无法记了，而是需要锁住列的上限，逐渐扩大列的范围

 

方法2：辅助列+数据透视表（有的版本解决不了重复值的问题）

• 仍然需要辅助列，月份
• 汇总值一般默认都是求和，但是这里不能用求和
• 需要调整汇总值，点击它会弹出菜单，选择计数而不是求和
• 有的版本有唯一计数，有的版本，数据透视表无法去重

   

方法3 用数组公式来解决

我自己改良的函数

• =SUMPRODUCT(1/(COUNTIF(B5:B18,B5:B18)*IF(VALUE(MID(A5:A18,6,1))=5,1,99999999)))
• 用数组公式，这个必须是数组公式
• 行列数的思想
• 看待数组
• 多个条件需要用乘法
• 月份5的条件要设计为999999，为是为了1/999999 等于极小数约等于0

1 网上大多数答案，都是查找本列的不重复个数

比如这种公式

=SUMPRODUCT(1/COUNTIF(B5:B18,B5:B18))

2 而复杂情况，可能是需要根据其他列的查，比如本文要解决的问题

=SUMPRODUCT(1/COUNTIF(B5:B18,B5:B18))

这个只返回了B列里不重复的数据个数，确实是10个

=SUMPRODUCT(1/(COUNTIF(B5:B18,B5:B18)*IF(VALUE(MID(A5:A18,6,1))=5,1,99999999)))

这个返回了B列里不重复的数据个数 且 月份=5的，个数是5个

不能用这个错误写法

=SUMPRODUCT(1/(COUNTIF(B5:B18,B5:B18)*IF(VALUE(MID(A5:A18,6,1))=5,1,99999999)))

=SUMPRODUCT(1/(COUNTIF(B5:B18,B5:B18)*IF(VALUE(MID(A5:A18,6,1))=5,1,0))) 这样就会报错，因为1/0报错

下面是详细过程，有兴趣的可以看

 

 

 

 

 

3 EXCEL365的公式？

• subtotal
• SUMPRODUCT(1/(COUNTIFS(A2:A10000,O2:O10000)))
• 我查了下我的非365版本里
• subtotal (参数，范围)
• 比如subtotal (1，范围) =average
• 比如subtotal (2，范围)= count
• 比如subtotal (9，范围) =sum
• 暂时不知道有什么用处