絕對(duì)干貨 excel中一對(duì)多查找問(wèn)題解決思路匯總 附公式模型（絕對(duì)干貨是什么意思）

公式模型一：預(yù)備知識(shí)：數(shù)組，邏輯值的概念，if函數(shù)的較好掌握，small函數(shù)的基本用法，index的基本用法。

f3的公式：

=IFERROR(INDEX($C$3:$C$11,SMALL(IF($B$3:$B$11=$E3,ROW($1:$9),99),COLUMN(A1))),"")

數(shù)組公式，輸入或者復(fù)制公式后，按住ctrl和shift鍵按回車，公式自動(dòng)出現(xiàn)大括號(hào)，向右向下拉即可。結(jié)構(gòu)分析：這個(gè)公式的核心結(jié)構(gòu)是index（結(jié)果范圍，small（if（條件范圍=條件值，row（），一個(gè)超過(guò)結(jié)果范圍個(gè)數(shù)的數(shù)字），column（））），加了個(gè)iferror是為了將錯(cuò)誤值顯示為空。

必須理解這個(gè)公式的思路，才能夠靈活套用這個(gè)結(jié)構(gòu)去解決問(wèn)題。拿這個(gè)例子來(lái)說(shuō)，b列是條件列，c列是結(jié)果列，e列是具體的條件，最終的結(jié)果需要橫著拉（針對(duì)同一產(chǎn)品而言）。

這個(gè)公式的核心是if數(shù)組的運(yùn)用，$B$3:$B$11=$E3可以得到一系列的邏輯值，而僅當(dāng)b列中為"A"的項(xiàng)目對(duì)應(yīng)的項(xiàng)才是true，從而返回ROW($1:$9)這個(gè)數(shù)組的對(duì)應(yīng)數(shù)字，而對(duì)于其他不是"A"的項(xiàng)目，對(duì)應(yīng)的就是99，具體來(lái)看，這里的if的結(jié)果就是{1;99;99;4;99;99;99;99;9}，可以發(fā)現(xiàn)，這個(gè)數(shù)組里不是99的只有3個(gè)，1,、4和9，而這三個(gè)數(shù)字對(duì)應(yīng)在$B$3:$B$11這個(gè)范圍里，正好就是"A"對(duì)應(yīng)的位置，為了依次得到這三個(gè)數(shù)字，就需要small這個(gè)函數(shù)了，因?yàn)楣叫枰獧M向拉動(dòng)，所以用了column作為small的第二參數(shù)。到這一步，index的結(jié)果就不需要多講了。

通過(guò)這個(gè)思路的分析，需要特別注意的幾個(gè)地方就是數(shù)組維度的對(duì)應(yīng)，也就是if里面的第一參數(shù)這個(gè)條件數(shù)組不需要多說(shuō)，第二個(gè)參數(shù)的row包含的個(gè)數(shù)應(yīng)該和條件范圍是一樣多的（并且應(yīng)該絕對(duì)引用），第三參數(shù)這個(gè)在本例來(lái)說(shuō)用10都可以，因?yàn)閿?shù)據(jù)源只有9個(gè)，如果多的話可以用9^9（9的9次方=387420489）這個(gè)應(yīng)該是足夠大了。當(dāng)然在if里面用column效果也是一樣的，只不過(guò)row看起來(lái)簡(jiǎn)潔一點(diǎn)，千萬(wàn)不要和small的第二參數(shù)混淆了。

思路解釋起來(lái)是很繞口的，更好的方法是模擬一個(gè)少一點(diǎn)的數(shù)據(jù)源，不超過(guò)10行的都可以，然后使用公式求值或者f9功能一步一步看看公式結(jié)果是怎么變化的，從而去了解公式的計(jì)算過(guò)程。

公式模型一：INDEX(返回區(qū)域,SMALL(IF(查找區(qū)域=查找值,ROW(查找區(qū)域),99,COLUMN(A1)))套用時(shí)需要看清案例一的特點(diǎn)：查找值在查找區(qū)域內(nèi)是間隔出現(xiàn)的。

公式模型二：預(yù)備知識(shí)：IF、ROW、COUNTIF、OFFSET、MATCH等函數(shù)的基本用法。

首先觀察這個(gè)題目和第一個(gè)例子有什么區(qū)別？查找值在查找區(qū)域是連續(xù)的，結(jié)果是向下拉的，也許還有別的差異。那么可以用公式模型一來(lái)完成嗎？答案是肯定的，E2公式如下：

=INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1)))，下拉即可，注意還是數(shù)組的，可以結(jié)合這個(gè)例子再去領(lǐng)會(huì)一下公式模型一的思路。

