在工作中會經常碰到數據被分成多個文件,每個文件裡面又有多個表格,而且每月或每年都要進行彙總分析,如果數據量少的話就進行手工複製,實在太多的話只能使用vba,但vba熟練的人少且學習成本高,現在來看一看用power query怎麼解決的?
如上圖,裡面有2017-2019年的銷售數據,而且每個excel裡面都有兩個工作簿,分別為銷量與金額。
新建一個excel文件,切換到數據選項,單擊"新建查詢",選擇"從文件",選擇"從文件夾",選擇等待合併的數據所在的文件夾,最後點擊確定。如下圖顯示
這時,該文件內的所有文件都將被識別出來。由於每個文件裡有不同的表,不能直接合並,因此在彈出的對話框中點擊"轉換數據",進入power query編輯界面,如下圖
在查詢編輯器中可以看到該文件夾內的所有數據,工作簿的數據都在第一列Content,我們在第二列"name"選擇所需要分析的工作簿"2017、2018、2019"
.
在"添加列"選項卡,新建一個自定義列輸入下面公式:=Excel.Workboo([content],true)。(在英文輸入法下輸入公式,並注意大小寫)如下圖:
展開後,在item列中選擇金額,點確定,如下圖:
在data列中,點擴展-展開-不勾選-確定
最後我們得到如下圖的數據,並在該數據中按住ctrl鍵選擇相應的列,點擊右鍵-刪除其他列
接下來,在"name"列中-"轉換"卡-提取-分隔符之前的文本,並輸入".",如下圖:
最後我們就得到三個文件的中金額的三年彙總,如下圖:
我們把這查詢改名為"金額"。
我們在左邊的查詢中,複製該查詢,並改名為"銷量"
之前執行這個步驟,篩選了"金額",點擊右鍵-刪除到末尾,重新進行篩選,在"item"中選擇"銷量"
接下來的步驟按"金額"查詢表進行操作,得到兩張數據查詢表分別是"金額"與"銷量",點擊其中一個查詢表-主頁-合併查詢-將查詢合併為新查詢(該操作相當於vlookup函數),如下圖:
金額與銷售查詢表中,以"生產廠家省份"為進行查找比對(可以參照vlookup函數),在聯接種類中選擇"完全外部",即"金額"與"銷售"表中,都要在最終表選表中體現。在數據表中對"銷售"列進行數據擴展-選擇本期銷售量-點擊確定如下圖:
對上面的表格進行進一步的彙總,美化,"主頁"選項卡-"分組依據",按照下圖進行按年、生產廠家省份對金額、銷量進行彙總:
將第一列的"name"修改為"年份",並按照"年份"進行升序,如下圖:
點擊"關閉並上載",得到處理後的數據:
如果就這樣結束的話,就太對不起pq的自動化數據處理,在文件中新增2019-副本文如下圖
我們只需分別在"金額"與"銷量"查詢表中,在第二個步驟"篩選的行"中,把"2019-副本"文件勾選即可-確定即可。如下圖:
"銷售"查詢表也是如上述步驟進行操作,關閉pq。最後在顯示的數據表中右鍵-刷新即可如下圖:
你看,數據是不是全部刷新了,而且新增了2019-副本的銷售及金額數據。
雖然上述的步驟有點多,但是主要都是鼠標操作,比excel的公式及vba方便很多。
如果上述教程對你有幫助,請點個關注或有什麼問題也可以留言,謝謝!!!