在工作表中加總數值的方法

計數與加總

加總 (數值相加) 是資料分析中不可或缺的一部分,例如累加西北地區的銷售小計,或是逐筆總計每週收據。使用 Excel 提供的多種技巧,您就可以加總資料。

為了協助您做出最佳抉擇,本文提供全面性的方法摘要,並佐以資訊,協助您快速決定所要使用的技巧,還有深入探究的文章連結。

將儲存格內容加總與計算儲存格的數目是兩件完全不同的事情,勿混為一談。如需有關計算儲存格數目、欄數目或資料列數目的詳細資訊,請參閱 COUNTIF 函數

本文內容


單純的加法和減法

您可以使用簡單的公式、按一下按鈕,或使用工作表函數,來相加和減去數值。

使用簡單公式將儲存格中的值相加

如果您只需要快速取得結果,就可以將 Excel 作為迷你計算機使用。方法是使用加號 (+) 算術運算子。

公式 描述 結果
=5+10 使用 + (加號) 運算子可以將兩個值或更多個值相加。 15
=A2+B2 將兩個或多個儲存格中的值相加。在這裡,假設 A2 = 5 且 B2 = 10。 15
=A2+B2+20 將兩個儲存格中的值與您在公式中直接輸入的數字相加。在這裡,假設 A2 = 5 且 B2 = 10。 35

頁首 頁首

使用簡單公式減去儲存格中的值

方法是使用減號 (-) 算術運算子。例如,公式 =12-9 會顯示結果為 3。

如需有關如何在公式中使用算術運算子的詳細資訊,請參閱用 Excel 當計算機一文。

頁首 頁首

使用按鈕將欄或列中的值相加

您可以用 [自動加總] 快速加總一欄或一列中的數字範圍。在有數字的欄下方或列的右邊,對著空的儲存格按一下,然後按一下 [自動加總]。Excel 會選取它判斷為最有可能的資料範圍。再次按一下 [自動加總] 接受 Excel 選取的範圍,或您自行選取範圍,然後按一下 [自動加總]

使用 [自動加總] 快速相加資料列

如需有關如何使用 [自動加總] 的詳細資訊,請參閱用 Excel 當計算機 一文。

頁首 頁首

使用函數將範圍中的值相加

SUM 函數用在您要將來自不同範圍的值相加或相減時,或是結合多個範圍的數值時,都很方便。使用 SUM 函數可以將您在左括弧和右括弧內指定的所有引數 (引數:函數用來執行作業或計算的值。函數使用的引數類型是函數特定的。函數中使用的一般引數包含數字、文字、儲存格參照及名稱。)相加。每個引數都可以是一個範圍、一個儲存格參照或一個正數值或負數值。

要輸入簡單公式,請在儲存格中鍵入 =SUM,後接左括弧。接著請鍵入一個或多個數字、儲存格參照或儲存格範圍,並且用逗號分開。然後,鍵入右括弧並按一下 ENTER,顯示結果。您也可以用滑鼠選取含有您想要加總的資料之儲存格。

 
1
2
3
A
出席
4823
12335

以上表中的資料為例,以下所有公式均使用 SUM 函數傳回相同的值 (17158):

  • =SUM(4823,12335)
  • =SUM(A2,A3)
  • =SUM(A2:A3)
  • =SUM(A2,12335)

下圖顯示使用 SUM 函數的公式,將儲存格 A2 的值和 12335 相加。在公式之下有工具提示,提供您使用 SUM 函數的指引。

使用 SUM 函數加上儲存格和值

 附註 

  • Excel 中沒有 SUBTRACT 函數。要使用函數減去值,請利用 SUM 函數並使用負值。例如,公式 =SUM(30,A3,-15,-B6) 將儲存格 A3 中的值加上 30,減去 15,並減去儲存格 B6 中的值。
  • 您可以利用 SUM 函數中引數的形式,以任意組合,最多將 255 個數值或儲存格或範圍參照相加。

如需詳細資訊,請參閱 SUM 函數一文。

頁首 頁首

使用函數減去範圍中的值

使用 SUM 函數,輸入您在公式中做為負數而要減去的數字,就可以減去數字。

 
1
2
3
A
出席
29072
12335

以上表中的資料為例,以下所有公式均使用 SUM 函數傳回相同的值 (16737):

  • =SUM(29072,-12335)
  • =SUM(A2,-A3)
  • =SUM(A2,-12335)
  • =SUM(A2,(-1*(A3)))

頁首 頁首

藉由組成群組和進行樞紐分析的方式來加總資料

您可以使用大綱或樞紐分析表,將您的資料組成群組和摘要。

使用大綱將欄中的值相加

