时间戳

首页 > 技术文章 > Excel数据分析工具：PowerPivot

Excel数据分析工具：PowerPivot

gc2770 2021-06-28 21:31 原文

数据分析有三大作用：

  • 现状分析

  • 原因分析

  • 预测分析

大多数情况下，用Excel的数据透视表进行汇总分析即可，但数据量一大，Excel就跑不动了，那可以采用Access数据库和Microsoft Query，可以使用SQL语句进行查询汇总分析。

数据分析归纳起来有两大类：

  • 一类是呈现现状的描述性分析 。主要通过对比与细分进行现状和原因分析，可以制作数据透视表，求和、求平均值以及数据分组了解其构成，甚至通过不同时间维度的对比，查找数据变化原因，最后制作相关图标对现状进行呈现及描述。

  • 另一类是展望未来的预测性分析 。主要分析现有数据间的相关性，探寻数据之间存在的联系，并进一步通过建立相关回归模型的方式对未来进行合理的预测。

现在介绍一个工具，侧重于描述性分析的PowerPivot。

一、PowerPivot是什么

在PowerPivot中，Excel的行、列限制已被取消，这样我们能方便地操作更大型的数据。它的核心功能表现在：

  • 整合多数据源：PP可以从几乎任意地方导入任意数据源的数据，包括Web服务、文本文件、关系数据库等数据源。

  • 处理海量数据：可以轻松组织、连接和操作大型数据集中的表，处理大型数据集（通常几百万行）时所体现的性能就像处理几百行一样。

  • 操作界面简洁：通过固有的Excel功能（如数据透视表、数据透视图、切片器等），以交互方式浏览、分析和创建报表，只要我们熟悉Excel，就可以使用PowerPivot。

  • 实现信息共享：PowerPivot for SharePoint可以共享整个团队的工作簿或将其发布到Web。

Access、Microsoft Query和PowerPivot这三个工具分别在什么时候使用呢？ 在这里插入图片描述

每个工具有自己的优势及劣势，采用PowerPivot进行数据处理分析的集中情况：

  • 习惯使用Excel数据透视表进行数据分析

  • 数据量大，超出Excel范围，且未安装Access

  • 无须进行或只进行一些简单的数据处理操作

界面

  1. 单击【PowerPivot】选项卡【数据模型】组的【管理】； 在这里插入图片描述

  2. 【主页】选项卡主要用于添加新数据、从Excel和其他应用程序中复制和粘贴数据、获取外部数据源、制作报表、应用格式设置，以及排序和筛选数据等；

  3. 【设计】选项卡主要用于添加和删除列字段、在PowerPivot窗口或数据透视表上显示或隐藏列字段、更改表属性、创建和管理关系，以及修改与现有数据源的连接等；

因为我们还没创建PowerPivot表，所以各个选项都是灰色的。我们仍然以“用户明细表”、“订购明细表”的数据作为基础， 对用户购买行为进行分析，以便了解用户行为，制定相应的运行策略，提升用户价值与用户忠诚度。

三、确定分析思路

对于用户购买想行为分析，适合采用5W2H的方法进行分析，用思维导图表示如下所示： 在这里插入图片描述

四、导入数据

前面说到大型数据一般以TXT文本形式存储，所以我们将“用户明细.txt”文件导入。

  1. 在PowerPivot窗口中，单击【主页】选项卡【获取外部数据】组中的【从其它源】按钮，下拉选择【文本文件】； 在这里插入图片描述

  2. 在弹出的【表导入向导】中，将名称更改为【用户明细】，文件路径选择当前文件所在的路径。 在这里插入图片描述

  3. 后续的步骤根据提示操作。

  4. 按照刚才的步骤，将“订购明细”的文本文件也导入PowerPivot中。

五、简单数据分析

PowerPivot有一个巨大的优势，就是可以集成多数据源进行数据透视表或数据透视图的操作，来汇总、分析、浏览和呈现摘要数据。

1. 创建数据透视表

针对刚才分析框架中的具体问题来分析，首先要了解用户主要购买什么产品（What），即各产品的销量分布，操作步骤如下：

  1. 在PowerPivot窗口中，单击【主页】选项卡【报表】组组中的【数据透视表】按钮；

  2. 选择放置新建的数据透视表的位置。、

  3. 单击【确定】按钮，将弹出如下图的内容： 在这里插入图片描述

  4. 将“订购明细”表中的“产品”字段拖至“行标签”，“数量”移至“值”区域进行求和。 在这里插入图片描述

通过此表，我们了解到各个产品的销售分布情况，发现产品A和产品D的销售数量比较大。

六、多表关联分析

如果想了解购买用户的特征（Who），例如地域分布、哪个地区购买的用户最多、哪个地区购买的用户最少。 在PowerPivot工具中，无须使用类似VLOOKUP函数进行字段匹配，只需要像Access数据库那样建立两表之间的关联关系，即可把两表根据关键字段关联起来。 此时，我们需要将“订购明细”表与“用户明细”表根据关键字段“用户ID”创建关系，操作步骤如下：

  1. 在“订购明细表”中，单击“用户ID”任意一个数据单元格；

  2. 在【设计】选项卡【关系】组中，单击【创建关系】按钮，将弹出【创建关系】对话框，其中【表】字段和【列】字段将自动填充相应信息； 在这里插入图片描述

  3. 在【相关查找表】下拉列表中选择“用户明细”，在【相关查找列】上选择“用户ID”，如下图所示。创建关系时，必须为【相关查找列】选择具有唯一值的列。 在这里插入图片描述

  4. 在关系创建成功后，字段“用户ID”上会显示一个小图标；同时可以通过【设计】选项卡中【关系】组的【管理关系】功能来查看现有的关系列表，检查是否已成功创建所有关系。 在这里插入图片描述

  5. 创建一个新的数据透视表；

  6. 将“用户明细”表中的“省份”字段拖拉到【行标签】；将“订购明细表”中的“用户ID”字段拉到【值】区域进行计数；

  7. 对数据透视表中的“用户ID”字段进行降序排列。 在这里插入图片描述

