Power Pivot数据建模

本章概览

Power Pivot是Excel的数据建模和分析增益集,能够处理百万行级别的数据,创建复杂的数据模型,并使用DAX语言进行高级计算。它是Excel向商业智能(BI)方向发展的重要工具。

学习目标

掌握Power Pivot数据模型基础
学会创建表关系和星型模型
熟练使用DAX基础函数
掌握度量值和计算列
学会KPI和层次结构应用

前提条件 Excel 2013或更高版本(专业增强版/Microsoft 365)

27.1 Power Pivot入门

27.1.1 启用Power Pivot

检查是否可用

功能区是否有"Power Pivot"选项卡
如果没有,需要启用

启用方法

文件 → 选项 → 加载项
管理:COM加载项 → 转到
勾选"Microsoft Power Pivot for Excel"
确定

Power Pivot窗口

Power Pivot → 管理
打开Power Pivot数据模型窗口

组成:
- 表视图:查看和编辑数据
- 关系图视图:创建和管理表关系
- 计算区域:创建度量值

27.1.2 导入数据

从Excel导入

Power Pivot → 管理 → 从其他源
选择"Excel文件"

或:
先用Power Query导入和清洗
再加载到数据模型

从数据库导入

从其他源 → SQL Server/Access/Oracle
输入连接信息
选择表或视图

从Power Query导入

Power Query → 关闭并上载 → 关闭并上载到
勾选"仅创建连接"
勾选"将此数据添加到数据模型"

27.1.3 数据模型 vs 普通表

数据模型优势

1. 处理能力:
   - 普通表:最多104.8万行
   - 数据模型:数百万行

2. 关系:
   - 普通表:VLOOKUP函数
   - 数据模型:建立表关系

3. 计算:
   - 普通表:公式
   - 数据模型:DAX度量值

4. 存储:
   - 普通表:明文存储
   - 数据模型:列式压缩

27.2 创建表关系

27.2.1 关系类型

一对多关系(常用)

示例:
客户表(一) ←→ 订单表(多)
一个客户可以有多个订单

产品表(一) ←→ 订单明细(多)
一个产品可以在多个订单明细中出现

一对一关系

示例:
员工表 ←→ 员工详细信息表

多对多关系

Power Pivot不直接支持
需通过桥接表实现

示例:
学生 ←→ 选课记录 ←→ 课程

27.2.2 创建关系

方法一:拖放(关系图视图)

1. Power Pivot → 关系图视图
2. 拖动主表的主键到相关表的外键
3. 自动创建关系

示例:
拖动"客户表[客户ID]"到"订单表[客户ID]"

方法二:管理关系

设计 → 创建关系
1. 选择表1和列
2. 选择相关表2和相关列
3. 确定

示例:
表1: 订单表
列: 客户ID
相关表: 客户表
相关列: 客户ID

注意事项

- 主键列必须唯一(无重复)
- 数据类型必须相同
- 一个表只能有一个活动关系连接另一表
- 多个关系可存在,但只有一个激活

27.2.3 星型模型设计

概念

中心:事实表(Fact Table)
- 记录业务事件
- 包含度量值(销售额、数量等)
- 包含外键

外围:维度表(Dimension Table)
- 描述性信息
- 包含主键
- 提供筛选和分组

示例:销售数据模型

        维度表
          ↓
    ┌─────────┐
    │  日期表  │
    └────┬────┘
         │
    ┌────↓────┬─────────┬─────────┐
    │         │         │         │
┌───↓───┐ ┌──↓───┐ ┌──↓───┐ ┌──↓───┐
│产品表 │ │客户表│ │地区表│ │销售表│← 事实表
│       │ │      │ │      │ │(中心)│
└───────┘ └──────┘ └──────┘ └──────┘

关系设置

销售表[产品ID] → 产品表[产品ID]
销售表[客户ID] → 客户表[客户ID]
销售表[地区ID] → 地区表[地区ID]
销售表[日期]   → 日期表[日期]

27.3 DAX基础

27.3.1 DAX简介

什么是DAX

Data Analysis Expressions
数据分析表达式

用途:
- 创建计算列
- 创建度量值
- 创建计算表

DAX vs Excel公式

相似:
- 使用函数
- 使用运算符(+、-、*、/)

