樞紐分析表 · 試算表資料分析
Pivot Table
什麼是樞紐分析表?
- 拖曳就能做出的交叉彙總表
- 原始資料必須一列只有一筆
- 容易換個問法,從多個角度來檢視
開始使用樞紐分析表,最先改變的是製作報表的流程。通常每當收到"請按部門整理"、"這次改按季度"之類的要求,就會再多做一張新表。樞紐分析表是保持原始資料不動、只換掉檢視角度的方式,因此過去「一個要求就多做一張表」的結構,會變成「一份原始資料接上多個畫面」的結構。結果,負責人的時間就從做表,轉移到解讀數字上。
值區域除了合計,還可以改成計數、平均、最大、最小、百分比等;日期欄位則只要按右鍵一次,就能依月、季、年分組。不過,像'受訓人數有幾位'這類扣除重複的不重複計數,預設的樞紐分析表算不出來,必須在建立時加入資料模型,才會出現彙總選項。類似的工作也能用 SUMIFS 完成,但用途不同。格式固定的定期報表用公式,持續更換條件來探索資料則適合用樞紐分析表。若每月都要合併多個檔案,或列數超過數十萬筆,就該改用 Power Query 或 BI 工具了。
現場樞紐分析表的數字會出錯,原因幾乎都不在功能本身,而在原始資料。若外部取得的金額是以文字格式儲存,該列就會悄悄從加總中漏掉。在原始資料新增列也一樣,若沒有把範圍設成表格(Ctrl+T),新資料就不會被反映。再加上忘了重新整理,或直接擷取套用篩選的畫面,會議資料的總額就會和財務結帳數字相差 KRW 數千萬。更棘手的是,樞紐分析表不會顯示錯誤,只會呈現看似合理的表格,因此在報告前,必須先把總計與原始資料的加總核對一次。
1991 年,Lotus Improv 率先推出以多個維度重組資料的概念;1994 年微軟在 Excel 5.0 以「樞紐分析表」之名導入,使其普及化。此後,大多數試算表軟體都提供了相同的功能。
這是一家擁有 620 名員工、經營汽車零組件製造的 A 公司的故事。人才開發團隊 3 人負責執行每年 KRW 430,000,000 (≈ US$321,800) 的教育訓練經費,每季為經營會議製作依部門、依課程的彙總表,整整花了四天。因為受訓紀錄分散在各團隊的 12 個 Excel 檔案裡。(虛構範例)
- 人才開發團隊金課長將 12 個檔案合併到同一份工作表,去除合併儲存格與各團隊小計列共 47 列,並重新整理原始資料,使日期、部門、課程名稱、學員與金額都一筆資料一列。
- 將範圍設為表格後,列放部門、欄放月份、值放金額合計,3 分鐘內就做出第一張交叉表,旁邊的專員問:「這個以前是花四天做的嗎?」
- 將日期依季度分組、數值改為每人平均後,才終於看見一個事實:生產本部領導力課程的單價,每人比行政職系高出 KRW 180,000 (≈ US$135)。
- 財務團隊李副理指出"總額與 KRW 28,000,000 (≈ US$21,000) 對不上",確認後發現,委託機關提供的結算表中,部分金額是以文字格式儲存,因此漏在合計之外。改成數字後,就與結帳數字吻合了。
- 季度彙總作業從四天縮短為半天。會議中高階主管說「也依職級看看」,金課長隨即更換欄位,3 分鐘內就把畫面呈現出來。
Ja-ryong Gu
金鍾赫