有一份銷售紀錄,想知道王小明總共賣了多少、平板賣了幾筆、金額超過三萬的有幾筆,你會怎麼做?用篩選之後再手動加總,資料一改就得重做。Excel 的 SUMIF、COUNTIF 這一組「條件統計函數」,可以依照條件自動加總、計數、算平均,資料更新時結果也會跟著更新。
本文以「銷售紀錄」為例,用 5 個步驟帶你學會。使用 Excel 2016 以上的繁體中文版,SUMIFS、COUNTIFS 等多條件函數在 Excel 2007 以後的版本都有。文中的圖片為示意圖,不是實際畫面。
步驟一:認識 SUMIF 的三個參數
SUMIF 的意思是「符合條件才加總」,寫法是:
=SUMIF(條件範圍, 條件, 加總範圍)
- 條件範圍:要拿來檢查的範圍,例如業務員那一欄。
- 條件:要符合的內容,例如「王小明」。
- 加總範圍:符合條件的列,要加總哪一欄的數字,例如金額。
它的兩個親戚是:
- COUNTIF:符合條件的有幾筆,只需要條件範圍和條件。
- AVERAGEIF:符合條件的平均,參數和 SUMIF 相同。
步驟二:寫出第一個 SUMIF
範例資料放在 A2:C9,A 欄是業務員、B 欄是產品、C 欄是金額。要算王小明的總金額:
- 點選要放結果的儲存格。
- 輸入
=SUMIF(A2:A9,"王小明",C2:C9),按 Enter。
結果是 102,000,也就是王小明三筆的金額 60,000、12,000、30,000 的總和。文字條件要放在雙引號裡,而且要使用半形的符號。
要一次算出每個人,可以把「王小明」改成存放姓名的儲存格 E2,並用 F4 把範圍加上 $ 固定,再向下複製:
=SUMIF($A$2:$A$9,E2,$C$2:$C$9)
步驟三:加上 COUNTIF 與 AVERAGEIF
同樣的概念,在旁邊的欄位寫上:
- 筆數:
=COUNTIF($A$2:$A$9,E2),王小明是 3 筆。 - 平均:
=AVERAGEIF($A$2:$A$9,E2,$C$2:$C$9),王小明平均每筆 34,000。
三個公式都向下複製,一張依業務員統計的小報表就完成了,之後原始資料有增減,這張報表的數字也會跟著改變。
步驟四:條件的各種寫法
條件不是只能寫文字,還有幾種常用的寫法:
- 直接寫文字:
"王小明"。 - 引用儲存格:直接寫儲存格位址,例如
E2,不需要加引號。 - 比較條件:數字比較要和符號一起放進雙引號,例如
">=30000"。如果門檻放在儲存格 F1,則寫成">="&F1,用 & 把符號和儲存格連起來。 - 萬用字元:
*代表任意多個字,?代表任意一個字,例如"王*"會統計所有姓王的。 - 不等於:
"<>筆電",統計不是筆電的資料。
步驟五:多個條件用 SUMIFS 與 COUNTIFS
如果要同時符合兩個以上的條件,例如「王小明賣出的筆電」,就用 SUMIFS:
=SUMIFS(C2:C9,A2:A9,"王小明",B2:B9,"筆電")
結果是 90,000。它的規則和 SUMIF 有兩個差異,要特別留意:
- 加總範圍放在最前面,和 SUMIF 的順序相反。
- 後面的「條件範圍、條件」成對排列,可以一組接一組往後加。
COUNTIFS 與 AVERAGEIFS 用法類似,COUNTIFS 沒有加總範圍,直接寫成對的條件,例如 =COUNTIFS(A2:A9,"李美玲",B2:B9,"平板"),結果是 2 筆。
常見問題
Q1:結果是 0,但明明有符合的資料?
常見原因有:條件文字前後多了空格、範圍沒有對齊,或是金額欄的數字被存成文字。先檢查資料,再確認公式裡的範圍是否正確。
Q2:SUMIFS 出現 #VALUE!?
所有的範圍大小必須一致,例如都是 2 到 9 列。如果加總範圍是 C2:C9,條件範圍卻是 A2:A10,就會出錯。
Q3:寫了 >=30000,結果卻不對?
比較符號和數字要一起放在雙引號裡,寫成 ">=30000",而且符號要用半形。
Q4:想要「或」的條件,例如王小明或陳大華?
SUMIF 一次只能一個條件。可以把兩個 SUMIF 相加:=SUMIF(A2:A9,"王小明",C2:C9)+SUMIF(A2:A9,"陳大華",C2:C9)。
小結
條件統計的重點是:先分清楚「條件範圍」和「加總範圍」,一個條件用 SUMIF/COUNTIF/AVERAGEIF,多個條件用 SUMIFS/COUNTIFS。如果需要一次看到多個維度的整張統計表,樞紐分析表會更方便;但要在固定的報表中放進特定數字,這一組函數最靈活。
沒有留言:
張貼留言