絕對干貨 excel中一對多查找問題解決思路匯總 附公式模型

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

f3的公式：

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

數組公式，輸入或者復制公式后，按住ctrl和shift鍵按回車，公式自動出現大括號，向右向下拉即可。結構分析：這個公式的核心結構是index（結果范圍，small（if（條件范圍=條件值，row（），一個超過結果范圍個數的數字），column（））），加了個iferror是為了將錯誤值顯示為空。

必須理解這個公式的思路，才能夠靈活套用這個結構去解決問題。拿這個例子來說，b列是條件列，c列是結果列，e列是具體的條件，最終的結果需要橫著拉（針對同一產品而言）。

這個公式的核心是if數組的運用，$B$3:$B$11=$E3可以得到一系列的邏輯值，而僅當b列中為"A"的項目對應的項才是true，從而返回ROW($1:$9)這個數組的對應數字，而對于其他不是"A"的項目，對應的就是99，具體來看，這里的if的結果就是{1;99;99;4;99;99;99;99;9}，可以發現，這個數組里不是99的只有3個，1,、4和9，而這三個數字對應在$B$3:$B$11這個范圍里，正好就是"A"對應的位置，為了依次得到這三個數字，就需要small這個函數了，因為公式需要橫向拉動，所以用了column作為small的第二參數。到這一步，index的結果就不需要多講了。

通過這個思路的分析，需要特別注意的幾個地方就是數組維度的對應，也就是if里面的第一參數這個條件數組不需要多說，第二個參數的row包含的個數應該和條件范圍是一樣多的（并且應該絕對引用），第三參數這個在本例來說用10都可以，因為數據源只有9個，如果多的話可以用9^9（9的9次方=387420489）這個應該是足夠大了。當然在if里面用column效果也是一樣的，只不過row看起來簡潔一點，千萬不要和small的第二參數混淆了。

思路解釋起來是很繞口的，更好的方法是模擬一個少一點的數據源，不超過10行的都可以，然后使用公式求值或者f9功能一步一步看看公式結果是怎么變化的，從而去了解公式的計算過程。

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

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

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

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

其實這個例子完全可以不用數組公式來處理，因為和例一最重要的一個差別就是a列的重復值是連續的，可以看做例一的特殊情況。把上面這個公式下拉多行會發現有亂碼出現，如果要排錯的話，還是可以用iferror，當然也可以用if+countif來實現，排錯公式如下（都是數組哦）：=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來排錯看起來是長一點，但是這個思路需要明白，關鍵就是row和countif的值做比較，而這個應用也是很有用的，下面就來看公式模型二，這個公式不需要數組哦：=IF(ROW(A1)>COUNTIF(A:A,D$3),"",OFFSET(B$2,MATCH(D$3,A:A,0)-3+ROW(A1),))其實這個公式的核心在于offset的作用了，尤其是offset的第二個參數，使用了match和row來共同得出行的偏移量，如果明白了offset在這里的作用，那么公式模型二也就沒什么難以理解的了。

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

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

圖：

輔助列的公式為，A3：=COUNTIF($B$3:B3,$E$3)，結果列公式為，F3：=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$19,3,0),"")無需排序，無需數組，會用vlookup就能解決這類問題，推薦初學者學習掌握。

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

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

當然，對于發燒友級別的朋友來說，還有其他的思路，不過相對就很難理解了，這里不去一一介紹了，能夠理解并且能夠套用上面說的一個或者幾個公式模型就已經很不錯了，由于水平有限，以上解釋中難免有不足之處，對于發現的問題希望各位表友可以予以指正，這里不勝感激。同時對于解釋不甚理解的朋友也可以加入討論群進行詢問

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。