本文分享excel用公式篩選完成一對(duì)多查找，是比較經(jīng)典的excel篩選函數(shù)公式自動(dòng)查找公式數(shù)據(jù)。

總是聽(tīng)到高手們說(shuō)有個(gè)萬(wàn)金油公式，可到底什么是萬(wàn)金油公式，這個(gè)Excel公式又能干什么呢？不妨先看看下面這個(gè)效果圖：

這個(gè)例子就是一個(gè)典型的一對(duì)多查找，查找條件是部門(mén)，在數(shù)據(jù)源內(nèi)每個(gè)部門(mén)對(duì)應(yīng)的都是多個(gè)數(shù)據(jù)，萬(wàn)金油公式最主要的用途就是用來(lái)解決一對(duì)多查找等一些相對(duì)復(fù)雜的問(wèn)題。上面動(dòng)畫(huà)中的公式為：

=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)),"")

看到這個(gè)公式，或許很多朋友都會(huì)驚嘆：這么長(zhǎng)的公式，看不懂哇！

今天就和大家一同破解這個(gè)看不懂但又很強(qiáng)悍的公式套路，耐心往下看哦……

上面這個(gè)公式一共用了六個(gè)函數(shù)：IFERROR、INDEX、SMALL、IF、ROW和MATCH，其中的IFERROR和MATCH是本例中輔助性的兩個(gè)函數(shù)，其余的四個(gè)INDEX-SMALL-IF-ROW就是萬(wàn)金油公式啦。

因此我們先來(lái)學(xué)習(xí)這個(gè)核心部分的原理：

F4單元格的公式為：

=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))

先從INDEX說(shuō)起，這個(gè)函數(shù)基本功能是給出一個(gè)區(qū)域，然后根據(jù)對(duì)應(yīng)的行列位置返回查找結(jié)果，上圖中INDEX查找的數(shù)據(jù)區(qū)域就是姓名所在的區(qū)域$A$2:$A$21。

INDEX函數(shù)的基本結(jié)構(gòu)是：INDEX（查找區(qū)域，第幾行，第幾列），如果區(qū)域是單行或者單列的話，后面兩個(gè)參數(shù)可以省略一個(gè)。通俗點(diǎn)說(shuō)，你拿著電影票去找座位，整個(gè)大廳的座位就是區(qū)域，第幾排第幾座就是公式中的后面兩個(gè)參數(shù)，通過(guò)這種方式可以準(zhǔn)確找到目標(biāo)位置。

在上面這個(gè)例子里，區(qū)域是在一列，所以我們只需要確定每個(gè)數(shù)據(jù)在第幾行就行。

明白這一點(diǎn)的話，我們的重點(diǎn)就該放到INDEX的第二個(gè)參數(shù)了：

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))

注意看上面這個(gè)圖，銷售部一共有四條記錄，分別在數(shù)據(jù)區(qū)域的第5、8、9和16行（數(shù)據(jù)區(qū)域是從第二行開(kāi)始）。

因此我們希望公式下拉的時(shí)候，INDEX的第二個(gè)參數(shù)分別是5、8、9和16這四個(gè)數(shù)字（這一點(diǎn)一定要想明白）。

注意，接下來(lái)我們即將接觸到萬(wàn)金油最核心的部分，請(qǐng)保持高度集中的注意力……

SMALL函數(shù)的基本結(jié)構(gòu)：SMALL（一組數(shù)，第幾小的數(shù)）

建議自己模擬個(gè)簡(jiǎn)單的數(shù)據(jù)來(lái)充分理解這個(gè)函數(shù)，方法如下：

在A列輸入一些數(shù)字，公式的意思是這列數(shù)字中最小的一個(gè)，結(jié)果是2，很好理解對(duì)不對(duì)，將公式的第二個(gè)參數(shù)改成2，再看看結(jié)果：

倒數(shù)第二小的是4。

如果希望繼續(xù)得到第三小的數(shù)，該怎么做我想大家都能想到，但是會(huì)有個(gè)問(wèn)題，我們只能手動(dòng)修改第二參數(shù)，并不能通過(guò)下拉來(lái)實(shí)現(xiàn)這個(gè)參數(shù)的變化，如果要想可以下拉的話，第二參數(shù)就需要用到ROW函數(shù)，也就是這樣修改：

ROW函數(shù)非常簡(jiǎn)單，得到的就是參數(shù)的行號(hào)，通過(guò)這個(gè)公式，我們就把A列的數(shù)據(jù)從小到大排了個(gè)序，覺(jué)得有意思嗎？

回到我們的萬(wàn)金油公式，5、8、9和16這四個(gè)數(shù)字代表什么意思還記得吧，我們需要用SMALL函數(shù)依次得到這四個(gè)數(shù)字，思路是通過(guò)判斷C列是否與F2一致，如果一樣得到行號(hào)，如果不一樣，就得到一個(gè)比最大行號(hào)還大的數(shù)字（目的是為了防止被查找到）：

要實(shí)現(xiàn)這個(gè)目的，就需要IF函數(shù)的介入，于是就有了：

IF($C$2:$C$21=$F$2,ROW($1:$20),99)，用這一段來(lái)作為SMALL的第一個(gè)參數(shù)。

關(guān)于這段IF，就比較容易理解了，我們可以借助F9來(lái)看看這段公式的結(jié)果：

因?yàn)槲覀兊臄?shù)據(jù)就20個(gè)，所以IF的第三個(gè)參數(shù)使用99就足夠了，如果數(shù)據(jù)量比較大的話，可以用9^9，表示9的9次方，反正足夠大就行。

搞清楚這個(gè)IF的話，再來(lái)看這段SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))是不是就沒(méi)那么暈了。

關(guān)于SMALL這部分，一定要明白是隨著公式下拉的時(shí)候，逐個(gè)得到我們希望得到的那幾個(gè)數(shù)字，然后用這些數(shù)字作為INDEX的第二參數(shù)，就可以得到最終需要的結(jié)果。

萬(wàn)金油的核心就是INDEX、SMALL、IF和ROW，請(qǐng)大家務(wù)必反復(fù)琢磨，把這部分原理搞清楚。還有非常重要的一點(diǎn)需要強(qiáng)調(diào)，萬(wàn)金油公式是一個(gè)數(shù)組公式，因此需要我們按著Ctrl和shift再回車(chē)。

至于一開(kāi)始的公式，考慮到要查找多列的內(nèi)容，所以INDEX的數(shù)據(jù)區(qū)域用的$A$2:$D$21，多列的時(shí)候，就需要提供列位置才能找到目標(biāo)值，因此用MATCH(F$3,$A$1:$D$1,0)來(lái)確定數(shù)據(jù)在第幾列。

每個(gè)部門(mén)的數(shù)據(jù)都不一樣多，我們需要將公式多向下拉幾行，這時(shí)候就會(huì)產(chǎn)生一些錯(cuò)誤值，在公式的最外層使用IFERROR函數(shù)屏蔽了錯(cuò)誤值，使得查詢結(jié)果看起來(lái)非常干凈。

今天只是使用了一對(duì)多查找這樣一個(gè)例子來(lái)解釋萬(wàn)金油公式的原理，實(shí)際上萬(wàn)金油的套路還有很多，大家喜歡的話以后繼續(xù)分享相關(guān)的實(shí)例，當(dāng)然，如果看完本文的話能夠自己去解讀一些復(fù)雜的公式就更好了。

相關(guān)學(xué)習(xí)推薦：excel教程

以上就是深入解析Excel萬(wàn)金油篩選公式“INDEX-SMALL-IF-ROW”的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！