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