若您的資料是以一般方式排列 (即列和欄),且可以依欄的值以邏輯方式組成群組,就可以藉由建立大綱的方式來將資料組成群組和摘要。建立大綱可以藉由插入小計和總計的方式,摘要資料。假設您有以下銷售數據,這些數據是依地區和月份排列。

 
1
2
3
4
5
6
7
A B C
地區 月份 銷售額
東區 一月 $18,000
東區 二月 $23,000
東區 三月 $19,000
西區 一月 $17,000
西區 二月 $27,000
西區 三月 $21,000

下圖顯示有小計的大綱,依地區組成群組,還有總計。

有小計和總計的大綱

使用 [小計] 命令 ([資料] 索引標籤,[大綱] 群組) 可建立大綱、小計及總計。

原始資料包括東區的三列資料及西區的三列資料 (列 2 到 7)。請注意,小計運算將第 5 列的東區總計插入東區資料的最後一列及西區資料的第一列之間。

若您依序按一下儲存格 A4 及 [小計] 命令,Excel 就會建立一個大綱,在其中插入 [東區總計][西區總計][總計],並用這些總計數字填入 [銷售] 欄中的儲存格。

如需詳細資訊,請參閱下列文章:

頁首 頁首

使用函數將清單或 Excel 表格欄中的值相加

要使用比 [小計] 命令更有彈性的替代方法,您可以用 SUBTOTAL 函數來計算含有隱藏列或欄之清單或 Excel 表格的小計和總計。在 SUBTOTAL 函數中,您需指定一個包括或不包括隱藏值的引數。

 附註   SUBTOTAL 函數可以控制是否將隱藏列包含在結果中,以及一律忽略已經篩選的列。

例如,假設您要計算儲存格 C2 到 C7 中資料的小計和總計,但您忽略列 3 和列 6 中的隱藏資料。您可以使用以下這樣的函數:

=SUBTOTAL(109,C2:C7)

第一個引數 (109) 指定您要相加範圍中的值並忽略隱藏的值。要加入隱藏的值,您應該使用 9 來作為第一個引數。

如需詳細資訊,請參閱 SUBTOTAL 函數一文。

頁首 頁首

加總資料,並從不同的觀點審視資料

當一份工作表含有上千或上萬筆相似資料的列時,很難得知這些數字所代表的意義。這時可以在 Excel 中將資料建立成樞紐分析表,顯示小計、總計,還可以根據您所指定的類別來摘要資料。

只要選取資料範圍或 Excel 資料表中的一個儲存格,然後在 [插入] 索引標籤的 [表格] 群組中,按一下 [樞紐分析表],就能快速建立一份樞紐分析表。

要顯示樞紐分析表的功用,請注意,在以下範例中,銷售資料含有許多列 (實際上有 40 列資料,但圖僅顯示其中一部分)。另外,此資料尚未經過摘要處理,而且沒有小計或總計。

在樞紐分析表中使用的資料

根據相同資料所建立的樞紐分析表會顯示小計、總計,並會提供可以迅速理解的精簡摘要。

在樞紐分析表中彙總和合計的資料

建立並使用樞紐分析表工作,可能需要初步準備資料,並需熟悉某些概念。如需協助快速入門的詳細資訊,請參閱以下文章:

頁首 頁首

根據一或多個條件加總資料

使用工作表函數便可以套用條件 (又稱為準則),如此一來,就可以只相加符合您指定條件的資料。

使用一個函數或結合多個函數,根據一項條件將範圍中的值相加

請使用 SUMIF 函數或在 IF 函數內以巢狀的方式使用 SUM 函數。

使用 SUMIF 函數,或結合使用 SUMIF 函數,就可以根據一項準則來相加數字。

例如,公式 =SUMIF(A2:A6,">20") 只將範圍 A2 到 A6 中大於 20 的數字相加起來。

 附註   以下資訊較為技術性,但絕對值得您去瞭解。

如果 SUMIF 函數不存在,結合使用 IFSUM 函數,您還是可以產生相同的答案。用這種方式來結合函數又稱為「巢狀方式」,因為是在一個函數之中使用另一個函數。

要建立仿 SUMIF 函數的公式,您應輸入會將範圍 A2 到 A6 視為一個陣列的公式,也就是說,該公式會將這個範圍視為一個含有五個儲存格的實體。

您的公式應該會像這樣:{=SUM(IF(A2:A6>20,A2:A6))}。包圍公式的「大括弧」表示這是一個陣列公式。陣列公式需要特別的處理方式:不是自行鍵入大括弧,而是要按下 CTRL+SHIFT+ENTER 鍵, 接著 Excel 會用大括弧將公式 =SUM(IF(A2:A6>20,A2:A6)) 包起來。如果您自行鍵入大括弧,就無法建立陣列公式。

