Excel 数据整理工具 ——PowerQuery

大家好，我是处理数据的小爽鸭~

之前的文章，我们讲过项目部领导老叶为了方便分析，需要将如下图的左表转化处理成右表。

前面我们介绍过 Word 替换法，还有 Textsplit 函数的方法。详情戳文末链接。

不过，相信很多小伙伴都知道，PowerQuery 也是 Excel 专门用做数据清洗的利器。

所以，今天小爽就来带大家来看看这个案例的 PQ 解法。

适用版本：Excel2016 以上（WPS 不适用）

难度系数：⭐⭐⭐

戳戳手：该案例属于 PQ 中等难度，需要使用到一些 M 函数，大家重在学习思路。

我们先将数据导入到 PQ 编辑器中。

选中数据区域，在【数据】选项卡下，单击【来自表格 / 区域】，【确定】。

1、拆分到行

如果只有一列需要拆分，那好办。

我们直接用拆分列，拆分到行。

如下图：

单列就拆分好了。

但是，我们需要拆分到行的有两列。怎么办?

那，分别拆分，然后将所需列合并？

分别拆开。

然后再合并。

可以是可以，但是如果待拆分的列有多列，还要一个个拆分，然后拼接转表，这显然并不是个好的选择。

2、合并列

接下来，我们来看看另外的做法。

具体思路：

利用Table.CombineColumns将需要处理的列合并；

合并后的每一行，进行循环 (List.Transform) 拆分逗号Text.Split；

处理后再转表处理 (Table.FromColumns)；

最后展开表 (Table.ExpandTableColumn)。

看不懂，没关系，下面有详细步骤 ↓

具体步骤：

单击 fx 新建步骤。

❶ 获取待拆分的列名的列表 name

= List.Skip (Table.ColumnNames 源))

Table.ColumnNames(源)，是用来获取表格标题的函数。也就是 {"姓名","参与完结项目","业绩评分"}。

List.Skip表示跳过几个，第二参数不写默认为 1，所以跳过 1 个，也就是 {"参与完结项目","业绩评分"}。

这样的好处是，后面新增列，也能够获取最新待拆分的列名形成的 list。

将步骤命名为 name。

❷ 合并待拆的列

单击 fx 新建步骤。

公式栏中输入：

= Table.CombineColumns 源,name,each _,"a")

=Table.CombineColumns(表，需要合并的列名形成的列表，合并的方式，新列名)

案例中我们需要合并的列名，也就是第一步的 name。

= Table.CombineColumns 源,//表 name, //需要合并的列名形成的 listeach _, //合并的处理，先不做处理 "a" //合并后的列名称

❸ 循环合并处理，将文本按照逗号拆分

修改Table.CombineColumns第三参数合并处理

= Table.CombineColumns 源,name,each List.Transform (_,(x)=Text.Split (x,",")),"a")

如下图所示。

这里主要是针对合并处理 的每一行，循环进行拆分。

List.Transform (_,   //合并列每一行形成的 list。(x)=Text.Split (x,",") //将每个数据按照逗号进行拆分)

❹ 拆分后的数据，按照列进行转表

将Table.CombineColumns第三参数拆分后的列表按照列转表。

= Table.CombineColumns 源,name,each   Table.FromColumns         List.Transform (_,(x)=Text.Split (x,",")),             name),"a")

将拆分后的 lists，使用Table.FromColumns转换成表。

=Table.FromColums(lists, 对应标题)

按列转表后对应的标题，就是 name。

这一步命名为合并。

❺ 展开表格

最后一步，把表扩展开就可以。

单击展开按钮，取消勾选【使用原始列名作为前缀】，单击【确定】按钮。

直接展开，参数是写死的。展开的列名其实也就是 name。

所以，我们将公式改成如下图所示。这一步可以命名为结果。

= Table.ExpandTableColumn (合并,"a",name)

最终的 M 函数：

let    源 = Excel.CurrentWorkbook (){[Name="表 1"]}[Content],        name = List.Skip (Table.ColumnNames (源)),            合并 = Table.CombineColumns (                            源,                                       name,                                                                                                   each                                                                                     Table.FromColumns (                                                    List.Transform (_,(x)=>Text.Split (x,",")),                                                                                             name),            "a"),                           结果 = Table.ExpandTableColumn (合并, "a",name) in    结果

3、最后的话

本文讲解的是 PQ 解法，该方法涉及多个函数。

▋STEP01 获取待拆分的列名的列表

❶Table.ColumnNames可以获取表格的标题

❷List.Skip可以跳过指定个数

= List.Skip (Table.ColumnNames(源)

▋STEP02 合并指定列，先不做处理

Table.CombineColumns(表，name，each _，新列名)

▋STEP03 Table.CombineColumns的第三参数处理

❶ 循环拆分

List.Transform+Text.Split

❷ 按列转表

Table.FromColumns

▋STEP04 利用Table.EnpandTableColumn展开合并的列

第二参数拆分的列名，不要写死。

关于该案例的其他做法，请戳：文章。

将数据表转化后，老叶就可以通过数据透视表进行进一步的分析啦。

如下图：

比如上个季度，每个项目成员参与的项目数，总分是多少；每个项目有多少人参加，参与成员有谁。

也可以对数据进行可视化。

从整个案例中，我们可以看到整个数据的过程。

数据录入-数据清洗-数据分析-数据可视化-[数据汇报]

不同过程，所需要掌握的 Excel 知识点有所不同。

❶ 数据录入

数据验证 / 单元格格式/...

❷ 数据清洗

函数 / VBA / PowerQuery

❸ 数据分析

函数 / 数据透视表 / PowerPivot

❹ 数据可视化

图表 / 表格美化等

本文来自微信公众号：秋叶 Excel （ID：excel100），作者：小爽

广告声明：文内含有的对外跳转链接（包括不限于超链接、二维码、口令等形式），用于传递更多信息，节省甄选时间，结果仅供参考，IT之家所有文章均包含本声明。