Excel 資料相加及計數方式的摘要彙總

計數與加總
加總 (加總值) 是資料分析中不可或缺的一部分,無論是計算西北地區銷售金額小計,還是計算每週款項的總金額。Excel 提供多種可用來計算加總資料的技術。

為了協助您選擇最佳的選項,本文提供各種方法的綜合摘要及支援資訊,以協助您快速決定要使用的技術,並提供深入探討的文章連結。

加總不應與計數混淆。如需有關如何計算資料之儲存格、欄或列數目的詳細資訊,請參閱計算資料儲存格、列或欄的數目

本文內容

簡單加法和減法

您可以透過簡單的公式、按一下按鈕或使用工作表函數加減數字。

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

如果您只需要快速的結果,可以使用 Excel 做為迷你計算機。使用加號 (+) 算術運算子執行這項計算。舉個例說,如果您在儲存格輸入公式 =5+10,Excel 會顯示結果 15

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

頁面頂端

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

使用減號 (-) 算術運算子執行這項計算。例如,公式 =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) 會加上 30 與儲存格 A3 的值,減去 15,然後再減去儲存格 B6 的值。

  • 您最多可以包含 255 個數值、儲存格或範圍參照,任意組合它們以做為 SUM 函數的引數。

如需詳細資訊,請參閱 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

地區

月份

銷售額

東部

1 月

$18,000

東部

2 月

$23,000

東部

3 月

$19,000

西部

1 月

$17,000

西部

2 月

$27,000

西部

3 月

$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 建立一個樞紐分析表,以顯示小計、總計,並根據您指定的類別摘要資料。

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

為了解樞紐分析表的強大功能,請注意下列範例中的銷售資料包含許多列 (實際上有 40 列的資料,圖片中僅顯示一小部分)。此外,尚未摘要資料,並且沒有小計或總計。

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

樞紐分析表根據相同的資料顯示了小計、總計,並提供簡單明瞭的摘要。

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

建立和使用樞紐分析表之前可能需要進行一些初步的資料準備,並熟悉一些概念。

如需協助您開始使用的詳細資訊,請參閱下列文章:

在樞紐分析表中計算值

樞紐分析表中的小計和總計欄位

頁面頂端

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

您可以使用工作表函數套用條件 (又稱為準則),僅加總符合您指定之條件的資料。

使用單一函數或函數組合根據單一條件加總範圍內的值

使用 SUMIF 函數,或將 SUM 函數放入 IF 函數內。

您可以使用 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)。

