【原】frequency有条件的提取不重复值

刘卓学EXCEL 2021-04-02

展开全文

同学们，大家好。今天要分享的内容是有条件的提取不重复值。关于提取不重复值（也就是去重复），之前只说过单列的提取方法，今天加深一点难度，说说有条件的提取不重复值。

先来看下数据源和提取后的效果。如下图所示，AB两列是数据源，是两个店铺的水果销售记录。每个店铺的销售记录中，会有重复的水果。同时两个店铺销售的水果也会有相同的。现在要提取出每个店铺不重复的水果。效果如右边所示。D2单元格设置了数据验证，可以下拉选择。AB两列设置了条件格式，方便对比。

那这个问题该如何解决呢？我学会的第1种方法就是match=row。按这个思路来考虑的话，其实是先把AB两列同时去重复，也就是把A列和B列连接成1列，然后去重复。这样得到的数据就是不重复的，但是这些不重复的数据中既包含A店的，也包含B店的。所以还要满足一个条件，要么等于A店，要么等于B店。实际就是多列去重复再加一个条件。

在E2单元格输入公式=INDEX(B:B,SMALL(IF((MATCH(A$2:A$15&B$2:B$15,A$2:A$15&B$2:B$15,)=ROW($2:$15)-1)*(A$2:A$15=D$2),ROW($2:$15),4^8),ROW(A1)))&""，按ctrl+shift+enter三键结束，向下填充。

其中MATCH(A$2:A$15&B$2:B$15,A$2:A$15&B$2:B$15,)=ROW($2:$15)-1这部分的结果如下图C列所示，true对应的是第1次出现的，也就是不重复的，false对应的是重复出现的。比如下图红框的B店和西瓜第2次出现时，就是false。这个公式实际就是将2列连接成1列，然后去重复。

接下来，由于去重复后的数据既包含A店，又包含B店，所以我们还要加条件。比如现在我们要提取A店的，那么公式为=(MATCH(A$2:A$15&B$2:B$15,A$2:A$15&B$2:B$15,)=ROW($2:$15)-1)*(A$2:A$15=D$2)这部分，返回的结果如下图C列所示。1对应的就是A店不重复的水果。剩下的就是一对多查询的套路了，返回对应的行号，然后用small一一提取，用index返回对应的内容。

下面说一下frequency+lookup的套路。在F2单元格输入公式=LOOKUP(,0/FREQUENCY(1,(COUNTIF(F$1:F1,B$2:B$15)=0)*(A$2:A$15=D$2)),B$2:B3)&""，不用三键，向下填充。

这里关键的地方在于frequency，第1参数选1是有原因的，先说第2参数吧。在第2参数中，我们让条件成立的（也就是我们想要的）返回1，不成立的（我们不想要的）返回0，它肯定返回一个由0和1构成的数组。这样的话第1参数的1，就会在第2参数的第1个1的位置计数1。然后用lookup查找到对应的内容。

那怎么样让我们想要的返回1，不想要的返回0呢？那我们就要知道我们想要的是什么。首先肯定是A$2:A$15=D$2，也就是A列中的店铺等于D2的店铺。同时COUNTIF(F$1:F1,B$2:B$15)=0，也就是B列的水果在F1没有出现过的。合起来的话就是A列的店铺等于D2，且对应的水果在F1中没有出现，这2个条件同时满足的就是我们需要的。那么只需要将这两部分相乘就可以了。

公式为=(COUNTIF(F$1:F1,B$2:B$15)=0)*(A$2:A$15=D$2)，返回的结果如下图C列所示，等于1的都是A店的，且在F1中没有出现过的。由于是第一次提取，所以肯定都没有出现过。然后我们用frequency在第一个1的位置计数1，用lookup返回第1个1对应的梨子。这样就提取出第一个不重复的水果。我就说到这里，大家可以按F9查看公式运行结果，也可以像我这样分步查看运算结果。