|
那么我们来看下数据源。如下图所示，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 |
|
|
