基于VBA技術的教務管理數據自動化處理系統研究和應用

張萬昌 黃寧

摘要：高校教務管理中數據處理是一項重要的基礎工作。文章針對教務管理中數據處理工作量大，周期性重復，數據復雜，耗時長等問題，利用VBA編程二次開發數據自動化處理系統。可實現按多個標志字符串為條件將數據分列、以單元格內容為完整字符串從中查找特定字符串實現多種方式替換、指定多個條件列實現多條件匯總、數據分類提取到不同工作表，刪除特定行和序號重置等功能。該系統在編程過程中依據功能之間的邏輯順序進行系統化程序設計，使用中可根據需求選定不同功能、設置不同參數，從數據整理到匯總、數據提取實現全自動化執行，按照設定條件生成不同結果。

關鍵詞：Excel;VBA;二次開發;教務管理;數據處理;自動化

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

文章編號：1009-3044（2022）11-0065-04

1 引言

Microsoft Office是日常工作中應用最為廣泛的一款辦公軟件，Excel作為其應用程序之一，擁有較強的數據存儲能力，同時具備一定的數據處理能力。對于數據量少結構簡單且規范的數據， Excel可滿足處理需求，但是對于數據量大結構復雜的數據僅靠Excel自帶的功能來處理是很困難的。Excel集成了VBA（Visual Basic for Application）編程環境，可以通過二次開發來增強Excel的數據處理能力。VBA是寄生于應用程序之內的一種面向對象的編程語言，語法簡單，易學易懂，同時可實現可視化編程，交互性強。這些特點使得它在很多行業得到了廣泛的應用，王錦秀針對期刊編輯過程中的一些問題，利用Word VBA 實現表格轉置、求和校驗、三位分節等功能[1];崔玉潔基于 Word VBA 建立智能編校系統，可以對敏感詞與重復詞句進行標注、格式調整[2];劉超利用VBA開發了爆破輔助設計系統，有效地提高了設計精度[3];王喬利用VBA編程來實現煤田鉆孔綜合成果整理和煤田鉆孔孔斜批量處理等[4-5]。通過搜索知網文獻，在高校教務管理中，利用VBA編程集中在教學工作量的統計、報表的匯總，成績測評以及成績管理，教學數據分析和評價等方面的應用，這些工作數據量小，數據結構規整，且均是對單一數據的統計分析，在單一功能方面實現了一定的自動化操作[6-9]。而對于數據量大結構復雜數據的處理以及同時實現多個功能全自動操作的研究很少。

甘肅開放大學是國家開放大學辦學體系的省級分校，每學期的新生和畢業生數據約2萬條之多，對這么多的數據進行處理是非常繁雜的工作。稍有不慎很容易出錯，這對教務管理人員是非常大的考驗，通過一定的方法實現自動化處理變得十分必要。

2 原始數據特征和工作需求分析

原始數據特征和需求分析是進行數據處理和統計工作的基礎。只有了解原始數據結構、數據類型，以及不同數據之間的關系，再結合需求，才能提出對應的解決思路或方法。

國家開放大學教務管理系統中可以下載省校總的原始數據表，數據共18列，采用其中4列數據即可完成工作需求。這4列數據標題依次為學校名稱、姓名、專業和學歷層次。每行數據互不重復，單元格數據全部為文本（字符串） 格式，學校名稱所在列單元格的數據包含了學生所屬的市級分校和學習中心的完整信息，該列單元格中字符串長度不唯一，結構不規范，是數據整理的關鍵。可歸為以下6類形式：“***分校***”“***分校”“***學院***”“***學院”“***工作站”“***教學點”（不含“分校”或“學院”字符，是一個獨立的學習中心） ，“分校”或“學院”前面的字符長度也不完全相同（圖1） 。

實踐中，需要完成以下工作才能得到最終結果：第一、將學校名稱這一列數據分為分校和學習中心兩列數據，同時一方面分校名稱必須按照標準名稱進行統一，另一方面由于某些學習中心實際管理分校并非原始數據中的分校，需要更改，這是數據整理階段需要完成的工作;第二、將整理好的數據按照分校、專業、學歷層次進行統計;第三、將統計好的數據按照不同學歷層次分表提取，不同的工作表中以市級分校或獨立的學習中心為行標題、以專業為列標題;第四、刪除合計列數值為0的行;第五、所有工作表的序號重置。

數據提取到的表需要設計成標準模板。所有表在一個Excel文件下，不同表的名稱以不同的學歷層次命名。所有表均是統一的格式設置，行標題和列標題位置一致;由于分校的前后順序有要求，所有表中行標題按照順序寫有完整的分校名稱（學習中心） 。標準模板如圖2所示。

3 系統設計過程

3.1 可視化界面設計

