學Excel函數公式，怎能不會這個組合套路？

學Excel函數公式，怎能不會這個組合套路？

我們對數據進行查詢時，經常會使用VLOOKUP函數。但有時我們提取符合條件的結果是多個，而不是一個，這時候VLOOKUP就犯難了。

舉個例子

如下圖，左側A1:C10是一份學員名單表，現在需要根據F1單元格的“EH圖班”這個指定的條件，在F2:F10單元格區域中，提取該班級全部學員名單。

F1的值是“EH圖表班”，需要在F2:F10單元格區域得到圖表班相關成員的人名。

接下來就分享一個函數查詢方面的萬金油套路：

INDEX+SMALL+IF

F2單元格輸入以下數組公式，按住Ctrl+Shift鍵不放，再按回車鍵，然后向下填充：

=IFERROR(INDEX(B:B,SMALL(IF(A:A=F,ROW(:)),ROW(A1))),””)

1. 公式講解

IF(A:A=F,ROW(:))

這部分，先判斷A1:A10的值是否等于F1，如果相等，則返回A列班級相對應的行號，否則返回FALSE，結果得到一個內存數組：

{FALSE;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE;10}

再來看這部分：

SMALL(IF(A:A=F,ROW(:)),ROW(A1))

SMALL函數對IF函數的結果進行取數，隨著公式的向下填充，依次提取第1、2、3……n個最小值，由此依次得到符合班級條件的行號。

隨后使用INDEX函數，以SMALL函數返回的行號作為索引值，在B列中提取出對應的姓名結果。

當SMALL函數所得到的結果為錯誤值#NUM時，意味著符合條件的行號已經被取之殆盡了，此時INDEX函數也隨之返回一個錯誤值，為了避免公式返回一個錯誤值，最后使用IFERROR函數進行規避，使之返回一個空文本””。

2. 其它說明

很多時候，一些朋友喜歡把INDEX+SMALL+IF的套路寫成：

=INDEX(B:B,SMALL(IF(A:A=F,ROW(:),4^8),ROW(A1)))&””

或

=INDEX(B:B,SMALL((A:A<>F)/1%+ROW(:),ROW(A1)))&””

這兩個套路，通過引值真空單元格搭配&””的方法，很巧妙的規避了錯誤值的出現，而且公式的長度得到了精簡，是IFERROR函數未出現前處理錯誤值的常用技巧。

只是當公式的查找結果為數值或者日期時，這個方法會把數值變成文本值，并不利于數據的準確呈現以及再次統計分析。

比如一個簡單的SUM求和，對于此類文本數據的統計都是麻煩的，原因是大部分統計函數都忽略文本值，不予計算。

所以通常還是建議大家使用IFERROR函數來處理錯誤值。

3. 練手題

最后留下一道練手題，如下圖，根據A1:C10區域的數據，將E列相關班級的姓名，填充到F2:I5區域。

示例文件下載：

http://pan.baidu.com/s/1nuDoYFZ

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。