如何去掉一列資料中前幾位數字和字母,只保留後面的數字?
一列資料中前四位是數字和字母,後幾位是純數字,只想取後面數字的值。分裂和替換不太行,操作完後面的數字值就變了,還有其他方法或者公式嘛?
無規律文字中提取數字?漢字和拼音混雜在一起怎麼單獨提取出來?
比如(下圖):我想把
部門/職位
分開;提取編碼中的
數字
;以及
漢字和拼音
分開提取。
不用函式巢狀,不用VBA,用Power Query就可以輕鬆解決這個問題!辦公室小白也可以活學活用!(喝杯咖啡穩一穩)
遵循Power Query資料處理的原則,我會按照以下幾個步驟進行圖文講解:(記住:只要你用PQ處理資料,都是這幾個步驟!!)
1。 提取資料
2。清洗資料(拆分,自定義列,M函式等)
3。上載資料
如果你想看影片教程的話,全網搜尋“簡妮的Office指南”。
01
提取資料
要知道PowerQuery有一個"萬能充電器"似的資料聯結器,花樣繁多,但是每種資料你也要找到正確的連線口才行,今天我們的資料是Excel中的一個普通表格,那麼它可以直接匯入PowerQuery當中嗎?都說了是普通表格,那肯定是不行的,必需是經過“包裝”的表格:
普通錶轉超級表(Ctrl+T建立)
普通表命名為區域
普通表命名為動態區域
普通表設定為列印區域
這次我們就按照第一種普通錶轉為超級表的方法去連線資料:
1.1
選中表格區域
,Ctrl+T建立超級表,在表設計
選項下修改表名稱。
如果不改名稱的話,excel自動命名為表1,表2。。。。,過了10天半月你大機率想不起來是幹啥的
修改表名稱
1.2
選中超級表的任一單元格,點選資料選項卡的
來自表格/區域,
默默等待幾秒,就可以進入PowerQuery的編輯器了。
來自表格區域
02清洗資料
接下來就是大刀闊斧地清洗資料了!PowerQuery處理資料其實可以分為兩部分:
一部分是圖形化操作介面,就是使用者按下按鈕就可以操作(傻瓜式);
還有一部分就是M語言,用程式碼指揮輸出(進階式);
如果想要處理資料爐火純青的話,兩部分肯定都是不能少的。不過今天的案例:主要是用到拆分命令和Text。Remove函式
2。1拆分部門和職位列:
主頁選項卡→拆分列→按分隔符→選擇自定義,輸入/分隔符→確定
部門職位就拆分為兩列了。
大部分情況PowerQuery都會自動識別,PowerQuery是沒有Ctrl+z這樣的撤銷操作的,就只能刪除步驟
2.2
提取
數字並
並加上字首
透過觀察編碼列我們知道,數字都是參雜在字母中間,所以我們可以
先拆分:
主頁選項卡
→
拆分列
→
按照從非數字到數字的轉換
得到結果之後,再拆分:
主頁選項卡→拆分列→從數字到非數字的轉換
給數字加上字首“C-”:
轉換選項卡→格式→新增字首→輸入“C-”→確定
2.3
提取
拼音漢字
用到兩個知識點:
1。自定義列:可以自己寫M語言處理指定列資料,結果會反映到自定義列上
2。Text。Remove函式,移除指定文字的指定字元(具體解釋可以看官網)
通俗來說,這個函式有兩個引數:第一個引數是要處理的文字;第二個引數是要移除的符號。第二個引數可以一個也可以多個,如果多個就要用{}括起來
所以,如果我們要移除所有字母的話:
新增列選項卡
→
自定義列
修改列名為姓名_漢字
寫入公式:
Text.Remove([#“姓名(拼音)],{“A”.."z"})
{第一個引數就是源姓名列,在右下角的方框內選擇對用列名點選插入就行(不用自己一個個打出來)
第二個引數就是
{“A”.."z"}
,表示所有大小寫字母
要移除所有漢字的話:
新增列選項卡
→
自定義列
修改列名為姓名_拼音
寫入公式:
Text.Remove([#“姓名(拼音)],{“一”.."龜"})
第一個引數就是源姓名列,在右下角的方框內選擇對用列名點選插入就行
第二個引數就是
{“一”.."龜"}
,表示幾乎所有常用漢字
轉換選項卡下有個格式命令,可以改變字母的大小寫(按自己需求來)
2.4重新命名
列名
,刪除
不需要的列
03上載資料
資料處理好之後,主頁選項卡下,選擇
關閉並上載至
注意:如果選擇
關閉並上載
就不會彈出這個提示框,會自動將資料匯入到新工作表中。
選擇
關閉並上載至
會讓你四選一,如果反悔也是可以改的,這次選擇表。
這樣我們處理好的資料就上傳到excel表中了。
不管以後更新多少資料,只要在表格區域右鍵重新整理就可以了!
如果學會的話就動動手指操作一下吧,工作中碰到相當的問題也可以運用這個方法哦,俗話說學以致用,在用中學習會是最快的。
連結:
https://
pan。baidu。com/s/1K0lwsd
l87Ri09hJx39lfAw?pwd=5kre
提取碼:5kre
簡妮的Office指南/職場提升/持續精進
Excel/PowerQuery/VBA/PowerBI
關注我,一起成長吧!
上一篇:設計學是美術的相關專業嗎?
下一篇:這類畢業生,最高獎6萬!