【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel




Excel在工作上有個痛點,資料散佈在各個檔案,合起來很麻煩。這篇文章以證交所台積電月成交資訊為例,介紹用Power Query將資料夾裡的所有檔案合併匯入到Excel。

一、證交所台積電的個股月成交資訊

證交所網頁上,「首頁\交易資訊\營運資訊\個股月成交資訊」,設定好「資料日期」和「股票代碼」,按下「查詢」,「CSV下載」「107年2330台積電月成交資訊」。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第1張

二、Power Query從資料夾取得資料

上方功能區路徑:「資料\取得及轉換資料」。上一節是「啟動Power Query編輯器」,其實2016 Excel版本已將Power Query內化到「資料」指令中,這裡可以快捷選擇「取得資料\從檔案\從資料夾」,浮窗的補充說明可參考。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第2張

三、資料夾視窗

出現熟悉「資料夾」視窗,按「瀏覽」。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第3張

四、台積電CSV檔案

第一個步驟下載的台積電CSV檔案都放在「FMSRFK_2330」這個資料夾,選好了之後「確定」。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第4張

五、資料匯入預覽

Power Query的資料匯入的預覽視窗,因為要把三個「.csv」合在一起並且要做些處理,將「合併」下拉,點選「合併與轉換資料」。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第5張

六、合併檔案

「指定每個檔案的設定」,證交所的CSV檔案格式沒有問題,不會有匯入錯誤的情形,可以直接「確定」。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第6張

七、資料標題欄位處理

進入到Power Query編輯器。觀察所匯入的原始資料,首先Excel預設建立「Source.Name」、「Column1」、「Column2」、…等欄位,顯然不是我們要的,資料裡裡第二列「年度」、「月份」、「最高價」、…是比較適合的表格標題欄位。因此於上方功能區執行「常用\轉換\使用第一個資料列作為標頭」:「將這個資料的第一個資料列升成資料行標頭」

按一次會先把「Source.Name」那一列刪除,「107年2330台積電 月成交資訊」那一列上移上移為標頭(標題欄位),再按一次,會把「107年2330台積電 月成交資訊」那一列刪除,「年度」那一列上移為標頭。如此實際操作,讀者應該可以熟悉這個指令的作用。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第7張

八、原始資料篩選過瀘

處理好了標題欄位,進一步處理資料內容。中還有很多的不需要的,熟悉Excelxcel的讀者應該都有用過「篩選」這個很好用的指令,Powe Quey內建了篩選功能。在「年度」那裡將點一下三角形,「搜尋」視窗裡勾選「107」、「 108」、「109」,透過這樣簡單操作,原始資料裡的雜質都被濾掉清洗掉了。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第8張

九、關閉並載入

資料乾淨許多,一整個清爽!打完收功了,最左上角的「關閉並載入」:將變更儲存到這個查詢中,關閉查詢編輯器視窗,並將結果載入到預設目的地。」

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第9張

十、Excel表格資料

透過Power Query的神奇之手,成功將證交所網頁的月成交資訊變成Excel表格資料。其實有效率取得資料只是個開始,如何讓資料產生價值,那就看會計人和投資人如何運用了。

【Excel Power BI】檔案太多了?Power Query取得資料夾所有檔案,合併整理匯入Excel Power BI 第10張

我們喜歡Excel、每天用Excel,不知不覺中被Excel綁架了。Excel一直有個隱藏的先天限制,它把數據輸入、資料處理、報表輸出,全部都放在座標方格紙的工作表上,在儲存格裡輸入內容,在表格裡整理資料,然後在同樣格式的工作表呈現報告圖表。

這麼過了幾十年,大數據時代、人工智能的時代,微軟終於端出了Power BI,幫助Excel好好來一次革命。從這節文章的範例清楚可以看到,我們把資料留在電腦(也可以是資料庫、系統、網頁),Power Query先把資料撈進來,洗一洗、清一清,乾淨了再精準丟給Excel。Excel做它該做的事,這是根本性的技術突破。歡迎各位讀者繼續追隨贊贊小屋「會計人的Excel小教室」,跟爬山一樣爬Power BI這座小山,讓它成為你的工作上得力助手,你最說得出口的電腦技能。






當前文章延伸閱讀:
Power BI