【原】sumifs中通配符之不包含關(guān)鍵字的用法

劉卓學(xué)EXCEL 2021-04-02

展開全文

小伙伴們，大家好。今天來分享下sumifs中通配符不包含的用法。好像我們平時(shí)用到不包含的比較少，反正我是用的比較少，通常都是包含用的多。下面還是來看一個(gè)實(shí)例，來自于論壇一位朋友的問題。

求不同編號(hào)的倉庫的總數(shù)量(不包含“報(bào)廢”和“過期”的倉庫)

下圖左表是數(shù)據(jù)源，右表是求出的結(jié)果。以編號(hào)001為例說明，A列的編號(hào)是001，B列的倉庫不包含關(guān)鍵字“報(bào)廢”和“過期”，對C列的數(shù)量求和。也就是我用紅色框標(biāo)記出來的數(shù)量。這是個(gè)多條件求和的問題，你會(huì)怎么做呢？

我最開始的思路是用編號(hào)001的所有倉庫的總數(shù)量減去編號(hào)001的包含“報(bào)廢”倉的總數(shù)量，再減去編號(hào)001的包含“過期”倉的總數(shù)量，這也是常規(guī)的思路。所以公式為=SUM(SUMIFS(C:C,A:A,E3,B:B,{"*","*報(bào)廢*","*過期*"})*{1,-1,-1})。

這是sumifs的數(shù)組用法，其中B列的條件是個(gè)常量數(shù)組，有3個(gè)元素，分別是"*","*報(bào)廢*","*過期*"。"*"代表任意長度的文本，可以是空文本""，但不能代表空單元格和數(shù)字；"*報(bào)廢*"代表任意包含“報(bào)廢”2字的文本；"*過期*"代表任意包含“過期”2字的文本。

所以SUMIFS(C:C,A:A,E3,B:B,{"*","*報(bào)廢*","*過期*"})就相當(dāng)于對3種類型的倉庫求總數(shù)量。分別是編號(hào)為001的所有倉庫的總數(shù)量，編號(hào)為001的包含“報(bào)廢”倉的總數(shù)量，編號(hào)為001的包含“過期”倉的總數(shù)量。

=SUM(SUMIFS(C:C,A:A,E3,B:B,{"*","*報(bào)廢*","*過期*"})*{1,-1,-1})這部分乘以了{(lán)1,-1,-1}這樣一個(gè)常量數(shù)組，其實(shí)就是用總的減去“報(bào)廢”的，減去“過期”的。但是這個(gè)公式得到的結(jié)果是錯(cuò)誤的。因?yàn)锽7單元格既包含“報(bào)廢”，又包含“過期”。這樣的話就把B7單元格對應(yīng)的數(shù)量減了2次。

改進(jìn)后的公式為=SUMIFS(C:C,A:A,E3,B:B,"<>*報(bào)廢*",B:B,"<>*過期*")。這樣的話其實(shí)更簡單直接，但是大多數(shù)人不會(huì)想到，可能不等于和通配符一起使用的教程比較少吧。意思就是A列的編號(hào)是001，并且B列的倉庫既不包含“報(bào)廢”，又不包含“過期”，對符合這些條件的記錄求總數(shù)量。

其中"<>*報(bào)廢*"的意思就是不等于包含“報(bào)廢”2個(gè)字的字符串，簡單來說就是不包含“報(bào)廢”。這里還有一個(gè)多條件“且”的問題。比如B列的倉庫既不包含“報(bào)廢”，又不包含“過期”，就要寫為=SUMIFS(C:C,A:A,E3,B:B,"<>*報(bào)廢*",B:B,"<>*過期*")。

而不能寫在一個(gè)數(shù)組中，比如=SUMIFS(C:C,A:A,E3,B:B,{"<>*報(bào)廢*","<>*過期*"})這樣，這樣寫的話是對不包含“報(bào)廢”的求一個(gè)總數(shù)量，不包含“過期”的再求一個(gè)總數(shù)量，返回的結(jié)果有2個(gè)值。

多條件求和的話，除了sumifs外還可以用sum，sumproduct等。比如=SUMPRODUCT((MMULT(--ISERR(FIND({"報(bào)廢","過期"},B$3:B$12)),{1;1})=2)*(A$3:A$12=E3)*C$3:C$12)也可以得到結(jié)果。

延續(xù)上一個(gè)公式的思路，還可以用替換函數(shù)substitute，公式為=SUMPRODUCT((SUBSTITUTE(SUBSTITUTE(B$3:B$12,"報(bào)廢",),"過期",)=B$3:B$12)*(A$3:A$12=E3)*C$3:C$12)。

最后說一點(diǎn)我自己的感想，做完這個(gè)題，感覺函數(shù)自己沒有學(xué)通，不會(huì)舉一反三。比如下面這個(gè)題，我會(huì)寫函數(shù)，但是換了今天的題目我就不會(huì)了，吃飯時(shí)突然間有了靈感才想通的，這不就是一回事嘛。