全面解析通配符在SUMIF中的應(yīng)用
?
作者:老菜鳥來(lái)源:部落窩教育發(fā)布時(shí)間:2021-03-11 11:20:27點(diǎn)擊:6802
編按:
通配符是所有模糊查找必不可少的存在,不同的通配符搭配不同的使用方法,可以有無(wú)限的可能。在眾多的函數(shù)中,SUMIF函數(shù)作為一個(gè)按條件求和的函數(shù),也常常需要各式通配符的助力,發(fā)揮出它更強(qiáng)大的實(shí)力……
在開始今天的話題之前,先聊聊什么是通配符。
通配符是EXCEL中一個(gè)常用的輔助符號(hào),通配符只有3種:
① 問(wèn)號(hào)——?,表示任意一個(gè)字符。
② 星號(hào)——*,表示任意多個(gè)字符。
③ 波浪符——~,將已有通配符轉(zhuǎn)為普通字符。
因此通配符可以表達(dá)許多不同的含義,比如:
大多數(shù)常用EXCEL函數(shù)都是支持通配符功能的,其中有:VLOOKUP、HLOOKUP、MATCH、SUM、SUMIF、SUMIFS、COUNTIF、COUNTIFS、MAXIFS、MINIFS、SEARCH、SEARCHB函數(shù)等。
今天,老菜鳥給大家介紹的就是SUMIF函數(shù)和通配符之間的應(yīng)用。
經(jīng)??垂娞?hào)教程的小伙伴對(duì)于SUMIF函數(shù)一定不陌生,這是一個(gè)按條件求和的函數(shù),基本結(jié)構(gòu)是SUMIF(條件區(qū)域,條件,求和區(qū)域),通配符主要用在“條件”中。
下面將通過(guò)5類問(wèn)題全面介紹通配符在SUMIF中的應(yīng)用。相信看完的小伙伴,都能輕松的掌握這一門簡(jiǎn)單又有效的通配符技能。(所有示例都使用同一個(gè)數(shù)據(jù)源,如圖所示。)
問(wèn)題1:收貨單號(hào)以“2017”開頭的“收貨數(shù)量”合計(jì)
對(duì)于這類問(wèn)題,在實(shí)際應(yīng)用中有兩種情況。第一種情況,“2017”是一個(gè)固定的條件,可以直接加到條件中;第二種情況,“2017”這個(gè)“條件”是變化的,可以把“2017”放入某個(gè)單元格內(nèi),再作為條件來(lái)用。
針對(duì)兩種情況,公式略有區(qū)別。
情形①:直接使用“2017”做條件
公式為:=SUMIF(B:B,"2017*",E:E)
在這個(gè)公式中,條件是"2017*",2017后面的*表示任意長(zhǎng)度的任意內(nèi)容,也就是以“2017”開頭的收貨單號(hào)。
注意:條件大多數(shù)時(shí)候需要用引號(hào)標(biāo)注,僅當(dāng)條件是單元格時(shí),或者是用數(shù)字說(shuō)明時(shí)不需要加引號(hào)。
情形②:使用含“2017”的單元格做條件
公式為:=SUMIF(B:B,G5&"*",E:E)
在這個(gè)公式中,條件是G5&"*",當(dāng)條件中存在單元格、符號(hào)(通配符也是一種符號(hào))和其他內(nèi)容時(shí),要用&連接,同時(shí)符號(hào)要用引號(hào)。
這個(gè)公式的好處是:
如果條件是多個(gè)不同的值,可以用一個(gè)公式下拉得到各自的結(jié)果。例如要分別統(tǒng)計(jì)開頭是2017、2018和2019的收貨數(shù)量合計(jì),結(jié)果如圖所示。
問(wèn)題2:收貨單5、6兩位是12的收貨數(shù)量合計(jì)
公式為:=SUMIF(B:B,"????12*",E:E)
這個(gè)公式中的條件為"????12*",四個(gè)問(wèn)號(hào)表示在12前面需要有四個(gè)任意字符,星號(hào)表示在12后面有長(zhǎng)度不限的任意字符。
注意:
在這里不能使用"*12*"做條件,公式=SUMIF(B:B,"*12*",E:E)在12前后都使用了星號(hào),會(huì)將序號(hào)為8、12和13的收貨數(shù)量也加上。這三個(gè)單號(hào)中也有12,但不是第5、6兩位。
問(wèn)題3:姓李的收貨員收貨數(shù)量合計(jì)
公式為:=SUMIF(C:C,"李*",E:E)
這個(gè)公式中的條件為"李*",通配符的用法與問(wèn)題1類似,不再贅述。
問(wèn)題4:姓李且名字是兩個(gè)字的收貨員收貨數(shù)量合計(jì)
公式為:=SUMIF(C:C,"李?",E:E)
注意:
這個(gè)公式中的條件為"李?",且通配符中的問(wèn)號(hào)必須是英文狀態(tài)的。
在這里,問(wèn)號(hào)與星號(hào)的區(qū)別在于一個(gè)問(wèn)號(hào)就表示一個(gè)字符,長(zhǎng)度是1;而星號(hào)可以表示任意長(zhǎng)度,包括零長(zhǎng)度。例如,姓名中只有“李”這樣一個(gè)字,也會(huì)被“李*”統(tǒng)計(jì)在內(nèi),這時(shí)候*就表示零長(zhǎng)度。
問(wèn)題5:?jiǎn)翁?hào)不詳(單號(hào)為星號(hào))的收貨數(shù)量合計(jì)
要統(tǒng)計(jì)單號(hào)為星號(hào)的,也有兩個(gè)方法。
方法①:=SUMIF(B:B,"~*",E:E)
解析:在星號(hào)前面加個(gè)波浪符(數(shù)字1左邊的按鍵),就把星號(hào)變成普通符號(hào),不再是通配符了。
方法②:=SUMIF(B:B,"?",E:E)
解析:條件用一個(gè)問(wèn)號(hào),表示長(zhǎng)度為一個(gè)字的任意內(nèi)容,星號(hào)當(dāng)然符合這個(gè)條件了。
補(bǔ)充提醒:
這里,提醒小伙伴們需要避免的一個(gè)錯(cuò)誤,不能單獨(dú)使用"*"做條件:
公式=SUMIF(B:B,"*",E:E) ,這里表示的是對(duì)所有單號(hào)不為空的收貨數(shù)量進(jìn)行合計(jì)。
解析:星號(hào)僅在與其他內(nèi)容組合時(shí)才可以表示任意長(zhǎng)度。如果直接用星號(hào),不是表示零長(zhǎng)度,而是表示所有非空內(nèi)容。
好了,通過(guò)上面這些例子,相信大家對(duì)于如何使用通配符一定非常清楚了,善于在公式中使用通配符,就能讓函數(shù)發(fā)揮更多威力,SUMIF遇上通配符是不是變得更厲害了,快去試試其他函數(shù)和通配符結(jié)合會(huì)有什么效果吧。
本文配套的練習(xí)課件請(qǐng)加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
在excel中,按條件查找最大、最小值,這3個(gè)方法最好用!
無(wú)往而不利的SUMIF面對(duì)這種條件求和竟然傻眼了!
Excel教程:sumifs函數(shù)常量數(shù)組簡(jiǎn)化公式
模糊查找不等于瞎子摸象,數(shù)值劃分等級(jí)和簡(jiǎn)稱查全稱你該這么干
版權(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提取身份證信息合集!(建議收藏)-下篇
- 明明沒(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ù)