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之家所有文章均包含本声明。

文章价值:
人打分
有价值还可以无价值
置顶评论
    热门评论
      文章发布时间太久,仅显示热门评论
      全部评论
      一大波评论正在路上
        取消发送
        软媒旗下人气应用

        如点击保存海报无效,请长按图片进行保存分享