|
圖26-8展示的是某單位銷售記錄表的部分內 容，需要提取不重復的客戶代表姓名。在F2單元格中輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵將公式向下復制到單元格顯示空白為止。{=INDEX(C:C,SMALL(IF(MATCH(C$2:C$11,C:C,)=ROW($2:$11),ROW($2:$11),4^8),ROW(A1)))&''}公式中的“MATCH(C$2:C$11,C:C,)”部分利用MATCH函數在C列中依次查找C2:C11單元格區域中每個元素首次出現的位置，結果如下。然后將以上內存數組結果與數據所在行號“ROW($2:$11)”進行比對，如果查找的位置序號與數據自身的位置序號一致，則表示該數據是首次出現；否則是重復出現。當MATCH函數結果與數據自身的位置序號相等時，返回當前數據行號；否則返回65 536。再通過SMALL函數將行號從小到大依次取出，最終由INDEX函數返回該位置的姓名，得到不重復的姓名列表。在F2單元格中輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵將公式向下復制到單元格顯示空白為止。{=INDEX(C:C,1+MATCH(,COUNTIF(F$1:F1,C$2:C$12),))&''}公式中的“COUNTIF(F$1:F1, C$2:C$12)”部分利用COUNTIF函數在公式所在位置上方的單元格區域中，分別查找C$2:C$12單元格區域中每個數據的個數。COUNTIF函數的第一參數F$1:F1利用絕對引用和相對引用的技巧，形成一個自動擴展的數據范圍。當公式向下復制時，查找區域依次變為$F$1:$F2、$F$1:$F3、…、$F$1:$F11。COUNTIF函數返回一個由0和1構成的數組，其中0表示該姓名在公式上方未出現過，1表示該姓名在公式上方已出現過。然后用MATCH函數在COUNTIF函數返回的數組中查找第一個0的位置，即查找下一個尚未出現的姓名所在的位置。再利用INDEX函數，根據MATCH函數的結果從C列中返回對應位置的內容。由于數據表有一個標題行，因此，將MATCH函數的結果加1，用于匹配在數據表中的位置。本例中COUNTIF函數的第二參數C$2:C$12比實際數據區域多出一行，目的是當公式復制的行數超出不重復數據的個數時，得到的內存數組中最后一個元素始終為0，從而避免MATCH函數由于查找不到0而返回錯誤值。在H2單元格中輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵將公式向下復制到單元格顯示空白為止。{=INDEX(C:C,MIN(IF(COUNTIF(H$1:H1,C$2:C$11),4^8,ROW($2:$11))))&''}公式中的“IF(COUNTIF(H$1:H1,C$2:C$11),4^8,ROW($2:$11))”部分表示，如果數據在公式之前的范圍中出現過，則返回65536；否則返回對應的行號。以H3單元格為例，由于H2單元格中的姓名已經在C列出現過，因此返回內存數組結果如下。{65536;65536;65536;5;6;7;8;9;10;11}隨著公式的向下復制，用MIN函數依次提取尚未出現的姓名的最小行號。最后用INDEX函數得到該行號對應的姓名。圖26-9展示的是某公司各部門值班表的部分內容，需要提取不重復的姓名列表。在F2單元格中輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵將公式向下復制到單元格顯示為空白為止。{=INDIRECT(TEXT(MIN(IF(COUNTIF(F$1:F1,B$2:D$8)=0,ROW($2:$8)*1000+COLUMN(B:D),2^20)),'R0C000'),0)&''}公式使用COUNTIF函數，判斷公式所在行之前的區域中是否包含有B2:D8單元格區域中的姓名。如果數據列表中的姓名沒有出現過，返回對應的行號乘以1 000加列號，否則返回2^20即1 048 576。行號乘以1000加列號的目的是行號放大1 000倍后再與列號相加，使其后3位為列號，之前的部分為行號，相加時互不干擾，以F2單元格公式為例，此部分的結果如下。{2002,2003,2004;3002,3003,……,6004;7002,7003,7004;8002,8003,8004}再使用MIN函數提取出加權計算后的最小行列號組合值2002。TEXT函數將其轉換為“R1C1”引用樣式的文本型單元格地址字符串“R2C002”。INDIRECT函數第二參數使用0，表示以“R1C1”引用樣式返回對文本型單元格地址字符串的引用。R2C002就是引用工作表中第二行第二列的單元格，即B2單元格。圖26-10展示的是保險公司客戶記錄表的部分內容，需要根據G1單元格中指定的客戶區域，篩選出該區域不重復的客戶編號。在F4單元格中輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵將公式向下復制到F9單元格。{=INDEX(B:B,SMALL(IF((MATCH(A$2:A$17&B$2:B$17,A:A&B:B,)=ROW($2:$17))*(A$2:A$17=G$1),ROW($2:$17),4^8),ROW(A1)))&''}公式中的“MATCH(A$2:A$17&B$2:B$17,A:A&B:B,)”部分先使用連接符將區域和客戶編號兩個字段連接形成單列數據，然后用MATCH函數返回連接后的字符串首次出現的位置。再使用 MATCH 函數的位置結果與序號比較，并結合區域判斷條件“ (A$2:A$17=G$1) ”，讓符合區域條件且首次出現的客戶編號記錄返回對應行號，而不符合區域條件或是重復的客戶編號記錄返回65536。最后利用SMALL函數從小到大提取出行號，并借助INDEX函數返回對應的客戶編號記錄。 
--------------------------------------------------------------------- 推薦圖書 
1. 專家云集：多位身處各行各業，并身懷絕技的微軟全球有價值專家與您無私分享。多年對 Excel的研究結果進行揭秘。
2. 知識點全覆蓋：詳盡而又系統地介紹了 Excel 2016函數與公式的所有技術特點和應用方法，全面覆蓋相關知識點，完備知識體系無人能及。
3. 解決實際問題：大量源自實際工作的典型案例，通過細致地講解，生動地展示各種應用技巧，快速提高讀者的辦公效率，讓讀者提前完成手頭工作，不用加班。
4. 專業級深度剖析：對常常困擾學習者的功能性特性進行深入剖析，可以讓讀者既能知其然，又能知其所以然。
|
