Excel里函數中的萬金油,你確定不要點進來看看?
Excel里有個號稱「萬能」的函數組合,這個函數組合就是INDEX SMALL IF,很多應用場合都能看到它們的身影。
這個函數的特點是:能提取出滿足某條件或某些條件的所有數據,可以一次性返回單行單列或多行多列的結果。
讓我們通過一個實例來認識一下它們到底有多「萬能」吧!
公式應用情景介紹
如下圖,A1:F9單元格為某次考試結果,每個人一行數據。現在需要在B11單元格(棕色底色填充)輸入班級名稱,在A14:F21區域返回該班級所有學生考試成績信息。
本例假設需要查詢【1班】所有學生的成績。
解決問題思路分析
我們的目的是要提取出A列班級名稱與B11單元格內容一致的所有數據,如果:1)能提取出符合條件的所有行數;
2)這些行數能從小到大一個一個的輸出;
3)可以從原始數據中按第2步輸出的行數把數據引用過來,那麼問題就解決了。
恰好,在Excel中存在這麼幾個函數能達到目的,下面我們來逐個介紹。
3如何提取出符合條件的所有行數?
我們可以使用IF函數判斷來生成所有符合條件的行號數組,如下:
=IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))
$A$2:$A$9=B11判斷A列數據是否和B11單元格內容一致,會返回TRUE和FALSE的數組。
如果$A$2:$A$9=B11則返回A2:A9的行號,否則返回FALSE。
本例生成的數組為棕色部分(A2=1班,所以返回A2的行號2,以此類推),按F9可以看到如下數組:
{2;FALSE;FALSE;5;6;FALSE;FALSE;9}
如果要滿足多個條件,可以用
=IF((條件區域1=條件)*(條件區域2=條件2),ROW(區域))
這種方式來實現,例如要返回【班級】為「1班」且【總分】>200的查詢結果,此部分可以改成:
=IF(($A$2:$A$9="一班")*($F$2:$F$9>200),ROW($A$2:$A$9))
4如何順序輸出上一步獲得的行號?
由於上一步獲得的符合條件的行號數組是數字和FALSE的組合,我們利用SMALL函數就可以逐步輸出符合條件的行號。
舉例來說,SMALL({11,22,33},1)=11,也就是SMALL返回第一參數中第1(第二參數)小的數字。
同理,SMALL({11,22,33},2)=22。
為了下拉公式方便,我們一般用ROW()函數做SMALL的第二參數,保證下拉公式時自動提取第2小,第3小……
也就是用:
=SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))
將第一步產生的數組中符合條件的行號從最小的開始連續輸出。從下圖可以看到,符合條件的行號已經可以連續輸出了。
錯誤值可以先不用關心,後面我們用其他函數屏蔽。
接下來,用函數提取數據
最後一步提取數據可以選用的函數比較多,本例介紹INDEX函數方法。
簡單說,INDEX函數可以實現從第一參數中提取出第二參數個數據的目的。
例如:=INDEX({11,21,31},1)返回11,也就是從{11,21,31}提取第1個數字;=INDEX({11,21,31},2)返回21,也就是從{11,21,31}提取第2個數字。
那麼,我們用(紅色部分是上一步SMALL函數,第一個返回值為2):
=INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1)))
就可以返回A1:A9中的第2個值,也就是第1個「1班」;下拉公式就返回了第2個「1班」……
完成階段
我們把上一步的函數組合放在查詢結果區域的第一列,Ctrl Shift enter運行公式,並下拉,結果如下:
這樣就獲得了班級的返回結果。
後面要獲取姓名和各科成績等信息怎麼辦呢?
通過分析,我們知道了SMALL函數輸出的結果已經固定不變了,現在只需要改變INDEX函數引用的列即可。
因此我們將INDEX函數的第一參數設置成了A$1:A$9【行絕對引用,列相對引用】,這樣,我們向右拉動公式就會自動變成B$1:B$9。
完成效果圖如下:
屏蔽錯誤值
可以看到上步產生的結果還有錯誤值存在,我們用IFERROR函數去屏蔽錯誤值,如果INDEX函數返回的是錯誤值,則在單元格返回空。
=IFERROR(INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))),"")
這樣就最終完成了查詢功能的設置。
補充說明
一般還有用INDEX(大區域,SMALL(IF(條件區域=條件,ROW(條件區域),4^8),ROW(A1)))&""這種方式去屏蔽錯誤值,這種屏蔽錯誤值的思路是,如果條件區域=條件就返回條件區域行號,否則返回一個比較大的用不到的行號,比如說4^8行或數據區域下面的一個空行行號。這樣,INDEX在輸出完所有符合條件的行後開始調用4^8行的值(空單元格),為了避免返回0所以再加上一個&""。
本例未使用此種方法,主要是由於後面要查詢的其他列有數字,用上述&""的方法數字會變成文本。
如果有日期的時候,日期格式存儲的數據會變成一個數字。
圖文作者:ExcelHome超級版主 shaowu459
推薦閱讀:
※婚姻好不好,僅僅只是看看生肖沖不沖嗎?
※俗話說:「小拇指過三關,不愁吃喝穿」看看你有嗎?
※出生在農曆幾月的女人最旺夫?看看前五名里有沒有你
※寫給女人看的!建議男的也看看
※【轉載】你是大吉之相,還是貧賤之相?看看鼻子上的它就知道了