如果您Excel工作薄中有許多不同的工作表,您需要將這些 Excel 工作表的數據合併到一張工作表中。怎麼做?如果手動執行多個excel頁面合併成一個頁面,是一件既費時又費力的事情。從多個工作表複製和粘貼數據並不是一件容易的事,而且您可能時間也不夠。因此,我們有另外一種方法,可以輕鬆讓您將多個excel頁面合併成一個頁面。
Excel頁面合併成一個的方法
Excel頁面的合併分為兩種情況,一種是工作表合併、一種是工作簿合併。一個Excel表格叫做工作簿,每個工作簿中的Sheet叫做工作表。
不考慮極端的情況,如工作表/工作簿表頭不一致、列數不同等,這裡預設需要合併的工作表/工作簿表頭都是一致的。
合併同一工作簿下所有Sheet頁面
合併同一工作簿下所有Sheet頁面的資料,針對資料的多少,有不同的方法。
方法一:剪貼薄合併Sheet頁面
如果需要合併的資料比較少,我們可以利用剪貼板將所有sheet頁面合併到一個。
- 首先叫出Excel剪貼薄,它在開始功能區的最左邊。
- 接著選中第一個sheet的內容,按CTRL+X快速鍵,將sheet1內的所有內容剪切到剪貼薄中,同樣的操作,將所有sheet的內容,全部CTRL+X剪切到剪貼薄。
- 全部剪切完成,在新建的sheet中,點擊左側剪貼薄中的“全部粘貼”按鈕,完成工作表的合併。
方法二:VBA法合併Sheet頁面
不需要看懂代碼,將代碼複製粘貼到需要合併的工作簿中來,運行即可,表格合併完成。
明細代碼如下所示(直接複製可用):
Sub 合併當前工作簿下的所有工作表() On Error Resume Next Application.ScreenUpdating = False Application.DisplayAlerts = False Set st = Worksheets.Add(before:=Sheets(1)) st.Name = "合併" For Each shet In Sheets: If shet.Name <> "合併" Then i = st.Range("A" & Rows.Count).End(xlUp).Row + 1 shet.UsedRange.Copy st.Cells(i, 1).PasteSpecial Paste:=xlPasteAll End If Next Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox "已完成" End Sub
方法三:Power Query合併Sheet頁面
Power Query是Excel2016及其以上才有的功能,如果你的Excel版本較低,強烈建議安裝較高版本的Office軟體。
- 依次點擊【資料】—【獲取資料】—【自檔】—【從工作簿】。選中需要合併的工作簿,點擊【導入】。
- 之後進入到導航器介面,選中資料夾,點擊【轉換資料】。
- 進入Power Query編輯介面,取消勾選不需要的表名,之後點擊Data列擴展資料,點擊確定,完成資料表的合併,最後一步,點擊左上角的“關閉並載入”即可將資料載入到Excel當中去。
三者區別:
- 剪貼板法適合少量表格的合併
- VBA方法適合任意版本的Excel版本的多數資料合併
- Power Query法只適合Excel2016及以上版本的多數資料合併
工作簿的合併-所有的Excel工作簿的合併
工作簿的合併指合併在同一個文件下下所有的Excel工作簿。
方法一:Windows 批次處理命令
首先我們需要將Excel表格檔另存為CSV檔,這一步可以通過VBA批量操作。
打開任意工作簿,調出Visual Basic 介面,輸入以下代碼(如何插入代碼上面有介紹,這裡不再贅述)
Sub xlsxtocsv() Application.ScreenUpdating = False Application.DisplayAlerts = False t = ActiveWorkbook.Name mypath = ActiveWorkbook.Path & "\" myfile = Dir(mypath & "*.xlsx") Do Until Len(myfile) = 0 If myfile <> t Then Workbooks.Open Filename:=mypath & myfile ActiveWorkbook.SaveAs Filename:=mypath & Left(myfile, InStr(myfile, ".") - 1) & ".csv", FileFormat:=xlCSV End If If myfile <> t Then ActiveWorkbook.Close myfile = Dir Loop Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
執行完畢後,將會將每一個表格生成一份csv檔。
- 新建一個txt文本文檔,在其中輸入:copy *.csv all.csv。
- 保存檔,將檔尾碼名修改為bat檔;或者另存為,另存為介面需要注意兩點:第一點:檔案名記得帶.bat的副檔名;第二點:保存類型選擇【所有檔】
- 點擊確定,就會出現一個可執行檔,類型為【windows批次檔】。
- 按兩下此檔之後,當前資料夾內的所有csv檔全部被合併在一起。
此方法只適用於csv檔,不適用於xls/xlsx檔(也就是Excel表格),所以我們在第一步,需要將所有的Excel檔轉化成csv格式。
方法二:VBA法
VBA運行方法都是一致的。
新建一個工作簿,打開,進入Visual Basic 介面,輸入以下代碼,點擊運行,等待程式完成之後,工作簿完成合併。
Sub 合併目前的目錄下所有工作簿() Dim Wb As Workbook Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path MyName = Dir(MyPath & "\" & "*.xlsx") AWbName = ActiveWorkbook.Name Do While MyName <> "" If MyName <> AWbName Then Set Wb = Workbooks.Open(MyPath & "\" & MyName) c = ThisWorkbook.Sheets("sheet1").UsedRange.SpecialCells(xlCellTypeLastCell).Row If c = 1 Then '防止合併的工作簿第一行空著 c = 0 End If ActiveSheet.UsedRange.Copy ThisWorkbook.Sheets("sheet1").Cells(c + 1, 1) '合併工作簿的第一個sheet名字為:sheet1 Wb.Close False End If MyName = Dir Loop Application.ScreenUpdating = True MsgBox "已完成" End Sub
方法三 Power Query
- 依次點擊【資料】—【獲取資料】—【自檔】—【從資料夾】,選中需要合併的資料夾,點擊【打開】,最後點擊【轉換資料】。
- 進入Power Query編輯介面,點擊【Content】列,這裡選擇【參數1】,點擊【確定】。
- 接著點擊【Data】列擴展資料,點擊【確定】,完成工作簿的合併,針對不需要的列可以右鍵刪除,最後關閉並上載資料到Excel表格即可。
Windows 批次處理適用於CSV檔,VBA適用於任意版本(如果Excel是2007以前版本,需要將代碼中的XLSX修改成XLS)、Power Query適用於高版本Excel,各有所長。
小結
按照本文介紹的方法即可將多個excel頁面合併成一個頁面。您學會了嗎?