文/小魚兒 編輯/美琪
雷哥給大家分享過數值區域的拆分,如此簡單!>這篇文章,是單純數值區域的拆分,那複雜型(混合型)的區域拆分,如何操作呢?
原案例 VS 現案例
某售樓處的車庫資訊如下左側所示,每棟樓對應的車庫編號區間已標註明確,現欲將每個編號對應哪棟樓的明細全部記錄下來,如何快速操作呢?
分析:車庫編號是【文字+數字】混合型的,我們仍然可以使用PowerQuery來進行編號的拆分。
注意:以下操作是在Excel 2019版本中進行的,不同版本,操作介面會有些許差異~
STEP1 獲取資料到PQ
任意單擊表格中【任意非空單元格】來啟用該表格,點選【資料】-【自表格/區域】-【確定】,注意確定之前確認是否勾選了【含標題】,就打開了PQ操作介面;
STEP2 按分隔符拆分列
在彈出的介面中選擇“車庫編號”整列(點選“車庫編號”單元格),然後再單擊【轉換】選項卡-【拆分列】-【按分隔符】;
在彈出的對話方塊中選擇【自定義】(連線短槓),拆分位置可以選擇【每次出現分隔符時】(此示例中選擇任意選項均不受影響)-【確定】。
STEP3 替換文字,更改型別
然後選中“車庫編號。1”和“車庫編號。2”兩列(點選“車庫編號。1”單元格選中該列,然後按住shift鍵,再點選“車庫編號。2”單元格),選擇【轉換】-【替換值】,在彈出的介面中【要查詢的值】中輸入“AG”,【替換為】不填寫。單擊【確定】;
點選【轉換】中的【資料型別:文字】的下拉三角,選擇【整數】,或者直接右鍵單擊【更改型別】-【整數】;
這時發現預設的左對齊變成了右對齊;
STEP4 新增自定義列
選擇【新增列】-【自定義列】;在彈出的對話方塊中把【新列名】改成“車庫編號”(其實不改名也可以,因為僅是個過渡列,最後還要刪除;文末的動圖操作裡就沒有改名),在自定義公式裡面輸入:{[車庫編號。1]。。[車庫編號。2]},或者輸入&點選結合操作(下面截圖右側,點選相應【可用列】並插入),然後單擊【確定】。
STEP5 完成拆分
然後單擊上面操作後介面中的“車庫編號”單元格右側的圖示,選擇【擴充套件到新行】;
STEP6 重新改回文字型別,組合文字
然後將“車庫編號”這列的型別變更為“文字”,此時數值又從右對齊,回到了左對齊;
然後單擊【新增列】-【自定義列】,在彈出的對話方塊中重新命名“各車庫編號”,輸入或者結合右側點選插入法輸入:“AG”&[車庫編號],單擊【確定】;
STEP7 刪除多餘列,關閉並上傳Excel
然後選中“車庫編號。1”和“車庫編號。2”及“車庫編號”3列(選擇各列名單元格時,按住shift鍵),右鍵【刪除列】即可;
最後關閉並上載到EXCEL,完成車庫編號的拆分。
動態操作圖如下:
總結
PQ中,複雜混合型區域(含文字及數字)的拆分,與單純數字區域的拆分相比,其實只是多了一個【文字及數字先拆分,經過處理後,再將兩者合併的過程】。
關鍵步驟在於先拆分,然後再替換掉文字並將型別改為“整數”形式(比單純數字拆分多的步驟),這樣能提取出數字部分的最小值和最大值,然後自定義列,公式為={[最小值所在列名]..[最大值所在列名]},完成填充最小值最大值之間的數值,即完成數值部分的拆分,剩下的就是還原文字及數字的組合啦。
你學會了嗎?
如何獲取練習原始檔
原創不易,如果你喜歡這篇文章,歡迎分享到朋友圈,或者轉發給同事朋友。也歡迎你在留言區,分享您的壓箱底神器。
- END -
Copyright 2021雷哥 @雷哥Office
All Rights Reserved。