|
SUMIF函數(shù)作為一個條件求和函數(shù)，竟然還能實現(xiàn)諸多的查找功能？ 不管是多表查找還是逆向查找、橫向查找，SUMIF都能勝任，全面秒殺VLOOKUP函數(shù)！ 下面我們介紹8種SUMIF查找運用，全面解讀SUMIF的查找功能。 SUMIF用于查找，其函數(shù)結(jié)構(gòu)也只有區(qū)區(qū)三個參數(shù)。 =SUMIF(條件區(qū)域,指定的條件,查找區(qū)域) 千萬不要小看這三個參數(shù)，以為他解決不了復(fù)雜的問題。 和那些愛嵌套的函數(shù)（對，VLOOKUP，說的就是你）相比，SUMIF崇尚極簡主義，一不愛找函數(shù)幫忙，二不愛省略參數(shù)，一貫的作風就是將復(fù)雜問題簡單化。 1. SUMIF可以在多張表中查找數(shù)字 如下圖所示，當我們需要查詢多個表格里的數(shù)據(jù)，比如工號為1003、1004、1011、1012的員工姓名，但這些員工姓名分別在兩個表中。
如果要用VLOOKUP來寫公式的話，又得尋求其他函數(shù)的幫助了。 與其說他是查找之王，不如說他是交際之花。 VLOOKUP：各位大哥，行行好~ 眾函數(shù)：你怎么又來了！ 掃碼加入，領(lǐng)取今日課件 函數(shù)公式： =IFERROR(VLOOKUP(I2,$A$2:$C$9,3,0),VLOOKUP(I2,$E$2:$G$9,3,0)) 這里是用兩個VLOOKUP函數(shù)的查找結(jié)果來作為IFERROR的兩個參數(shù)，如果不懂這個函數(shù)的同學(xué)可以看我們的IFERROR專題文章。 用SUMIF來解決這個問題就簡明扼要多了，什么嵌套，什么求助，通通沒有！小爺我遇神殺神，遇佛殺佛。 函數(shù)公式 =SUMIF(A$2:E$9,I2,C$2:G$9)
注：多張表必須位于同一張工作表中。 2.SUMIF可以輕松實現(xiàn)逆向查找數(shù)字 VLOOKUP默認從左向右查詢，如果要用VLOOKUP返回左邊列的效果，有些“高手”習(xí)慣用IF({0,1},,)這種句式去重構(gòu)數(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è)務(wù)人員名單不重復(fù)，所以求和即是查找。 當條件區(qū)域和求和區(qū)域是多行多列時，SUMIF會依次去判斷條件區(qū)域是否滿足條件，如果滿足，則將查找區(qū)域的數(shù)據(jù)提取出來，得到結(jié)果。 5.被查找值與條件值數(shù)據(jù)格式不一樣也能用SUMIF查找 使用VLOOKUP進行查找時，講究的是表里如一，除開大小寫區(qū)分外必須嚴格相同。這就導(dǎo)致他經(jīng)常在查找上出錯，相同的數(shù)據(jù)，只因一個是文本，一個是數(shù)值，那結(jié)果必然就是錯誤！ SUIMF顯然沒有這些窮講究，只要條件符合，就能立馬匹配到數(shù)據(jù)。 函數(shù)公式：=SUMIF(A1:A12,E3,C1:C12) 條件區(qū)域是A1到A12，條件值是E3，要返回的區(qū)域是C1到C12。 即我們?nèi)1到A12的數(shù)據(jù)區(qū)域查詢E3這個值，然后返回對應(yīng)的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ù)學(xué)歷、職稱、技能等級查找得分，然后求3個得分之和。 比如王虹青，學(xué)歷本科加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也能行，比如我們要查找王虹青在學(xué)歷這一欄的得分。 函數(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) 要獲取這3個結(jié)果之和，外套一個SUM即可搞定，因為是數(shù)組公式，需要按Ctrl+Shift+Enter三鍵結(jié)束。 =SUM(SUMIF($B$11:$B$19,$B2:$D2,$C$11:$C$19))
這個公式很好地詮釋了一個道理：條條大路通羅馬，但有的人一出生就在羅馬。 8.SUMIF可以進行模糊查找 上面這些示例，其實很好地說明了一個問題： SUMIF的第二參數(shù)是三個參數(shù)中唯一的變量，他可以是單個條件，也可以是多個條件。 他還可以與通配符使用，達到模糊查找的功能，這一點與VLOOKUP不分高低。 如下圖所示，我們要查找這些業(yè)務(wù)員的考核得分，在條件區(qū)域中，有些人的名字是連在一起的。 可以用通配符*與連接符&來完成這個公式。 函數(shù)公式=SUMIF(A$2:A$9,"*"&D2&"*",B$2:B$9)
SUMIF簡單三把斧，走遍天下無敵手。 有人可能會問，既然SUMIF那么好用，那為什么聲名赫赫的卻是VLOOKUP呢？ 難道他有后臺？ 答案是：SUMIF雖好用，卻有兩個限制條件，這兩個限制條件會讓SUMIF的查找功能大打折扣。 限制1：結(jié)果必須為數(shù)字 上面演示的案例，它們的結(jié)果都是數(shù)值。當且僅當查詢的結(jié)果是數(shù)值的時候，才可以使用 sumif進行查詢，因為它本質(zhì)上是一個求和函數(shù)。 限制2：:查找值不允許存在重復(fù) 利用sumif進行數(shù)據(jù)查詢，如果查找值存在重復(fù)，sumif函數(shù)就會把它們加在一起，因為它本質(zhì)上是一個求和函數(shù)。 求和的本質(zhì)，限制著查找功能的使用。 這就好比上天入地無所不能的孫悟空，頭上永遠戴著一個緊箍咒一樣。 好的，以上就是今天的所有內(nèi)容啦！感謝大家的觀看~~ 《7天Excel訓(xùn)練營》，視頻+筆記+模板+答疑，掃碼加入！ |
|
|
