筛选出不重复的记录(正序和逆序)
那么我们来看下数据源。如下图所示,A列是源数据,有北上广深4个城市,它们有重复的,现在要筛选出不重复的记录,结果如CD两列所示。这是筛选出第1次出现的城市,也是正向查询。
下图是筛选最后1次出现的记录,也就是逆向查询第1次的记录。我在数据源中都用条件格式标出了,方便大家查看。
我这里用的是2个工作表,第1个工作表放的是正向查询,第2个表是逆向查询。
先来看正向查询,在C2单元格输入公式=INDEX(A:A,SMALL(IF(FREQUENCY(ROW(A:A),MATCH(A$2:A$15,A$2:A$15,)),ROW($2:$16),4^8),ROW(A1)))&"",按ctrl+shift+enter三键结束,向下填充。
这里我用了frequency,因为这个函数实在是太强大了,能学会几个套路就感觉很厉害了。由于这里的城市是文本,所以直接用frequency还不行,先要将文本转为数字,那就用match函数。也就是MATCH(A$2:A$15,A$2:A$15,)这部分,查找每个城市的位置,由于用的是精确查找,match只返回第1次出现的位置,所以相同的城市返回相同的数字。
FREQUENCY(ROW(A:A),MATCH(A$2:A$15,A$2:A$15,))这部分其实和MATCH(A$2:A$15,A$2:A$15,)=ROW($2:$15)-1是差不多的意思,都是第1次出现的记录成立。也可以写为FREQUENCY(ROW(2:15)-1,MATCH(A2:A15,A2:A15,))或FREQUENCY(MATCH(A2:A15,A2:A15,),ROW(2:15)-1),用ROW(A:A)是为了简写一下,不要把ROW(A:A)放在FREQUENCY的第2参数。
剩下的就是万金油的用法了,index+small+if等,这里要注意的问题是frequency返回的结果要比第2参数的元素多1个,所以if返回对应的行号要多加1个,改为ROW($2:$16),否则会多一个错误值。这个就说到这里,其实和match=row的套路差不多。
下面看第2个公式,在D2单元格输入公式=LOOKUP(,0/FREQUENCY(1,--ISNA((MATCH(A$1:A$15,D$1:D1,)))),A:A)&"",向下填充。这个公式的好处是不用按三键结束。用的是lookup+frequency的套路,其实还要用到match或countif动态区域的用法。也就是我之前文章《查找不重复记录的几个套路(删除重复项)》的第3种套路。这个公式大家感兴趣自己研究一下吧。正序筛选不重复就说到这里。
接下来说下,筛选最后1次的记录,也就是逆向查询。在D2单元格输入公式=INDEX(A:A,SMALL(IF(MATCH(T(OFFSET(A$1,16-ROW($2:$15),)),T(OFFSET(A$1,16-ROW($2:$15),)),)=ROW($2:$15)-1,17-ROW($2:$15),4^8),ROW(A1)))&"",按三键结束,向下填充。
这个公式就是用的match=row的套路,只不过借用offset的多维引用将正序转为逆序,也就是将A2:A15转为A15,A14,A13......A2,当然最后是一个内存数组。这样的话用match查找,就是从下往上查找第1次的记录。由于查找区域的顺序颠倒了,所以返回的行号也要颠倒一下,由ROW($2:$15)变为17-ROW($2:$15)。剩下的就是small+index了。
在C2单元格输入另外一个公式=INDEX(A:A,SMALL(IF(FREQUENCY(ROW(A:A),MATCH(A$2:A$15,A$2:A$15,)+1/ROW($2:$15)),ROW($2:$16),4^8),ROW(A1)))&"",按三键结束,向下填充。这个公式还是万金油的套路,但是逆向查找不重复的时候用的是frequency函数。
最关键的是FREQUENCY(ROW(A:A),MATCH(A$2:A$15,A$2:A$15,)+1/ROW($2:$15))这部分。MATCH(A$2:A$15,A$2:A$15,)是查找城市的位置,相同的城市返回相同的数字,后面加1/ROW($2:$15)是为了将相同的数字转为不同的数字,并且顺序是从大到小的,这样frequency就会找到最小的位置,也就是最后1次的位置。找到位置后,剩下的就是万金油的套路了,返回对应的行号,提取第1个最小的行号,返回对应的内容;提取第2个最小的行号,返回对应的内容......
链接:
https://pan.baidu.com/s/1LX9asoSNQOCQQDq_wBS7bg
提取码:gys3