使用 VBA實現Excel表單數據的自動抽取與匯總

［摘要］ 本文介紹如何使用VBA開發通用型Excel數據抽取和匯總工具，從格式相同內容不同的多份Excel表單中抽取部分或者全部數據并自動匯總到Excel文件中，以方便后續統計分析工作。

［關鍵詞］ Excel； VBA； 表單； 數據抽取； 匯總

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2011 . 14. 046

［中圖分類號］TP317.1 ［文獻標識碼］A ［文章編號］1673 - 0194（2011）14- 0078- 03

１背景介紹

在日常工作中，經常會遇到從多份格式相同但內容不同的Ｅｘｃｅｌ表單中抽取部分或者全部數據到一個Ｅｘｃｅｌ文件中，以便進行統計分析工作，如調查表匯總統計、實驗報告匯總統計和監測數據匯總統計等。如果表單份數和內容較少、格式簡單、時間富裕、準確率要求不高，可以采用人工抽取數據并匯總的方式完成工作。但如果表單份數和內容非常多、格式復雜、時間緊迫、準確率要求高，采用人工抽取數據并匯總的方式將無法在短時間內準確完成工作。因此，如何準確、快速地從Ｅｘｃｅｌ表單中抽取數據并匯總是數據處理工作中的一個難題。

ＶＢＡ（Ｖｉｓｕａｌ Ｂａｓｉｃ Ｆｏｒ Ａｐｐｌｉｃａｔｉｏｎ）是Ｏｆｆｉｃｅ內嵌的應用開發工具，基于ＶＢＡ開發的應用程序可以實現一些有規律的、重復性的工作，以替代手工工作方式，提高工作效率和準確率。本文將介紹如何使用ＶＢＡ開發數據抽取與匯總工具，實現從格式相同、內容不同的多份Ｅｘｃｅｌ表單中自動抽取部分或全部數據并匯總到一個Ｅｘｃｅｌ文件中。

２設計思想

使用ＶＢＡ開發的Ｅｘｃｅｌ數據抽取與匯總工具的目標是將多份格式相同、內容不同的Ｅｘｃｅｌ表單中的部分或者全部數據自動抽取并匯總到一個Ｅｘｃｅｌ文件中，以方便后續的統計分析工作。該工具具有通用性和靈活性兩個主要特點。通用性：該工具適用于所有格式相同內容不同的Ｅｘｃｅｌ表單，使用者通過前端展示頁面配置數據匯總模板中的參數，使該工具適應新的數據抽取和匯總要求，而不需要改動程序。靈活性：該工具可以讓使用者根據工作需要自由抽取表單中的數據項，可以部分抽取也可以全部抽取，靈活自由。為此，程序需要解決以下３方面問題：① 判斷需要抽取數據的Ｅｘｃｅｌ目標文件。② 判斷從每個Ｅｘｃｅｌ文件的Ｓｈｅｅｔ表單中需要抽取哪些數據。③ 設置怎樣的循環才能將目標文件所有Ｓｈｅｅｔ表單中的數據抽取并匯總到一個Ｅｘｃｅｌ文件中。

為了解決上述問題，該工具使用Ｅｘｃｅｌ作為前端展示頁面，通過可配置的程序參數，實現操作簡便、自由靈活、可重復使用的數據抽取與匯總功能。其中，程序參數包括：

（１） 目標文件的存儲路徑：程序根據該參數查找目標文件，并將目標文件列表保存在前端展示頁面的指定位置。

（２） 待抽取數據的存儲位置：程序根據該參數在目標文件中定位，并從中抽取數據。

（３） 結果文件的保存路徑和名稱：程序根據該參數將結果文件存儲在指定路徑下，并以約定的方式為其命名。

３功能實現

基于ＶＢＡ的Ｅｘｃｅｌ 數據抽取與匯總工具主要由３個功能模塊組成，即目標文件列表讀取功能模塊、數據抽取與匯總功能模塊和生成結果文件功能模塊。

３．１目標文件列表讀取功能模塊

