lookup+frequency+countif组合提取不重复值

你好,我是刘卓。欢迎来到我的公号,excel函数解析。今天接着昨天的文章,继续分享有条件的提取不重复值的另一种方法,用的是lookup+frequency+countif的组合。
还是昨天的案例,当指定商品后,需要提取出其对应的不重复品牌,效果如动图所示。F3设置了数据验证,G列是不重复品牌的结果。
在G3单元格输入下面的公式,不用三键,直接向下填充。

=LOOKUP(,0/FREQUENCY(1,(A$3:A$18=F$3)-COUNTIF(G$2:G2,B$3:B$18)),B$3:B$18)&""

这个公式用的是countif动态区域去重法,结合frequency函数,可以不用按三键。而且公式长度也大大缩短。
因为这个公式确实有难度,我对它的理解也不是很深刻,而且countif的区域会动态变化,所以不好讲解,我只说一下它大概的思路,步骤就不再演示了,想学的同学可以自己拆解公式。
我还是以“空调”这个商品为例说明,对于这个问题,首先我们要找出满足条件的记录。那就是商品是“空调”,且对应的品牌没有在G列的结果中出现过。
A$3:A$18=F$3这部分是判断A列的商品是否为"空调",结果会有两种情况:是(true)或者不是(false)。
COUNTIF(G$2:G2,B$3:B$18)这部分判断B列的品牌有没有在G列的结果区域中出现过,结果也会有两种情况:出现过的返回1,没有出现过的返回0。
(A$3:A$18=F$3)-COUNTIF(G$2:G2,B$3:B$18)这部分让二者相减,会产生4种情况:
等于“空调”的减去品牌在G列出现过的,结果为0;等于“空调”的减去品牌未在G列出现过的,结果为1;不等于“空调”的减去品牌在G列出现过的,结果为-1;不等于“空调”的减去品牌未在G列出现过的,结果为1,如下图所示。

最终,两部分相减的结果是由-1、0、1这3个数组成的数组,而满足条件的记录对应的数字是1,所以FREQUENCY(1,(A$3:A$18=F$3)-COUNTIF(G$2:G2,B$3:B$18))这部分用frequency定位第一个1的位置,最后用lookup返回该位置对应的品牌。

如果用减法理解不了,那么用乘法会相对容易一些,在H3单元格输入下面的公式,向下填充。

=LOOKUP(,0/FREQUENCY(1,(COUNTIF(H$2:H2,B$3:B$18)=0)*(A$3:A$18=F$3)),B$3:B$18)&""

链接:

https://pan.baidu.com/s/1Ur_xqy9IjZFVGprb5QBnUA

提取码:kjpp
(0)

相关推荐