通過Excel開發工具菜單點擊VB編輯器或Alt+F11快捷鍵進入VBA編輯窗口。點擊插入用戶窗體，在屬性窗口修改窗體名稱為myform，caption屬性為“數據自動化處理系統”。點擊工具箱插入控件，在屬性窗口修改caption及Font相關屬性，添加的控件類型及作用如表1所示。拖動控件可以調整位置和大小[10]，設計界面如圖3所示。

3.2 定義公共變量和自定義函數

在代碼編輯窗口，定義公共變量：字符型變量 file，Workbook對象變量 wb，Worksheet對象變量 sh。編寫自定義函數Public Selected_File（） 和Public Selected_Folder（） ，兩個函數通過FileDialog對象顯示選擇對話框，前者選擇一個Excel文件，后者用于選擇文件夾，兩個自定義函數返回值均是字符型。

3.3 控件事件過程

1） 控件屬性初始化設置。在窗體的activate事件過程中對不同控件的屬性進行初始化設置，除了只用于顯示的文本框，其他可以輸入的文本框value屬性設置為代碼所在工作簿第一張工作表指定的單元格值。周期性重復工作每次用到相同的參數，在第一次輸入之后，關閉程序時保存工作簿，下一次點擊之后顯示的是上一次使用的參數，這樣可以避免重復輸入。對只用于顯示的文本框的Locked 屬性設置為True[11];所有復合框用list方法和Array函數添加用于提示性的字符串和“A”到“Z”的字符值，將ListIndex值設置為0，顯示第一個值（提示性字符串） ，Style屬性設置為2，復合框只允許選擇而不允許輸入。

2） 命令按鈕“選擇并打開原始數據文件”事件過程。在命令按鈕“選擇并打開原始數據文件”的Click事件過程中調用Selected_File（） 函數，將返回值賦值給公共變量file，前面的文本框的value屬性設置為flie，用于顯示選擇的文件路徑，文本框的locked屬性設置為True;用Workbooks對象集合的Open方法打開原始數據文件，實例化對象變量wb和sh，wb表示原始數據文件對象，sh代表文件中的第一張工作表對象。

3） 數據整理頁面控件事件過程。數據整理頁面的“按標志字符串分列”“同位替換”和“異位替換”功能區文本框，均不許在文本框輸入空格、非打印字符、中文逗號和連續的逗號，首尾也不允許出現逗號。可在所有文本框的change和exit事件過程中可寫入不同的代碼，刪除首尾逗號必須在exit事件過程中實現，在change事件過程中會導致無法輸入逗號。

具體代碼編寫過程中，在change事件過程中使用replace函數可以實現刪除空格、替換中文逗號為英文逗號、刪除兩個連續逗號的操作;使用Clean函數可以清除非打印字符[12]126;在exit事件過程中組合使用right函數和left函數可以刪除首尾英文逗號。

在change事件過程中可以不用循環語句來刪除數量大于兩個的連續逗號，這是由于當文本框內容發生變化時change事件便會觸發，變化一次觸發一次，在不斷觸發過程中即可實現刪除多個連續逗號的目的。

4） 多條件匯總頁面控件事件過程。該頁面文本框的change 事件過程中，與“數據整理”功能區有所不同。“輸入匯總工作表名稱”標簽對應的文本框除了不能出現空格和非打印字符外，再無其他要求，只需使用replace函數和Clean函數便可實現;“輸入匯總條件列”標簽對應文本框要求輸入作為匯總條件的列號（不區分大小寫） ，可以是多列，中間為英文逗號隔開，不能出現英文字母和英文逗號以外的其他任何字符，只允許單個英文字母和英文逗號間隔出現，首尾也不能出現英文逗號，因此需要使用for循環語句檢查文本格式，循環過程中，用Mid函數依先后順序截取每一個字符，使用Like方法對比是否是英文字母或英文逗號，不是則提示錯誤并刪除重新填寫，Like語句為Mid（TextBox.Value， i， 1）? Like? "[a-zA-Z，]"[12]130。另外需要判斷有沒有連續的英文字母出現，出現則提示錯誤并刪除重新填寫，Like語句為TextBox.Value? Like? "*[a-zA-Z] [a-zA-Z]*"，涉及兩個連續字符比較，不能使用change事件，需在exit事件過程中寫入代碼;“如需求和請輸入求和列號”標簽對應文本框只允許出現單個英文字母，只用一次like方法，判斷不是則刪除重寫，Like語句為TextBox.Value? Like? "[a-zA-Z]"。