試試看!    

將下表中的資料複製到位於儲存格 A1 的工作表中。貼上資料後,您會注意到,儲存格 A10 含有一個 #VALUE! 錯誤。這表示您需要將儲存格 A10 轉換成陣列公式。按下 F2,然後按 CTRL+SHIFT+ENTER 鍵,即可將儲存格轉換成陣列公式。Excel 會在儲存格 A8 和 A10 中顯示結果 (65)。

 
1
2
3
4
5
6
7
8
9
10
A
寬度
18
29
36
11
16
使用 SUMIF 函數
=SUMIF(A2:A6,">20")
使用 SUM 及 IF 函數
=SUM(IF(A2:A6>20,A2:A6))

如需更多資訊,請參閱 SUMIF 函數一文,以及將數字相加文章中的<根據單一條件將數字相加>一節。

另外,知識庫文章使用 SUM(IF()) 而不使用 CountBlank() 的時機針對使用 SUMIF 函數的時機提供額外的指引。

頁首 頁首

使用函數,根據一或多項條件將欄中的值相加

使用 DSUM 函數可將清單或資料庫的記錄欄位 (欄) 中符合您所指定條件 (又稱為準則) 之數字相加。

當您有一個欄清單,且您認為以個別儲存格範圍來定義條件會比使用巢狀函數要來得容易時,也可以使用 DSUM 函數。

如需詳細資訊,請參閱 DSUM 函數一文。

另請參閱將數字相加一文中的<根據儲存於個別範圍內的條件將數字相加>小節,以瞭解詳細資訊。

頁首 頁首

使用一個函數或結合多個函數,根據多項條件將範圍中的值相加

使用 SUMIFS 函數。提供您想要加總的範圍、含有條件的範圍,以及可套用到條件範圍的條件。或者,您也可以用巢狀方式來使用 SUMIF 函數。

如需詳細資訊,請參閱 SUMIFS 函數一文。

請參閱將數字相加一文中的<根據多個條件將數字相加>小節,瞭解有關如何將 SUMIF 函數共同使用的詳細資訊。

另請參閱以下知識庫文章,瞭解更多資訊:

頁首 頁首

加總平方值與陣列值

您可以使用工作表函數將兩個或多個欄中彼此相對應的值相加。這種運算可能和判斷加權平均值 (請參閱以下計算平均成績點數的範例) 一樣簡單,也可能會是複雜的統計或工程計算。

加總一或多個陣列中對應值的乘積

使用 SUMPRODUCT 函數。例如,您可以使用 SUMPRODUCT 函數,並將所得除以學分數,來計算學生的平均成績點數,如下圖所示。

將兩個陣列中對應的值相乘

公式 =SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6) 將每項課程所得的成績乘以學分數,並將這些乘積 (61.3) 加總,再除以總學分數 (18),得到平均成績點數 (3.41)。這種計算又稱為加權平均值。

將數個陣列 (陣列:用來建立產生多個結果或運算一組以列及欄排列之引數的單一公式。陣列範圍共用一個公式;一個陣列常數是用作一個引數的一組常數。)相乘,您就可以在更高階的數學運算中使用 SUMPRODUCT 函數。

在維度相同的陣列中使用 SUMPRODUCT 函數很簡單,但若您需要更多彈性,也可以在陣列公式 (陣列公式:針對一或多組值執行多個計算,然後傳回單一結果或多個結果的公式。陣列公式括在大括弧 { } 中,且藉由按 CTRL+SHIFT+ENTER 輸入。)中使用 SUM 函數。

如需詳細資訊,請參閱 SUMPRODUCT 函數一文。

以下四個函數通常只會在高階的統計或複雜的數學應用中使用,因此這裡僅作簡短描述。如需詳細資訊,請按一下函數名稱,直接前往該函數的參考主題。

  • SUMSQ 函數 傳回某個範圍中數字清單或值清單的平方之總和。例如 SUMSQ(2,3) 會傳回 13。
  • SUMX2PY2 函數 將兩個陣列中對應值之平方總和相加。
  • SUMX2MY2 函數 傳回兩個陣列中對應值之平方差的總和。
  • SUMXMY2 函數 傳回兩個陣列中對應值差的平方總和。

頁首 頁首

特殊案例 (逐筆總計、唯一值)

您可以用工作表函數來建立總計,這種總計會隨著您將更多資料加入一個範圍或資料表而自動更新,或僅將一個範圍或資料表中的唯一值相加。

使用函數建立逐筆總計

