收到 Excel「髒數據」直接炸毛?而我只刷新一下就完成了數據清洗

職場 發佈 2020-01-22T09:55:17+00:00

現在終於有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、列印技巧等…

表哥表妹們肯定都有一個共識:BI 不怕需求難,最恨數據不規範!


一天收上來幾十份 Excel 數據表,一看數據格式,各種五花八門的問題,直接炸了。這成千上萬條數據要怎麼清洗?


我曾經教過大家用函數清洗數據的方法,具體可參見 Excel 數據源清洗,用這兩個函數批量刪除空格和換行


用函數可以完成大部分數據清洗,但是不能 100%,比如數字前面多了一個「0」,象 01234 這種,用文中兩個函數沒法讓它變成 1234。


而且用函數畢竟還是費時,我今天要教大家另外一個大殺器:只要將數據傳到 Power Query 跑一圈,什麼都不用做,所有數據都清洗好了。


而且這方法一勞永逸,以後哪怕再有新增的不規範數據,只要刷新一下就能自動更新出一張乾淨的數據表。


案例:


下圖中的學號本該是 6 位數字,可是現在每個單元格左上方都有個綠色小箭頭,說明數字格式有問題。


請用最快捷的方式清洗數據,讓所有學號恢復成 6 位 的數字格式。


解決方案:


1. E 列的「學號」格式是規範的,但是由於 A 列數據源的不規範,在 F 列使用 vlookup 函數查找姓名時,根本找不到。


2. 現在開始清洗步驟:選中需要清洗的數據區域的任意單元格 --> 選擇菜單欄的「數據」-->「從表格」


3. 彈出的對話框中會自動選中數據表區域,且勾選了「表包含標題」--> 點擊「確定」


數據表就傳到 Power Query 編輯器中,並且已經自動清洗乾淨了!


你看,我在 Power Query 中什麼都沒有做,數據就自動清!洗!干!凈!了!


4. 選中菜單欄的「主頁」-->「關閉並上載」-->「關閉並上載」


Excel 工作簿中會出現一個新的 Sheet2,其中存放的是 Power Query 洗過的數據。


5. 現在回到 Sheet1,將 F 列中 vlookup 公式的待檢索區域換成 Sheet2 的表格區域,就發現姓名都查找出來了,說明「學號」列的數據都清洗乾淨了。


6. 我們現在來試一下:如果新增不規範數據,刷新以後是否會自動清洗?


在 A15:B15 行新增數據,A15 單元格的學號同樣是不規範數據;在 F15 單元格用 Ctrl+D 複製樓上的公式。


因為 Sheet2 中還沒有這個新增的學號,所以此時還查找不到對應的姓名。


7. 切換到 Sheet2 --> 選中表格的任意區域 --> 右鍵單擊 --> 在彈出的菜單中選擇「刷新」。


新增的學號和姓名就出現在表格末尾,而且數據自動清洗好了。


8. 再次回到 Sheet1,此時可以看到 F15 單元格成功查找到了「李四」。

很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。

現在終於有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、列印技巧等……學完全本,你也能成為 Excel 高手。

關鍵字: