編按：

說到Excel的替換操作，大家首先想到的一定是SUBSTITUTE和REPLACE函數(shù)?？墒?，今天需要處理的替換問題，這兩個(gè)函數(shù)也束手無策，那要怎么做呢？下面，小E要介紹的就是Excel中，比函數(shù)更強(qiáng)大的“萬能”替換方法——Power Query！一起來看看吧！

哈嘍，大家好，作為在職場中摸爬滾打多年的Exceler，函數(shù)一直是我們的好朋友，關(guān)鍵時(shí)候?qū)憘€(gè)函數(shù)就可以解決很多數(shù)據(jù)方面的問題。但是隨著數(shù)據(jù)的五花八門，有時(shí)候我們也會(huì)發(fā)現(xiàn)，面對某些問題，函數(shù)好像不是那么“靈光”了，這個(gè)時(shí)候大家就需要考量下是不是可以用Power Query。下面就借用替換問題開啟我們的“從函數(shù)到Power Query”之路。

在Excel中說到替換，函數(shù)家族中的兩大替換函數(shù)SUBSTITUTE和REPLACE函數(shù)就當(dāng)仁不讓了。

例1：已知開戶行信息和賬號需要提取銀行名稱。（如下圖），這時(shí)就可以利用SUBSTITUTE函數(shù)替換。

SUBSTITUTE的基礎(chǔ)語法是：

SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個(gè)])

最后一個(gè)參數(shù)，[替換第幾個(gè)]，是可以省略的。

所以，提取銀行賬號可以直接在E2處輸入公式：

=SUBSTITUTE(C2,D2,"")。

例2:將電話號碼的中間4位數(shù)字處理成星號。

REPLACE的基礎(chǔ)語法是：

REPLACE（要替換的字符串，開始位置，替換個(gè)數(shù)，新的文本）

在B2中輸入公式：

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

總結(jié)一下，前面兩個(gè)問題，函數(shù)都表示毫無壓力！so easy~

沒錯(cuò)，會(huì)這兩個(gè)函數(shù)就可以解決大部分的替換問題。不過如果你認(rèn)為“革命之路”到此為止了就錯(cuò)了，還有它們也解決不了的情況~

看下面這組數(shù)據(jù)：

下圖需要根據(jù)邀請人員，參會(huì)人員，查找出缺席人員名單。問題本質(zhì)上其實(shí)也是替換的問題，但是參會(huì)人員名字在邀請成員名單中并不連續(xù)，就不好套用上面的兩大替換函數(shù)了。

先壓壓驚，Power Query表示它已經(jīng)迫不及待了 ~

小貼士：Power Query是2016版本及以上的Office Excel才有的功能，16版本以下需要安裝插件哦~

Step.01

先將數(shù)據(jù)加載進(jìn)Power Query編輯器。

操作：

用鼠標(biāo)點(diǎn)擊“添加列”，“自定義列”。在彈出的自定義列編輯器中輸入M函數(shù)：=Text.Split([邀請人員],"，")。

在新列名處為新增的列取一個(gè)標(biāo)志性的名稱，這里取名為的“邀請人員2”。

M函數(shù)解釋：

該函數(shù)的意思就是將[邀請人員]這一列數(shù)據(jù)按照逗號分隔，并將分割后的數(shù)據(jù)存放在List數(shù)據(jù)類型中。

按照同樣的操作，將參會(huì)人員也進(jìn)行分割。

Step.02

邀請人員和參會(huì)人員這兩列我們都進(jìn)行分割了，此時(shí)大家可以看到PQ界面中有4列數(shù)據(jù)。

接下來就是在“邀請人員2”中替換“參會(huì)人員2”，說起來容易，做起來也很容易，只需要一個(gè)M函數(shù)即可。下面，大家一起來！

跟上面一樣，添加自定義列后，在自定義編輯欄處編輯公式：

=List.Difference([邀請人員2],[參會(huì)人員2])

M函數(shù)解釋：

Difference英文意思就是不一樣的，所以引申下這個(gè)函數(shù)就是找不同。List.Difference([邀請人員2],[參會(huì)人員2])也就是在“邀請人員2”中找跟“參會(huì)人員2”中不同的人員，找出來的就是缺席人員名單了。

公式輸入完成后，用鼠標(biāo)點(diǎn)擊確定，然后選擇“擴(kuò)展按鈕”中的“提取值”。

選擇按逗號分隔，用鼠標(biāo)點(diǎn)擊確定。

現(xiàn)在，大家就將缺席人員名單找出來了。

最后，可以只保留“缺席人員”這一列，刪除其他列。然后將數(shù)據(jù)加載到工作表中。

總結(jié)：

Power Query可以和函數(shù)互補(bǔ)！當(dāng)遇到用函數(shù)思維很燒腦、無從下手的問題時(shí)，試試Power Query，或許可以看見另外一番景象哦！

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

瞬間整理完上千條數(shù)據(jù)，Excel中的Power Query工具也太好用了吧！

如何用power query進(jìn)行數(shù)據(jù)清洗？

表頭順序不一致的工作簿如何合并？用Power Query一秒搞定！

Excel一鍵生成報(bào)表教程：Power Query多表合并案例

版權(quán)申明：

本文作者過兒；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。