数据分析工具

数据透视表的英文叫做Pivot Table, 那Power Pivot又是什么东东？它们两个有什么区别呢？通过上一篇文章对Excel数据透视表Pivot Table的介绍，大家应该对数据透视表有了基本的了解，这是Excel学习中的重中之重，也是我们学习Power Pivot的基础。如果还没有熟练掌握数据透视表，可以翻看上一篇文章：数据透视表：菜鸟也能做数据分析。

但是在学习Power Pivot之前，还是需要有数据分析的思维和方法，毕竟Power Pivot只是工具，要把工具使用好还得明白原理，知道怎么做远比做本身要重要。推荐大家看一下猴子老师的 数据分析课。课程内容包括：数据分析思维和方法论、业务基础知识与数据分析工具；课程讲师是知乎数据分析首席讲师、畅销书《数据分析思维》作者猴子老师。主打数分新手必备，掌握思维才能扎实使用各种数据分析工具，

数据透视表Pivot Table本质上就是个筛选器和计算器，通过不同的筛选器，在列/行的交叉维度下，计算各种不同的指标，完成多维度交叉数据分析。Power Pivot在形式上与Pivot没有差别，我们可以简单认为是数据透视表Pivot Table的升级，在但功能上却比数据透视表强大很多，主要体现在以下三点：

1）Power Pivot能够融合多个数据源，并通过关联搭建模型，功能更加强大；

2）Power Pivot能够高效处理海量数据，突破Excel百万行数据处理的限制；

3）Power Pivot拥有比数据透视表更强大的分析功能，完成更加复杂的分析；

Excel的数据透视表连接的是单个数据表，而Power Pivot连接的是数据模型，数据模型可以是很多表关联形成的表。举个简单的例子，我们用Excel创建透视表，只能针对单张表，无法对多张表建立一个透视表。而PowerPivot可以利用关联关系连接多个表，搭建庞大的数据模型，进行更加复杂的分析，所以Power Pivot是一个数据建模工具，功能更强大。

对于Excel来说，每个工作表只能存储100W条数据，超过100W条的数据则无法处理，而且数据量较大的情况下处理速度较慢。要解决这个问题，可以使用一些BI工具，如PowerBI、Tableau等，但这些BI工具学习难度大，技术门槛高，短期内不一定能够上手学会。

而Power Pivot就是介于Excel和复杂BI工具之间的一种，它既可以处理百万级以上的数据，同时和EXCEL深度融合，简单易上手，可直接通过EXCEL建模，然后用透视表进行各种分析，对于一些无BI基础的小白上手简单、十分友好。

Excel透视表只能完成基本的聚合以及有限的扩展运算，比如求和、平均值等。但遇到类似于同比，环比，年度至今累计等稍微复杂的分析往往很难处理。

而Power Pivot可以直接从一个或多个来源获取数据，结合DAX语言构建各种各样的度量值，搭建数据模型并直接形成“超级透视表”进行数据探索！

比如要计算去年同期的值，Power Pivot中通过DAX使用SAMEPERIODLASTYEAR函数便可实现。

我们已经大致了解了Power Pivot及其功能，那到底怎么使用Power Pivot？在Excel 2013以上的版本中，Power Pivot没有直接出现在菜单栏中。第一次使用它时，我们需要从COM加载项中安装。

单击文件 > 选项 > 加载项，从管理列表中选择“COM加载项”，然后单击“转到”，选中 Microsoft Power Pivot for Excel 复选框，然后单击确定。

这样，Power Pivot选项卡就会在功能区上出现。

我们通过一个实际案例介绍Power Pivot的应用场景，并演示 Power Pivot的使用步骤。

我们以一个经典的电商销售分析为场景，从数据库中导出销售明细数据，这个数据记录了指定时间段内所有销售交易明细。除此之外，还有所有客户的详细信息数据、以及所有产品详细信息的数据，分别存储在3个CSV文件中。我们想将这3个CSV文件导入到Power Pivot中，对其进行分析，并找出销量前5位的产品、分析不同国家不同类别产品的平均售价。