目標文件列表讀取功能是根據設定的路徑將所有Ｅｘｃｅｌ目標文件讀取出來，并將文件名顯示在前端展示頁面的指定位置，形成目標文件列表，以方便使用者檢查核對。該功能模塊的關鍵是設置正確的循環關系，以便將路徑參數下所有目錄中的Ｅｘｃｅｌ目標文件讀取出來。該功能的核心代碼如下：

Ｐｒｉｖａｔｅ Ｓｕｂ ＲｅａｄＦｉｌｅＬｉｓｔ＿Ｃｌｉｃｋ（）

Ｄｉｍ ｍＦｉｌｅＰａｔｈ Ａｓ Ｓｔｒｉｎｇ

ｍＦｉｌｅＰａｔｈ ＝ Ｒａｎｇｅ（＂用于存儲路徑位置＂）．Ｖａｌｕｅ

ＣｈＤｉｒ （ｍＦｉｌｅＰａｔｈ）

ＣｈＤｒｉｖｅ Ｌｅｆｔ（ｍＦｉｌｅＰａｔｈ， １）

Ｉｆ Ｒｉｇｈｔ（ｍＦｉｌｅＰａｔｈ， １） ＜＞ ＂＼＂ Ｔｈｅｎ

ｍＦｉｌｅＰａｔｈ ＝ ｍＦｉｌｅＰａｔｈ ＆ ＂＼*．ｘｌｓ＂

Ｅｌｓｅ

ｍＦｉｌｅＰａｔｈ ＝ ｍＦｉｌｅＰａｔｈ ＆ ＂*．ｘｌｓ＂

Ｅｎｄ Ｉｆ

Ｄｉｍ ｍＮｅｘｔＲｏｗ Ａｓ Ｉｎｔｅｇｅｒ

ｍＮｅｘｔＲｏｗ ＝ 目標文件列表所在的行數

Ｄｉｍ ｍＦｉｌｅＮａｍｅ Ａｓ Ｓｔｒｉｎｇ

ｍＦｉｌｅＮａｍｅ ＝ Ｄｉｒ（ｍＦｉｌｅＰａｔｈ， ｖｂＮｏｒｍａｌ）

Ｄｏ Ｗｈｉｌｅ ｍＦｉｌｅＮａｍｅ ＜＞ ＂＂

Ｒａｎｇｅ（＂目標文件列表所在列＂ ＆ ｍＮｅｘｔＲｏｗ）．Ｖａｌｕｅ ＝ ｍＦｉｌｅＮａｍｅ

ｍＦｉｌｅＮａｍｅ ＝ Ｄｉｒ

ｍＮｅｘｔＲｏｗ ＝ ｍＮｅｘｔＲｏｗ ＋ １

Ｌｏｏｐ

Ｅｎｄ Ｓｕｂ

３．２數據抽取與匯總功能模塊

數據抽取功能模塊是根據配置參數在Ｅｘｃｅｌ目標文件的指定位置抽取數據，并以行記錄的形式將數據保存到結果文件中。該功能的關鍵有兩個部分：① 首先建立兩個數組，分別用于存儲數據匯總模板中的數據項目名稱和數據存儲位置，然后通過循環將配置參數依次讀取到數組中，并將數據項目名稱保存到結果文件對應Ｓｈｅｅｔ表單中的第一行。② 設計３層嵌套循環，將所有Ｅｘｃｅｌ目標文件的所有Ｓｈｅｅｔ表單中的待抽取數據項依次抽取并匯總出來。第１層是目標文件列表循環，逐項打開目標文件列表中的文件；第２層是文件內的Ｓｈｅｅｔ表單循環，逐項打開目標文件中的每個Ｓｈｅｅｔ表單；第３層是待抽取的數據循環，根據配置參數逐項讀取目標數據，并將其保存到結果文件對應的Ｓｈｅｅｔ表單中。該功能的核心代碼如下：

（１） 建立兩個數組，保存數據匯總模板中的數據項目名稱和數據存儲位置。

