数据分析师必备的数据清洗和数据建模工具用法总结，超全易收藏

我们知道Excel数据处理的量级是百万级，如果是千万级的数据量级，Excel已经不能支持，无法响应，这个时候就需要特别的工具进行处理，由此，微软发明了Excel组件。

比如Power Query用于数据清洗，Power Pivot用于数据建模，Power Map用于三维地图可视化，Power View用于创建交互式图表，下面一起学习这些组件如何使用。

示例工具：Microsoft Office 专业增强版2019

本文讲解内容：Excel组件介绍

适用范围：批量级数据清洗和数据建模

一、Power Query

1、初识Power Query

Power Query 是Excel 的一个外接程序，可为信息工作者、BI 专业人员和其他 Excel 用户提供数据发现、数据转换和数据充实的顺畅体验，具体来说Power Query的功能如下。

• 数据连接，可以连接的数据包含本地文件Excel、文本文件等，还有数据库文件；
• 数据关联，多个数据文件之间可以根据公共列进行关联；
• 数据清洗，对多个数据进行关联后可以数据清洗

在Microsoft Office2016及以上的版本中，已经包含Power Query插件，对于低版本的Office来说，需要单独安装Power Query插件才能使用其功能，如下在数据选项卡下获取和转换，则是Power Query的功能。

2、数据获取

数据获取常用的有两种方式，一种是从本地文件获取，另一种是从数据库获取，比如这里从工作簿获取数据，可以将同一工作簿内不同Sheet表进行汇总，从文件夹获取数据，可以将同一文件内不同工作簿的数据进行合并，上面两种方式均只要在对应文件路径下找到对应的文件即可导入成功。

从数据库导入数据，前提是自己的计算机有数据库，比如这里点击从从SQL Server数据库中导入数据。

比如这里从本地数据库data中导入全部数据，关于SQL语句，在后面的章节会做讲解，点击确定，即可数据导入成功。

这里我们使用一个案例数据，选择获取与转化中的从表格，直接打开Power Query数据转化界面。

打开的界面似曾相识，与PowerBI数据转化的界面一模一样。

3、数据清洗

数据类型转换，选取数据字段，在转化里可以修改数据类型为小数。

数值排序，点击降序排列可以对销售额排序。

数据筛选，点击数据筛选按钮，这里只筛选2021年的销售数据。

分组求和，在转换里点击分组依据，分类字段是客户地址-省，自定义新列名，对于销售额做求和操作。

点击关闭并上载，即可将数据清洗后的结果上载到表格中。

二、Excel组件安装

Power Map、Power Pivot、Power View这三个插件在在Microsoft Office2016及以上的版本中自带，但需要从COM加载项中加载出来，点击文件，选项，在加载项管理里面选择COM加载项，然后点击转到。

在COM加载项中勾选Power Map、Power Pivot、Power View点击确定。

三、Powerpivot

1、初识Powerpivot

Powerpivot可以用于数据分析和模型建立，与Excel数据透视表相比，Powerpivot的数据分析功能绝对是升级版，尤其在模型建立方面，Powerpivot有着得天独厚的优势，用于处理大型数据集并且建立关系和创建模型，而Excel仅能处理小数据集，并且不能建立关系和创建模型。

2、获取数据

点击Power Pivot菜单，在管理选项卡下点击获取外部数据，这里同样可以获取本地数据以及数据库数据。

这里使用本节的案例数据，直接点击添加到数据模型，相当于将案例数据插入到Power Pivot中。

Power Pivot中点击插入一个数据透视表。

将年拖入到行，订单编号拖入到值，由于订单编号有重复值，这里在值字段设置中选择非重复计数，这个非重复计数的功能在普通的数据透视表是没有的，只有在Power Pivot才会有这个功能使用。

每一年非重复的订单编号数如下所示。

3、数据建模

Power Pivot可以建立关系，点击设计，在创建关系选项卡下点击创建，这里表1_2的主键是客户地址-省，表1的主键是客户地址-省，分别选择后，点击确定，创建关系。

在主页点击关系图视图，可以看到两张表以客户地址-省为主键连接在一起，且这种连接关系是一对多的连接关系。

四、Power Map

1、初识Power Map

Power Map是Excel的一个加载项功能，用于创建三维可视化地图，在地理可视化方面功能丰富，使用地图数据创建我们日常所熟知的热力图、空间分布图等，并且可以捕获屏幕截图，以视频的方式导出地图效果。

2、地图可视化

选取我们的地图数据，插入一个三维地图。

在图层这里设置将可视化更改为簇状柱形图，位置选择客户地址-省，高度设置为各省销售额求和，类别选择客户地址-省，每一个省份代表一种颜色。

做出的地图可视化结果如下。

五、Power View

在文件，选项中点击自定义功能区，选择不在功能区中的命令，找到Power View命令，接着点击新建组，重命名为MyReports，点击添加，点击确认即可。

可以看到，在插入选项卡下已经存在Power View命令选项。

选取数据，点击Power View即可创建图表，关于图表创建这部分内容，我会在PowerBI章节进行讲解，这里只需要有这个概念即可。