Excel财务小技巧之26「Power Pivot实操基础」
Excel财务小技巧之26「Power Pivot实操基础」
前两篇我们先讲了Power Query，这篇开始我们进入Power Pivot的学习。日常应用也是按照这个顺序，先使用Power Query做数据清洗，再用Power Pivot搭建模型，用于后续的跨表透视及数据分析。1.Power Pivot 简介
（1）Power Pivot 是什么？
Power Pivot 是Excel的内置功能，作为Excel的原生加载项存在。它的核心功能是把分散在多张表中的数据通过“关系”连接起来，让 Excel 能够在多张表之间实现跨表查询和计算。（2）Power Pivot支持哪些Excel版本？从Excel 2016开始，Windows专业版及以上版本内置了Power Pivot功能。以下版本的 Excel不支持 Power Pivot：Excel for Mac、Excel 网页版、家庭和学生版。开发工具→COM加载项→勾选 Microsoft Power Pivot for ExcelPower Pivot内最常用的3个功能如下图红框：管理（数据模型）、度量值、添加到数据模型，稍后我们来具体介绍这些概念第一，处理大数据量。Excel 普通工作表到 100 万行左右就会明显变慢甚至崩溃。Power Pivot 把数据加载到内存中，可以处理几十万甚至几百万行数据，刷新速度也不受影响。第二，跨表关联和透视。不再需要 VLOOKUP/XLOOKUP逐表逐列匹配，而是通过唯一字段建立表间关系。关系建好后，还可以实现跨表透视。第三，支持DAX（Data Analysis Expressions）语言做动态计算。可以写出普通透视表和 Excel 公式做不出来的计算——比如年初至今累计、同比环比、动态排名等。这些计算随着你筛选条件的变化自动刷新，不用每次重写公式。Power Pivot 有很强大的跨表透视和分析功能。Excel基础的数据透视表的字段只能来自某一个表格，但是通过Power Pivot搭建模型之后，可以实现行、列、值分别来自不同表。省去了中间的函数匹配环节，且不会增加因匹配带来的冗余数据二、Power Pivot 模型搭建前置工作：数据规范在讲数据规范之前，有必要搞清楚2个概念：事实表VS维度表。事实表的特点是行数通常很多，而且里面的数值（金额、数量）是需要被求和、平均的。事实表建议只保留三类字段：业务标识（订单号）、主键/唯一值（用来连接维度表的字段）、数值字段；尽量不要在里面存放描述性的文本信息（比如：门店名称、大区名称、品牌次），这些信息应该放在维度表里。维度表有点类似于业务中常说的映射表/mapping表。维度表描述对象的属性，比如门店、产品、客户、日期。维度表的特点是每个实体只出现一次，行数少，里面的字段是做筛选和分组的依据。维度表里的每一个值都是唯一的标识，比如门店维度表里"门店编码"列不会有重复值，这是事实表所不具备的。来自维度表。比如门店表里的“门店编码”，每个门店只有一个编码，不会重复。主键的作用是让 Excel 知道“这个就是它，唯一的那个”。唯一值：就是主键的特点——这一列里的值不能重复。你可以把它理解成身份证号，一个人只能有一个，一个号只能对应一个人。在 Power Pivot 里建关系之前，先确认维度表的主键列没有重复值，否则关系会乱，或者导致关系创建失败。来自事实表，是事实表里用来“指”向主键的那个字段。比如销售明细表里的“门店编码”，这一列可以有重复（同一个门店卖了很多笔）。 一张大宽表里，凡是描述性的列（门店名称、大区、品牌、品类……），都应该单独拆出来放到维度表里；凡是数值性的列（金额、数量），留在事实表里。一是节省空间，门店名称、大区这些文本列在事实表里会重复几万次，拆出来之后模型更轻。二是更新方便，门店的大区调了，你只需要修改门店表里的那一行，不需要在几万行销售明细里逐个改。三是分析灵活，透视表的行标签、列标签、筛选条件都来自维度表，动态计算自动聚合。维度表的主键必须覆盖事实表中用到的所有外键。比如维度表只有员工 BJ00201、BJ00202，但事实表里出现了 BJ00203，这个员工就匹配不到，关系也建不起来。
另外，主键和外键的数据类型也要一致，尽量都保持文本格式。
有两种方式把数据加载到 Power Pivot 数据模型：（1）从Power Pivot窗口导入数据（不推荐）点击“Power Pivot”选项卡 → “管理”，打开 Power Pivot 窗口。在该窗口的主页选项卡中，点击“从数据源获取”，选择“Excel 文件”，找到数据文件即可导入。使用Power Query和Power Pivot的通常流程是先用Power Query做数据清洗，再直接加载到Power Pivot中在 Power Query 中，把销售事实表和产品信息维度表分别都处理好。每个查询做“关闭并上载至”，在弹出的窗口中选择“仅创建连接”，并勾选“将此数据添加到数据模型”。如果在数据清洗结束后，没有添加到数据模型，可以在数据选项卡→查询和连接中找到对应的查询，右击选择加载到再在导入数据界面选择“仅创建连接”，并勾选“将此数据添加到数据模型”这样数据就会被加载到数据模型中，而不会在工作表里占用行数。把销售事实表和产品信息维度表都添加到数据模型后，需要在它们之间建立关系，Excel 才能知道“SKU编码”在两张表之间是对应的。在上面一步，我们已经把销售清单和产品信息维度表加载到了数据模型中，现在我们在Power Pivot界面完成关系的创建。在“Power Pivot”选项卡中，点击“管理”，打开 Power Pivot 窗口。结果如下：你会看到“销售数据”和“产品信息”，分别为事实表和维度表，且显示出了表内的字段找到销售事实表中的“SKU编码”字段，按住鼠标左键，拖动到产品信息维度表的“SKU编码”字段上松开。（反方向拖也可以，只要字段拖对就可以）此时出现了报错，这就是我们之前讲到的：维度表的主键必须是唯一值我们回到数据源，使用条件格式识别出维度表的重复值并删除重复值，再点击保存选中SKU编码列 → 条件格式 → 突出显示重复值再进入到关系图视图界面，按上面的方法重新建立关系，结果如下：创建成功之后，可以关闭 Power Pivot 窗口回到 Excel 工作表。点击 Excel 工作表的任意空白单元格→插入 → 数据透视表→来自数据模型会看到字段都是以表的形式出现（VS普通透视表：以列名的形式出现）如下图带橙色标记的为来自查询的表，也是我们后续创建数据透视表时需要使用到的表这就是 Power Pivot 的优势所在——没有合并宽表，通过建立关系，实现跨表透视Step1：将店员信息表加载到Power QueryStep2：将店员信息表加载到Power Pivot，并创建关系【注意】即使列字段名称不一致，但是内容匹配，也可以成功创建关系。行/列/值分别来自3个不同的表，成功创建跨表透视，如下：
1. 报错：无法创建关系，主键包含重复值
原因：维度表的主键（如 SKU 编码、门店编码）有重复。
解决：回到 Power Query，对维度表删除重复项
刷新 Power Query → 刷新 Power Pivot
重新建立关系
2. 建了关系，但透视表不出数 / 显示空白
原因如下：
主键与外键格式不一致（一个文本、一个数字）
事实表有维度表里不存在的编码
解决：
统一改成文本格式
检查编码是否完全匹配
3. 透视表数据异常：翻倍、变多、重复计算
最常见原因：
关系建错或者没有建立关系
解决：
修正关系
案例：
透视出结果如下，各个店员的销量都一致，显然不符合实际业务场景
原因是店员信息表没有和事实表建立关系：
修正后结果：
4. 刷新后数据不变 / 不更新
原因：
只刷新了工作表，没刷新数据模型
Power Query 步骤出错
解决：
数据 → 全部刷新
Power Pivot → 全部刷新
检查 PQ 是否有报错步骤
5. 字段找不到 / 表不显示
原因：
没有勾选添加到数据模型
表被隐藏
解决：
查询和连接 → 右键加载到 → 仅创建连接 + 添加到数据模型
Power Pivot 中查看表是否被隐藏
把问题截图和具体的问题详细描述给AI，AI会帮忙识别可能的原因并指导操作1.既然可以用现有字段进行跨表透视，为什么还要学DAX函数、学建度量值？跨表透视确实能直接拖拽现有字段做汇总——比如把“实际收款金额”拖到值区域，求和、计数、平均值都能算。但它的能力止步于此。
举个例子：你想看“毛利率”，表里没有这个字段，跨表透视就做不到这一步，因为它只能对已有的列做简单聚合，没法定义“新计算”。
再比如“年初至今累计销售额”：你需要根据当前筛选的日期区间，动态累加从1月1日到当前日期的销售额。透视表本身也做不到。
DAX 度量值的价值就在于：你可以把任意复杂的计算规则写成一个“可复用的公式”，放进值区域里。 这个公式会随着你加的筛选器（年份、大区、品牌）自动重新计算，不用你每次手动改。
简单说：跨表透视做“现成数字”的加减，DAX 做“规则”的动态计算，包括：定义一个新字段、定义筛选范围等。
2.既然学了合并表，为什么还需要用Power Pivot创建关系？合并查询的方式是物理合并，比如：把产品信息表中的品牌、型号等多个字段合并到销售信息表，会导致销售信息表体积很大。
Power Pivot 的关系是逻辑连接——各表独立存放，只在需要的时候通过外键去查。
什么时候用合并查询？ 你要把数据导出给其他系统或者发给不会用数据模型的同事，他们需要一张完整的宽表。
什么时候用关系？ 你在 Excel 内部自己做动态分析，希望模型轻量、灵活、易维护。
本期是Power Pivot的第一篇，主要介绍了Power Pivot的功能、数据规范、模型搭建和跨表透视基础实操下期我们继续学习Power Pivot，介绍DAX和度量值的概念、介绍常用度量值写法，并通过实操案例，使用DAX函数写度量值。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
