Excel教程：PowerQuery，万能的批量数据替换技巧！

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

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

在Excel中说到替换，函数家族中的两大替换函数SUBSTITUTE和REPLACE函数就当仁不让了。

例1：已知开户行信息和账号需要提取银行名称。（如下图），这时就可以利用SUBSTITUTE函数替换。

SUBSTITUTE的基础语法是：

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

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

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

=SUBSTITUTE(C2,D2,"")。

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

REPLACE的基础语法是：

REPLACE（要替换的字符串，开始位置，替换个数，新的文本）

在B2中输入公式：

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

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

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

看下面这组数据：

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

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

小贴士：Power Query是2016版本及以上的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，或许可以看见另外一番景象哦！