|
今天和小伙伴们分享几个查找不重复记录的套路，这个不重复的记录相当于用删除重复项这个功能，比如表中有2个相同的名字，只取第一次出现的记录。也就是如果一条记录重复出现多次，只取第一次出现的记录。 -01- offset多维引用 1.将A列中的部门去除重复项 如下图所示，去除重复项的结果如E列所示，最简单的方法就是用数据中-删除重复值这个命令。现在我们说的是用函数的方法来实现，在E2单元格中输入公式=IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(OFFSET(A$2,,,ROW($2:$11)-1),A$2:A$11)=1,ROW($2:$11)),ROW(A1))),"")，按三键结束，下拉填充。 上面那个公式主要用到offset的多维引用，现在不懂也没有关系。可以用辅助列的方法来完成。先简单说一下思路，主要是找到每个部门第一次出现的行号，然后提取出每个行号，最后返回A列的对应的单元格。 在B列添加一个辅助列，在B2单元格中输入公式=IF(COUNTIF(A$2:A2,A2)=1,ROW())，结果如上图B列所示。COUNTIF(A$2:A2,A2)是动态区域，下拉时，区域会扩展，返回的结果相当于每个部门出现的次数。外面再用if函数判断，如果countif返回的结果等于1，那么返回对应的行号，否则返回false。 在C2单元格中输入公式=IFERROR(INDEX(A:A,SMALL(B$2:B$11,ROW(A1))),"")。SMALL(B$2:B$11,ROW(A1))是将辅助列的行号从小到大的提取出来。small是返回数组中第k个最小值。第一个最小值是2，下拉第二个最小值是3......然后用index返回A列中对应的位置。iferror是用来处理错误，将small产生的错误值转为空文本""。 -02- match( )=row( ) 2.将A列中的部门去除重复项 第2种套路主要用的是match函数和row函数。在E15单元格输入公式=IFERROR(INDEX(A:A,SMALL(IF(MATCH(A$15:A$24,A$15:A$24,)=ROW($15:$24)-14,ROW($15:$24)),ROW(A1))),"")，按三键结束，下拉填充。 思路还是查找各部门第一次出现的行号。主要的方法就是用match查找到位置，然后和序列号比较，如果相等就是第一次出现的位置，那么返回它的行号。下面还是用辅助列说明。 在B列添加辅助列，B15单元格输入公式=IF(MATCH(A15,A$15:A$24,)=ROW(A1),ROW())，下拉。MATCH(A15,A$15:A$24,)是查找A15的值在A$15:A$24这个区域的位置。ROW(A1)下拉时构建一个以1开始的序列号。如果match返回的位置和对应的序列号相等，那么就是部门第一次出现的位置，要返回它的行号。 在辅助列中算出了各部门第一次出现的行号，剩下的就一样了。在C15单元格中输入公式=IFERROR(INDEX(A:A,SMALL(B$15:B$24,ROW(A1))),"")，下拉完成。 -03- match+countif动态区域 3.将A列中的部门去除重复项 第3种套路主要是用match和countif的动态区域。在C28单元格输入公式=IFNA(INDEX(A:A,MATCH(,COUNTIF(C$27:C27,A$28:A$37),)+27),"")，按三键结束，下拉完成。这个公式还是有点难理解的，好处就是公式比上面2个短。它是把上一次的结果放入下一次的计算区域中。 countif函数的第一参数是动态区域，第2参数是各部门的数据。查找各部门在第一参数中的个数，没有的返回0，有的返回对应的个数，构成一个数组。然后用match查找第一个0的位置，再加上表头的行号就是在A列中的行号。最后用index返回相应的值。 这里的关键是countif的第一参数在第一次的时候肯定不会包含A28:A37，就相当于countif的第一参数是一个空的数组，用A列的每个单元格和空的数组进行比较，如果A28没有在空数组中出现，就把A28放到空数组中，然后A29和那个数组比较，如果A29也没有出现在那个数组中，那么把A29也放到那个数组中；如果A29已经出现在那个数组中，那么不管了，直接看A30有没有出现在那个数组中......以此类推。 链接： https://pan.baidu.com/s/1Q59brvhw4woFO0CfFMCkGg 提取码：w6or |
|
|
