多個Excel文件匯總分析

  周三 · 分析   關鍵詞:多表匯總

工作中經常會碰到要對多個Excel文件進行匯總分析的情況,如果要匯總的表格不多,我們可以通過復制黏貼合併數據後,再進行透視。如果要匯總的文件比較多,那該怎麼辦呢?

1舉例說明

一個文件夾中有多個省份的銷售數據文件,每個文件的數據格式相同,我們要匯總每個城市每種產品的銷售額。

多個Excel文件匯總分析

表格比較少的情況下我們會想到用復制黏貼,如果省份比較多,數據經常更新,那我們要每次都重新做嗎?

之前我們介紹過使用VBA的方法進行多文件的匯總,今天我們用Excel中的新工具進行操作,這個工具在16版中叫「獲取轉換數據」或「獲取和轉換」,10或13版可以到微軟官網免費下載「Power Query」插件。

多個Excel文件匯總分析

2操作步驟

■ 步驟一

打開放置匯總數據的Excel文件,在「數據」選項卡中下拉「獲取數據」,自文件,從文件夾。

多個Excel文件匯總分析

說明:如果是早期版本安裝了Power Query,這一步操作是在「Power Query」選項卡中進行的,具體操作是一樣的。

■ 步驟二

選擇文件夾路徑,確定。

多個Excel文件匯總分析

■ 步驟三

點擊「編輯」。

多個Excel文件匯總分析

■ 步驟四

選擇「Content」和「Name」列,在「開始」選項卡中下拉「刪除列」,點擊「刪除其他列」。

多個Excel文件匯總分析

■ 步驟五

在「添加列」選項卡中點擊「自定義列」,自定義列公式寫「=Excel.Workbook([Content],true)」,確定。

多個Excel文件匯總分析

注意:此處的公式大小寫一定要完全一致。

■ 步驟六

選擇「Content」,點擊「刪除列」。

多個Excel文件匯總分析

■ 步驟七

點擊「自定義」列的展開按鈕,取消「使用原始列名作為前綴」,確定。

多個Excel文件匯總分析

■ 步驟八

選擇「Name」和「Data」列,刪除其他列。

多個Excel文件匯總分析

■ 步驟九

點擊「Data」列的展開按鈕,確定。

多個Excel文件匯總分析

■ 步驟十

雙擊「Name」標題,選改為「省份」,選擇「省份」列,點擊「替換值」,查找「.xlsx」,替換為空,確定。

多個Excel文件匯總分析

■ 步驟十一

下拉「關閉並上載」,點擊「關閉並上載至」。

多個Excel文件匯總分析

選擇「數據透視表」,確定。

多個Excel文件匯總分析

說明:如果選擇「表」,會將合併後的數據加載到Excel表格中;「數據透視表」會對合併的數據創建透視表;「數據透視圖」會對合併的數據創建透視表和數據透視圖;「僅創建連接」是僅對合併的數據以連接的形式存在於Excel中,不會加載數據到Excel,後面還可以用透視表引用這個連接。

■ 步驟十二

現在就可以使用透視表對所有的數據進行分析了。

多個Excel文件匯總分析

如果數據源有變化,不管是表格中的數據有變化,還是有新的省份文件增加,都不用重新再做一遍,只需要刷新透視表就可以了,是不是很方便?

多個Excel文件匯總分析

本文由Excel實務原創,作者白永乾。

Excel實務承接全國Excel培訓,歡迎留言聯繫。