如何複製範例工作表資料

  • 建立空白的活頁簿或工作表。

  • 選取 [說明] 主題中的範例。

    附註: 請不要選取列或欄名。

    選取 [說明] 中的範例
    選取 [說明] 中的範例
  • 按 CTRL+C。

  • 選取工作表中的儲存格 A1,然後按 CTRL+V。

  • 若要在檢視結果與檢視傳回結果的公式之間切換,請按 CTRL+` (抑音符號)。

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))

另請參閱知識庫文件 XL:使用 SUM(IF()) 而不使用 CountBlank() 的時機提供額外的指南,讓您了解使用 SUMIF 函數的時機。

頁面頂端

使用函數根據一或多個條件加總欄的值

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

當您有欄清單且覺得在分開的儲存格範圍中定義條件比使用巢狀函數還容易,也可以使用 DSUM 函數。

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

頁面頂端

使用精靈根據多個條件加總範圍內的值 (僅適用於 Excel 2007)

您可以使用條件式加總精靈 ([公式] > [解決方案] > [條件式加總]) 根據多個條件加總值。

重要: 條件式加總精靈僅適用於 Excel 2007 及先前的版本。如需詳細資訊,請參閱不再提供條件式加總精靈增益集了嗎?

如果無法使用此命令 ([解決方案] 群組可能也不會顯示),請安裝和載入 [分析工具箱] 增益集。

如何載入分析工具箱

按一下 [Microsoft Office 按鈕] Office 按鈕影像 、按一下 [Excel 選項],然後按一下 [增益集] 類別。

  1. 選取 [管理] 清單中的 [Excel 增益集],然後按一下 [執行]。

  2. 選取 [現有的增益集] 清單中的 [分析工具箱] 方塊,再按一下 [確定]。

  3. 如果精靈顯示在 [非使用中應用程式增益集] 清單中,請選取精靈,然後按一下 [執行] 以使用該精靈。

  4. 必要時,請依循安裝程式上的指示操作。

若要使用條件式加總精靈,請選取資料範圍,並按一下 [條件式加總] 命令 (位於 [公式] 索引標籤上的 [解決方案] 群組)。依照步驟 1 到 4 完成精靈。下圖顯示精靈的步驟 2,其中有一個條件︰要加總的值必須大於 100。

[條件式加總] 精靈:第 2 步驟,共 4 個步驟

頁面頂端

使用單一函數或函數組合根據多個條件加總範圍內的值

使用 SUMIFS 函數。請提供您要加總的範圍,這個範圍要包含條件,以及適用於條件範圍的條件。或者,您可以巢狀的方式使用 SUMIF 函數。

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

另請參閱下列知識庫文件,以取得詳細資訊:

頁面頂端

加總平方值與陣列值

您可以使用工作表函數加總彼此對應之兩個以上的欄值。這種運算可以像計算加權平均值一樣簡單 (請參閱下列計算成績平均值的範例),也可以如統計或工程計算一樣複雜。

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

使用 SUMPRODUCT 函數。例如,您可以使用 SUMPRODUCT 函數並將其結果除以學分數,以計算某位學生的成績平均值,如下圖所示。

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

公式 =SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6) 將每堂課的成績乘以學分數,加總這些乘積 (61.3),將加總結果除以總學分數 (18),然後得到成績平均值 (3.41)。這類計算又稱為加權平均值。

您也可以將數個陣列乘起來,以更進階的數學運算方式使用 SUMPRODUCT 函數。

SUMPRODUCT 函數搭配有相同維度的陣列時很容易使用,但如果您需要更多彈性,也可以在陣列公式使用 SUM 函數。

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

下列四個函數通常僅用於進階統計或複雜的數學應用,因此只會在此提供簡短的說明。如需詳細資訊,按一下函數名稱即可直接移至其參考主題。

  • SUMSQ 函數:傳回數字清單或特定範圍內值的平方值加總。例如,SUMSQ(2,3) 傳回 13。

  • SUMX2PY2 函數:可加總兩個陣列中對應值平方值的加總。

  • SUMX2MY2 函數:傳回兩個陣列中對應值平方值差距的加總。

  • SUMXMY2 函數:傳回兩個陣列中對應值差距的平方值加總。

頁面頂端

特殊情況 (計算加總、唯一值)

您可以使用工作表函數建立當您在某個範圍或表格中加入其他資料時可自動更新的總計,或者僅加總某個範圍或表格中的唯一值。

使用函數來建立計算加總

使用 SUM 函數。例如,在工作表中建立下列名稱的欄標題:存款、提款及餘額。

  1. 在 [餘額] 欄下的第一個儲存格,輸入您的初始餘額。

  2. 在餘額正下方的儲存格 (在此案例為 C3) 輸入一個公式,以加上 [存款] 欄中同一列的值,並減去 [提款] 欄的值 (=SUM(C2,A3,-B3)

  3. 將此公式向下拖曳到您新增的所有列。在下列範例中,此公式會拖曳到列 4。

    如果將範例複製到空白工作表上,就會更容易了解。

    如何複製範例

    • 建立空白的活頁簿或工作表。

    • 選取 [說明] 主題中的範例。

      附註: 請不要選取列或欄名。

      選取 [說明] 中的範例

      選取 [說明] 中的範例

    • 按 CTRL+C。

    • 選取工作表中的儲存格 A1,然後按 CTRL+V。

    • 若要在檢視結果與檢視傳回結果的公式之間切換,請按 CTRL+` (抑音符號),或在 [公式] 索引標籤的 [公式稽核] 群組中,按一下 [顯示公式] 按鈕。

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 會在包含公式的儲存格中顯示 ### 字元。

如果將範例複製到空白工作表上,就會更容易了解。

如何複製範例

  • 建立空白的活頁簿或工作表。

  • 選取 [說明] 主題中的範例。

    附註: 請不要選取列或欄名。

    選取 [說明] 中的範例

    選取 [說明] 中的範例

  • 按 CTRL+C。

  • 選取工作表中的儲存格 A1,然後按 CTRL+V。

  • 若要在檢視結果與檢視傳回結果的公式之間切換,請按 CTRL+` (抑音符號),或在 [公式] 索引標籤的 [公式稽核] 群組中,按一下 [顯示公式] 按鈕。

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 狀態列會顯示這些儲存格資料的相關資訊。比方說,如果選取工作表上的四個儲存格,其中包含值 2、3、文字字串 (例如「雲端」) 和 4,下列所有的值都會同時顯示在狀態列上:平均值、計數、數字計數、最小值、最大值及加總。以滑鼠右鍵按一下狀態列,可顯示或隱藏任一值或所有的值。這些值顯示在下圖中。

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

頁面頂端

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×