條件值位置不固定,可能在A列也可能在B列,該如何查找?
?
作者:老菜鳥來源:部落窩教育發(fā)布時(shí)間:2023-02-13 13:44:21點(diǎn)擊:757
編按:
有一種比較特別的數(shù)據(jù)查找,條件值(如人名)的位置不固定在某列,而是可能存在A、B、C等列中的任何一列中,也就是A、B、C列的數(shù)據(jù)是并列的,都是人名;那如何在并列的多列數(shù)據(jù)中查到條件值并返回對應(yīng)的需要值呢?很顯然不適合直接用VLOOKUP函數(shù)——因?yàn)樗髼l件值必須位于查找區(qū)域的首列。今天就來給大家分享幾個(gè)函數(shù)公式。
在群里看到一個(gè)數(shù)據(jù)匹配的問題覺得挺有趣,將數(shù)據(jù)模擬了一下,如圖所示,要根據(jù)人物名稱匹配出對應(yīng)的書名。
類似的還有根據(jù)員工姓名匹配部門、根據(jù)配件名稱匹配對應(yīng)的成品等等,都是在并列的多列數(shù)據(jù)中查找某個(gè)值再返回對應(yīng)的值。
下面就來具體分析一下解決這類問題的常用思路。
常規(guī)數(shù)據(jù)匹配思路:
首先要明確的是目標(biāo)結(jié)果(書名)所在位置是A2:A5這個(gè)單元格區(qū)域,只要能夠確定是這個(gè)區(qū)域的第幾行,就能得到所需結(jié)果。
而要確定目標(biāo)在第幾行,就需要根據(jù)提供的人物與B2:J5這個(gè)區(qū)域中的單元格去比較,這也是解決問題的最核心所在。
這一步有兩個(gè)公式可以實(shí)現(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)輸入。
兩個(gè)公式中都用到了$B$2:$J$5=A8,這個(gè)等式會得到一組邏輯值,人物所對應(yīng)的具體單元格會返回TRUE。
兩個(gè)公式的另外一個(gè)共同點(diǎn)就是ROW($A$1:$A$4),這部分相當(dāng)于常量數(shù)組{1;2;3;4}。
公式1是利用SUMPRODUCT計(jì)算出一組邏輯值和數(shù)組的乘積之和,最終得到符合條件的姓名所在的行號。
關(guān)于SUMPRODUCT函數(shù)的詳細(xì)示例參考教程:Excel的求和函數(shù)之王!
公式2則是利用IF函數(shù)返回?cái)?shù)據(jù),其中TRUE所對應(yīng)的是一個(gè)數(shù)字。
再用MAX得到這一組數(shù)據(jù)中的最大值,從而得到姓名所在的行號。
要理解這個(gè)思路需要對數(shù)組有一定的認(rèn)識才行,關(guān)鍵在于公式中IF函數(shù)的第一參數(shù)是一組邏輯值而不是一個(gè)邏輯值,所以IF函數(shù)返回的結(jié)果也是一組數(shù)據(jù),必須借助MAX得到需要的結(jié)果。
理解了以上兩個(gè)公式,其實(shí)要解決問題就非常容易了,使用INDEX、OFFSET和INDIRECT函數(shù)都可以得到正確結(jié)果。
以下采用公式1的進(jìn)行介紹,有興趣的同學(xué)可以自己替換公式2去研究一下。
INDEX函數(shù)解法:
公式為:=INDEX($A$2:$A$5,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)))
INDEX根據(jù)SUMPRODUCT得到的行號,在目標(biāo)區(qū)域中得到對應(yīng)的書名。
OFFSET函數(shù)解法:
公式為:=OFFSET($A$1,SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$1:$A$4)),)
OFFSET以A1單元格為基點(diǎn),按照SUMPRODUCT得到的行數(shù)向下偏移,就得到了對應(yīng)的書名。
INDIRECT函數(shù)解法:
公式為:=INDIRECT("A"&SUMPRODUCT(($B$2:$J$5=A8)*ROW($A$2:$A$5)))
注意這個(gè)公式中的ROW($A$2:$A$5)和前幾個(gè)公式稍有區(qū)別,因?yàn)?span>INDIRECT需要的是表格中的行號,而不是目標(biāo)區(qū)域中的行號,所以這里用的$A$2:$A$5而不是$A$1:$A$4。
以上分享的這些公式都是常規(guī)數(shù)據(jù)匹配思路,最后再來分享兩個(gè)不一樣的思路,分別用到了合并函數(shù)CONCAT函數(shù)和TEXTJOIN函數(shù),并且都是數(shù)組公式,需要三鍵(Ctrl、shift、enter)輸入。
如果你的Excel或WPS也有這兩個(gè)函數(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,""))
這兩個(gè)公式的本質(zhì)都是一樣的,利用IF函數(shù)返回一組文本,只有一個(gè)是書名,其他都是空值。
將這一組文本合并后就得到所需要的結(jié)果。
以上就是今天要分享的全部內(nèi)容,不知道你學(xué)會了多少,留一個(gè)自測題吧。
如果大家知道答案的話,可以在評論區(qū)留言告訴我們。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
三種常用的核對數(shù)據(jù)方法,到底哪一種才是你的菜?
再因?yàn)楹藢?shù)據(jù)而加班,買塊豆腐吧!難道12種方法不夠你用?!
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!