10種職場人最常用的excel多條件查找方法?。ńㄗh收藏)
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2020-03-02 09:51:01點擊:12473
編按:
哈嘍,大家好!條件查找是我們工作中比較常見的技巧,但是說到多條件查找,很多同學可能會愣住,該用什么函數(shù)呢?比較熟悉的VLOOKUP,它的基礎(chǔ)用法好像也只適用于單條件查找。別急,今天老菜鳥為大家總結(jié)了10種職場人士最常見的多條件查找的方法,趕緊來看看吧!
對于Excel中多條件匹配的問題,永遠是一個熱點話題,而根據(jù)匹配結(jié)果是數(shù)字還是非數(shù)字的時候,有些查找方法又不能通用,今天就對多條件匹配的常見方法做一次梳理,并對每種方法的適用情況做出說明,希望對大家的工作能有所幫助。
先來看看今天用到的案例:
需要從數(shù)據(jù)源(A-D列)中按照機構(gòu)和姓名分別匹配出職級(非數(shù)字內(nèi)容)和基本工資(數(shù)字內(nèi)容),以下就結(jié)合本案例來介紹多種常用的公式套路。
一、SUMIFS函數(shù)實現(xiàn)多條件匹配
SUMIFS函數(shù)本來是一個多條件求和的函數(shù),格式為:
SUMIFS(求和列,條件列1,條件1,條件列2,條件2……)
但是當同時滿足多個條件的結(jié)果只有一個時,多條件求和就等同于多條件匹配。
公式為:=SUMIFS(D:D,A:A,F2,B:B,G2)
要引用的列就是實際求和列,本例中是D列,而條件列分別就是A列和B列。
優(yōu)勢:使用SUMIFS多條件匹配時,比較方便高效,公式簡單易用,再增加條件也比較容易。
不足:當要匹配的結(jié)果不是數(shù)字時,就不能使用這個方法了,同時當滿足多個條件的數(shù)據(jù)不是唯一值時,結(jié)果可能錯誤。
推薦指數(shù):★★★★
二、SUMPRODUCT函數(shù)實現(xiàn)多條件匹配
SUMPRODUCT函數(shù)的功能是計算多個數(shù)組或區(qū)域的數(shù)據(jù)乘積之和,利用比較運算和邏輯值的特性,也可以實現(xiàn)多條件求和的功能,用來處理多條件匹配,其實和SUMIFS的原理差不多,公式為:
=SUMPRODUCT(($A$2:$A$15=F2)*($B$2:$B$15=G2)*$D$2:$D$15)
鑒于之前有多篇教程分析過SUMPRODUCT的用法和原理,這里就不對這個公式過多闡述了。不熟悉的同學可以查看往期教程《加了*的 SUMPRODUCT函數(shù)無所不能》
優(yōu)勢:再增加條件時比較容易。
不足:數(shù)組計算,數(shù)據(jù)量大時比較卡,對函數(shù)的用法不理解容易出錯,并只對數(shù)字的匹配結(jié)果適用。
推薦指數(shù):★★★
三、輔助列+VLOOKUP函數(shù)實現(xiàn)多條件匹配
VLOOKUP函數(shù)本來是按單條件匹配的,這個函數(shù)很多朋友都應(yīng)該熟悉,之前也多次分享過相關(guān)教程。
這里簡單說一下函數(shù)的結(jié)構(gòu):
VLOOKUP(查找值,查找區(qū)域,第幾列找,精確查找)
本例中的公式為:=VLOOKUP(G2&"-"&H2,$A$1:$E$15,4,0)
在這個公式中,查找條件不是一個單元格,而是使用&將機構(gòu)和姓名兩個條件做了連接G2&"-"&H2。
在連接兩個條件時,建議在中間添加一個分隔符,這在某些情況下可以避免出現(xiàn)錯誤。
因為我們使用了自己構(gòu)造的條件,而這個條件在數(shù)據(jù)源中并不存在,因此需要在數(shù)據(jù)源的最左側(cè)添加一個輔助列,如上圖中A列所示。
輔助列的公式為:=B2&"-"&C2
優(yōu)勢:公式對于匹配結(jié)果為數(shù)字或者非數(shù)字的時候都可以使用,適用面比SUMIFS更廣泛。
不足:要增加輔助列才能使用,雖然難度指數(shù)降低了,但是會略顯繁瑣。
推薦指數(shù):★★★★
四、VLOOKUP+IF函數(shù)實現(xiàn)多條件匹配
這是VLOOKUP較有難度的一種套路,公式為:
=VLOOKUP(F2&G2,IF({1,0},$A$2:$A$15&$B$2:$B$15,$C$2:$C$15),2,0)
對于這個公式套路,關(guān)鍵是IF這部分,篇幅所限,不對公式原理做過多解釋,只要掌握IF函數(shù)的第二參數(shù)是將數(shù)據(jù)源中的兩列進行合并,第三參數(shù)是數(shù)據(jù)源中需要匹配結(jié)果的列,同時公式需要按ctrl+shift+enter鍵完成輸入,遇到問題能夠套用就行了。
優(yōu)勢:不用輔助列,一步到位。
不足:不適于新手使用,數(shù)據(jù)量較大時計算緩慢。
推薦指數(shù):★★★
五、VLOOKUP+CHOOSE函數(shù)實現(xiàn)多條件匹配
這是將前一個公式中的IF換成了CHOOSE函數(shù),公式為:
=VLOOKUP(F2&G2,CHOOSE({1,2},$A$2:$A$15&$B$2:$B$15,$C$2:$C$15),2,0)
這個公式套路的核心是CHOOSE,同樣是構(gòu)造了一個合并后的查找列,對于具體原理,需要了解的可以留言,一般能夠套用公式解決問題就足夠了,這個公式同樣需要三鍵輸入。
優(yōu)勢:不用輔助列,一步到位。
不足:不適于新手使用,數(shù)據(jù)量較大時計算緩慢。
推薦指數(shù):★★★
六、LOOKUP函數(shù)實現(xiàn)多條件匹配
在常用的引用函數(shù)里,LOOKUP無疑是最受高手喜歡的一個函數(shù)了,這個函數(shù)充滿了很多套路,例如多條件匹配時,公式套路就是:
=LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),結(jié)果區(qū)域)
就本例而言,公式為:
=LOOKUP(1,0/(($A$2:$A$15=F2)*($B$2:$B$15=G2)),$C$2:$C$15)
關(guān)于LOOKUP,之前同樣分享過很多教程,需要理解原理的同學,可以查看往期教程《VLOOKUP&LOOKUP雙雄戰(zhàn)(五):野馬崛起!》,一般來說,能夠按套路套用公式解決問題就足夠了。
優(yōu)勢:不使用輔助列一步到位,增加條件也方便,適用場合廣泛,不管數(shù)字還是文本都可以使用這個套路。
不足:會讓新手有一點畏懼心態(tài)。
推薦指數(shù):★★★★★
七、更多實現(xiàn)多條件匹配的方法
就公式而言,除了上面分享的五種套路,其實還有一些,這里只列舉公式套路和適用條件,其它的就不一一舉例了,例如:
=MAX((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)*結(jié)果區(qū)域),這個公式是一個數(shù)組公式,只適合結(jié)果為數(shù)字的情況。
=INDEX(結(jié)果區(qū)域,MATCH(條件1&條件2,條件區(qū)域1&條件區(qū)域2,0)),數(shù)組公式,量大時比較卡。
=XLOOKUP(條件1&條件2,條件區(qū)域1&條件區(qū)域2,結(jié)果區(qū)域),新函數(shù),只有office365版本可以用。
=FILTER(結(jié)果區(qū)域,(條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),新函數(shù),只有office365版本可以用。
小結(jié):對于多條件匹配問題,推薦使用第1、3和6三種方法,其實如果不用公式的話,數(shù)據(jù)透視表和高級篩選是更好的選擇,這些方法會在今后的教程中分享給大家。
本文配套的練習課件請加入QQ群:747953401下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:
相關(guān)推薦:
使用MAX按條件查找《查詢界黑馬——MAX,竟讓查詢之王VLOOKUP也甘拜下風?》
SUMPRODUCT的使用《加了*的 SUMPRODUCT函數(shù)無所不能》
CHOOSE函數(shù)解析《請也為Excel中的《無名之輩》choose獻上一份理解!》
多條件查找《VLOOKUP&LOOKUP雙雄戰(zhàn)(五):野馬崛起!》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!