| | | | | | | | | | | | | | | |
问题1: | | | | | | | | | | | | | | |
| 计数要求,统计标记列满足条件X的项目不重复个数 | | | | | | | |
解题思路: | | | | | | | | | | | | | | |
| 对于还比较陌生多条件计数,但是又很想用这个来解决问题的,首先我们来看满足条件的项目到底有哪些: | | |
| 笔者很苦逼的自己点了一下: | | | | | | | | | | |
| 满足条件的项目: | | | | | | | | | | | | |
| 5 | 6 | 10 | 8 | 9 | a | 13 | 14 | 15 | | | | | | |
| 一共有:9个 | | | | | | | | | | | | | |
| 那如果用公式怎么做到把这项目数出来呢 | | | | | | | | | |
| | | | | | | | | | | | | | | |
1. | 首先我们来看我们熟知的条件计数函数:countifs/countif(=COUNTIFS(D6:D20,">"&D22)) | | | | |
| 那我们把公式写上,得到结果:11个 | | | | | | | | | |
| 很明显和我们自己点的出入很大,不过数据比较少,人工点出来的肯定正确,计算结果是错误的,原因很明显,计数把项目中重复的都算在内了 |
2. | 那这时候我们想到了:=sum(1/countif(C6:C20,C6:C20))的去重公式(sumproduct同理),这个公式的原理就是对每一个区域中的元素在区域中进行计数,重复计数的会累加个数,然后用倒数把累加的个数变为“1” |
| 接下来我们来看看结果:10个 | | | | | | | | | | |
| 然后有手工数了一遍,全部项目中确实是有10个不重复值 | | | | | | |
3. | 聪明的朋友肯定这时候想到了把上面2个公式都用上,看看结果呢?:=SUM(IFERROR(1/COUNTIFS(C6:C20,C6:C20,D6:D20,">"&D22),)) |
| 然后我们继续看看2个公式结合会变成什么样:11个 | | | | | | | |
| 很明显和答案9个还是不一样,大家瞬间觉得无爱了吗? | | | | | | | |
4. | 我们继续想难道是条件要乘在countifs外面?那就试试:=SUM(IFERROR(1/COUNTIFS(C6:C20,C6:C20)*(D6:D20>4),)) | | |
| 结果:8个 | | | | | | | | | | | | | |
5. | 怎么会变少了呢? | | | | | | | | | | | | |
| | 那我们把sum里面的数组放到数据源边上比对一下,发现 | | | | | | |
| | | 3.公式的第一个,第二个值对应的a,13不在大于4的范围内应该被算为0。 | | | | |
| | | 4.公式的第12,13个值对应的a,13在大于4的范围内都是第一次出现应该是1 | | | |
6. | 这样一来大家是不是灵光一闪?Bingo! | | | | | | | | | |
| 就是这么简单,我们只需要把2个公式结合一下:=SUM(IFERROR(1/COUNTIFS(C6:C20,C6:C20,D6:D20,">"&D22)*(D6:D20>D22),)) | |
| 结果:9个 | | | | | | | | | | | | |
| 另外还有很多公式同样可以得到正确结果,比如:=SUM((D6:D20>D22)*(ROW(1:15)=MATCH(C6:C20&(D6:D20>D22),C6:C20&(D6:D20>D22),))) |
| 结果:9个 | | | | | | | | | | | | |