這個經典套路你學會了嗎？

06-30

有小夥伴想查找第一列中未在第二列中出現的數據，這個首先想到的是用經典函數組合Index Small If。很多情況下，使用公式更利於表格的設計和自動更新。今天我們就來拓展一下，看看這個經典組合能實現什麼功能。如果你還有其他用法，歡迎留言告訴我。

應用一：按條件查詢所有記錄

以下數據記錄，我們需要根據G2:G3中的產品，來完成以下查詢。

查詢產品符合G2:G3區域中內容的記錄

查詢產品不符合G2:G3區域中內容的記錄

在B10單元格輸入以下公式並拖動複製到B10:E14的單元格區域。

=IFERROR(INDEX(B$1:B$6,SMALL(IF(COUNTIF($G$2:$G$3,$C$2:$C$6)=0,ROW($C$2:$C$6),9E 307),ROW(B1))),"")

查詢產品符合G2:G3區域中內容的記錄

在B18單元格輸入以下公式並拖動複製到B18:E22的單元格區域。

=IFERROR(INDEX(B$1:B$6,SMALL(IF(COUNTIF($G$2:$G$3,$C$2:$C$6)>0,ROW($C$2:$C$6),9E 307),ROW(B1))),"")

公式說明：

這是數組公式，輸入完公式後，需要按Ctrl Shift Enter結束；

第一個公式可以翻譯為，看一下C2:C6區域內每個值是否存在於G2:G3區域中（Countif實現），如果不存在，則返回該值所在的單元格行號，否則返回一個較大值。這樣形成一個不在G列表的銷售員所在的行號和極大值組成的數組，即{9E 307;9E 307;4;5;6}。用small，隨著滑鼠拖動複製公式，依次取第一個最小值4，第二個最小值5，第三個最小值6。最後用index取出值就可以了。

應用二：提取一列中的不重複值

如下圖，在A列有很多數據，需要用公式提取出所有不重複的記錄

在C2單元格輸入以下公式。

=IFERROR(INDEX($A$1:$A$13,SMALL(IF(MATCH($A$2:$A$13,$A$2:$A$13,0) 1=ROW($A$2:$A$13),ROW($A$2:$A$13),9E 307),ROW(A1))),"")

公式說明：

還是數組公式，按三鍵結束錄入；

Match查找每個數據在A2:A13這個區域中的位置， 1後如果和Row相等，表示這個是第一次出現。用Small依次取出第一個、二個、三個最小值所代表的位置，再用Index取值就可以了。

應用三：提取多列中的不重複記錄

根據銷售員和產品的組合判斷是否是重複行，取出不重複的記錄。

在B11單元格輸入以下公式。

=IFERROR(INDEX(B$1:B$6,SMALL(IF(MATCH($B$2:$B$6&$C$2:$C$6,$B$2:$B$6&$C$2:$C$6,0) 1=ROW($B$2:$B$6),ROW($B$2:$B$6),9E 307),ROW(B1))),"")

公式說明：

這也是一個數組公式，輸入完後按三鍵結束；

Match的數組形式，檢查每一行中兩列的組合在兩列組合的數組中的位置， 1天判斷和行號是否一致，如果不一致，說明它是重複的行。

應用四：多個欄位「或」條件查詢

需要查詢以下數據表中產品包含「空調」或銷量>70的記錄。

在B11輸入以下公式並複製填充到B11:E15的區域。

=IFERROR(INDEX(B$1:B$6,SMALL(IF(NOT(ISERROR(FIND("空調",$C$2:$C$6))) ($E$2:$E$6>70),ROW($C$2:$C$6),9E 307),ROW(A1))),"")

公式說明：

這還是一個數組公式，需要按三鍵結束；

用Find查找是否包含「空調」，找到就返回數字，否則返回錯誤值。用Not(IsError())來判斷是否滿足第一個條件。 ($E$2:$E$6>70)表示或者滿足這個條件。

--End--