Excel 的 SUMIF
函數可以依照使用者自訂的判斷條件,計算符合條件的數值總和。
在 Excel 表格中計算數值的加總時,最簡單的方式就是使用 SUM
函數,但如果想要依照某些判斷條件篩選加總的數值,就要改用 SUMIF
函數。以下是 SUMIF
函數的基本用法教學與應用範例。
COUNTIF
與 COUNTIFS
函數的教學。
SUMIF
用法假設我們有一個商品銷售的 Excel 表格,上面有各個銷售員販售各種商品的資料。
如果我們想要快速算出「葵花寶典」這個商品的販售總數,就很適合使用 SUMIF
函數來處理,這裡我們示範兩種操作方式,一種是使用 Excel 工具列上面的「插入函數」工具,另外一種是直接用鍵盤輸入 Excel 儲存格中的公式。
Excel 工具列的「插入函數」工具可以讓使用者透過視窗介面來插入各種函數,函數與資料範圍的選擇都可以使用滑鼠操作,這種做法適合初學者或是不習慣寫程式的人使用。
Step 1
在 Excel 中選擇一個要放置計算結果的儲存格(這裡我選擇右邊的 H4 儲存格),接著點選「公式」頁面中的「插入函數」功能。
Step 2
在「數學與三角函數」類別中,選擇「SUMIF」函數。
Step 3
接著選擇 SUMIF
函數的三個引數。
第一個 Range 引數是指要用來進行條件判斷用的檢查資料,這裡我們要做的事情就是找出商品是「葵花寶典」的資料,所以檢查資料的範圍就要設定為商品那一欄,也就是 C2:C16
。
第二個 Criteria 引數是判斷條件,這裡我要找尋的商品是「葵花寶典」,所以這一欄就直接填入 葵花寶典
。
第三個 Sum range 引數則是實際用來計算加總的數值資料範圍,而這裡我們實際要計算的數值就是「葵花寶典」的銷售數量,所以選擇數量那一個欄位,也就是 E2:E16
。
這樣設定好之後,SUMIF
函數就會把 Range 範圍的資料一個接著一個拿來檢查,如果符合 Criteria 所設定的條件的話,就把 Sum range 對應位置的數值加起來,如果不符合條件則跳過該筆資料。
設定好三項引數之後,按下「確定」。
Step 4
這樣在 H4 儲存格中就會出現計算的結果,也就是「葵花寶典」的總銷售數量。
對於習慣撰寫程式的人,可以在 Excel 儲存格中直接輸入公式,公式用法如下:
=SUMIF(檢查資料,判斷條件,加總資料)
以這裡計算「葵花寶典」總銷售數量的例子來說,就是這樣寫:
=SUMIF(C2:C16,"葵花寶典",E2:E16)
其實上面所介紹的「插入函數」工具就只是產生這一行公式而已,熟悉 SUMIF 的語法之後,其實直接打比較快。
如果我們想要找出「葵花寶典」與「辟邪劍譜」兩項商品的總銷售數量,可以直接使用兩個 SUMIF 計算個別商品的銷售數量總和,再把他們加起來:
=SUMIF(C2:C16,"葵花寶典",E2:E16)+SUMIF(C2:C16,"辟邪劍譜",E2:E16)
這樣就可以得到兩種商品的銷售數量總和,這種方式是最直覺的做法。
另外一種方式是使用多項判斷條件,同時計算兩項商品的個別銷售數量,再使用 SUM
函數把兩個總數加起來:
=SUM(SUMIF(C2:C16,{"葵花寶典","辟邪劍譜"},E2:E16))
這種方式也可以得到同樣的結果,而公式寫起來也比較精簡。
SUMIF
也可以依照日期來篩選加總的資料。
如果我們想要計算 2017 年 4 月份之後,所有商品的銷售總量,可以這樣寫:
=SUMIF(A2:A16,">=2017/4/1",E2:E16)
這裡我們將檢查資料的範圍設為日期那一欄,而判斷條件則設定為 ">=2017/4/1"
,代表 2017 年 4 月 1 日當天或是之後。
這樣就可以計算出 2017 年 4 月份開始的所有商品銷售總量。
如果想要計算介於兩個日期中間的資料,可以使用兩個 SUMIF
來處理。
若我們只想要計算 2017 年 4 月以及 5 月的銷售總量,可以先用 SUMIF
計算 4 月份開始的銷售總量,再扣掉 6 月份之後的總量:
=SUMIF(A2:A16,">=2017/4/1",E2:E16)-SUMIF(A2:A16,">=2017/6/1",E2:E16)
這樣就得到我們想要的結果了。
另一個方式是改用 SUMIFS 這個可以接受多個判斷條件的 Excel 函數,其第一個參數是加總資料,後方可接任一個檢查資料與判斷條件的組合配對:
=SUMIFS(E2:E16,A2:A16,">=2017/4/1",A2:A16,"<2017/6/1")
這樣就可以得到 4 月以及 5 月的銷售總量。
如果要依照數值的判斷條件來加總,就直接選擇數值的檢查資料,配上數值的判斷條件即可。
這是計算單價在 100 元以上的商品銷售總量:
=SUMIF(D2:D16,">=100",E2:E16)
如果文字的判斷條件不是很明確時,可以配合萬用字元(星號 *
或問號 ?
)來匹配比較模糊的文字。
假設我們想要找出所有姓「岳」的銷售員(「岳靈珊」、「岳不群」)所有的銷售總量,就可以這樣寫:
=SUMIF(B2:B16,"岳*",E2:E16)
星號(*
)代表任意的文字(不限長度),這樣 SUMIF
就會檢查所有的銷售員資料,把所有以「岳」開頭的銷售員都抓出來,計算其對應的銷售數量總和。
另外一個常用的萬用字元是問號(?
),其代表單一個任意的文字,所以如果我們想要比對「岳」開頭、而且後面街上兩個任意文字的資料,就可以這樣寫:
=SUMIF(B2:B16,"岳??",E2:E16)
這樣同樣可以找出「岳靈珊」與「岳不群」的資料。
如果檢查用的資料與加總用的資料相同,則可將 SUMIF
的第三個加總資料引數省略,這樣的話 SUMIF
就會使用第一個引數的資料進行檢查與加總。
若要把所有總價在 300
以上的資料加起來,可以這樣寫:
=SUMIF(F2:F16,">=300")
這樣就得到所有總價在 300
以上加總。
參考資料:簡書