利用Excel的宏功能實現多工作簿指定數據提取

祿小穎

摘 要 本文結合學生提交的多人健康檔案表，提出了使用Excel提取多個工作簿中的指定信息到一個Excel文件的方法。闡述了數據的初始化和使用宏命令匯總多個工作簿中內容到一個Excel文件，介紹了使用INDIRECT函數提取指定單元格的方法。

關鍵詞 Excel 宏編程 數據提取

中圖分類號：G642文獻標識碼：A

1初始化

因工作需要，學校通過微信、QQ等在線方式發放了面向學生的健康檔案表，要求學生本人填寫并回傳給班主任，以便于班主任老師詳細掌握本班學生狀況。如圖1所示。

在健康檔案表中，包含了學生本人電話、家庭住址、父母電話等多項聯系方式。每個學生提交給老師一個以自己學號和姓名命名的文件。如果把全班學生的關鍵信息，匯總到一個表格中，則非常便于班主任老師工作的展開。

2思路

總體思路是，首先對學生提交的文件進行梳理。其次使用宏命令，將全班學生每人一個工作簿中的sheet1工作表匯總到一個新建工作簿中的不同工作表。最后，在新建工作簿中，依次提取每一個工作表中指定位置的數據到新建的“總攬”工作表。

3具體實現

3.1批處理重命名

學生的文件是以學號和姓名來命名的，由于姓名各不相同，不利于編程進行自動執行，所以先進行文件名的整理優化。

使用批命名軟件，將學生的文件名改為“S+學號”。需要注意的是，由于系統的排序特點，最好將10號之內的學生學號改為兩位數，即1號寫為01號。如圖2所示。

3.2使用宏命令匯總到一個工作簿

新建一個Excel文件，在其工作表標簽上點擊右鍵，選擇“查看代碼“。在打開的VBA編輯窗口中粘貼以下代碼。關閉VBA編輯窗口。在Excel文件中，點擊“工具”---“宏”---“學生匯總”，然后“執行”。在打開的對話窗口中，選擇要的全部個工作薄。稍微等待一下，就合并匯總完成了。代碼如圖3所示。

3.3提取不同工作表中同一位置數據

學生信息中，所需提取的是學生姓名、學生本人手機號、父母手機號、家庭住址。最終效果如圖4所示。

為達到最終效果，需要使用INDIRECT函數，該函數含義為引用并顯示其內容。INDIRECT函數的語法格式是=INDIRECT（ref_text，[a1]）。INDIRECT函數的引用的兩中形式，一種加引號，一種不加引號。比如，=INDIRECT（"A1"）——加引號，文本引用。=INDIRECT（A1）——不加引號，地址引用。

下面以“姓名”列為例介紹提取的方法。學生電話和其他列操作類似。由于在上一步匯總完畢的文件中，每一張工作表里面，b2單元格中的數據是學生姓名，所以，在最終的工作表中，“學生”列下面的第一個單元格，輸入函數=INDIRECT（"s"&ROW（B1）&"！b2"）。

其含義是，取“s”（每一個工作表都是s開頭，如s1、s2等），連接ROW（B1），再連接“b2”，結果就是：s1！b2，也就是獲取列s1工作表的b2單元格的數據。

因為ROW（B1）=1，所以兩個&之間連接的是數字1。用鼠標下拉時，雙引號內的內容不會發生變化，而ROW（B1）下拉一行就變成了B2，而ROW（B2）=2。所以下拉后，就變成了s2！b2，也就是獲取s2工作表的b2單元格的數據。

經過以上步驟，可以獲得全體學生的姓名，再用類似方法，可以獲取電話和住址信息。

4總結

經歷了初始化，宏命令匯總和指定單元格提取，可以把多個文件中散亂的學生情況，挑選重要信息，集中顯示在一個工作表中，從而提高了工作效率，也方便了班主任老師的查閱。

參考文獻

[1] 錢力濤.使用Excel實現測評得分統計[J].科技風，2019（11）：69.

[2] 高楠.利用Excel的宏功能實現多數據類型單元格中純數字的提取[J].計算機產品與流通，2017（09）：197.