2013年8月15日

合併多個 Excel 檔到一個工作表中

最近工作遇到一個問題,需要去檢驗近千個檔案的內容是否有問題。檢驗的方式雖然可以透過函數來判斷,但是上千個檔案量光是打開就覺得麻煩,覺得在驗證之前一定要把檔案想辦法合併起來。

巨集合併

M Riza 先生提出一個很棒的方法,"Merge multiple excel files into a single spreadsheet (MS Excel 2007)"。

Step by Step

  1. 開啟 Excel,按下 Alt + F11 叫出巨集編輯器,貼入網站中的 Code
  2. 修改第8行的路徑,改成需要合併的 Excel 檔存放路徑
  3. 按下執行鍵就 OK 了

心得

  • 儘管是 CSV 檔案也能正確合併。
  • 這邊要注意一件事情,Code 當中的 A2 是起始的儲存格,在合併過程中會忽略掉標題列,直接從第二列開始複製;若是你想連標題列都合併起來,請改成 A1。IV 是他的複製欄寬,可以改成自己想要的欄寬。
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
  • 值得注意的是,雖然這個方法是給 Excel 2007 使用,但 Excel 2007 開始有個重大變化,就是 Excel 最大列數從 2^16 次方改成了 2^20 次方。這代表你可以處理的資料從 Excel 2003 的 65,536 筆增加至 1,048,576 筆。
心得第二點的發現是因為我合併了所有的檔案,還很慶幸原來只有六萬多筆,最後慚愧地發現原來資料數量有三十萬筆,而 Code 當中卻只設定到 65,536 筆,只需要把 A65536 改成 A1048576 即可。

2^4 代表的是16倍的資料處理量,過去 Excel 2003 只能處理六萬筆資料,如今拜軟體、處理器的提升,我們比起過去的人擁有更大的優勢來處理資料,感謝技術的進步。

JMC Excel

JeeShen Lee 李毅胜先生也寫了一個 Excel 巨集,"JMC Excel – Join, Merge, Combine multiple Excel sheets or Excel workbooks",畫面是我喜歡的簡潔類型,試用版可以一次合併 10 個檔案,如果你想要完整版的話可以寫信給作者花個 $7 購買他的檔案來使用看看。

不過在我這次的案子當中,我沒有成功操作這個 Excel 檔案,可能是因為我是要合併 CSV 的緣故吧。


如果我是要合併工作表呢(Worksheets)?

一個 Excel 檔裡面會有很多個分頁(工作表,worksheet),如果你是要合併多個工作表成為單一一個工作表的話,我也有不小心瞄到這個介紹,由 彰化一整天 所寫的「如何快速將Excel多個工作表合併成一個 」。

2 則留言:

蔡妮蓁 提到...

謝謝你的分享,很受用!

kent 提到...

謝謝分享,很有用。另外推薦一個也不錯的小工具:輕鬆合併多個Excel工作簿文檔中工作表為一個工作簿,網址是:http://www.fashiondigital.net/zh-hant/%E8%BC%95%E9%AC%86%E5%90%88%E4%BD%B5%E5%A4%9A%E5%80%8Bexcel%E5%B7%A5%E4%BD%9C%E7%B0%BF%E4%B8%AD%E5%B7%A5%E4%BD%9C%E8%A1%A8%E7%82%BA%E4%B8%80%E5%80%8B%E5%B7%A5%E4%BD%9C%E7%B0%BF.html