如何在 Excel 建立樞紐分析表
若要在 Excel 建立樞紐分析表,請按一下資料中的任一儲存格,選擇「插入」>「樞紐分析表」,然後把欄位拖曳到「列」與「值」。Excel 會依類別加總資料,完全不需要公式。本指南說明五個步驟、如何重新整理結果,以及如何用公式檢查樞紐分析表的總計。
文末連結的課程讓你用小型範例表練習相同的摘要,讓你在動到真實資料前先熟悉操作。
1. 準備資料
樞紐分析表需要整齊的清單:第一列是標題列,每一欄的名稱都不同,每一列代表一筆記錄,而且資料內部不能有空白列或空白欄。合併的儲存格,或夾在清單中間的小計,都會造成錯誤的總計。如果數字是以文字格式儲存,請先修正,因為樞紐分析表會計算文字的筆數,而不是加總。
2. 插入樞紐分析表
按一下清單中的任一儲存格,然後前往「插入」>「樞紐分析表」(在 Windows 上的按鍵是 Alt、N、V)。Excel 會自動選取整個清單。選擇「新工作表」,讓樞紐分析表有空間可以擴充,然後按一下「確定」。如果你還不確定要哪一種摘要,可以試試「插入」>「建議的樞紐分析表」,它會提供幾種現成的版面配置。
3. 拖曳欄位
右側的欄位清單會顯示你的欄標題。將文字欄位(例如「地區」)拖曳到列,將數字欄位(例如「銷售額」)拖曳到值。Excel 會依每個地區加總銷售額。若要將總計依另一個欄位拆分到上方,請將第二個欄位拖曳到欄;若要一次只看其中一部分,請拖曳到篩選。
數字預設會以加總計算。若要改為計數或平均,請按一下「值」方塊中的欄位,然後選擇「值欄位設定」。
4. 用公式檢查總計
在相信任何摘要之前,先用手算檢查一個數字。SUMIFS(或只有一個條件時使用 SUMIF)會算出與樞紐分析表相同的某一類別總計;若兩者相符,就證明資料與版面配置都正確。
範例:北區總計(樞紐分析表的顯示結果)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 地區 | 銷售額 | 北區總計 | |
| 2 | 北區 | 100 | 180 | |
| 3 | 南區 | 80 | ||
| 4 | 北區 | 50 | ||
| 5 | 東區 | 70 | ||
| 6 | 南區 | 20 | ||
| 7 | 北區 | 30 |
100 + 50 + 30 = 180。樞紐分析表中北區那一列必須顯示相同的數字。
5. 重新整理與分組
樞紐分析表不會在來源資料變更時自動更新。請在表格上按一下滑鼠右鍵,選擇「重新整理」,或按 Alt+F5。如果你在清單下方新增列,請先把來源轉換為 Excel 表格(Ctrl+T),樞紐分析表才會看見新增的列。若要將日期依月份或年份分組,請在「列」區域中的日期上按一下滑鼠右鍵,然後選擇「分組」。
常見錯誤
資料中有空白儲存格或空白列。 Excel 會在空白列處停止,其餘資料就不會列入樞紐分析表。
修正: 移除空白列,為每一欄加上標題,並將清單轉換為 Excel 表格。
忘記重新整理。 你變更了來源中的數字,但樞紐分析表仍顯示舊的總計。
修正: 在樞紐分析表上按一下滑鼠右鍵,選擇「重新整理」,或按 Alt+F5。
數字以文字格式儲存。 樞紐分析表顯示「計數」而不是「加總」,總計也會錯誤。
修正: 將欄位轉換為真正的數字,然後重新整理樞紐分析表,並把欄位設定改回「加總」。
常見問題
製作樞紐分析表需要公式嗎?
不需要。樞紐分析表會從欄位清單彙總資料。當你想檢查某個總計,或需要固定的版面配置時,像 SUMIFS 這樣的公式就很有用。
為什麼我的樞紐分析表顯示「計數」而不是「加總」?
當欄位包含文字或空白儲存格時,Excel 會計數該欄位。請修正來源,讓該欄每個儲存格都是數字,然後重新整理。
我可以從多個工作表建立樞紐分析表嗎?
可以,但先把資料合併成一個表格會比較容易。Power Query 可以附加或合併工作表,並讓結果保持最新。
相關函數
- SUMIFSSUMIFS 會同時加總符合多個條件的值,例如某個地區的已付款訂單。包含語法、範例,以及它與 SUMIF 的差異。.
- SUMIFSUMIF 會加總符合單一條件的值,例如某個地區的所有銷售額,或所有大於 100 的金額。包含語法、範例與常見錯誤。.
- COUNTIFCOUNTIF 會計算範圍中符合一個條件的儲存格:文字、數字、像 ">100" 這樣的比較,或萬用字元。包含語法、範例與常見錯誤。.
- AVERAGEAVERAGE 會傳回數字或範圍的算術平均值,並略過文字與空白儲存格。包含語法、範例、結果的捨入方式與常見錯誤。.
更多關於 樞紐分析表
本指南說明如何建立樞紐分析表並核對一個總計。本文補充如何在信任表格之前先讀懂它、如何改為顯示百分比,以及如何在它旁邊放公式而不讓它失效。
信任結果之前先檢查
樞紐分析表總是顯示整齊的格線,因此錯誤的總計看起來和正確的一樣可信。每次都請檢查三件事。第一,用來源資料的 =SUM(B2:B7) 核對總計。第二,看數字上方的標籤:Sum of Sales 和 Count of Sales 看起來很像,但意義不同。第三,檢查列標籤是否有近似重複的項目,例如「North」與尾端帶空格的「North 」,樞紐分析表會把它們顯示為兩列。
=SUMIFS(B2:B7,A2:A7,"North")顯示百分比而非加總
總計不一定是讀者需要的。在「值」區域中的數字上按一下滑鼠右鍵,選擇「值顯示方式」,再選取「總計的百分比」,即可看到每個地區的占比。「欄彙總的百分比」適用於每一欄是一個月份的表格,而「累計於」則顯示數字如何逐步累積。樞紐分析表仍保留底層的加總,因此你隨時可以切換回去。
樞紐分析表旁的公式
建立公式時如果按一下樞紐分析表的儲存格,Excel 會寫入 GETPIVOTDATA 參照,它以名稱而非位置指向某一個數字。表格重新排列時它仍然有效,但許多人覺得它難懂。若想改用一般的儲存格參照,請手動輸入位址,而不要用按一下的方式。
樞紐分析表在重新整理時會增減大小,因此切勿把資料放在它的正下方。若要建立隨資料更新的固定版面,請用 SUMIFS 與 COUNTIFS 來建立。想深入學習,請閱讀[條件加總課程](/learn/conditional-sums)或[如何計算不重複值](/guides/count-unique-values-in-excel)。