龐大的資料總是使你難以統計各項資料的總數嗎?或者是工作上常常需要了解每位客戶的歷年資料統整,這些生活上零零總總的數目要用甚麼樣的函數才能夠計算出總數,或者有條件的加總數字呢?
Excel 中的 SUM 函數與其他延伸的函數,就能夠令你解決這些工作上的需求。
使用 SUM 函數可以讓你加總你所圈選的數據範圍,例如你需要知道今日的花費總共是多少,設定好函數適用的範圍後,就可以獲得這個問題的答案。
那麼 SUM 是甚麼樣的函數呢? 它的運作邏輯是甚麼?
SUM 的函數原理就如同它的名稱一樣,你選取一整排或列的儲存格項目之後,它就會自動加總各個儲存格當中的數值,並顯示出計算後的總數。
本篇文章會介紹這個 SUM 函數的各種參數與實際應用,還有與它相關的進階型函數。
SUM函數說明
函數寫法
=SUM(number1,[number2]…)
也就相當於
=SUM(第一項參數,[可加選參數]…)
從函數內可以看見 SUM 函數內最少擁有1項參數,每種參數可以是一個單一的數值,或是一個範圍內的儲存格,並可以根據資料的多寡增添參數,以下則是相關函數的說明。
- 「number1」第一項參數:此參數就是你所要加總的第一項範圍,可以是某個儲存格、單一數字、圈選範圍。
- 「[number2]」可加選參數:當要選取的範圍不只有一列或是一行時,就可以利用多次選取範圍的方式,而第二項參數就能依照這樣的情況,來增添到計算的範圍當中。
接下來我們將會以微軟網頁版(OneDrive)的免費線上 Excel 表格做為示範操作。
並以圖中的員工銷售表為範例,實際說明函數的各步驟將會如何達成我們所需要的結果。
設定 SUM 函數
假設今天想要知道目前為止的表單內的銷售額共計為多少,那麼我們可以在銷售總額旁的空格,插入 SUM 函數並將第一項函數圈選設定為 F3:F14,這時就能馬上得出總額。
而如果現在改為想知道日期10-12號以外的銷售總額,那麼我們可以將第一項參數設為 F3:F5 並加入逗號隔開第一項參數後,將增添的參數設置為 F10:F14 之後使用括號就能看見計算出的總額為6700。
SUMIF 函數
但上述 SUM 函數只有在單純數字總計時便於使用,但如果我們還想知道不同的條件之下,個別銷售員的銷售額統計,或是某個日期之後的銷售額,那麼我們就必須改為使用 SUMIF 函數。
SUMIF 函數就等同於在執行加總數字之前,多增添一張特定條件的濾網,我們可以將不同的條件預先輸入函數,讓 SUMIF 函數做判斷,而條件相符的儲存格才能夠進行加總,達到我們想要了解的特定資訊總和。
函數寫法
=SUMIF ( range, criteria, [sum_range])
也就相當於
=SUMIF (搜尋範圍,判斷準則,實際加總範圍)
從函數內可以得出 SUMIF 函數內擁有3項參數,以下則是相關函數的說明。
- 「range」搜尋範圍:此參數就是你所要判斷的條件範圍,函數將會在這個範圍之內搜尋條件相符合的儲存格進行判斷與加總。
- 「criteria」判斷準則:此參數就是你所要在前一項參數內查找的判斷條件,例如你如果想要搜尋10月15號之前的資訊那麼你就可以設定為”<=2020/10/15″即可,記得如果要搜尋的是特定文字或是數字條件的話,則要記得判斷準則加上引號” “才能正確回傳資訊,不然就會變為0或是錯誤的結果。
- 「[sum_range]」實際加總範圍:前項條件判斷過 後,這邊則是可以選取實際所要相加的數字範圍,那麼 SUMIF 函數就會以此範圍來做數字的加總。
那麼現在假如想知道員工 Mike 在10月3日到18日間的銷售額的話,那麼就可以將第一項參數設置為 D3:D14,第二項參數則設置為”Mike”,而第三項參數的實際加總範圍則圈選為 F3:F14,那麼函數最終就會變成=SUMIF(D3:D14, “Mike”,F3:F14),那麼我們就能夠得到銷售總額為2290的結果。
如同這張圖一樣可以看出最終結果,但也可以利用將判斷準則的”Mike”轉變成為特定的儲存格 J4,這樣一來隨著儲存格內的資訊變化,也能夠馬上使 SUMIF 函數的加總結果有所不同。
SUMIFS 函數
但是除了單純的一項條件外,工作上通常還會需要有多重的條件判斷資訊,才能夠在眾多的數據當中,找到特定條件下的加成總額。
因此則需要進一步改用 SUMIFS 函數來幫助進一步做特定資料的彙整與相加。
SUMIFS 函數說明
函數寫法
=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2], …)
也就相當於
=SUMIFS(實際加總範圍,第一項搜尋範圍,第一項判斷準則,第二項搜尋範圍,第二項判斷準則,…)
從函數內可以看見 SUMIFS 函數內擁有3種參數以上,而相關函數的說明則如下。
1.「sum_range」實際加總範圍:此參數就是你需要在函數判斷完條件之後,實際上要加總的數字範圍,但要注意的是加總數字的範圍要與判斷範圍的大小一致,不然很有可能出現加總時的缺漏。
2.「criteria_range」判斷範圍:此參數就如同先前 SUMIF 函數的 range 參數一樣,你必須先選取一個判斷準則的範圍,這樣 SUMIFS 函數才能夠在這個範圍內查找符合的條件。
3.「criteria」判斷準則:這邊就也是如同先前 SUMIF 函數的判斷準則,當你設置好判斷的條件後,SUMIFS 函數就會以此來做判別,找尋出與判斷範圍內相符合的儲存格進行篩選。
之後第二項條件的判斷範圍與判斷準則,也是雷同於前面的參數說明,基本上可以想成原本只有一層條件濾網的情況下,但現在有了兩層濾網可以供你再次的篩選特定的條件判斷。
那麼接下來假設情況變為需要知道 Mike 十月三日到十八日在 D 區的銷售額,那麼我們就可以將 SUMIFS 函數的第一項參數設定為 F3:F14,這代表最終我們需要在這個範圍內的數字做加總。
而到了第一項條件的判斷範圍的部分,則可以設定成 D3:D14,而判斷準則可以設定為 J4,這時就是設定好在 D3:D14 的範圍內查找我們所需要的條件,透過先前的技巧我們這裡直接設定為特定儲存格,這樣只要變動 J4 儲存格內的人名,就能夠變為不同的員工。
第二項條件的判斷範圍則同樣步驟的設定為 E3:E14,判斷準則設定為 J5 儲存格,表示函數將會從這個地區的範圍內選出 J5 儲存格內包含的地區,那麼最終 SUMIFS 函數將會顯示為=SUMIFS(F3:F14,D3:D14,J4,E3:E14,J5),那麼在兩層的條件判斷之下,就能得出 Mike 在 C 地區的銷售額為1010。
由這張圖就能看出最終 SUMIFS 函數的判斷總額。
以上就是最常用的 SUM、SUMIF、SUMIFS 函數的使用說明,透過這些函數,我們才能夠在資料的海洋裡找尋我們所需要的數額進行加總,但 Excel 同樣需要經過多次的反覆使用才能夠熟悉各個函數的操作,將這些函數牢牢地記住後,往後處理資料的工作就能夠準確且快速地完成。