二维码 购物车
部落窝在线教育欢迎您!

Power Query的数据替换技巧比Excel函数更万能!

 

作者:过儿来源:部落窝教育发布时间:2021-05-20 10:54:25点击:1994

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

说到Excel的替换操作,大家首先想到的一定是SUBSTITUTE和REPLACE函数。可是,今天需要处理的替换问题,这两个函数也束手无策,那要怎么做呢?下面,小E要介绍的就是Excel中,比函数更强大的“万能”替换方法——Power Query!一起来看看吧!

 

哈喽,大家好,作为在职场中摸爬滚打多年的Exceler,函数一直是我们的好朋友,关键时候写个函数就可以解决很多数据方面的问题。但是随着数据的五花八门,有时候我们也会发现,面对某些问题,函数好像不是那么“灵光”了,这个时候大家就需要考量下是不是可以用Power Query。下面就借用替换问题开启我们的“从函数到Power Query”之路。

 

Excel中说到替换,函数家族中的两大替换函数SUBSTITUTEREPLACE函数就当仁不让了。

 

1:已知开户行信息和账号需要提取银行名称。(如下图),这时就可以利用SUBSTITUTE函数替换。

 

 

SUBSTITUTE的基础语法是:

SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])

最后一个参数,[替换第几个],是可以省略的。

 

所以,提取银行账号可以直接在E2处输入公式:

=SUBSTITUTE(C2,D2,"")

 

 

2:将电话号码的中间4位数字处理成星号。

REPLACE的基础语法是:

REPLACE(要替换的字符串,开始位置,替换个数,新的文本)

 

B2中输入公式:

=REPLACE(A2,3,6,"******")

 

 

总结一下,前面两个问题,函数都表示毫无压力!so easy~

没错,会这两个函数就可以解决大部分的替换问题。不过如果你认为“革命之路”到此为止了就错了,还有它们也解决不了的情况~

 

看下面这组数据:

下图需要根据邀请人员,参会人员,查找出缺席人员名单。问题本质上其实也是替换的问题,但是参会人员名字在邀请成员名单中并不连续,就不好套用上面的两大替换函数了。

 

 

先压压惊,Power Query表示它已经迫不及待了 ~

 

小贴士:Power Query2016版本及以上的Office Excel才有的功能,16版本以下需要安装插件哦~

 

Step.01

 

先将数据加载进Power Query编辑器。

 

操作:

用鼠标点击“添加列”,“自定义列”。在弹出的自定义列编辑器中输入M函数=Text.Split([邀请人员],"")

在新列名处为新增的列取一个标志性的名称,这里取名为的“邀请人员2”。

 

M函数解释:

该函数的意思就是将[邀请人员]这一列数据按照逗号分隔,并将分割后的数据存放在List数据类型中。

 

 

按照同样的操作,将参会人员也进行分割。

 

 

Step.02

邀请人员和参会人员这两列我们都进行分割了,此时大家可以看到PQ界面中有4列数据。

 

 

接下来就是在“邀请人员2”中替换“参会人员2”,说起来容易,做起来也很容易,只需要一个M函数即可。下面,大家一起来!

 

跟上面一样,添加自定义列后,在自定义编辑栏处编辑公式:

=List.Difference([邀请人员2],[参会人员2])

 

M函数解释:

Difference英文意思就是不一样的,所以引申下这个函数就是找不同。List.Difference([邀请人员2],[参会人员2])也就是在“邀请人员2”中找跟参会人员2”中不同的人员,找出来的就是缺席人员名单了。

 

 

 

公式输入完成后,用鼠标点击确定,然后选择“扩展按钮”中的“提取值”。

 

 

选择按逗号分隔,用鼠标点击确定。

 

 

现在,大家就将缺席人员名单找出来了。

最后,可以只保留“缺席人员”这一列,删除其他列。然后将数据加载到工作表中。

 

 

总结:

Power Query可以和函数互补!当遇到用函数思维很烧脑、无从下手的问题时,试试Power Query,或许可以看见另外一番景象哦!

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

瞬间整理完上千条数据,Excel中的Power Query工具也太好用了吧!

如何用power query进行数据清洗?

表头顺序不一致的工作簿如何合并?用Power Query一秒搞定!

Excel一键生成报表教程:Power Query多表合并案例

版权申明:

本文作者过儿;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。