跳到主要内容

Power Pivot 与数据模型

问题​

Power Pivot 是什么？如何用 Excel 数据模型处理大数据量分析？

答案​

什么是 Power Pivot​

Power Pivot 是 Excel 内置的数据建模引擎，使用列式存储（xVelocity/VertiPaq），可以：

  • 处理数百万到数千万行数据（突破 Excel 104 万行限制）
  • 建立多表关系模型（类似数据库）
  • 使用 DAX 公式做复杂计算

数据模型​

创建关系​

Power Pivot → 管理 → 关系图视图
拖动字段连接两个表 → 自动创建关系
关系类型说明示例
一对多（1:N）最常见产品表 → 订单表
多对多（M:N）需桥接表学生 ↔ 课程

星型模型​

数据模型推荐使用星型模型：

表类型特点内容
事实表行数多、数值数据订单金额、数量、时间戳
维度表行数少、描述信息产品名称、客户地区、日期属性

DAX 基础​

DAX（Data Analysis Expressions）是 Power Pivot 和 Power BI 的公式语言。

度量值 vs 计算列​

类型计算时机存储用途
度量值查询时动态计算不占存储聚合指标（总销售额、平均单价）
计算列数据刷新时计算占存储行级派生（利润率、年龄段）
优先使用度量值

度量值不占额外内存，且随切片器筛选动态变化。只有需要行级筛选或排序时才用计算列。

常用 DAX 函数​

聚合函数：

总销售额 := SUM(订单[金额])
订单数 := COUNTROWS(订单)
平均客单价 := DIVIDE(SUM(订单[金额]), DISTINCTCOUNT(订单[客户ID]))

CALCULATE（更改筛选上下文）：

// 北京销售额（无论透视表怎么筛选，都只算北京）
北京销售额 := CALCULATE(SUM(订单[金额]), 门店[城市] = "北京")

// 去年同期销售额
去年同期 := CALCULATE(SUM(订单[金额]), SAMEPERIODLASTYEAR(日期[Date]))

时间智能函数：

函数用途示例
TOTALYTD年初至今TOTALYTD(SUM(订单[金额]), 日期[Date])
TOTALQTD季初至今TOTALQTD(SUM(订单[金额]), 日期[Date])
SAMEPERIODLASTYEAR去年同期同比计算
DATEADD偏移日期DATEADD(日期[Date], -1, MONTH)
PARALLELPERIOD平移整个周期上月/上季/上年

同比环比示例：

// 同比增长率
YoY增长率 := 
VAR 本期 = SUM(订单[金额])
VAR 去年同期 = CALCULATE(SUM(订单[金额]), SAMEPERIODLASTYEAR(日期[Date]))
RETURN DIVIDE(本期 - 去年同期, 去年同期)

// 环比增长率
MoM增长率 := 
VAR 本期 = SUM(订单[金额])
VAR 上期 = CALCULATE(SUM(订单[金额]), DATEADD(日期[Date], -1, MONTH))
RETURN DIVIDE(本期 - 上期, 上期)

Power Pivot 与普通数据透视表对比​

维度普通透视表Power Pivot 透视表
数据量≤104 万行数千万行
多表关联不支持支持关系建模
计算能力基础聚合DAX 复杂计算
时间智能需手动公式内置时间函数
存储效率全量存储列式压缩（通常 10:1）

常见面试问题​

Q1: 什么时候应该用 Power Pivot 而不是普通透视表？​

答案：

  • 数据量超过 10 万行
  • 需要多表关联分析
  • 需要同比/环比等时间智能计算
  • 需要自定义复杂度量值
  • 数据来自多个数据源

Q2: DAX 中 CALCULATE 的作用是什么？​

答案：CALCULATE 是 DAX 最核心的函数，作用是修改筛选上下文后再计算：

// 无论透视表如何切片，始终计算全部总销售额
全部总额 := CALCULATE(SUM(订单[金额]), ALL(订单))

// 同时满足多个条件
高端北京 := CALCULATE(
    SUM(订单[金额]),
    门店[城市] = "北京",
    产品[类型] = "高端"
)

Q3: 如何创建日期维度表？​

答案：在 Power Pivot 中用 DAX 生成：

日期表 = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2025,12,31)),
    "年", YEAR([Date]),
    "月", MONTH([Date]),
    "季度", "Q" & QUARTER([Date]),
    "年月", FORMAT([Date], "YYYY-MM"),
    "星期", WEEKDAY([Date], 2),
    "是否工作日", IF(WEEKDAY([Date], 2) <= 5, "工作日", "周末")
)

然后将日期表标记为"日期表"以启用时间智能功能。

相关链接​