Excel 数据整理工具 ——PowerQuery

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

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

Excel 数据整理工具 ——PowerQuery

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

不过,相信很多小伙伴都知道,Po, . K r | E j 4 )werQuery 也是 Excel 专门用做数据清洗的利器。

所以,今天小爽就来带大家来看_ q m x 5 m G 6 [看这个案例的 PQ 解法。

适用版本:Excel2016 以上(WPS 不适用)

难度系, i H q数:⭐⭐⭐

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

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

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

Excel 数据整理工具 ——PowerQuery

1、拆分到行

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

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

如下图:

Excel 数据整理工具 ——PowerQuery

单列就拆分好了。

Excel 数据整理工具 ——PowerQuery

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

那,b t X 8 T , 3分别拆分,然后将所需列合并?

分别拆开。

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

然后再| + 7合并。

Excel 数据整理工具 ——PowerQuery

K 2 U K z ; 3 ?以是可以,但是如果待拆分的列有多列,还要一! ` ~ F个个拆分,然后拼接转表,这显然并不是个好的选择。

2、合并列

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

O X e Y c 3 r体思路:

利用Table.Combing P 6 4eColumns将需要处理的列合并;

合并后的每一行,进行循环 (p } tList.Transform) 拆分逗号Text.Split

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

最后展开表 (Table.ExpandTableColumn)。

看不懂,没关系,下面有详细步骤 ↓z ) A H S

具体步骤:

单击 fH 9 f _ x 2 rx 新建步骤。

Excel 数据整理工具 ——PowerQuery

❶ 获取待O b [ K h V / I n拆分的列名的列表 name

=List.Skip(Table.ColumnNames源))

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

List.Skip表示跳过几个,第二参数A ( & U B$ r e , 9 h i F K写默认为 1,所以跳过 1 个,也就是 {“参与完结( 1 2 + U 2项目”,”业) Z ] J b绩评分”}。

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具7 % K ——PowerQuery

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

将步骤命名为 name。

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

❷ 合并待拆的列

单击 fx 新建步骤。

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——Pc w HowerQuk { @ , 1 4 *ery

公式栏中输入:

=Table.CombineColumns源,name,each_,"a")
Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

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

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

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

❸ 循环合并( s [ ) e q – _ G处理,将文本按照逗号拆分

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

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

如下图所示。

Excel 数据整理工具 ——PowerQuery

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

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

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

Table.CombineColumns第三参数拆分后的列表5 # O 0 1 @ ? `按照列转表。

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

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

=Table.FromC/ / P 9olums(lists, 对应标题)o [ _ $ _

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

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——\ x l o | 1 +PowerQuery

这一步命名为合并。

Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

❺ 展开表格

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

单击展开按钮,取消勾选【使用原始列c : e F – B名作为前缀】,单z ? Q U I J ( ,击【确定】按钮。

Excel 数据整理工具 ——PowerQuery

Excel 数, f g据整理工具 ——PowerQuV J @ery

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

Excel 数据整理工具 ——PowerQuery

所以,我们将公式改成如下图所示。这一步E L \ , x可以命名为结果。

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

Excel 数据整理工具 ——PowerQuery

最终的 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
结果
Excel 数据整理工具 ——PowerQuery

Excel 数据整理工具 ——PowerQuery

3、最后的话

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

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

Table.ColumnNames可以0 f [ 2 b获取表格的标题

List.Skip可以跳过指定个数

=List.Skip(Table.ColumnNames(源)

Y t F – ?STEP02合并指定列,先不做处理

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

▋STEP03Table.Combi& 3 4 % L i qneColumns的第三参数处理

❶循环拆分

List.Transform+C * ) 1 5 * eTe6 ~ L Txt.Split

❷按列转表

TablQ 2 _ * \ M r N Te.FromColumns

▋STEP04利用Table.E! Z P F H mnpandTableColumn展开合并的列

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

关于该案例的其他做法M P V # ; 5,请戳:文章

将数据表转化后,老叶就o 6 6可以通过数a { U j * S _ 4 W据透视表进行进一步的分析啦。

如下图:

比如上个季度,每个项目成员参与的项目数,总分是多少;每个项1 G Q o 1 p H目有多少人参加,参与成员有谁。

Excel 数据整理工具 ——PowerQuery

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

Excel 数据整理工具 ——PowerQuery

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

数据录入-数据清洗-数据分析D ) G b 6 2 – m-数据可视化-[数据汇报]

不同M / ; , | ) B %过程,所需要掌握的 Excel 知识\ 8 . .点有所不同。

❶ 数据录入

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

❷ 数据清L , + Q ] f

函数 / VBA / PowerQuery

❸ 数据分析

函数 / 数据透视表 / PowerPivot

❹ 数据可{ $ – a视化

图表G : W D m . o 7 / 表格美化等

免责声明: 本站提供的一切软件、教程和内容信息仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络收集整理,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑或手机中彻底删除上述内容。如果您喜欢该程序和内容,请支持正版,购买注册,得到更好的正版服务。我们非常重视版权问题,如有侵权请邮件与我们联系处理。敬请谅解!

给TA打赏
共{{data.count}}人
人已打赏
office办公

Excel 使用函数快速合并 1000 个单元格

2023-12-9 10:15:08

office办公

教大家用 Excel 做出漂亮的百分比图表

2022-12-27 11:38:13

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索