一个跨表透视的例子告诉你为什么要学PowerPivot

使用PowerPivot的第一步就是将数据加载到PP模型中。而将数据加载的PP中可以有两种形式从外部数据源加载或者从当前Excel工作簿加载。本文先通过一个例子讲讲如何将当前工作表中数据加载到PP模型中。

本次案例中包含三张表：客户、发票、发票明细。

如果需要按客户及发票日期对销量或者收入进行分析，需要将三张表中的字段合并起来。

• 通过CustomerID，将客户名称合并到InvoiceHeader；
• 通过InvoiceNumber，将发票日期和客户名称合并到InvoiceDetails；

在过去你需要在三个表之间不断的套用VLOOKUP函数，以达到将需要使用的字段合并到同一张表中进行分析的目的。

一、转换Excel智能表格

我们在以前的文章中介绍过Excel的智能表格，可以在点击【插入】选项卡-【表格】-勾选【表包含标题】-【确定】创建，也可以使用快捷键「Ctrl+T」快速创建。

将Excel数据加载到PP之前，建议将数据表转换成智能表格，并且给表起个有明确意义的名字，而不是使用默认的【表1】这样的容易混淆的名字。

在【表设计】选项卡-【属性】中修改表名

分别将客户表、发票、发票明细转换成智能表，并准确命名。这一步如果跳过不做，将数据添加到模型的时候，Excel也会自动应用智能表格，并且无法修改表名称。

二、添加到数据模型

这是正式进入PowerPivot建模的重要一步。我们选中数据表中任意单元格，点击【PowerPivot】选项卡-【添加到数据模型】

这时PowerPivot将复制一份原始表格，并且打开一个新的程序窗口。这个新的程序窗口就是PowerPivot。

虽然PP窗口看起来和Excel很像，但是在PP模型窗口中并没有行列号（比如：不存在A1、B2等），而且你也无法修改某一单元格内的数值。你看到数据只是Excel中数据的一个映射，与原数据保持着链接。

三、创建关系

将本例中的三个表分别加入到PowerPivot模型中，可以看到PP模型中包含的三张独立的工作表。点击表名就可以在不同表之间切换。

PowerPivot中的数据表格会跟随Excel中的原始数据表格的变化而自动更新，从Excel中直接加载到PowerPivot模型中的表又叫链接表（linked table）。

下一步是在【关系图视图】中建立表与表之间的关系。

这里表间的关系是客户表和发票表通过CustomerID列连接；发票表与发票明细表通过 InvoiceNumber 列连接。

也就是我们可以在发票表中对CustomerID列使用VLOOKUP函数，从客户表中查找需要的列，比如客户名、国家等。在发票明细表中对InvoiceNumber使用VLOOKUP函数从发票表中查找发票日期等。

在PP中创建关系很简单，将关系字段CustomerID拖拽到关联表的同名字段上，松开鼠标就可以将关系创建好。

三个表创建好关系后如下图所示。PP在表与表之间创建了一条线，这条线就和数据库中的 join 功能相似。

在PowerPivot中的关系都是指的多对一关系。在数据库和PowerBI中还有一对一，多对多的关系。有了这三条线的存在，我们就可以使用三个表中的任意字段进行透视分析了。

点击PP中主页的透视表，在新的工作表中创建透视表。

这时候出现在我们眼前的就像是一个普通的数据透视表。

仔细观察就能看到这个透视表与普通透视表的不同之处。那就是数据透视表字段中包含了三张表格。

普通数据透视表的字段只能来自于同一张表，PowerPviot的超级透视表可以跨多个表格进行透视。也就是说我们不再需要用VLOOKUP将表格字段合并再透视了。

比如我们可以将客户表的客户名字段拖到行区域，再将发票明细表的销量拖到值区域。这样就轻松实现了跨表透视。

End.爱数据网专栏作者：JaryYuan专栏名称：Power BI 自动化与可视化专栏简介：Excel、Power BI、Python等学习交流园地。Excel黑科技挖掘，Python小技巧。致力于办公自动化、职场效率提升、数据分析及可视化设计个人公众号：JaryYuan