不同:
- DAX引用整列或表
- DAX自动处理上下文
- DAX有专用聚合函数

27.3.2 基本语法

引用列

同表: [列名]
其他表: 表名[列名]

示例:
[销售额]
产品表[产品名称]

引用表

表名
示例:
RELATED(客户表[客户名称])

常量

数字: 100
文本: "北京"(双引号)
日期: DATE(2024,1,1)
布尔: TRUE / FALSE

运算符

算术: + - * / ^
比较: = < > <= >= <>
逻辑: && (AND) || (OR)
连接: & (文本连接)

27.3.3 计算列 vs 度量值

计算列

特点:
- 存储在表中
- 行级计算
- 占用内存
- 创建位置:表视图

创建:
在表下方空白行输入公式

示例:
总额 = [单价] * [数量]

度量值(推荐)

特点:
- 不存储,动态计算
- 聚合计算
- 节省内存
- 创建位置:计算区域

创建:
Power Pivot → 度量值 → 新建度量值

示例:
总销售额 = SUM([销售额])

选择建议

计算列:
- 需要在行级别计算
- 需要筛选、排序、分组

度量值:
- 聚合计算(求和、计数、平均值)
- 动态计算(随筛选条件变化)
- 大多数情况推荐使用

27.4 常用DAX函数

27.4.1 聚合函数

SUM/AVERAGE/MIN/MAX

总销售额 = SUM([销售额])
平均单价 = AVERAGE([单价])
最高价格 = MAX([单价])
最低价格 = MIN([单价])

COUNT/COUNTA/COUNTROWS

# COUNT: 计数数字
订单数 = COUNT([订单号])

# COUNTA: 计数非空
客户数 = COUNTA([客户名称])

# COUNTROWS: 计数行
总行数 = COUNTROWS(订单表)

DISTINCTCOUNT

# 计数唯一值
唯一客户数 = DISTINCTCOUNT([客户ID])
唯一产品数 = DISTINCTCOUNT([产品ID])

27.4.2 CALCULATE函数

语法

CALCULATE(表达式, 筛选器1, [筛选器2], ...)

基础用法

# 北京地区销售额
北京销售额 = CALCULATE(
    SUM([销售额]),
    地区表[地区] = "北京"
)

# 高价产品销售额
高价产品销售额 = CALCULATE(
    SUM([销售额]),
    产品表[单价] > 100
)

多条件筛选

# 北京地区的苹果销售额
= CALCULATE(
    SUM([销售额]),
    地区表[地区] = "北京",
    产品表[产品名] = "苹果"
)

ALL函数移除筛选

# 总销售额(忽略所有筛选)
总销售额 = CALCULATE(
    SUM([销售额]),
    ALL(销售表)
)

# 占比计算
销售额占比 =
DIVIDE(
    SUM([销售额]),
    CALCULATE(SUM([销售额]), ALL(销售表))
)

27.4.3 时间智能函数

日期表要求

必须包含连续日期
必须标记为日期表(设计 → 标记为日期表)

TOTALYTD/TOTALQTD/TOTALMTD

# 年初至今销售额
年初至今销售 = TOTALYTD(
    SUM([销售额]),
    日期表[日期]
)

# 季初至今
季初至今销售 = TOTALQTD(SUM([销售额]), 日期表[日期])

# 月初至今
月初至今销售 = TOTALMTD(SUM([销售额]), 日期表[日期])

同比环比

# 去年同期销售额
去年销售额 = CALCULATE(
    SUM([销售额]),
    SAMEPERIODLASTYEAR(日期表[日期])
)

# 同比增长率
同比增长率 =
DIVIDE(
    SUM([销售额]) - [去年销售额],
    [去年销售额]
)

# 上月销售额
上月销售额 = CALCULATE(
    SUM([销售额]),
    PREVIOUSMONTH(日期表[日期])
)

# 环比增长率
环比增长率 =
DIVIDE(
    SUM([销售额]) - [上月销售额],
    [上月销售额]
)

DATEADD

# N天前/后
N天前销售 = CALCULATE(
    SUM([销售额]),
    DATEADD(日期表[日期], -7, DAY)  # 7天前
)

# N月前/后
去年同月 = CALCULATE(
    SUM([销售额]),
    DATEADD(日期表[日期], -12, MONTH)
)

27.4.4 关系函数