在未使用Power Pivot之前，我们会将文件导入到三个不同的工作表Sheet中，然后使用 VLOOKUP 将3个Sheet中的数据进行关联，形成一个大宽表，最后利用数据透视表对大宽表进行分析，对于数据量较大的情况，VLOOKUP的效率就很低了。

但是有了Power Pivot，我们可以将以上3个CSV文件直接导入到数据模型中进行高效存储，然后在3个表之间创建关联关系（而不是VLOOKUP一个个低效地匹配），最后再使用DAX创建度量值，最后在数据透视表中进行分析。

使用Power Pivot进行数据分析的第一步是将数据导入到Excel，将数据导入 Excel 的最佳方法是使用Power Query。Power Query是一种内置于 Excel 的工具，可以方便地实现外部数据的导入和转换。但是Power Query 不在本文的讨论范围内，我们以销售明细表.csv文件为例介绍导入的流程。

单击数据 > 从文本/CSV，在“导入数据”对话框中找到 CSV 文件，然后单击“确定”。然后，就会出现个带有文件预览的窗口。单击窗口底部的“编辑”按钮。

将加载 Power Query 编辑器窗口。我们可以在Power Query里使用很多工具来对数据进行预处理和转换，例如更改某列数据类型，新增一列等。

如果我们不需要使用Power Query进行数据处理和转换，可以按照以下步骤直接将数据导入 Power Pivot中。单击数据 > 从文本/CSV >选择对应文件>加载到，将出现导入数据窗口。选择“仅创建连接”并选中“将此数据添加到数据模型”。

此时数据就加载到模型中了。但是，在工作表里看不到具体的数据，因为我们仅创建了连接，并未真实存储数据。下图显示了已加载的销售明细表.csv文件。它包含106,693行数据，数据量已经很大了，但对于 Power Pivot 的计算性能来说也是小菜一碟。

对其他2个 CSV 文件重复此过程，把产品信息表和客户信息表也加载到数据模型中。加载完成后如下图所示。

零基础又想入门学习数据分析师的小伙伴，可以点击下面的卡片，是知乎官方组织的数据分析训练营，花0.1元就能买到知乎站内数据分析顶级大神猴子老师的课程，不仅教数据分析工具技术，更主要是教数据分析思维和方法，这个“天大的馅饼”可不要错过了！

数据已经加载到Power Pivot 中，单击 Power Pivot 选项卡上的“管理”按钮，将显示 Power Pivot for Excel 窗口。默认的视图是数据视图，类似于工作表。但是这只是一个预览，并不是真正的数据存储。

除了“数据视图”之外，还有一个“关系图视图”，可以通过单击主页选项卡上的“关系图视图”按钮打开。关系图视图通过图形化的方式呈现数据表，非常适合创建表之间的关系。

下面就在“关系图视图”下给三个表之间创建关系，也就是创建数据模型，为了方便操作，将销售明细表拖到产品信息表和客户信息表下。

销售明细表包含交易的明细信息，是实际发生的事实，我们称之为“事实表”。产品信息表和客户信息表包含了产品和客户的信息，和“事实表”中的某些字段相关，我们称为“维表”。我们在客户信息表vs销售明细表、产品信息表vs销售明细表之间创建两个“一对多”关系。因为客户可以与我们进行一次或多次销售。产品也是如此，一个产品可以销售一次或多次。

创建表之间的关系非常简单，将销售明细表中的 Customer ID 字段和客户信息表中的 ID 字段之间单击并拖动即可。同理，把销售明细表中的 Product ID 字段和产品信息表中的 ID 字段之间创建关系。

下图显示了已创建好的关系。数据的过滤方向用箭头显示，同时还显示了 1 和星号 (*) 符号来表示“一对多”的关系类型。

