基于VBA技術(shù)的教務(wù)管理數(shù)據(jù)自動化處理系統(tǒng)研究和應(yīng)用

張萬昌 黃寧

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

關(guān)鍵詞：Excel;VBA;二次開發(fā);教務(wù)管理;數(shù)據(jù)處理;自動化

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

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

1 引言

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

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

2 原始數(shù)據(jù)特征和工作需求分析

原始數(shù)據(jù)特征和需求分析是進行數(shù)據(jù)處理和統(tǒng)計工作的基礎(chǔ)。只有了解原始數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)類型，以及不同數(shù)據(jù)之間的關(guān)系，再結(jié)合需求，才能提出對應(yīng)的解決思路或方法。

國家開放大學教務(wù)管理系統(tǒng)中可以下載省?？偟脑紨?shù)據(jù)表，數(shù)據(jù)共18列，采用其中4列數(shù)據(jù)即可完成工作需求。這4列數(shù)據(jù)標題依次為學校名稱、姓名、專業(yè)和學歷層次。每行數(shù)據(jù)互不重復，單元格數(shù)據(jù)全部為文本（字符串） 格式，學校名稱所在列單元格的數(shù)據(jù)包含了學生所屬的市級分校和學習中心的完整信息，該列單元格中字符串長度不唯一，結(jié)構(gòu)不規(guī)范，是數(shù)據(jù)整理的關(guān)鍵。可歸為以下6類形式：“***分校***”“***分?！薄?**學院***”“***學院”“***工作站”“***教學點”（不含“分?！被颉皩W院”字符，是一個獨立的學習中心） ，“分校”或“學院”前面的字符長度也不完全相同（圖1） 。

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

數(shù)據(jù)提取到的表需要設(shè)計成標準模板。所有表在一個Excel文件下，不同表的名稱以不同的學歷層次命名。所有表均是統(tǒng)一的格式設(shè)置，行標題和列標題位置一致;由于分校的前后順序有要求，所有表中行標題按照順序?qū)懹型暾姆中ＣQ（學習中心） 。標準模板如圖2所示。

3 系統(tǒng)設(shè)計過程

3.1 可視化界面設(shè)計

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

3.2 定義公共變量和自定義函數(shù)

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

3.3 控件事件過程

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

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

3） 數(shù)據(jù)整理頁面控件事件過程。數(shù)據(jù)整理頁面的“按標志字符串分列”“同位替換”和“異位替換”功能區(qū)文本框，均不許在文本框輸入空格、非打印字符、中文逗號和連續(xù)的逗號，首尾也不允許出現(xiàn)逗號?？稍谒形谋究虻腸hange和exit事件過程中可寫入不同的代碼，刪除首尾逗號必須在exit事件過程中實現(xiàn)，在change事件過程中會導致無法輸入逗號。

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

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

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

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

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

不同功能的實現(xiàn)思路如下：

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

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

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

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

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

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

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

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

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

4 應(yīng)用實例

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

5 結(jié)束語

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

實踐表明，利用VBA技術(shù)開發(fā)的數(shù)據(jù)自動化處理系統(tǒng)功能強大，自動化程度高，可以實現(xiàn)需求，解決實踐問題。該系統(tǒng)的功能也是通用的，可以應(yīng)用到其他方面，具有一定的創(chuàng)新和應(yīng)用價值。

參考文獻：

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

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

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

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

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

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

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

[8] 劉庭立，陳雪泉，張文治，等.高?；贓xcel Vba考試系統(tǒng)的研究[J].廣東職業(yè)技術(shù)教育與研究，2020（2）：46-47.

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

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

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

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

收稿日期：2022-02-25

基金項目：甘肅開放大學2021校立項目：基于網(wǎng)絡(luò)安全態(tài)勢感知平臺安全運維的研究（項目編號：2021-YB-03）

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