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

JaryYuan
JaryYuan
JaryYuan
40
文章
0
评论
2020-12-1213:12:00 评论 471 1666字
摘要

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

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

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

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

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

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

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

一、转换Excel智能表格

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

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

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

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

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

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

二、添加到数据模型

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

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

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

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

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

三、创建关系

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: