在excel中有一個(gè)“萬(wàn)能”的函數(shù)組合，它們就是Index+small+If+row函數(shù)，它們被稱(chēng)之為“萬(wàn)金油”公式，主要就是因?yàn)檫@一組“萬(wàn)能”函數(shù)組合在工作中應(yīng)用廣泛，在N多種問(wèn)題的解決方法上都出現(xiàn)過(guò)它們的身影。

接下來(lái)就給大家分享3個(gè)萬(wàn)金油公式的應(yīng)用場(chǎng)地。

1.一對(duì)多查找

一般數(shù)據(jù)查找常用的是VLOOKUP函數(shù)，但是VLOOKUP函數(shù)只能返回查詢(xún)區(qū)域的首個(gè)匹配的查詢(xún)結(jié)果，但這里我們要查找的是一個(gè)姓名對(duì)應(yīng)的多個(gè)產(chǎn)品，想要返回查找值對(duì)應(yīng)的多個(gè)結(jié)果就需要用到“萬(wàn)金油”公式。

在D2單元格輸入公式：=IFERROR(INDEX($B$2:$B$12,SMALL(IF($A$2:$A$12=$D$2,ROW($1:$11)),ROW(A1))),'')

此公式是數(shù)組公式，需要按 Ctrl+Shift+Enter 結(jié)束公式。

公式說(shuō)明：IF($A$2:$A$12=$D$2,ROW($1:$11)) 用IF函數(shù)判斷A2:A12區(qū)域的值是否等于D2，如果等于則返回A列產(chǎn)品對(duì)應(yīng)的行號(hào)，如果不等于返回FALSE。 按F9結(jié)果得到一個(gè)內(nèi)存數(shù)組：{FALSE;2;FALSE;4;FALSE;FALSE;7;FALSE;FALSE;10;FALSE}，得出4個(gè)相等的行號(hào)。

SMALL函數(shù)對(duì)IF函數(shù)結(jié)果進(jìn)行取數(shù) ，公式向下填充，依次提取1,2,3……N個(gè)最小值，最后用INDEX根據(jù)SMALL函數(shù)提取的行號(hào)得出結(jié)果。

最后，當(dāng)SMALL函數(shù)得到的結(jié)果為錯(cuò)誤值時(shí)，表示符合條件的行號(hào)已被提取完，所以INDEX也會(huì)返回錯(cuò)誤值，為了避免返回錯(cuò)誤值，用IFERROR函數(shù)返回假空

2.提取數(shù)字

在A(yíng)列混合數(shù)據(jù)中提取數(shù)字，C2單元格輸入公式：=IFERROR(INDEX($A$2:$A$12,SMALL(IF(ISNUMBER($A$2:$A$12)=TRUE,ROW($1:$11)),ROW(A1))),'') 按Ctrl+Shift+Enter 結(jié)束公式。

公式說(shuō)明：IF(ISNUMBER($A$2:$A$12)=TRUE,ROW($1:$11)) 用ISNUMBER函數(shù)判斷A列中的數(shù)據(jù)是否是數(shù)字，是數(shù)字返回true，否則返回false，然后用IF函數(shù)再判斷ISNUMBER函數(shù)的結(jié)果是等于TRUE則返回對(duì)應(yīng)的行號(hào)，其余跟上一個(gè)案例一樣。

3.提取唯一值

在A(yíng)列重復(fù)數(shù)據(jù)中提取唯一值，D2單元格輸入公式：=IFERROR(INDEX($A$2:$A$16,SMALL(IF(MATCH($A$2:$A$16,$A$2:$A$16,0)=ROW($1:$15),ROW($1:$15),20),ROW(A1))),'') 按Ctrl+Shift+Enter 結(jié)束公式。

公式說(shuō)明：IF(MATCH($A$2:$A$16,$A$2:$A$16,0)=ROW($1:$15),ROW($1:$15),20) 用MATCH函數(shù)查找A2:A16中數(shù)據(jù)在A(yíng)2:A16區(qū)域中出現(xiàn)的位置，MATCH函數(shù)查找只會(huì)返回區(qū)域中首個(gè)匹配的數(shù)據(jù)，所以重復(fù)出現(xiàn)的數(shù)據(jù)都只有一個(gè)位置。再用IF函數(shù)判斷MATCH函數(shù)查找出的位置是否等于所在的行號(hào)，如果等于則返回所在的行號(hào)，如果不等于則返回一個(gè)比數(shù)據(jù)行號(hào)要大的一個(gè)數(shù)字（比如A列數(shù)據(jù)有16行，只要比16大的數(shù)字且所在的行號(hào)是空單元格即可），按F9可看到公式運(yùn)算后得到的結(jié)果是{1;2;3;4;5;20;20;8;20;20;11;20;20;20;20}，然后用INDEX+SMALL函數(shù)提取出數(shù)字。

我是小螃蟹，如果您喜歡這篇教程，請(qǐng)幫忙點(diǎn)贊和轉(zhuǎn)發(fā)哦，感謝您的支持！