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, //需要合并的列名形成的 list each _, //合并的处理，先不做处理 "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），作者：小爽