通过数据透视表得到的分析结果，我们就可以清晰地了解购买用户地域分布情况，购买用户最多的三个身份是“上海”、“北京”、“广东”。后续可在此基础上，结合各省目标用户数分布，进行覆盖率的分析，还可以采用矩阵关联分析法，找出目标用户数多但覆盖率低的省份，对此制定出下一步的运营计划。

七、字段计算分析

1. 简单计算

简单计算与Excel类似，比如计算“订单金额 = 单价 * 数量”，操作步骤如下：

  1. 在PowerPivot窗口中，选择“订购明细表”；

  2. 在【设计】选项卡【列】组中单击【添加】按钮；或直接单击“订购明细表”最后一列【添加列】；

  3. 输入“=”，单击“数量”列的任意单元格或整列，再输入“”，单击“单价（元）”列的任意单元格或整列。整个公式为：“=‘订购明细’[单价(元)]‘订购明细’[数量]”；

  4. 按【Enter】键，此时系统将计算结果默认生成一列，列名为“计算列1”，可鼠标右击“计算列1”，并选择【重命名列】以修改列明，输入"订单金额"，重命名列名。 在这里插入图片描述

需要注意的是，PowerPivot的公司与Excel非常类似，但不能为表中的不同行创建不同公式，PowerPivot的公式是应用于整列中的。

简单计算中的一些常用场景如下表所示： 在这里插入图片描述

2. 函数计算

现在我们需要了解现有用户是在哪个月注册（Who）的，以了解现有用户的构成情况，而在“用户明细表”只有用户注册日期，我们需要使用相应的日期函数计算得到用户注册的月份数。

  • 在Excel中，MONTH的语法为：MONTH(serial_number)，其中serial_number是要查找的那个月的日期；

  • 在PowerPivot中，MONTH的语法是：MONTH(<date>)，其中date是提供对包含日期的列的应用或者通过使用返回日期的表达式。

操作步骤如下：

  1. 在PowerPivot窗口中，选择“用户明细表”；

  2. 在设计选项卡【列】组中单击【添加】按钮；或直接单击“用户明细”表最后一列“添加列”；

  3. 直接点击函数按钮fx。由于MONTH函数是日期和时间函数，因此弹出的【插入函数】对话框上的【选择类别】下拉列表中选择“日期和时间”，可进一步缩小范围。 与EXCEL类似，对于熟悉的函数，也可以直接输入等号然后输入公式。 在这里插入图片描述

  4. 选择MONTH函数，单击【确定】按钮。或直接输入函数名，公式栏将更新以显示该函数和左括号；

  5. 单击“注册日期”这一列的任一单元格或这一整列，即公式为“=MONTH（‘用户明细’[注册日期]”。这里需要注意：它不像Excel函数那样会自动添加右括号，需要我们自行输入。

  6. 单击右键选择【重命名列】，输入名称“注册月份”。

Excel也能完成类似的操作，要是100多万行的数据，PowerPivot还是能够快速的实现。

解决我们刚刚提出的WHO的问题，即用户注册分布情况：使用数据透视表。 在这里插入图片描述 补充一个知识点：

  • 我们刚才做的那些操作都是基于PowerPivot工作簿中的现有数据计算的，我们叫做“计算列”；

  • 而度量值是为使用PowerPivot数据的数据透视表（或）数据透视图而专门创建的公式。度量值用于数据透视表的【值】区域中。若是创建度量值，则需要使用标准聚合函数：MIN、MAX、COUNTA、SUM或AVERAGE，【度量值设置】对话框中的度量值就是使用标准聚合函数进行汇总计算的，这与Excel中数据透视表的【按值汇总】的部分功能相似。

八、数据分组分析

常用的数据分组方式主要包括数值分组与日期/时间分组两种。

1. 数值分组

在Excel中，数值分组可以用IF函数或VLOOKUP函数；日期/时间分组可以用日期/时间函数。此外，还可以通过直接在数据透视表创建组的方式来实现。不过在PowerPivot中数值分组不能通过数据透视表创建组来实现。

    =IF('用户明细'[年龄]<=20,"20岁及以下",IF('用户明细'[年龄]<=30,"21-30岁",IF('用户明细'[年龄]<=40,"31-40岁","40岁以上")))

在这里插入图片描述 在这里插入图片描述

2. 日期/时间分组

可以使用FORMAT函数，可对文本、数值、日期/时间等类型数据按指定格式要求进行格式化。这个函数在PowerPivot也可以用，我们采用FORMAT函数对日期进行分组，以便了解用户的注册月份分布（WHO）。 在这里插入图片描述 接着用数据透视表进行处理。

 

推荐阅读