很多人还不知道的Excel 20年来最大革新技术：Power Pivot

2019-8-24 09:30| 发布者: ieclass| 查看: 2164| 评论: 0

摘要: 如果透视表是Excel的明星，那超级透视表将是你未来的超级明星。很难一句话说清什么是Power Pivot什么是Power Pivot？当你真正开始思考这个问题的时候，你已经是深度Excel用户了。但这个问题很难一句话说清。看看大家 ...

智能拖地机器人，云鲸 NARWAL

如果透视表是Excel的明星，那超级透视表将是你未来的超级明星。

很难一句话说清什么是Power Pivot

什么是Power Pivot？当你真正开始思考这个问题的时候，你已经是深度Excel用户了。但这个问题很难一句话说清。看看大家不同的说法:
微软说PowerPivot 是一种数据建模技术，用于创建数据模型，建立关系，以及创建计算。可使用 PowerPivot 处理大型数据集，构建广泛的关系，以及创建复杂（或简单）的计算，这些操作全部在高性能环境中和所你熟悉的 Excel 内执行。
维基百科说Power Pivot is a feature of Microsoft Excel. It is available as an add-in in Excel 2010 and 2013, and is included natively in Excel 2016. PowerPivot extends a local instance of Microsoft Analysis Services Tabular that is embedded directly into an Excel Workbook. This allows a user to build a ROLAP model in PowerPivot, and use pivot tables to explore the model once it is built. This allows Excel to act as a Self-Service BI platform, implementing professional expression languages to query the model and calculate advanced measures.

意思是:Power Pivot是微软Excel的一项功能。它在Excel 2010以及Excel 2013中以插件形式存在，Excel 2016已内置包含这项功能。Power Pivot实际是将微软SSAS Tabular(表模式)直接嵌入到Excel工作簿中，它为用户提供了一种直接可以建立ROLAP模型的能力，并且可以直接使用透视表来探索，Excel实际上就成为了自助分析式商务智能平台，可以使用专业的表达式语言在模型中进行查询以及进行复杂的计算。

维基百科说的很准确，但很难直接理解透彻。
智能拖地机器人，云鲸 NARWAL

再来看看MrExcel.com创始人Bill这样说:Power Pivot是Excel在20年的改进中最棒的改进。

为什么这样说，太夸张了吧。
所以，真的需要一篇能够介绍Power Pivot的白话文，透视表(Pivot Table)已经太出名了，以至于Power Pivot也用了它的名字，但:Power Pivot却不是透视表(Pivot Table)。搞不好很多人就已经被误导了。

她出现得如此低调

如果说Power Pivot真的那么强大，她却不露声色，等你发现。若你正使用Excel 2016(不论是什么版本)创建透视表，你会发现:

如果你刚从WPS或Excel 2007偶然因为使用了Excel 2013 / Excel 2016，你会看到这里有两处略有区别:

使用此工作簿的数据模型
智能拖地机器人，云鲸 NARWAL
选择是否想要分析多个表，将此数据添加到数据模型

并没有出现Power Pivot，但这就是了，它不是透视表，或者说透视表没有变，但透视表具有了新的生命力。

不得不说的透视表

也许你使用透视表已经成了习惯，但有必要仔细琢磨一下透视表到底做了什么，让我们对它如此依赖，透视表成了Excel的明星。

让我们归零，从最简单的案例开始，这是一个简单的数据:

人们对于这样的数据会存在直觉式的问题:

按时期来看，每个月的销售额是怎样的趋势？
智能拖地机器人，云鲸 NARWAL
按地区来看，哪个地方销售状况更加好？

透视表的伟大就在于它给了用户洞察数据背后规律的能力，我们在这个表上创建透视表如下:

还可以使用Excel内置的数据可视化工具来对数据进行可视化:

事实上不止于此，Excel是世界上最伟大的数据可视化工具，它已经发展多年，目前非常成熟稳定，几乎所有的办公人员都可以使用，包括透视表。

问题来了

透视表是伟大的，而现实会变得越来越复杂，带来新的挑战:

以往的数据透视表只允许用户使对单个数据表建立透视表。这只适用于非常简单的情况，很快就无法满足现实的需求。现在用户往往需要联立多个表的数据展开分析，例如:产品，类别，客户，订单。这就是VLOOKUP函数这么出名的原因，因为Excel提供的这个函数可以将不同的数据整理到一个单表里，再用数据透视表处理。
智能拖地机器人，云鲸 NARWAL
Excel的每个工作表只能存储100W条数据，所以无法在真正的大数据集上使用数据透视表。从历史上来看，Excel作为客户端工具可以连接到SSAS分析服务来做OLAP分析的，但这需要IT技术人员假设好服务器、建立好整个数据仓库供业务人员来使用，这是十分昂贵的。另外在Excel中随着要处理的数据量级的增加(几十万级)，Excel对数据处理的速度会逐渐变慢，想象一下某一处修改会带来几十万次的单元格公式重新计算。
智能拖地机器人，云鲸 NARWAL
更重要的 问题是Excel透视表只能完成基本的聚合运算以及有限扩展，类似于同比，环比，年度至今，ABC分析等就很难进行。
对于这三类情况，是传统Excel(传统电子表格类软件)无法跨越的屏障。

超级透视表不是透视表

Pivot Table 译为“透视表”，Power Pivot 很自然地可以译为“超级透视表”，但很明显透视表已经足够好了，透视表本身并不需要改进。
需要改进的是:为透视表提供数据的直接来源本身。
那么正如你猜到的，Power Pivot就是这个角色，它提供了一种可以直接用透视表来分析的数据组织方式。这在Excel中称为:数据模型。
于是透视表就可以开始应对这样的情况:

不再使用VLOOKUP，却可以轻松处理。如果你正在学习透视表及VLOOKUP的Excel课程，不妨考虑是不是直接学得再彻底些。
使用Power Pivot可以做到这些以往无法在Excel中实现的:
获取数据:从一个或多个来源获取数据，并组织成一个数据模型。(获取数据的部分已经由专门的Excel工具Power Query来胜任，更加强劲。参考：)如下所示:

建立模型:为原有的数据添加使用自定义的表列以及自定义的度量值，并直接提供给透视表使用。如下所示:

突破限制:Power Pivot内部提供了高度压缩数据的方式，并在Excel内再建立一个数据库来保持高度压缩的数据。这意味着Excel可以存储存储高达上亿的数据而不需要离开Excel文件。如下所示:

需要强调的是:Power Pivot并不改变透视表本身，而是改变了透视表背后的直接数据来源。Power Pivot将透视表背后数据来源组织成数据模型并提供了强大的数据压缩与运算能力。

换到运动档，开启涡轮增压

如果你在驾驶Excel，那日常办公需要的仅仅是经济档。将Excel切换到运动档(引擎以无限可能运作不受限制)，开启涡轮增压(最大程度压缩数据不受限制)，来源于Excel内置的SSAS Tabular(前述维基百科的揭秘)引擎开始发威:

图太复杂没看懂？好吧，Excel120来做个关于Power Pivot的一句话定义:
Power Pivot就是微软把自家数据库大杀器SQL Server的分析服务引擎拆出来装到到大众式的Excel中做出来的性能怪兽法拉利。
我们需要的绝不仅仅不是办公函数(VLOOKUP)，而是真正的赛车引擎: