贊贊小屋

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值

Excel的SUMIF函數可以依照條件加總數值。本影片以應收帳款餘額表為範例,介紹萬用字元、金額大小、數列組合、極大值錯誤值作為特殊條件加總的進階用法。

會計工作中在報表資料整理好了之後,常常會依照特定條件加總金額,Excel這方面最適合的函數便是SUMIF。依照實務狀況不同,需要不同的設定條件,這一節以業務帳款餘額表作為範例,分享SUMIF函數的進階條件設定,以便靈活應用於各個實務工作場合。

一、業務帳款餘額表

以部門業務人員開展的應收帳款餘額,主要分類欄位有部門、業務員、銷售客戶、幣別,本節希望以這些分類利用SUMIF函數執行條件加總。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第1張

二、SUMIF基本用法

首先是最基本的SUMIF條件加總公式,依照部門加總「=SUMIF(A:A,I2,F:F)」,依照業務員加總:「=SUMIF(B:B,I6,F:F)」,依照幣別加總:「=SUMIF(D:D,I11,F:F)」,A欄、B欄、D欄分別為報表中的部門、業務員、幣別欄位。SUMIF函數第一參數為條件範圍,第二參數為條件值,第三參數為加總範圍,透過本步驟三個公式作為範例,應該不難理解SUMIF函數的用法。這裡特別在原始報表和三組函數公式都有合計金額,皆為「450,000」,簡單驗證公式設計是否無誤。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第2張

三、不等於作為條件

在上個步驟的基本用法基礎上,首先介紹如何以不等於作為條件值:「=SUMIF(A:A,”<>業務一部”,F:F)」,這裡的「”<>業務一部”」表示不等於業務一部的意思,在Excel工作表會以英文雙引號輸入純粹文字,於函數公式中,英文雙引號除了輸入純粹文字外,還可以作為數學計算的條件式,這裡J2公式的計算結果為「770,000」,是加總了非業務一部帳款「320,000」,同時也會加總合計「450,000」,因為A11的合計同樣是非業務一部。為解決原報表合計金額亦納入計算的問題,稍加修改公式:「=SUMIF(A2:A10,”<>業務一部”,F2:F10)」,將範圍限定。這缺失在資料筆數不多的情況下,很容易察覺,然而實務通常資料筆數繁多,所以一定要養成像第二驟那麼勾稽檢查的習慣。

如果已經熟悉「不等於業務一部」的公式設計,「不等於NTD」函數設計應該不成問題,僅僅照樣造句而已。順便補充,SUMIF的加總條件欄的範圍長度,必須和加總金額欄位一致,這裡比較了範圍未限定和範圍限定兩者函數公式寫法,相信會幫助理解SUMIF第一參數和第三參數範圍必須對稱的觀念。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第3張

四、數學計算式作為條件

上個步驟有提到英文雙引號可應用於數學計算條件式,在Excel還可以將雙引號數學式利用「&」與函數計算結果合在一起,例如「=SUMIF(F:F,”>=50000″)」中的「”>=50000″」代表以大於等於50000作為加總F欄的條件,「=SUMIF(F:F,”>=”&AVERAGE(F:F))」中的「”>=”&AVERAGE(F:F)」代表以大於等於F欄平均值作為加總F欄的條件,這兩個公式執行的是相同的任務:加總原始報表F欄本幣金額中大於平均值的帳款。

上個步驟有遇到原始報表合計金額所造成的小麻煩,此步驟乾脆將「合計本幣金額」和「平均本幣金額」以函數公式在表外計算作為參照:「=SUM(F:F)」、「=SUM(F:F)/(COUNTA(F:F)-1)」。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第4張

五、萬用字元星號

接下來介紹萬用字元「*」的用法。「*」可以作為替代不限文字長度的模糊查找符號,例如公式「=SUMIF(C:C,”*富廣*”,F:F)」中的「”*富廣*”」,代表任何有包含「富廣」兩個字的內容,所以「富廣」和「富廣A」皆符合條件。公式「=SUMIF(C:C,”*”&I8&”*”,F:F)」等同於「=SUMIF(C:C,”*長源*”,F:F)」,之所以特別設計為「”*”&I8&”*”」,用意是把萬用字元和儲存格參照合併,方便複製公式。這裡將J8儲存格公式向下拖曳延伸到J9,即得到包含「高鴻」的SUMIF加總公式:「=SUMIF(C:C,”*”&I9&”*”,F:F)」。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第5張

六、萬用字元問號

緊接著介紹萬用字元「?」的用法。「?」是替代一個字元長度的模糊查找符號。例如公式:「=SUMIF(C:C,”??”,F:F)」中的「”??”」,代表範圍內任何兩個字元長度的儲存格皆符合條件,因此計算結果為「130,000」,剛好是第二步驟「業務一部」、「王一」、「NTD」的加總計算金額。公式「=SUMIF(C:C,”??A”,F:F)」中的「”??A”」,代表長度是三個字元且以A結尾作為條件,因此計算結果「220,000」和第二步驟的「幣別」「USD」相同。依此類推,公式「=SUMIF(C:C,”??J”,F:F)」計算結果「100,000」,等同於第二步驟的「幣別」「JPY」的加總金額。透過這三個公式範例,讀者應能掌握萬用字元「?」的技巧。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第6張

七、SUMIF陣列公式

最後再補充較為特殊進階的用法。「=SUM(SUMIF(D:D,{“USD”,”JPY”},F:F))」為陣列公式,其作用等同於將「=SUMIF(D:D, “USD”,F:F)」和「=SUMIF(D:D, “JPY”,F:F)」兩個公式計算結果再以SUM加總,所以金額為220,000+100,000=320,000。在加總條件為兩個以上的情況時,使用陣列公式將會相當簡潔方便,讀者可進一步參考範例檔案中J6和J7的公式設定。

另外實務工作常常會有部份資料為錯誤訊息,例如J11儲存格「#DIV/0!」表示是除數為零的錯誤數學計算,如果直接以「=SUM(F:F)」加總,會跑出相同的「#DIV/0!」錯誤訊息。在此可以簡單利用極大值條件排除非數值儲存格,例如9e307在Excel中表示是9乘以10的307次方、數字9後面有307個0,是一個相當大的數值,所以公式「=SUMIF(F:F,”<9e307″)」意思是加總F欄中數值內容,計算結果即為不計錯誤訊息的合計本幣金額「450,000」。

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第7張

電腦和ERP系統普遍可使用的萬用字元

萬用字元「*」和「?」在Excel有普遍性的應用,不但像這節範例可作為函數的條件設定,也可以在篩選或格式化條件等指令中使用。筆者也有遇過公司ERP系統支持以萬用字元設定查詢和下報表條件。可考慮修改為:「再擴大而言,很多其他的電腦操作也會用到萬用字元,不侷限於Excel。」希望透過這一節範例分享,讀者可以熟稔運用這兩個強大的萬用字元。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

Excel SUMIF特殊條件加總:萬用字元、數列、錯誤值 SUMIF 第8張