深入解析Excel萬金油篩選公式「INDEX-SMALL-IF-ROW」
手機如何做表格：點選檢視
用你的EXCEL、PPT、WORD等技能在業餘時間來兼職賺錢：：點選入駐
總是聽到高手們說有個萬金油公式，可到底什麼是萬金油公式，這個Excel公式又能幹什麼呢？不妨先看看下面這個效果圖：
這個例子就是一個典型的一對多查詢，查詢條件是部門，在資料來源內每個部門對應的都是多個資料，萬金油公式最主要的用途就是用來解決一對多查詢等一些相對複雜的問題。上面動畫中的公式為：
=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")
看到這個公式，或許很多朋友都會驚歎：這麼長的公式，看不懂哇！
今天就和大家一同破解這個看不懂但又很強悍的公式套路，耐心往下看哦……
上面這個公式一共用了六個函數：IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中的IFERROR和MATCH是本例中輔助性的兩個函數，其餘的四個INDEX-SMALL-IF-ROW就是萬金油公式啦。
因此我們先來學習這個核心部分的原理：
F4單元格的公式為：
=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))
先從INDEX說起，這個函數基本功能是給出一個區域，然後根據對應的行列位置返回查詢結果，上圖中INDEX查詢的資料區域就是姓名所在的區域$A$2:$A$21。
INDEX函數的基本結構是：INDEX（查詢區域，第幾行，第幾列），如果區域是單行或者單列的話，後面兩個引數可以省略一個。通俗點說，你拿著電影票去找座位，整個大廳的座位就是區域，第幾排第幾座就是公式中的後面兩個引數，通過這種方式可以準確找到目標位置。
在上面這個例子裡，區域是在一列，所以我們只需要確定每個資料在第幾行就行。
明白這一點的話，我們的重點就該放到INDEX的第二個引數了：
SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))
注意看上面這個圖，銷售部一共有四條記錄，分別在資料區域的第5、8、9和16行（資料區域是從第二行開始）。
因此我們希望公式下拉的時候，INDEX的第二個引數分別是5、8、9和16這四個數位（這一點一定要想明白）。
注意，接下來我們即將接觸到萬金油最核心的部分，請保持高度集中的注意力……
SMALL函數的基本結構：SMALL（一組數，第幾小的數）
建議自己模擬個簡單的資料來充分理解這個函數，方法如下：
在A列輸入一些數位，公式的意思是這列數位中最小的一個，結果是2，很好理解對不對，將公式的第二個引數改成2，再看看結果：
倒數第二小的是4。
如果希望繼續得到第三小的數，該怎麼做我想大家都能想到，但是會有個問題，我們只能手動修改第二引數，並不能通過下拉來實現這個引數的變化，如果要想可以下拉的話，第二引數就需要用到ROW函數，也就是這樣修改：
ROW函數非常簡單，得到的就是引數的行號，通過這個公式，我們就把A列的資料從小到大排了個序，覺得有意思嗎？
回到我們的萬金油公式，5、8、9和16這四個數位代表什麼意思還記得吧，我們需要用SMALL函數依次得到這四個數位，思路是通過判斷C列是否與F2一致，如果一樣得到行號，如果不一樣，就得到一個比最大行號還大的數位（目的是為了防止被查詢到）：
要實現這個目的，就需要IF函數的介入，於是就有了：
IF($C$2:$C$21=$F$2,ROW($1:$20),99)，用這一段來作為SMALL的第一個引數。
關於這段IF，就比較容易理解了，我們可以藉助F9來看看這段公式的結果：
因為我們的資料就20個，所以IF的第三個引數使用99就足夠了，如果資料量比較大的話，可以用9^9，表示9的9次方，反正足夠大就行。
搞清楚這個IF的話，再來看這段SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))是不是就沒那麼暈了。
關於SMALL這部分，一定要明白是隨著公式下拉的時候，逐個得到我們希望得到的那幾個數位，然後用這些數位作為INDEX的第二引數，就可以得到最終需要的結果。
萬金油的核心就是INDEX、SMALL、IF和ROW，請大家務必反覆琢磨，把這部分原理搞清楚。還有非常重要的一點需要強調，萬金油公式是一個陣列公式，因此需要我們按著Ctrl和shift再回車。
至於一開始的公式，考慮到要查詢多列的內容，所以INDEX的資料區域用的$A$2:$D$21，多列的時候，就需要提供列位置才能找到目標值，因此用MATCH(F$3,$A$1:$D$1,0)來確定資料在第幾列。
每個部門的資料都不一樣多，我們需要將公式多向下拉幾行，這時候就會產生一些錯誤值，在公式的最外層使用IFERROR函數遮蔽了錯誤值，使得查詢結果看起來非常乾淨。
今天只是使用了一對多查詢這樣一個例子來解釋萬金油公式的原理，實際上萬金油的套路還有很多，大家喜歡的話以後繼續分享相關的範例，當然，如果看完本文的話能夠自己去解讀一些複雜的公式就更好了。
相關學習推薦：
以上就是深入解析Excel萬金油篩選公式「INDEX-SMALL-IF-ROW」的詳細內容，更多請關注TW511.COM其它相關文章！
