Excel隐藏的数据处理技巧

什么是PowerQuery？PowerQuery是实现数据获取和数据清理的一个工具，是PowerBI的一个组件，当然在Excel2016版本及以上已经内置为Excel的一个插件，可以用于数据连接和数据清洗。

本文使用案例数据给大家介绍PowerQuery的常用数据清洗方法，可以作为Excel数据处理的高阶用法，使用菜单操作即可完成复杂的数据清洗任务，下面一起学习PowerQuery在数据清洗中的用法。

示例工具：Excel2016

本文讲解内容：PowerQuery数据清洗

适用范围：Excel高阶数据处理方法

1、数据获取

点击数据在新建查询中选择从Excel工作簿导入，选择路径下的数据文件，全选两个sheet表中的数据，然后点击转换数据，进入PowerQuery界面。

2、标题设置

点击将第一行用作标题，可以将原本默认的标题设置为第一行为标题。

3、追加查询

将成绩表1和成绩表2纵向连接在一起，点击追加查询，默认第一个表为主表，选择要连接的第二个表。

4、向下填充

将科目中默认空值的向下填充，将原有空值补充完整。

5、拆分列

将原有带逗号分隔符的一列，按照分隔符分列为两列数据。

6、数据逆透视

将二维数据转换为一维数据，单击科目这一列，在逆透视列中选择逆透视其他列，这里将两列成绩透视为一列成绩。

7、删除列

点击删除列，将多余的列删除。

8、空值替换

将分数为空值的进行替换，点击替换值，在要查找的值中输入一个空单元格，替换值输入为0，点击确定。

9、提取人名

这里人名与分数在同一列，先提取人名，点击自定义列，写入如下公式：

Text.Remove([值],{"0".."9","分"})

上述公式表示将值这一列中的数字和分移除，只剩下人名。

10、提取分数

提取分数，点击自定义列，写入如下的公式：

Text.Remove([值],{"一".."龢"})

上述公式表示将值这一列中的所有文本进行移除，只剩下数字。

11、数据类型更改

数字提取后默认为文本格式，点击分数这一列，在数据类型选择整数。

12、删除分数为0的行

将分数为0的行在筛选项中取消勾选，即可删除分数为0的行。

13、降序排列

单击分数这一列，在排序中选择降序排列。

14、分组依据

分组依据类似为分类汇总功能，要计算各个科目分数的平均值，点击分组依据，分组选择科目，新列名命名为平均分数，柱选择分数。

- END -

对比Excel系列图书累积销量达15w册，让你轻松掌握数据分析技能，可以在全网搜索书名进行了解：