其實(shí)這個(gè)例子完全可以不用數(shù)組公式來(lái)處理，因?yàn)楹屠蛔钪匾囊粋€(gè)差別就是a列的重復(fù)值是連續(xù)的，可以看做例一的特殊情況。把上面這個(gè)公式下拉多行會(huì)發(fā)現(xiàn)有亂碼出現(xiàn)，如果要排錯(cuò)的話，還是可以用iferror，當(dāng)然也可以用if+countif來(lái)實(shí)現(xiàn)，排錯(cuò)公式如下（都是數(shù)組哦）：=IFERROR(INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1))),"")或者=IF(ROW(A1)>COUNTIF($A$3:$A$19,$D$3),"",INDEX($B$3:$B$19,SMALL(IF($A$3:$A$19=$D$3,ROW($1:$17),99),ROW(A1))))

用if+countif來(lái)排錯(cuò)看起來(lái)是長(zhǎng)一點(diǎn)，但是這個(gè)思路需要明白，關(guān)鍵就是row和countif的值做比較，而這個(gè)應(yīng)用也是很有用的，下面就來(lái)看公式模型二，這個(gè)公式不需要數(shù)組哦：=IF(ROW(A1)>COUNTIF(A:A,D$3),"",OFFSET(B$2,MATCH(D$3,A:A,0)-3+ROW(A1),))其實(shí)這個(gè)公式的核心在于offset的作用了，尤其是offset的第二個(gè)參數(shù)，使用了match和row來(lái)共同得出行的偏移量，如果明白了offset在這里的作用，那么公式模型二也就沒(méi)什么難以理解的了。

公式模型二：IF(ROW()>COUNTIF(),"",OFFSET(,MATCH(),,,)? 關(guān)鍵在于offset第二參數(shù)的構(gòu)造。注意:此公式不好之處在于查找區(qū)域必須排序,好處在于是普通公式運(yùn)算速度快。

公式模型三：對(duì)于一對(duì)多查找的問(wèn)題，基本就幾種情況，如果不用輔助列的話，使用公式模型一都可以解決，如果可以排序的話，推薦使用非數(shù)組的模型二來(lái)解決，但是方法不僅限于這兩種，如果可以使用輔助列的話，僅用vlookup都可以實(shí)現(xiàn)，而且無(wú)需數(shù)組，還是用實(shí)例二的數(shù)據(jù)，但是在數(shù)據(jù)源里我們加一個(gè)輔助列，如下

圖：

輔助列的公式為，A3：=COUNTIF($B$3:B3,$E$3)，結(jié)果列公式為，F(xiàn)3：=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$19,3,0),"")無(wú)需排序，無(wú)需數(shù)組，會(huì)用vlookup就能解決這類問(wèn)題，推薦初學(xué)者學(xué)習(xí)掌握。

公式模型四：還是用模型二里用到的例子，在介紹一種方法，使用INDIRECT、SMALL、IF、ROW這幾個(gè)函數(shù)來(lái)完成。=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))數(shù)據(jù)源不需要排序，是數(shù)組公式，這里是把模型一的index換成了indirect函數(shù)，了解兩個(gè)函數(shù)引用區(qū)別的話自然也就明白模型四的原理了。

這里需要介紹另一種排除錯(cuò)誤的方法，就是t函數(shù)，上面的公式可以變成=T(INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1))))三鍵結(jié)束下拉即可=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))&""而在公式后面加&""也是一種排錯(cuò)的方法。可以在前面的公式里使用一下這兩種排錯(cuò)方法，需要說(shuō)明一點(diǎn)的是，一般是結(jié)果為文本型的這兩個(gè)方法時(shí)候可以用。

當(dāng)然，對(duì)于發(fā)燒友級(jí)別的朋友來(lái)說(shuō)，還有其他的思路，不過(guò)相對(duì)就很難理解了，這里不去一一介紹了，能夠理解并且能夠套用上面說(shuō)的一個(gè)或者幾個(gè)公式模型就已經(jīng)很不錯(cuò)了，由于水平有限，以上解釋中難免有不足之處，對(duì)于發(fā)現(xiàn)的問(wèn)題希望各位表友可以予以指正，這里不勝感激。同時(shí)對(duì)于解釋不甚理解的朋友也可以加入討論群進(jìn)行詢問(wèn)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。