RELATED

# 从相关表获取值(多方引用一方)
在订单表中创建计算列:
客户名称 = RELATED(客户表[客户名称])
产品类别 = RELATED(产品表[类别])

RELATEDTABLE

# 获取相关表的多行(一方引用多方)
在客户表中创建度量值:
客户订单数 = COUNTROWS(RELATEDTABLE(订单表))
客户总消费 = SUMX(RELATEDTABLE(订单表), [金额])

USERELATIONSHIP

# 激活非活动关系
# 当两表间有多个关系时使用

示例:订单表有"下单日期"和"发货日期"
都关联到日期表,但只有一个活动

发货金额 = CALCULATE(
    SUM([金额]),
    USERELATIONSHIP(订单表[发货日期], 日期表[日期])
)

27.4.5 迭代函数

SUMX/AVERAGEX

# 逐行计算后聚合
总金额 = SUMX(
    订单明细,
    [单价] * [数量]
)

平均订单金额 = AVERAGEX(
    订单表,
    [总金额]
)

FILTER

# 筛选表返回子集
高价产品销售 = SUMX(
    FILTER(
        产品表,
        产品表[单价] > 100
    ),
    [销售额]
)

# 复杂条件
= SUMX(
    FILTER(
        订单表,
        订单表[金额] > 1000 && 订单表[地区] = "北京"
    ),
    [金额]
)

27.5 数据透视表与Power Pivot

27.5.1 基于数据模型的数据透视表

创建

Power Pivot → 数据透视表
选择新工作表或现有工作表

字段列表

显示所有数据模型中的表
可拖动字段到:
- 筛选器
- 列
- 行
- 值

优势

可使用多个表的字段
关系自动处理
可使用创建的度量值
性能更好(百万行数据)

27.5.2 切片器

创建切片器

插入 → 切片器
选择维度表的字段

推荐:
- 日期(年、季、月)
- 地区
- 产品类别
- 客户类型

切片器交互

多个切片器自动联动
筛选同时影响所有数据透视表和图表

27.6 KPI和层次结构

27.6.1 KPI(关键绩效指标)

创建KPI

1. 创建基础度量值:
   实际销售额 = SUM([销售额])

2. 创建目标度量值:
   目标销售额 = SUM([目标])

3. 右键"实际销售额" → 创建KPI
4. 定义KPI目标 → 选择"目标销售额"
5. 定义状态阈值:
   - <90%: 红色
   - 90-100%: 黄色
   - >100%: 绿色

在数据透视表中使用

字段列表 → 选择KPI字段
自动显示:
- 值
- 目标
- 状态(图标)

27.6.2 层次结构

创建层次结构

示例:时间层次
1. 右键"年" → 创建层次结构
2. 命名为"时间"
3. 右键层次结构 → 添加到层次结构 → 季度
4. 添加 → 月份
5. 添加 → 日期

结果:时间层次
  └ 年
    └ 季度
      └ 月份
        └ 日期

在数据透视表中使用

拖动层次结构到行
自动支持钻取:
点击+号展开下一级
点击-号折叠

常见层次

地理层次:
  国家 → 省份 → 城市 → 区县

产品层次:
  大类 → 中类 → 小类 → 产品

组织层次:
  公司 → 部门 → 团队 → 员工

27.7 实战案例

案例1:销售分析数据模型

表结构

事实表:
- 销售表(订单号, 产品ID, 客户ID, 日期, 数量, 单价)

维度表:
- 产品表(产品ID, 产品名, 类别, 成本)
- 客户表(客户ID, 客户名, 地区, 类型)
- 日期表(日期, 年, 季度, 月, 周)

建立关系

销售表[产品ID] → 产品表[产品ID]
销售表[客户ID] → 客户表[客户ID]
销售表[日期] → 日期表[日期]

关键度量值

# 基础度量
销售额 = SUMX(销售表, [数量] * [单价])
销售数量 = SUM(销售表[数量])
订单数 = DISTINCTCOUNT(销售表[订单号])
客户数 = DISTINCTCOUNT(销售表[客户ID])

# 成本和利润
成本 = SUMX(
    销售表,
    [数量] * RELATED(产品表[成本])
)
毛利 = [销售额] - [成本]
毛利率 = DIVIDE([毛利], [销售额])

