聲明：以下內(nèi)容屬于原創(chuàng)內(nèi)容，如有轉(zhuǎn)載，請注明作者與出處。

相信大多數(shù)的excel水平還不錯的小伙伴會都聽到這個函數(shù)一組合，業(yè)內(nèi)人普遍認(rèn)為這個組合是萬金油，在處理大多數(shù)的問題的時候都會遇到這個函數(shù)組合。但是對于這個函數(shù)組合并不是所有的人都明白其中的意義，如果你懂這個函數(shù)組合，那么說明你的函數(shù)水平已經(jīng)超乎了平常人的水平。下面就兩個例子給大家說下關(guān)于這個函數(shù)組合的意義。

【例題】如下圖所示，將以下人員按部門進(jìn)行歸類。分部以橫向與縱向兩個方向進(jìn)行歸類。要求：使用函數(shù)公式進(jìn)行歸類。

【解題方法與思路解析】

1、橫向

在F2單元格中輸入以下公式：

=IFERROR(INDEX($A$2:$A$14,SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1),'')

按組合鍵確定，并填充。

2、橫向

在G8單元格中輸入以下公式：

=IFERROR(INDEX($A$2:$A$14,SMALL(IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8),COLUMN(A1))-1),'')

按組合鍵確定，并填充。

以上兩個公式的用法都是一樣的，針對的是同一類的題目。該題目實際上就是一個一對多的查找匹配的問題，并且是反向的進(jìn)行。如果正向也是同樣的方法。下面對橫向的公式進(jìn)行解析：

(1)ROW($B$2:$B$14)，這里的意思是一個數(shù)組，返回的值為：{2;3;4;5;6;7;8;9;10;11;12;13;14}，是一個1列13行的一個數(shù)組，此處也可以寫成row($2:$14),是同一個道理；row(A1)返回的是A1單元格行號，隨著公式的下拉分別返回對應(yīng)的行的行號；

（2）IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8)，這一部分是使用if函數(shù)進(jìn)行條件判斷。$F8=$B$2:$B$14 返回值為：

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}，如果F8單元格的值等于區(qū)域$B$2:$B$14中的某一個單元格，則返回的為TRUE,否則為FALSE。大家翥知道，邏輯值是可以參與運(yùn)算的，即TRUE=1,FALSE=0; 4^8是一個很大的數(shù)，即65533，也就是說已前版本的軟件的行數(shù)的最大值。這里大家也可以使用比這個數(shù)更大的值，或者大于當(dāng)前的非空區(qū)域的行數(shù)即可。

（3）SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1這一步是使用samll取出IF($F8=$B$2:$B$14,ROW($B$2:$B$14),4^8)此運(yùn)算結(jié)果的一組最小值，隨著row(A1)的下拉的時候的不斷地增大，最上值的取值分別從1，2，……，一直至F$1=$B$2:$B$14這個條件返回值為TRUE的最后一個最小值。即F8單元格所對應(yīng)的區(qū)域的最后一個符合條件的值的下一個行號。而減去1的意思就是讓符合的值回到原位，或者SMALL(IF(F$1=$B$2:$B$14,ROW($B$2:$B$14),4^8),ROW(A1))-1這一步可以寫成：SMALL(IF(F$1=$B$1:$B$14,ROW($B$1:$B$14),4^8),ROW(A1))，這樣的話就不會產(chǎn)生錯位。

（4）利用index函數(shù)返回已經(jīng)查找出來的行號在區(qū)域$A$2:$A$1中對應(yīng)的值，如果以上產(chǎn)生錯誤值，那么使用iferror容錯函數(shù)，將其錯誤值屏幕為空白。最后完成了查找。

另外，以上也可以使用vlookup進(jìn)行查找，這里再不做過多的描述，如果有意愿者可以參考一下vlookup的一對多查找。