Ｄｏ Ｗｈｉｌｅ ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＜＝ （ｍＣｏｌｕｍｎＮｕｍｂｅｒ － ２）

ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ ２）．Ｖａｌｕｅ

ｍＳｏｕｒｃｅＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ ＋ １， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ ２）．Ｖａｌｕｅ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＴｉｍｅ）． Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝１， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ） ＝ ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １）

ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＝ ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ ＋ １

Ｌｏｏｐ

（２） 建立３層嵌套循環，抽取目標數據并保存。

Ｄｏ Ｗｈｉｌｅ ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１ ＜＞ ＂＂

Ｗｏｒｋｂｏｏｋｓ．Ｏｐｅｎ ＦｉｌｅＮａｍｅ：＝ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１

ｍＳｈｅｅｔＮｕｍｂｅｒ ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ．Ｃｏｕｎｔ

Ｄｏ Ｗｈｉｌｅ ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＜＝ ｍＳｈｅｅｔＮｕｍｂｅｒ

ｍＩｎｆｏＮｕｍｂｅｒ ＝ ０

Ｄｏ Ｗｈｉｌｅ ｍＩｎｆｏＮｕｍｂｅｒ ＜ ｍＣｏｌｕｍｎＮｕｍｂｅｒ － ２

ｍＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ）． Ｒａｎｇｅ（ｍＳｏｕｒｃｅＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ））

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＴｉｍｅ）．Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＳａｖｅＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＩｎｆｏＮｕｍｂｅｒ ＋ １） ＝ ｍＩｎｆｏ（ｍＩｎｆｏＮｕｍｂｅｒ）

ｍＩｎｆｏＮｕｍｂｅｒ ＝ ｍＩｎｆｏＮｕｍｂｅｒ ＋ １

Ｌｏｏｐ

ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ）．Ｎａｍｅ

ｍＩｎｆｏ（ｍＴｅｍｐｃｏｌｕｍｎＮｕｍｂｅｒ － １） ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｎａｍｅ

ｍＳａｖｅＲｏｗＮｕｍｂｅｒ ＝ ｍＳａｖｅＲｏｗＮｕｍｂｅｒ ＋ １

ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＝ ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＋ １

Ｌｏｏｐ

Ｗｏｒｋｂｏｏｋｓ（ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１）．Ｃｌｏｓｅ

ｍＲｏｗＮｕｍｂｅｒ ＝ ｍＲｏｗＮｕｍｂｅｒ ＋ １

ｍＳｈｅｅｔＴｅｍｐＮｕｍｂｅｒ ＝ １

ｍＳｏｕｒｃｅＦｉｌｅＮａｍｅ１ ＝ Ｗｏｒｋｂｏｏｋｓ（ｍＭａｉｎＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（ｍＭａｉｎＳｈｅｅｔＮａｍｅ）．Ｃｅｌｌｓ（ＲｏｗＩｎｄｅｘ：＝ｍＲｏｗＮｕｍｂｅｒ， ｃｏｌｕｍｎｉｎｄｅｘ：＝ｍＳｏｕｒｃｅＬｉｎｋＣｏｌｕｍｎＮｕｍｂｅｒ）．Ｖａｌｕｅ

Ｌｏｏｐ

３．３生成結果文件功能模塊

生成結果文件功能模塊在指定的路徑下生成保存數據抽取與匯總結果的Ｅｘｃｅｌ文件或Ｓｈｅｅｔ表單，并按照約定的方式為其命名。該功能的關鍵是判斷指定路徑下是否存在以當前日期命名的Ｅｘｃｅｌ文件，如果不存在，則新建一個Ｅｘｃｅｌ文件；如果存在則打開該文件，同時增加一個Ｓｈｅｅｔ表單存儲結果。為了在不修改程序的情況下可以同時存儲多次抽取數據的文件而不相互干擾，程序規定以當前日期（ｙｙ－ｍｍ－ｄｄ）作為Ｅｘｃｅｌ文件的名稱，以程序運行的時間（ｈｈ－ｍｍ－ｓｓ）作為存儲結果的Ｓｈｅｅｔ表單名稱。該功能的核心代碼如下：