5） 數據提取頁面控件事件過程。命令按鈕“選擇模板文件”的Click事件過程中調用Selected_File（） 函數選擇模板文件，對應的文本框用于顯示選擇的模板文件路徑;命令按鈕“選擇保存位置”的Click事件過程中調用Selected_Folder（） 函數選擇保存的文件夾位置，對應的文本框用于顯示選擇的文件夾路徑;該功能區“模板中行標題與列標題交叉的單元格為”要求輸入單元格名稱，例如“C3”，只能是一個英文字母和一個數字的組合，標簽對應文本框的exit事件過程中寫入相應代碼，使用的Like語句為 TextBox.Value? Like? "[a-zA-Z][0-9]"。

6） 主程序過程。命令按鈕“執行”的Click事件過程執行的是主程序，按照不同功能之間的邏輯關系，在程序編制過程中采用系統化設計，設計流程圖如圖4所示。

不同功能的實現思路如下：

①多條件分列功能實現。Excel自帶的分列功能只能按固定長度或分隔符分列，多條件分列可按照多個標志字符串進行分列。解決思路：首先將分列功能區文本框中的字符串用split函數分割并存入數組，用for循環語句遍歷需要分列的每一個單元格，內部嵌套for循環遍歷數組的每一個元素，如果單元格字符串中存在元素，則將元素以及元素之前的字符串截取保存到第一列，元素之后的字符串保存到第二列，并退出循環數組，如果單元格字符串中無任何一個標志字符串，則將原單元格內容放置到前一列，所有循環結束，給分列后的兩列添加標題。

②異位替換和同位替換功能實現。實踐中，某些情況下需要按照單元格字符串中存在某一子字符串而將單元格內容做整體替換，或者將其他列對應行單元格內容做整體替換。這種特殊的替換方式Excel中自帶的查找替換功能是無法實現的。具體實現方法：用split函數分割異位替換和同位替換的文本框中的字符串，并分別存入數組a1、a2和a3、a4。a1和a2對應下標的元素一個是查找值，一個是替換值，位置關系是一一對應的，數組a1中的元素互不相同，a2可以相同，這可以實現多個查找內容替換為同一個的目的;a3和a4的關系與a1和a2的關系是一樣的。首先進行異位替換操作，用for循環語句遍歷查找列的每一個單元格，內部嵌套for循環遍歷數組a1每一個元素，如果單元格字符串內部查找到元素，則將替換列對應單元格的值用數組a2中對應元素替換。同位替換過程同異位替換類似，只是查找和替換單元格為同一個單元格。

需要說明的是同位替換和異位替換功能均是多個查找和替換操作同時在進行，且均是從單元格字符串的內部查找，如果查找字符串的長度同單元格字符串長度一致，作用同整體查找替換相同。

③多條件匯總功能實現。多條件匯總與Excel中的分類匯總功能一致，不同的是通過編程實現的多條件匯總數據可以直接進行分類輸出，而Excel自帶的分類匯總形成的數據需要一個個查找使用，無法滿足自動化操作的需求。

實現思路：首先新建指定名稱的用于保存匯總數據的工作表，用split函數分割指定條件列文本框中的字符串并存入數組;用for循環語句遍歷原始數據工作表的每一行數據，內部嵌套使用for循環語句遍歷數組，數組的每一個元素代表了匯總條件的一列，在遍歷數組的過程中，用逗號鏈接所有匯總條件列的字符串，遍歷數組結束后，鏈接好的字符串作為字典的鍵存入字典，鍵對應的值進行累加計數，在這一過程中鏈接好的字符串如果是重復的，則字典不會加入新的鍵，對應的值增加1。外層循環結束之后，將字典的鍵全部存入新建的匯總工作表中的第一列，字典的值存入第二列，之后將第一列數據用逗號分列，匯總完成。

④分表提取數據功能實現。這一功能是將多條件匯總后的數據按照不同條件提取到模板工作簿中不同的工作表。匯總工作表中的數據有一列包含提取到的模板工作簿中所有工作表名稱，一列包含提取的行標題，一列包含提取的列標題，一列為提取的值。

編程思路：從匯總工作表第2行（第一行為標題） 開始遍歷以下各行數據，內部嵌套for循環語句遍歷模板工作簿中每一個工作表，如工作表名稱與匯總工作表名稱所在列單元格中的值相同，則將數據提取到該工作表;確定好需要提取到的工作表之后，從該工作表中行標題和列標題交叉的位置向下搜索與匯總工作表中行標題所在列單元格內容相同的單元格，搜索到后記錄該單元格行號，如果沒有搜索到則將行標題值填充到末端的空白單元格中，并記錄該空白單元格行號;再從交叉單元格向右搜索與匯總工作表列標題所在列單元格內容相同的單元格，同樣記錄到搜索到的單元格列號，沒有搜索到則填充到末端的空白單元格，并記錄空白單元格列號;最后將匯總工作表提取值所在列單元格的值填充到提取工作表中記錄到的行號和列號交叉的單元格中，循環結束之后所有數據提取完畢。

