Excel数据分析工具:PowerPivot

数据分析有三大作用:

  • 现状分析
  • 原因分析
  • 预测分析

大多数情况下,用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)。
在这里插入图片描述
接着用数据透视表进行处理。

  • 3
    点赞
  • 0
    评论
  • 13
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值