Excel 樞紐分析表 — 十分鐘把流水帳變成報表

Excel 樞紐分析表可以把幾千筆逐筆記錄的流水帳,在不寫任何公式的情況下,拖幾個欄位就變成分類加總的報表。能不能做到「十分鐘出報表」,關鍵不在操作技巧,而在原始資料是否符合「一列一筆紀錄、一欄一個欄位」的格式。資料格式對了,後面大約 80% 的步驟都是拖放欄位。 樞紐分析表解決的是什麼問題 樞紐分析表的功能,是把「逐

Excel 樞紐分析表可以把幾千筆逐筆記錄的流水帳,在不寫任何公式的情況下,拖幾個欄位就變成分類加總的報表。能不能做到「十分鐘出報表」,關鍵不在操作技巧,而在原始資料是否符合「一列一筆紀錄、一欄一個欄位」的格式。資料格式對了,後面大約 80% 的步驟都是拖放欄位。 樞紐分析表解決的是什麼問題 樞紐分析表的功能,是把「逐筆記錄」的資料依照你指定的維度重新分組、加總、排序。例如一份含日期、門市、品項、數量、金額的銷售流水帳,只要把「門市」拖到列、「月份」拖到欄、「金額」拖到值,就能得到門市 × 月份的營收交叉表。 這個功能已經發展了三十多年。根據 「Microsoft 在 1993 年推出的 Excel 5 首次加入 PivotTable 功能」(來源:Wikipedia) ,概念則源自 1991 年的 Lotus Improv。之後每個版本陸續加入交叉分析篩選器、時間軸、資料模型等功能,但核心操作至今沒有改變:列、欄、值、篩選四個區域。 樞紐分析表能處理的資料量取決於 Excel 本身的上限。依 「單一工作表上限為 1,048,576 列 × 16,384 欄,樞紐分析表的值欄位上限為 256 個」(來源:Microsoft 官方規格) ,一般中小企業一整年的銷售明細通常還在這個範圍內。超過百萬列的資料,就要改用 Power Pivot 的資料模型載入。 先整理資料:決定成敗的前三分鐘 樞紐分析表最常失敗的原因,是原始資料不是「整齊資料」(tidy data)。統計學者 Hadley Wickham 在 《Tidy Data》(Journal of Statistical Software, 2014) 提出三個原則:每個變數是一欄、每筆觀測是一列、每種觀測單位是一張表。Excel 樞紐分析表要的正是這種格式。 整理資料所花的時間通常比分析本身還多。 「資料科學工作者平均把約 45% 的時間花在資料載入與清理」(來源:Anaconda《State of Data Science 2020》) ,Excel 使用者的情況也差不多。建樞紐之前,請先檢查以下五點: 第一列只有一列標題 :不要用合併儲存格,也不要疊兩層標題。 沒有空白列或小計列 :手動加的「小計」會被當成一筆資料重複加總。 同一欄只放同一種資料型別 :金額欄若混進「—」或「待確認」,Excel 會改成「計數」而不是「加總」。 日期是真正的日期值 :看起來是「2026/09/01」的文字不能依月份分組。可以用 =ISNUMBER(A2) 檢查,回傳 TRUE 才是真日期。 不要把月份攤成欄位 :「一月、二月、三月」各佔一欄的寬表格要先轉成「月份、金額」兩欄的長表格。可以用 Power Query 的「取消資料行樞紐」一鍵轉換。 最後,選取資料範圍按 Ctrl + T 轉成「表格」。這樣之後新增的資料列會自動納入樞紐分析表的來源範圍,不必每次手動改範圍。 十分鐘建表流程:從插入到出報表 假設你有一份 5,000 筆的銷售流水帳,欄位為「日期、門市、業務、品項、類別、數量、金額」。以下步驟依時間順序排列。 第 1–2 分鐘:插入樞紐分析表 點選表格內任一儲存格,選「插入 → 樞紐分析表」,Windows 版也可以按 Alt → N → V 。建議選「新工作表」,讓報表和原始資料分開。沒有頭緒時,可以先用「建議的樞紐分析表」,Excel 會依欄位型別提供幾種版面讓你挑選。Microsoft 的完整操作說明見 〈建立樞紐分析表以分析工作表資料〉官方教學 。 第 3–5 分鐘:拖放四個區域 列 :放「門市」,每個門市一列。 欄 :放「日期」。Excel 2016 以後會自動分組成年、季、月,可以在日期上按右鍵選「群組」只留下「月」。 值 :放「金額」,預設是加總。若顯示「計數」,代表該欄混入了文字,要回頭清理資料。 篩選 :放「類別」,可以一次只看某個產品線。 第 6–8 分鐘:改變值的呈現方式 同一個「金額」欄位可以拖進值區兩次,分別用不同的計算方式呈現。在值上按右鍵選「值的顯示方式」,常用選項如下: 總計百分比 :看各門市佔全公司營收的比例。 與前一項的差異百分比 :以月份為基準,直接算出月增率。 排名(由最大到最小) :門市業績排名,不必另寫 RANK 公式。 要算平均客單價這類衍生指標時,用「樞紐分析表分析 → 欄位、項目和集 → 計算欄位」,輸入 =金額/數量 。計算欄位會先分別加總再相除,所以結果是加權平均,不是逐筆平均後再平均。 第 9–10 分鐘:加上互動篩選並格式化 插入「交叉分析篩選器」(Excel 2010 起提供)來選門市或業務,插入「時間表」(Excel 2013 起提供)來拖選日期區間。同一個篩選器可以透過「報表連線」同時控制多張樞紐分析表,做出簡易的儀

相關文章

相關工具書

由 FeiYueh 親自審稿驗證 · 最後更新於 2026-11-18. Independently maintained — not AI-generated boilerplate.

← Back to Blog