⑤刪除特定行和工作表序號重置功能實現。如果模板工作簿的工作表中預先填寫了行標題，在提取完數據之后，有些行并沒有提取到數據，合計所在的列數值為0，需要刪除該行。遍歷每一個工作表合計列的每一個單元格數據，如果值為0，則使用語句sh.Rows（i）.Select和Selection.Delete Shift：=xlUp可以刪除（sh代表一個工作表對象，i為行號） [12]260。 刪除行操作之后第一列的序號是不連續的，還需將序號重置，在這一過程中，要確定填寫序號的起始行和結束行位置，起始行是提取數據插入單元格的下一行，結束行每一張工作表刪除的行數不一，位置也是不同的，解決思路是從第一列數據的最后一行向上搜索，遇到單元格數據為數值時該行即為需要填寫序號的最后一行，確定好起始行和結束行后便可循環修改序號，實現序號重置。

在主程序的編寫過程中，根據窗口中輸入的參數情況判斷是否執行某一項功能，參數未設置情況下進入下一項功能，因此程序可以執行某一項功能，也可以按邏輯順序執行某幾項或全部的功能。

4 應用實例

選取甘肅開放大學2021年第一學期新生和畢業生數據進行測試，測試結果如表4所示。整個測試是在相同配置環境下進行的。通過數據自動化處理系統完成工作，所需時間僅為21分32秒，統計好的數據表按照指定位置保存，打開檢查數據，各工作表格式完整，均實現了刪除合計數為0的行，序號全部重置。與原數據表核對，結果正確無誤。人工操作與系統自動化操作對比數據如表2所示。表中人工操作時間是純工作時間。通過分析表中數據，人工操作時間主要在數據整理、分表提取數據以及核對結果等過程中，而實踐中完成整個工作的時間跨度通常在10天以上。對比兩種處理方式，采用數據自動化處理系統效率非常明顯，極大地節約了時間。

5 結束語

本文的應用案例中，同以往的工作方法相比，應用數據自動化處理系統使數據處理與統計工作效率顯著提高，所需時間不到原來的1.5%，同時消除了以往繁復操作導致的錯誤，保證了數據處理的質量，有效緩解了工作人員的勞動強度。

實踐表明，利用VBA技術開發的數據自動化處理系統功能強大，自動化程度高，可以實現需求，解決實踐問題。該系統的功能也是通用的，可以應用到其他方面，具有一定的創新和應用價值。

參考文獻：

[1] 王錦秀，康魯豫，李育燕.基于Word VBA的表格編校質量提升方法[J].編輯學報，2021，33（3）：322-326.

[2] 崔玉潔，文娟，廖坤，等.基于Word VBA技術的一鍵智能編校系統[J].編輯學報，2018，30（6）：624-626.

[3] 劉超，莫東旭，薛小蒙，等.基于Visual Lisp和VBA的爆破輔助設計系統研究與應用[J].礦業研究與開發，2020，40（9）：149-153.

[4] 王喬，竇延寶，石立華，等.應用Excel VBA編制煤田鉆孔綜合成果整理程序[J].煤田地質與勘探，2016，44（1）：27-30.

[5] 王喬，萬單靜，張博，等.應用Excel VBA編制煤田鉆孔孔斜批量處理程序[J].煤田地質與勘探，2017，45（1）：41-44.

[6] 王淏，亢娟娜.Excel VBA在測評成績計算中的應用[J].辦公自動化，2021，26（2）：26，63-64.

[7] 朱西敏，韓愛慶，張未未，等.基于Excel VBA的試卷質量分析與評價系統[J].智能計算機與應用，2020，10（3）：246-249，255.

[8] 劉庭立，陳雪泉，張文治，等.高校基于Excel Vba考試系統的研究[J].廣東職業技術教育與研究，2020（2）：46-47.

[9] 蔣勇.基于Excel VBA的等級考試成績管理系統設計[J].揚州職業大學學報，2020，24（4）：46-48，68.

[10] 龍馬高新教育.Office VBA從新手到高手[M].北京：人民郵電出版社，2015.

[11] 郭剛.Excel VBA入門與應用典型實例[M].北京：科學出版社，2009.

[12] Hart-Davis G.VBA從入門到精通[M].楊密，楊樂，柯樹森，譯.北京：電子工業出版社，2008.

收稿日期：2022-02-25

基金項目：甘肅開放大學2021校立項目：基于網絡安全態勢感知平臺安全運維的研究（項目編號：2021-YB-03）

作者簡介：張萬昌（1987—） ，男，甘肅會寧人，講師，學士，研究方向為工程管理、工程造價，現從事教學及教務管理工作;黃寧（1984—） ，女，甘肅天水人，講師，碩士，研究方向為計算機網絡。