使用 SUM 函數。例如,在工作表中建立名為「存款」「提款」「餘額」的欄標題。

  1. 「餘額」欄之下的第一個儲存格中,輸入您最初的餘額。
  2. 在您的餘額底下的儲存格 (這裡即 C3),輸入公式,加上同一列中「存款」欄的值,並減去「提款」欄的值 (=SUM(C2,A3,-B3)
  3. 將此公式往下拖到您所加的任一新列。在以下範例中,公式會被拖曳到第 4 列。
 
1
2
3
4
A B C
存款 提款 餘額
500
1000 625 =SUM(C2,A3,-B3)
1000 740 =SUM(C3,A4,-B4)

您可以像是使用支票登記簿一般地逐筆結計餘額,隨著您持續輸入新項目和新的值,而觀察儲存格項目值的增減。

如需詳細資訊,請參閱計算逐筆結計餘額

頁首 頁首

使用複合公式將範圍中的唯一值相加

在欄中輸入含有重複值的數值清單,然後結合使用 SUMIFFREQUENCY 函數,只將該範圍中的唯一值相加。

如需有關執行這種運算的詳細資訊,請參閱將數字相加一文中的<將唯一值相加>小節。

頁首 頁首

在公式中加入其他工作表或活頁簿的值

您在公式中加入其他工作表或活頁簿的儲存格或資料範圍參照,就可以予以相加或相減。要參照到其他工作表或活頁簿的儲存格或範圍,請使用下表中的指示。

若要參照到: 輸入項目 範例
同一份活頁簿中其他工作表的儲存格或範圍 工作表名稱後接驚嘆號,然後加上儲存格參照或範圍名稱。 Sheet2!B2:B4
Sheet3!SalesFigures
其他目前開啟之活頁簿中的儲存格或範圍 在中括弧 ([]) 內放活頁簿檔名,加上工作表名稱,後接驚嘆號,然後加上儲存格參照或範圍名稱。 [MyWorkbook.xlsx]Sheet1!A7
其他目前未開啟之活頁簿中的儲存格或範圍 在中括弧 ([]) 內放活頁簿完整路徑和檔名,加上工作表名稱,後接驚嘆號,然後加上儲存格參照或範圍名稱。如果完整路徑含任何空格字元,並以單引號包圍路徑開頭和工作表名稱結尾 (請見範例)。 ['C:\My Documents\[MyWorkbook.xlsx]Sheet1'!A2:A5

頁首 頁首

加總或減去日期與時間值

您可以使用日期和時間函數,甚至使用簡單的加法或減法,來計算專案所經過的時間和預計完成日期等等。下表顯示的幾個公式可計算經過的時間和天數。請注意,如果您減去日期或時間而得到負數值,Excel 在含有公式的儲存格中會顯示 ### 字元。

 
1
2
3
4
5
A B C
開始時間 結束時間 經過時間公式 說明 (結果)
11:55:24 AM 2:48:13 PM =B2-A2 以時、分及秒來顯示經過的時間。在這裡,Excel 以「AM」的格式顯示結果 (2:52:49 AM),因為差不到 12 小時。如果差超過 12 小時或更長的時數,Excel 就會顯示「PM」的格式。
       
開始日期 結束日期 經過天數公式 說明 (結果)
5/28/2008 6/03/2008 =B5-A5 將經過的天數顯示為數字 (6)。

使用日期和時間值計算可能會產生非預期的結果,因此請謹慎進行。

頁首 頁首

在 Excel 狀態列上顯示計算

若要快速得知在您工作表中選取的儲存格總數,請查看 Excel 狀態列 (位於文件視窗最下方的狀態列)。在狀態列上,Excel 會顯示含有數字資料的兩個或以上已選取儲存格之總和。狀態列的總計值會隨著您選取或取消選取儲存格而迅速更新。

Excel 狀態列

圖說文字 1 Excel 狀態列
圖說文字 2 Excel 狀態列上的計算加總值

選取一或多個儲存格時,有關這些儲存格中資料的資訊就會顯示在 Excel 狀態列上。例如,在工作表中若選取四個儲存格,其中含有值 2、3、一個字串 (例如「cloud」) 和 4,那麼以下的值便可以同時顯示在狀態列上:平均值、項目個數、數字計數、最小值、最大值及加總。以滑鼠右鍵按一下狀態列,可將這些值全部或其中一項加以顯示或隱藏。這些值顯示於下圖中。

狀態列顯示選取儲存格的計算與項目個數

注意    在 Excel 2007 之前的 Excel 版本中,這些相同的值可顯示在狀態列上,但一次只能顯示一個值。

頁首 頁首

 
 
適用:
Excel 2010, Excel 2007