|
哈嘍，小伙伴們，你們好呀~~ 在群里看到一個數(shù)據(jù)匹配的問題覺得挺有趣，如圖所示，根據(jù)人物名稱匹配出對應的書名。
條件值（如人名）的位置不固定在某列，而是可能存在A、B、C等列中的任何一列中，也就是A、B、C列的數(shù)據(jù)是并列的，都是人名； 那如何在并列的多列數(shù)據(jù)中查到條件值并返回對應的需要值呢？ 很顯然，這個問題，直接用VLOOKUP肯定是不行的——因為它要求條件值必須位于查找區(qū)域的首列。 今天就來給大家分享幾個函數(shù)公式。 1、常規(guī)數(shù)據(jù)匹配思路： 首先要明確的是目標結(jié)果（書名）所在位置是A2:A5這個單元格區(qū)域，只要能夠確定是這個區(qū)域的第幾行，就能得到所需結(jié)果。 而要確定目標在第幾行，就需要根據(jù)提供的人物與B2:J5這個區(qū)域中的單元格去比較，這也是解決問題的最核心所在。 這一步有兩個公式可以實現(xiàn)： 公式1 =SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4))
公式2 =MAX(IF($B$2:$J$5=A8,ROW($A$1:$A$4))) 數(shù)組公式，需要三鍵（Ctrl、shift、enter）輸入。
兩個公式中都用到了$B$2:$J$5=A8，這個等式會得到一組邏輯值，人物所對應的具體單元格會返回TRUE。
兩個公式的另外一個共同點就是ROW($A$1:$A$4)，這部分相當于常量數(shù)組{1;2;3;4}。 公式1是利用SUMPRODUCT計算出一組邏輯值和數(shù)組的乘積之和，最終得到符合條件的姓名所在的行號。 關(guān)于SUMPRODUCT函數(shù)的詳細示例參考教程：Excel的求和函數(shù)之王！ 公式2則是利用IF函數(shù)返回數(shù)據(jù)，其中TRUE所對應的是一個數(shù)字。
再用MAX得到這一組數(shù)據(jù)中的最大值，從而得到姓名所在的行號。 要理解這個思路需要對數(shù)組有一定的認識才行，關(guān)鍵在于公式中IF函數(shù)的第一參數(shù)是一組邏輯值而不是一個邏輯值，所以IF函數(shù)返回的結(jié)果也是一組數(shù)據(jù)，必須借助MAX得到需要的結(jié)果。 理解了以上兩個公式，其實要解決問題就非常容易了，使用INDEX、OFFSET和INDIRECT函數(shù)都可以得到正確結(jié)果。 以下采用公式1的進行介紹，有興趣的同學可以自己替換公式2去研究一下。 INDEX函數(shù)解法： 公式為： =INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)))
INDEX根據(jù)SUMPRODUCT得到的行號，在目標區(qū)域中得到對應的書名。 OFFSET函數(shù)解法： 公式為： =OFFSET($A$1,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)),)
OFFSET以A1單元格為基點，按照SUMPRODUCT得到的行數(shù)向下偏移，就得到了對應的書名。 INDIRECT函數(shù)解法： 公式為： =INDIRECT("A"&SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$2:$A$5)))
注意這個公式中的ROW($A$2:$A$5)和前幾個公式稍有區(qū)別，因為INDIRECT需要的是表格中的行號，而不是目標區(qū)域中的行號，所以這里用的$A$2:$A$5而不是$A$1:$A$4。 以上分享的這些公式都是常規(guī)數(shù)據(jù)匹配思路。 2、兩個非常規(guī)的思路 分別用到了合并函數(shù)CONCAT函數(shù)和TEXTJOIN函數(shù)，并且都是數(shù)組公式，需要三鍵（Ctrl、shift、enter）輸入。 如果你的Excel或WPS也有這兩個函數(shù)的話，可以研究一下了。 CONCAT函數(shù)解法： 公式為：=CONCAT(IF($B$2:$J$5=A8,$A$2:$A$5,""))
TEXTJOIN函數(shù)解法： 公式為：=TEXTJOIN("",,IF($B$2:$J$5=A8,$A$2:$A$5,""))
這兩個公式的本質(zhì)都是一樣的，利用IF函數(shù)返回一組文本，只有一個是書名，其他都是空值。
將這一組文本合并后就得到所需要的結(jié)果。 以上就是今天要分享的全部內(nèi)容，不知道你學會了多少，下面再來給大家留一個自測題吧。
如果是這個樣式的查找，又應該如何來寫公式呢？知道答案的同學，可以在評論區(qū)留言告訴我們喲~~ |
|
|