搭建好数据模型后，我们就可以基于模型创建数据透视表进行数据分析了。

单击插入 > 数据透视表。Excel 会自动检测数据模型，使用并从中创建数据透视表。选择数据透视表位于现有工作表上，然后单击确定。

数据透视表已经创建好，可以在字段列表中看到3个表，我们可以像使用透视表一样拖动各个表中的字段到数据透视表的区域。

现在我们就可以基于这个数据透视表进行数据分析了，回顾一下我们前面提到的第一个问题：计算销售额最高的5种产品。

将Product Name字段从产品信息表拖到行区域。然后将 Total Sales 字段从销售明细表表拖到值区域。这就可以得到每个产品的销售额汇总。

如果只要显示销售额前5的产品，其余产品不显示。可以单击行标签的过滤器箭头，选择值过滤器 > 前10项(T)，将10更改为5，然后单击确定，我们就得到了数据透视表中的销售额前5的产品。

上面的步骤中，我们将 Total Sales 字段拖到数据透视表的值区域时，它创建了一个隐式度量，所谓隐式度量就是系统自动对字段进行的求和、计数等聚合操作，不需要手动建立、系统自动生成的度量值。

我们可以像以前那样使用数据模型中的数据透视表。如果只是做简单的分析，这些默认的隐式度量就足够了。但是如果要做一些更复杂的计算，就需要使用DAX创建度量值了。

DAX语言是一个十分强大的语言，我们不在这里展开介绍，DAX可以灵活对帮我们创建各种复杂的度量值，远远超过了标准的SUM和AVERAGE，可以实现我们不同的分析需求。

再回顾我们之前提到的第二个问题：分析不同国家、不同类别产品的平均销售单价。

由于平均售价这个度量值在现有数据模型中并不存在，且无法通过隐式度量直接实现。所以我们看看如何在Power Pivot中使用 DAX 语言创建对应的度量值。

平均销售单价=销售额/销售量，基于这个公式我们创建一个平均销售单价的度量值。单击 Power Pivot 选项卡 > 度量 > 新建度量。

1.选择要存储度量值的表，我们把新建的度量将存储在销售明细表中。

2.输入度量值的名称，我们把度量值命名为“平均销售单价”。

3.度量值的说明。仅对度量值的解释说明，可以不填写。

4.在提供的框中输入以下公式：=SUM([Total Sales])/SUM([Units Sold])

5.单击检查公式按钮。没有错误提示就说明公式正确无误。

6.从格式类别中选择度量值的数据格式以及要显示的小数位数。单击确定。

创建好度量值后，我们可就以在数据透视表中使用它进行分析。使用我们在本教程前面创建的数据透视表，就会发现我们新创建的度量值已经显示在表字段列表中，前缀为fx。将其拖入值区域，将客户明细表中的Country字段和产品明显表中的Product Category字段拖入到行区域，就可以得到不同国家、不同类别产品的平均售价了。

本文介绍了什么是Power Pivot以及Power Pivot的强大之处，并通过实际的案例演示了Power Pivot的数据导入，数据建模，并基于数据模型建立数据透视表，对于简单的分析，我们使用隐式度量值即可进行分析，对于复杂的分析场景，我们还简单介绍了DAX这个强大的语言，通过DAX创建灵活、丰富的度量值，给Power Pivot赋予了更加强大的功能。

但是Power Pivot并不是数据分析的终极武器，为了更好地整合多种不同数据源、进行数据清晰处理、数据分析和数据可视化，微软推出了PowerBI这个神器，在一个软件上即可完成数据分析的全部流程，再搭配上灵活强大的DAX和丰富的可视化图表，让数据分析变得更加便捷和高效，我们在之后的文章中再接着介绍。

以上就是数据分析工具—Excel Power Pivot部分的内容，部分数据分析工具请翻看历史文章，更多数据分析工具的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和收藏哈~