Ｉｆ Ｄｉｒ（ｍＦｉｌｅＰａｔｈ ＆ ｍＳａｖｅＦｉｌｅＮａｍｅ） ＜＞ ＂＂ Ｔｈｅｎ

Ｗｏｒｋｂｏｏｋｓ．Ｏｐｅｎ ＦｉｌｅＮａｍｅ：＝ｍＳａｖｅＦｉｌｅＮａｍｅ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ．Ａｄｄ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（１）．Ｎａｍｅ ＝ ｍＴｉｍｅ

Ｅｌｓｅ

ｍＴｅｍｐ ＝ Ｗｏｒｋｂｏｏｋｓ．Ｃｏｕｎｔ

Ｗｏｒｋｂｏｏｋｓ．Ａｄｄ

Ｗｏｒｋｂｏｏｋｓ（ｍＴｅｍｐ ＋ １）．ＳａｖｅＡｓ ＦｉｌｅＮａｍｅ：＝ｍＳａｖｅＦｉｌｅＮａｍｅ， ＣｏｎｆｌｉｃｔＲｅｓｏｌｕｔｉｏｎ：＝ｘｌＬｏｃａｌＳｅｓｓｉｏｎＣｈａｎｇｅｓ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（１）．Ｎａｍｅ ＝ ｍＴｉｍｅ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（２）．Ｄｅｌｅｔｅ

Ｗｏｒｋｂｏｏｋｓ（ｍＳａｖｅＦｉｌｅＮａｍｅ）．Ｗｏｒｋｓｈｅｅｔｓ（３）．Ｄｅｌｅｔｅ

Ｅｎｄ Ｉｆ

４運行效果

下面以工作中的應用案例闡述使用數據抽取與匯總工具的主要步驟（見圖１）和運行效果（見圖２）。

（１） 配置數據匯總模板：使用者根據Ｅｘｃｅｌ表單內容，自由配置需要抽取的數據項名稱和數據存儲位置。其中數據項名稱用于顯示匯總表的表頭信息，數據存儲位置為表單中數據項的實際存儲位置。

（２） 配置目標文件存儲路徑：正確填寫Ｅｘｃｅｌ目標文件的存儲路徑。

（３） 讀取目標文件列表：該工具根據目標文件存儲路徑，將該路徑下的所有Ｅｘｃｅｌ文件讀取出來，并以列表的形式展示在頁面上。

（４） 數據抽取與匯總：上述步驟完成后，該工具根據參數將目標文件中的數據抽取出來并保存在結果文件中。

５結束語

在實際應用中，１ １６６個Ｅｘｃｅｌ文件，２ ３３２個表單，１０２ ６０８個數據項，使用該工具在內存為２．０ＧＨｚ，硬盤轉速為５ ４００轉／秒的計算機上運行３分３０秒左右，結果準確無誤。

使用ＶＢＡ開發的Ｅｘｃｅｌ數據抽取與匯總工具不僅具有投入成本低、簡單易用、小巧靈活、可移植性好等特點，更重要的是通用性強。通過配置數據匯總模板，可適用于所有格式相同、內容不同的多份Ｅｘｃｅｌ表單的數據抽取與匯總工作，提高了工作效率和準確率。

主要參考文獻

［１］ Ｅｘｃｅｌ Ｈｏｍｅ． Ｅｘｃｅｌ應用大全［Ｍ］． 北京：人民郵電出版社，２００８．

［２］ Ｅｘｃｅｌ Ｈｏｍｅ． ＥＸＣＥＬ數據處理與分析實戰技巧精粹［Ｍ］． 北京：人民郵電出版社，２００８．

［３］ 黃海． Ｅｘｃｅｌ ＶＢＡ語法與應用詞典［Ｍ］． 北京：中國青年出版社，中國青年電子出版社，２００９．

［４］ 姚文濤． Ｅｘｃｅｌ ＶＢＡ應用開發經典案例［Ｍ］． 北京：清華大學出版社， ２００９．

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文