Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能!
?
作者:過兒來源:部落窩教育發(fā)布時間:2021-05-20 10:54:25點擊:7153
編按:
說到Excel的替換操作,大家首先想到的一定是SUBSTITUTE和REPLACE函數(shù)。可是,今天需要處理的替換問題,這兩個函數(shù)也束手無策,那要怎么做呢?下面,小E要介紹的就是Excel中,比函數(shù)更強大的“萬能”替換方法——Power Query!一起來看看吧!
哈嘍,大家好,作為在職場中摸爬滾打多年的Exceler,函數(shù)一直是我們的好朋友,關(guān)鍵時候?qū)憘€函數(shù)就可以解決很多數(shù)據(jù)方面的問題。但是隨著數(shù)據(jù)的五花八門,有時候我們也會發(fā)現(xiàn),面對某些問題,函數(shù)好像不是那么“靈光”了,這個時候大家就需要考量下是不是可以用Power Query。下面就借用替換問題開啟我們的“從函數(shù)到Power Query”之路。
在Excel中說到替換,函數(shù)家族中的兩大替換函數(shù)SUBSTITUTE和REPLACE函數(shù)就當仁不讓了。
例1:已知開戶行信息和賬號需要提取銀行名稱。(如下圖),這時就可以利用SUBSTITUTE函數(shù)替換。
SUBSTITUTE的基礎(chǔ)語法是:
SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個])
最后一個參數(shù),[替換第幾個],是可以省略的。
所以,提取銀行賬號可以直接在E2處輸入公式:
=SUBSTITUTE(C2,D2,"")。
例2:將電話號碼的中間4位數(shù)字處理成星號。
REPLACE的基礎(chǔ)語法是:
REPLACE(要替換的字符串,開始位置,替換個數(shù),新的文本)
在B2中輸入公式:
=REPLACE(A2,3,6,"******")
總結(jié)一下,前面兩個問題,函數(shù)都表示毫無壓力!so easy~
沒錯,會這兩個函數(shù)就可以解決大部分的替換問題。不過如果你認為“革命之路”到此為止了就錯了,還有它們也解決不了的情況~
看下面這組數(shù)據(jù):
下圖需要根據(jù)邀請人員,參會人員,查找出缺席人員名單。問題本質(zhì)上其實也是替換的問題,但是參會人員名字在邀請成員名單中并不連續(xù),就不好套用上面的兩大替換函數(shù)了。
先壓壓驚,Power Query表示它已經(jīng)迫不及待了 ~
小貼士:Power Query是2016版本及以上的Office Excel才有的功能,16版本以下需要安裝插件哦~
Step.01
先將數(shù)據(jù)加載進Power Query編輯器。
操作:
用鼠標點擊“添加列”,“自定義列”。在彈出的自定義列編輯器中輸入M函數(shù):=Text.Split([邀請人員],",")。
在新列名處為新增的列取一個標志性的名稱,這里取名為的“邀請人員2”。
M函數(shù)解釋:
該函數(shù)的意思就是將[邀請人員]這一列數(shù)據(jù)按照逗號分隔,并將分割后的數(shù)據(jù)存放在List數(shù)據(jù)類型中。
按照同樣的操作,將參會人員也進行分割。
Step.02
邀請人員和參會人員這兩列我們都進行分割了,此時大家可以看到PQ界面中有4列數(shù)據(jù)。
接下來就是在“邀請人員2”中替換“參會人員2”,說起來容易,做起來也很容易,只需要一個M函數(shù)即可。下面,大家一起來!
跟上面一樣,添加自定義列后,在自定義編輯欄處編輯公式:
=List.Difference([邀請人員2],[參會人員2])
M函數(shù)解釋:
Difference英文意思就是不一樣的,所以引申下這個函數(shù)就是找不同。List.Difference([邀請人員2],[參會人員2])也就是在“邀請人員2”中找跟“參會人員2”中不同的人員,找出來的就是缺席人員名單了。
公式輸入完成后,用鼠標點擊確定,然后選擇“擴展按鈕”中的“提取值”。
選擇按逗號分隔,用鼠標點擊確定。
現(xiàn)在,大家就將缺席人員名單找出來了。
最后,可以只保留“缺席人員”這一列,刪除其他列。然后將數(shù)據(jù)加載到工作表中。
總結(jié):
Power Query可以和函數(shù)互補!當遇到用函數(shù)思維很燒腦、無從下手的問題時,試試Power Query,或許可以看見另外一番景象哦!
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學習Excel:
相關(guān)推薦:
瞬間整理完上千條數(shù)據(jù),Excel中的Power Query工具也太好用了吧!
如何用power query進行數(shù)據(jù)清洗?
表頭順序不一致的工作簿如何合并?用Power Query一秒搞定!
Excel一鍵生成報表教程:Power Query多表合并案例
版權(quán)申明:
本文作者過兒;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!