Sumif用法:如何設計Excel函數公式及進階萬用字元

Sumif是Excel職場應用必學的函數之一,本文先介紹基本的三個引數,如何利用數值、文字、數學計算式三種條件型態設計公式,最後補充較為進階的萬用字元。

一、SUMIF三個引數

在儲存格裡面輸入「=SUMIF(」,從輔助視窗可以看到這個函數有三個引數,分別是「range, criteria, [sum_range]」,代表的是條件範圍,條件、加總範圍。注意到第三個引數「[sum_range]」有特別使用中括號,在一般程式語法意思是此引數可省略,待會就會看到省略時的效果。

Sumif用法:如何設計Excel函數公式及進階萬用字元 1

二、兩個引數用法

首先介紹SUMIF函數兩個引數。例如右邊有一份簡單的速食餐廳銷售記錄,想要統計「黑牛堡」到底賣了多少錢? 可以設計公式:「=SUMIF(G2:G5,100)」,依照先前對於SUMIF函數三個引數的介紹,這個公式表示是以「G2:G5」作為條件範圍,第二個引數條件是「100」,Excel會依照SUMIF函數功能將兩份黑牛堡加總起來,每份單價是100元,所以函數計算的結果是「200」。

Sumif用法:如何設計Excel函數公式及進階萬用字元 3

三、三個引數用法

接下來是SUMIF函數三個引數用法,這也是一般比較常用的情况。

跟剛才一樣的資料,在此設計的公式為:「=SUMIF(F2:F5,”黑牛堡”,G2:G5)」,條件範圍漢堡名稱,條件就是某個最好吃的漢堡,條件求和的範圍則是銷售單價,這便是分析統計中很基本常用的條件求和。

注意上個步驟的條件是「100」,這裡的條件是「:”黑牛堡”」,兩相比較讀者應該會有個心得,在Excel設計公式時,如果是數字就直接輸入,如果是文字的話,記得兩邊要套上英文的雙引號,不然Excel可是沒有那麼聰明的喔。

以這裡簡單公式及範例資料,回過頭去看文章一開始關於SUMIF函數三個引數的說明,應該會更有感覺,白話翻譯是在某個條件範圍裡面,根據某個條件,去加總特定範圍的符合條件的數值。

Sumif用法:如何設計Excel函數公式及進階萬用字元 5

四、整欄條件範圍

先前步驟的公式都是直接限定範圍,例如「F2到F5」,不過其實Excel函數公式也可以直接用一整欄作為範圍,例如這裡看到的公式:「=SUMIF(F:F,B5,G:G)」,這麼設計的好處是如果我的資料是會變動的,可能有更改、新增或刪除,例如這裡看到的新增了「冰紅茶」和「冰綠茶」,那因為我的公式早就規劃好了,設定是一整個F欄和G欄,如此計劃起得上變化,Excel會自動套用更新的資料,聰明條件求和。

上個步驟的公式是在第二個條件引數裡輸入「黑牛堡」,如果希望條件直接顯示在Excel報表中,並且想要直接去更改,也可以設計為參照引用其他儲存格,例如這裡第二個引數是「B5」,表示是引用B5儲存格的,B5是「冰紅茶」,所以Excel會以「冰紅茶」作為條件。在瞭解了這個機制之後,如果有需要可以將B5的「冰紅茶」改成是「黑牛堡」,在完全沒動到SUMIF函數公式的情況下,各位讀者應該可以想見計算結果會如何變化。

Sumif用法:如何設計Excel函數公式及進階萬用字元 7

五、數學計算式條件

綜合先前步驟範例,已經跟各位介紹到SUMIF函數以數字和文字作為加總條件,在此介紹第三種型態的加總條件,是以數學計算式作為條件,例如這裡看到的公式:「=SUMIF(G:G,”<100″,G:G)」,中間所設定的條件是「”<100″」,意思是條件範圍必須小於100的才要加總,依照範例情况便是只有飲料類的商品才會被一一加起來。

對比這個步驟跟第二個步驟的公式,應該比較能瞭解為何在某些情况下,SUMIF函數第三個引數可以省略,例如這裡看到的條件範圍和加總範圍一模一樣,乾脆去掉更加簡潔。

Sumif用法:如何設計Excel函數公式及進階萬用字元 9

六、星號萬用字元

SUMIF函數雖然可以使用數值、文字、數學計算式作為條件的資料型態,實務上較常用到的應該還是文字,因為通常是以某個關鍵字作為分析統計的條件。在此補充萬用字元的用法。

設計公式:「=SUMIF(F:F,”*茶”,G:G)」,中間的「*荼」代表前面不管有多少個字或者是什麼樣的字,只要最後面是以「茶」結尾就算是符合條件,因此會把「冰紅茶」、「綠茶」、「茶」三項商品都加起來。

Sumif用法:如何設計Excel函數公式及進階萬用字元 11

七、問號萬用字元

上個步驟介紹了萬用字元中的星號,最後這裡的問號是另一個萬用字元。公式「=SUMIF(F:F,”??鷄?”,G:G)」,配合範例很容易看得懂「??鷄?」,問號作用是剛剛好佔一個字元位置,不管是哪個中文英文阿貓阿狗都OK,所以這裡的白話翻譯是必須4個字元,不能多不能少,而且第3個字元必須是「雞」,於是「小份雞翅」和「大份雞塊」SUMIF都可以吃,但是「雞腿堡」很抱歉沒有符合條件喔!

Sumif用法:如何設計Excel函數公式及進階萬用字元 13

SUMIF函數應用與進階

SUMIF函數的應用非常普遍,因為在很多時候報表都希望是以某個關鍵字條件匯總,例如在會計實務上,將原始傳票資料依照會計科目作為條件,大類小類區分匯總,財務報表便是這樣編製出來了。

本篇文章大致介紹了SUMIF函數基本用法,實務上有可能會遇到蠻多狀況,需用進階的特殊技巧執行條件求和,以後贊贊小屋還會這方面更多的分享。

歡迎成為頻道會員,取得影片範例檔案!