|
今天和小伙伴們分享幾個查找不重復記錄的套路，這個不重復的記錄相當于用刪除重復項這個功能，比如表中有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 |
|
|
