8個(gè)案例,全面解讀SUMIF函數(shù)的查找匹配功能
?
作者:逍遙來(lái)源:部落窩教育發(fā)布時(shí)間:2023-04-12 16:21:48點(diǎn)擊:2092
編按:
SUMIF函數(shù)作為一個(gè)條件求和函數(shù),竟然還能實(shí)現(xiàn)諸多的查找功能?確實(shí)!如果查找的是數(shù)字,不管是多表查找還是逆向查找、橫向查找,SUMIF都是最快的。下面我們介紹8種SUMIF查找運(yùn)用,全面解讀SUMIF的查找功能。
查找函數(shù),多年來(lái),VLOOKUP一直獨(dú)領(lǐng)風(fēng)騷。
人紅脾氣大,比如常讓人詬病的:不能逆向查找;不能表里不一;臉盲癥中度患者;中二癥重度患者等等。
有沒(méi)有函數(shù)能治治VLOOKUP這些臭毛病呢?
還真有!他就是SUMIF函數(shù)。
SUMIF用于查找,其函數(shù)結(jié)構(gòu)也只有區(qū)區(qū)三個(gè)參數(shù)。
=SUMIF(條件區(qū)域,指定的條件,查找區(qū)域)
千萬(wàn)不要小看這三個(gè)參數(shù),以為他解決不了復(fù)雜的問(wèn)題。
和那些愛(ài)嵌套的函數(shù)(對(duì),VLOOKUP,說(shuō)的就是你)相比,SUMIF崇尚極簡(jiǎn)主義,一不愛(ài)找函數(shù)幫忙,二不愛(ài)省略參數(shù),一貫的作風(fēng)就是將復(fù)雜問(wèn)題簡(jiǎn)單化。
1. SUMIF可以在多張表中查找數(shù)字
在多張表中查找數(shù)字用什么函數(shù)最快?當(dāng)然是SUMIF函數(shù)!如下圖所示,當(dāng)我們需要查詢多個(gè)表格里的數(shù)據(jù),比如工號(hào)為1003、1004、1011、1012的考試得分,但這些員工分別在兩個(gè)表中。
如果要用VLOOKUP來(lái)寫公式的話,又得尋求其他函數(shù)的幫助了。
與其說(shuō)他是查找之王,不如說(shuō)他是交際之花。
VLOOKUP:各位大哥,行行好~
眾函數(shù):你怎么又來(lái)了!
函數(shù)公式:=IFERROR(VLOOKUP(I2,$A$2:$C$9,3,0),VLOOKUP(I2,$E$2:$G$9,3,0))
這里是用兩個(gè)VLOOKUP函數(shù)的查找結(jié)果來(lái)作為IFERROR的兩個(gè)參數(shù),如果不懂這個(gè)函數(shù)的同學(xué)可以看我們的IFERROR專題文章。
用SUMIF來(lái)解決這個(gè)問(wèn)題就簡(jiǎn)明扼要多了,什么嵌套,什么求助,通通沒(méi)有!小爺我遇神殺神,遇佛殺佛。
函數(shù)公式 =SUMIF(A$2:E$9,I2,C$2:G$9)
注:多張表必須位于同一張工作表中。
2.SUMIF可以輕松實(shí)現(xiàn)逆向查找數(shù)字
VLOOKUP默認(rèn)從左向右查詢,如果要用VLOOKUP返回左邊列的效果,有些“高手”習(xí)慣用IF({0,1},,)這種句式去重構(gòu)數(shù)組,確實(shí)是解決了一時(shí)的問(wèn)題,但在我看來(lái),往這個(gè)方向走,你會(huì)很容易“走火入魔”,發(fā)展受限。
函數(shù)公式:
=VLOOKUP(E2,IF({1,0},B$2:B$12,A$2:A$12),2,0)
用SUMIF來(lái)解決這個(gè)問(wèn)題,輕而易舉。
還是平平無(wú)奇的三個(gè)參數(shù),條件區(qū)域、查找區(qū)域、條件,挨個(gè)往上套即可。
函數(shù)公式:
=SUMIF(B$2:B$12,E2,A$2:A$12)
3.SUMIF可以進(jìn)行橫向查找數(shù)字
VLOOKUP不支持橫向查詢,如果你非得指名道姓要他,那VLOOKUP也只得請(qǐng)出自己的兄弟HLOOKUP函數(shù)。
這是一個(gè)專門用于橫向查詢的函數(shù),其參數(shù)如下:
要查找的值,要查找的區(qū)域,返回第幾行,模糊匹配/精確匹配函數(shù)公式:=HLOOKUP(B7,$B1:$L3,3,0)
橫向查詢對(duì)于SUMIF來(lái)說(shuō),依然是小菜一碟,依葫蘆畫瓢即可。
函數(shù)公式=SUMIF($B1:$L1,B7,$B3:$L3)
4.條件區(qū)域和求和區(qū)域位于多行SUMIF也可以查找數(shù)字
當(dāng)條件區(qū)域和求和區(qū)域位于多行,怎么辦?就算是HLOOKUP想必也傻眼了吧,但是這種級(jí)別的問(wèn)題,根本難不倒SUMIF。
函數(shù)公式 =SUMIF($B1:$G5,B10,$B2:$G6)
這個(gè)功能其實(shí)是運(yùn)用了SUMIF的錯(cuò)行求和的原理,因?yàn)槊總€(gè)業(yè)務(wù)人員名單不重復(fù),所以求和即是查找。
當(dāng)條件區(qū)域和求和區(qū)域是多行多列時(shí),SUMIF會(huì)依次去判斷條件區(qū)域是否滿足條件,如果滿足,則將查找區(qū)域的數(shù)據(jù)提取出來(lái),得到結(jié)果。
5.被查找值與條件值數(shù)據(jù)格式不一樣也能用SUMIF查找
使用VLOOKUP進(jìn)行查找時(shí),講究的是表里如一,除開(kāi)大小寫區(qū)分外必須嚴(yán)格相同。這就導(dǎo)致他經(jīng)常在查找上出錯(cuò),相同的數(shù)據(jù),只因一個(gè)是文本,一個(gè)是數(shù)值,那結(jié)果必然就是錯(cuò)誤!
SUIMF顯然沒(méi)有這些窮講究,只要條件符合,就能立馬匹配到數(shù)據(jù)。
函數(shù)公式:=SUMIF(A1:A12,E3,C1:C12)
條件區(qū)域是A1到A12,條件值是E3,要返回的區(qū)域是C1到C12。
即我們?nèi)?span>A1到A12的數(shù)據(jù)區(qū)域查詢E3這個(gè)值,然后返回對(duì)應(yīng)的C1到C12里的某個(gè)值。
6.SUMIF查找不會(huì)返回錯(cuò)誤值,表格更美觀
VLOOKUP查找不到就會(huì)立刻返回錯(cuò)誤值,他才不會(huì)管大量的錯(cuò)誤值堆積,有沒(méi)有影響到表容表貌。
如果你想要屏蔽掉錯(cuò)誤值,那還得用IFERROR函數(shù)來(lái)幫幫忙。
函數(shù)公式=IFERROR(VLOOKUP(E2,$A$1:$C$12,3,0),"")
SUMIF對(duì)待錯(cuò)誤值顯然溫和了很多,查找不到,會(huì)返回0,這讓整張表看上去整潔了不少。
函數(shù)公式=SUMIF(A$1:A$12,E2,C$1:C$12)
7.SUMIF可以查找多個(gè)值并求和
現(xiàn)在我們要根據(jù)學(xué)歷、職稱、技能等級(jí)查找得分,然后求3個(gè)得分之和。
比如王虹青,學(xué)歷本科加2分,中級(jí)工程師加2分,中級(jí)技能加2分,一共6分,怎么做?
使用VLOOKUP函數(shù)的話,我們需要查找3次,最后再來(lái)套一個(gè)SUM函數(shù)。
函數(shù)公式:
=SUM(IFERROR(VLOOKUP(B2,$B$11:$C$14,2,0),0),IFERROR(VLOOKUP(C2,$B$15:$C$16,2,0),0),IFERROR(VLOOKUP(D2,$B$17:$C$19,2,0),0))
公式原理也不難,就是像疊羅漢一樣,3個(gè)VLOOKUP層層嵌套,用IFERROR函數(shù)將查找不到的值返回為0,最后用sum函數(shù)來(lái)求和。
這個(gè)函數(shù)沒(méi)有問(wèn)題,但實(shí)在是太長(zhǎng)了,就像老太娘的裹腳布。
好巧不巧,這個(gè)問(wèn)題,SUMIF也能行,比如我們要查找王虹青在學(xué)歷這一欄的得分。
函數(shù)公式=SUMIF($B$11:$B$14,B2,$C$11:$C$14)
那我們是不是又要嵌套3個(gè)SUMIF呢?
答案肯定不是啊!
什么檔次?和VLOOKUP一樣?那不是往SUMIF臉上打嗎?
直接修改SUMIF的第二參數(shù),將條件B2修改成$B2:$D2,即可一次性查找全部。
=SUMIF($B$11:$B$19,$B2:$D2,$C$11:$C$19)
注意:不是Office365版本的朋友,公式輸完后不能直接回車,否則得到的結(jié)果是錯(cuò)的。如果要查看運(yùn)算結(jié)果,可以在編輯欄選中整個(gè)公式,按F9鍵,3個(gè)結(jié)果分別是{2,2,2}。再按Ctrl+Z返回。
要獲取這3個(gè)結(jié)果之和,外套一個(gè)SUM即可搞定,因?yàn)槭菙?shù)組公式,需要按Ctrl+Shift+Enter三鍵結(jié)束。
=SUM(SUMIF($B$11:$B$19,$B2:$D2,$C$11:$C$19))
這個(gè)公式很好地詮釋了一個(gè)道理:條條大路通羅馬,但有的人一出生就在羅馬。
8.SUMIF可以進(jìn)行模糊查找
上面這些示例,其實(shí)很好地說(shuō)明了一個(gè)問(wèn)題:
SUMIF的第二參數(shù)是三個(gè)參數(shù)中唯一的變量,他可以是單個(gè)條件,也可以是多個(gè)條件。
他還可以與通配符使用,達(dá)到模糊查找的功能,這一點(diǎn)與VLOOKUP不分高低。
如下圖所示,我們要查找這些業(yè)務(wù)員的考核得分,在條件區(qū)域中,有些人的名字是連在一起的。
可以用通配符*與連接符&來(lái)完成這個(gè)公式。
函數(shù)公式=SUMIF(A$2:A$9,"*"&D2&"*",B$2:B$9)
SUMIF簡(jiǎn)單三把斧,走遍天下無(wú)敵手。
有人可能會(huì)問(wèn),既然SUMIF那么好用,那為什么聲名赫赫的卻是VLOOKUP呢?
難道他有后臺(tái)?
答案是: SUMIF雖好用,卻有兩個(gè)限制條件,這兩個(gè)限制條件會(huì)讓SUMIF的查找功能大打折扣。
限制1:結(jié)果必須為數(shù)字
上面演示的案例,它們的結(jié)果都是數(shù)值。當(dāng)且僅當(dāng)查詢的結(jié)果是數(shù)值的時(shí)候,才可以使用 sumif進(jìn)行查詢,因?yàn)樗举|(zhì)上是一個(gè)求和函數(shù)。
限制2:查找值不允許存在重復(fù)
利用sumif進(jìn)行數(shù)據(jù)查詢,如果查找值存在重復(fù),sumif函數(shù)就會(huì)把它們加在一起,因?yàn)樗举|(zhì)上是一個(gè)求和函數(shù)。
求和的本質(zhì),限制著查找功能的使用。
這就好比上天入地?zé)o所不能的孫悟空,頭上永遠(yuǎn)戴著一個(gè)緊箍咒一樣。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
10種職場(chǎng)人最常用的excel多條件查找方法?。ńㄗh收藏)
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者逍遙;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長(zhǎng)像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒(méi)有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問(wèn)題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說(shuō)是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!
最新教程
- SHEETSNAME,一鍵獲得所有工作表名稱完成目錄制作
- 延遲退休落地,快速查詢你上幾年才退休!
- 快速將PDF電子發(fā)票數(shù)據(jù)提取到發(fā)票明細(xì)登記表的方法
- 提取、查找、替換數(shù)據(jù)的王者——REGEXP正則函數(shù)
- Excel表10萬(wàn)行數(shù)據(jù),篩選卡頓怎么辦?
- 兩個(gè)不用嵌套的萬(wàn)能提取公式,含用REGEXP函數(shù)提取
- 用Excel制作刷題器,備考無(wú)憂 !
- 如何用LET+LAMBDA直接寫遞歸公式?
- 用函數(shù)合并多個(gè)工作表數(shù)據(jù)
- 用LAMBDA自定義顏色求和函數(shù)