Skip to content

Productivity › 試算表 › Power Pivot

Excel 中的 Power Pivot (Power Pivot in Excel) ​

Power Pivot 是 Excel 的資料模型 (Data Model) 層:把多張表建立關聯、用 DAX 定義可重用的量值 (measure),再讓樞紐分析表與 dashboard 共用同一個模型。它和 Power BI 的 semantic model 是同一套引擎,概念可以直接沿用到 Power BI 模型與 DAX。

什麼時候用 ​

適合一般樞紐分析表就夠
多張相關的表(交易、產品、客戶、日期)只有一張表
需要跨表計算、可重用的指標簡單彙總
資料超過工作表 104 萬列上限資料量小
多張樞紐表、圖、slicer、KPI 要一致一次性報表

啟用:檔案 → 選項 → 增益集 → COM 增益集 → 勾選 Microsoft Power Pivot for Excel(Windows 桌面版)。

流程 ​

text
Power Query 整理資料 ──► 載入資料模型 ──► 建立關聯 ──► 計算欄 / 量值 (DAX)
  ──► 樞紐分析表 / 樞紐分析圖 / slicer / KPI
工具負責
Power Query擷取、轉換、載入 (ETL)
Power Pivot關聯、DAX、量值、KPI

資料模型 ​

概念說明
事實表 (fact table)事件與數值:交易、訂單、量測;列數多
維度表 (dimension table)描述欄位:產品、日期、地區、客戶;每個 key 一列
星狀結構 (star schema)事實表在中間,維度表圍繞,以一對多關聯連接
關聯 (relationship)維度表的唯一 key(一)→ 事實表的外鍵(多);在「圖表檢視」拖拉建立
日期表 (date table)連續日期的維度表,標記為日期表後時間智慧函數才可靠

先想關聯,再寫 DAX;關聯沒定清楚,量值結果就不穩。比起把所有欄位 merge 成一張超寬表,star schema 更好維護、彙總也更正確。設計原則見 維度模型。

計算欄 vs 量值 ​

計算欄 (calculated column)量值 (measure)
計算層級逐列 (row context)依目前篩選彙總 (filter context)
何時計算資料重新整理時,結果存在表中查詢時(樞紐表每格各算一次)
佔記憶體是否
放在樞紐表的列、欄、篩選值
適合分類標籤、要用來切分或建關聯的欄加總、比率、轉換率、毛利率

需求是「報表上要顯示的彙總數字」時,先想量值,不要先建一堆計算欄。

dax
-- 量值:基本聚合
Total Sales := SUM ( Sales[Amount] )

-- 量值:比率(DIVIDE 自動處理分母為 0)
Margin % := DIVIDE ( [Total Profit], [Total Sales] )

-- 量值:改變篩選條件
North Sales := CALCULATE ( [Total Sales], Region[Name] = "North" )

-- 計算欄(在 Sales 表):從關聯的維度表取值
Category = RELATED ( Product[Category] )

Excel Power Pivot 中量值的寫法是 名稱 := 運算式(Power BI 用 =)。filter context、CALCULATE 的完整說明見 Power BI 模型與 DAX。

KPI 與 Dashboard ​

KPI 把實際值、目標、狀態放在同一個指標裡(Power Pivot → KPI → 新增 KPI:選一個量值為基準,設定目標值或目標量值與門檻)。樞紐分析表、樞紐分析圖、slicer 都接在同一個資料模型上時,dashboard 各元件的數字會一致;設計原則見 Dashboard 設計。

實務守則 ​

守則原因
資料先用 Power Query 整理乾淨再載入模型層不適合做清理
先建關聯,再寫 DAX量值依賴關聯傳遞篩選
量值優先,計算欄節制計算欄佔記憶體、不隨篩選變化
量值集中放在一張表、取清楚的名稱方便重用與查找
團隊共用、需要排程更新時模型搬到 Power BI