# 时间智能
年初至今销售 = TOTALYTD([销售额], 日期表[日期])
去年同期 = CALCULATE(
    [销售额],
    SAMEPERIODLASTYEAR(日期表[日期])
)
同比增长 = DIVIDE(
    [销售额] - [去年同期],
    [去年同期]
)

# 占比分析
销售额占比 = DIVIDE(
    [销售额],
    CALCULATE([销售额], ALL(产品表))
)

案例2:库存周转分析

额外表

库存表(产品ID, 日期, 期末库存)

度量值

# 平均库存
平均库存 = AVERAGE(库存表[期末库存])

# 库存周转率
库存周转率 = DIVIDE(
    [销售成本],
    [平均库存]
)

# 库存周转天数
周转天数 = DIVIDE(
    365,
    [库存周转率]
)

# 缺货天数
缺货天数 = CALCULATE(
    COUNTROWS(库存表),
    库存表[期末库存] = 0
)

27.8 最佳实践

27.8.1 数据模型设计

星型模型优先

√ 一个事实表,多个维度表
× 雪花模型(维度表再关联)
× 多个事实表直接关联

维度表设计

√ 主键唯一
√ 包含描述性字段
√ 相对稳定(不常变化)
√ 去除冗余字段

事实表设计

√ 包含度量值(数量、金额等)
√ 包含外键连接维度
√ 粒度一致
× 不存储可计算的值

27.8.2 DAX优化

度量值优先于计算列

# 不推荐(计算列)
总额 = [单价] * [数量]

# 推荐(度量值)
总额 = SUMX(表, [单价] * [数量])

使用变量

# 不推荐(重复计算)
毛利率 =
DIVIDE(
    SUM([销售额]) - SUM([成本]),
    SUM([销售额])
)

# 推荐(使用变量)
毛利率 =
VAR 销售额 = SUM([销售额])
VAR 成本 = SUM([成本])
RETURN
DIVIDE(销售额 - 成本, 销售额)

避免复杂嵌套

# 复杂度量值拆分为多个简单度量值
# 再组合使用

本章小结

核心要点

Power Pivot:处理百万行数据的BI工具
数据模型:建立表关系,星型模型
DAX语言:计算列和度量值
常用函数:聚合、CALCULATE、时间智能
KPI和层次:监控指标和钻取分析

Power Pivot vs 数据透视表

功能	数据透视表	Power Pivot
数据量	<100万行	数百万行
多表关系	不支持	支持
高级计算	有限	DAX强大
性能	一般	优秀
学习曲线	低	中高

学习建议

先掌握Excel公式和数据透视表基础
理解数据库基本概念(表、关系)
从简单DAX函数开始学习
多做实际案例练习
参考官方DAX函数文档

下一步学习

第28章:Excel VBA编程基础
第29章:Excel与Python集成
第30章:Excel自动化办公

思考练习

创建一个销售数据模型,包含产品、客户、日期维度
建立表关系,设计星型模型
创建度量值计算销售额、毛利、毛利率
使用时间智能函数计算同比、环比
制作包含KPI的仪表盘

练习提示

先设计模型结构再导入数据
确保关系正确建立
测试度量值计算是否准确
使用数据透视表验证结果

本章概览​

27.1 Power Pivot入门​

27.1.1 启用Power Pivot​

27.1.2 导入数据​

27.1.3 数据模型 vs 普通表​

27.2 创建表关系​

27.2.1 关系类型​

27.2.2 创建关系​

27.2.3 星型模型设计​

27.3 DAX基础​

27.3.1 DAX简介​

27.3.2 基本语法​

27.3.3 计算列 vs 度量值​

27.4 常用DAX函数​

27.4.1 聚合函数​

27.4.2 CALCULATE函数​

27.4.3 时间智能函数​

27.4.4 关系函数​

27.4.5 迭代函数​

27.5 数据透视表与Power Pivot​

27.5.1 基于数据模型的数据透视表​

27.5.2 切片器​

27.6 KPI和层次结构​

27.6.1 KPI(关键绩效指标)​

27.6.2 层次结构​

27.7 实战案例​

案例1:销售分析数据模型​

案例2:库存周转分析​

27.8 最佳实践​

27.8.1 数据模型设计​

27.8.2 DAX优化​

本章小结​

思考练习​