Excel多表數據匹配軟件在教務管理中的應用

盧志剛,李 穎

(馬鞍山師范高等專科學校 安徽馬鞍山 243041)

Excel自帶的Vlookup函數在實際應用過程中存在以下四個問題。一是了解此函數的人并不多，很多人依然習慣用人工方法進行數據匹配。二是易用性不夠好，普通人需要專門學習其用法且容易出錯。因此，關于Vlookup函數用法的教程在網絡上普通存在，不少網絡視頻公共課中也有專門講解該函數使用方法的章節。而且，知網上也有一些論文介紹其具體用法。例如，在高校學生戶籍信息管理時，可以使用該函數根據身份證號這個共通字段從學籍表中獲取學生的其他信息。在財務處理過程中，可使用Vlookup函數將職工銀行帳號加入到工資表中。在Excel函數教學過程中，可以通過多種方法強化學生對該函數的學習。三是靈活性尚不足。其使用限制條件較多，需要用戶按照其苛刻要求做好各項準備工作后才能較好地完成數據匹配任務。四是準確性有待提高，其模糊匹配功能易誤導用戶，數據匹配結果的反饋有時不夠準確。

為解決上述問題，本研究設計了一個通用的、可視化的Excel多表數據匹配軟件用于代替Vlookup函數。該軟件將Excel工作表視為一個標準的關系型數據庫，并采用二分查找算法來提高運行效率。為提高操作的易用性，它采用圖形化界面，使用戶不需要記憶和輸入任何代碼就可以完成數據匹配操作；為保證操作的靈活性，它提供了眾多可選的參數，用戶可以根據實際需要靈活選擇其中的某些參數；為確保數據的準確性，它使用了嚴苛的算法，用戶可以充分信任其數據匹配結果。

該軟件已經在我校教務、學籍、財務、資助等多個行政部門使用達8年以上，受到用戶廣泛的好評。總之，該軟件不僅能夠實現Vlookup函數的全部功能，還能“傻瓜化”地操作和靈活地配置，解決Vlookup函數在易用性、靈活性和準確性等方面的問題，降低了技術門檻，大大地提高了數據匹配的效率和質量。

1 現狀與需求

作為一名高校教務管理人員，筆者經常要面對大量甚至海量的教學管理類數據，如學生學籍信息、課程基本信息、選課記錄信息、成績表信息等，且多以Excel格式存儲。這些Excel工作表有的導出自教務管理信息系統，數據表達比較準確和規范，但數據有的比較零散，需要進行進一步的加工處理；另一些數據則來自于教師或學生，由于人工處理的隨意性，大部分數據不夠準確或規范，許多工作表中的數據需要后期加工處理后方可使用。

經過歸類和分析，按照用戶對數據進行加工處理的使用目的進行分類，至少有以下五種基本數據加工需求：①有的工作表數據不夠完整，需要獲取其它工作表中的數據進行補全處理；②有的工作表需要與其它工作表中的數據進行查重處理；③有的工作表需要與其它工作表中的數據進行查漏處理；④有的工作表數據不準確或者表達不規范，需要參照其它工作表中的權威數據進行查錯和規范化處理；⑤有的工作表還需要按照一定的條件與其它多個工作表進行合并處理。

2 對策與應用舉例

當然，以上部分需求通過Excel自帶的Vlookup函數就可以完成。例如，使用Vlookup函數可以補全信息。但是，面對其它更復雜的需求，Vlookup函數就難以很好地勝任，效率低下且容易出錯。

其實，以上五種數據加工處理的需求都可以通過前述的Excel多表數據匹配軟件來滿足。使用該軟件，用戶只需點擊鼠標而不需要輸入任何函數代碼即可完成數據匹配任務。而且，用戶還可以自定義各種參數，可以滿足各種個性化的應用需求。下面筆者結合教務管理中的數據匹配實例對上述五種需求進行分析，并對該軟件的應用方法進行較為詳細的說明。

2.1 補全

補全，指的是原始數據不夠完整或全面，需要補充完善其它更多信息。在教學運行管理過程中，教務管理人員經常需要對各種信息不全的學生名單進行補全處理。例如，已有在校生學籍庫和部分信息不完整的學生名單，如果要獲取這些學生正確的身份證號、所在班級、學號等信息，使用傳統的手工方法將會既費時又費力。此時，可以使用此程序來完成信息補全的任務。示意圖見圖1。

圖1 補全示意圖

圖1中，表1與表2中均存在有邏輯關系的“關聯字段”K，現需將字段CDE添加到表2中。由于有三個字段，因此如果使用Vlookup函數則需要三次操作。而使用本程序，用戶可以將表1中的CDE一次性添加到表2中的相應位置以實現信息補全，減少了重復操作。程序操作界面如圖2所示。

圖2 運行界面及補全操作示例

如圖2所示，用戶可以一次性將表1中權威和準確的學生性別、所在班級、所屬專業和所屬院系等信息匹配到表2中的相應單元格中，實現對學生名單的補全處理。在該例中，表1中存儲的是在校生名單總庫，內有學生各項基本學籍信息；表2為待匹配的部分在校學生名單，但只有姓名和學號字段。顯而易見，兩個工作表中都有“姓名”字段，故將“姓名”字段視為兩表間的“關聯字段”K并在下拉列表中選擇該字段名稱。性別、所在班級、所屬專業和所屬院系等四個“字段”是需要從表1中返回的數據列CDEF，用戶勾選列表框中的這些“字段”名稱，通過數據匹配操作即可將正確數據附加到表2中的相應單元格中。

由圖2可見，用戶可以自由選擇基準工作表(即表1)和待數據匹配的工作表(即表2)，并可以靈活地在工作簿中實時切換到不同的工作表，方便了用戶的操作；用戶還可以自由地選擇和指定兩個工作表之間存在邏輯關系的“關聯字段”，如圖2中的“姓名”字段；如果工作表中的第一行是工作表的標題，用戶還可以勾選“首行標題”復選框。總之，用戶操作起來非常方便，不需要寫任何代碼即可完成數據匹配任務。

2.2 查重

查重，指的是對比兩批數據，并找出其中的重復之處。例如，在做學生考試作弊處分審核時，經常需要查詢待處分學生中是否存在往年已有處分且未解除處分的情況。如果有的話則一般要加重處分，如果沒有則不需要加重處分。此時，合理利用本程序可以實現快速查重，從而大大提高工作效率。查重的示意圖見圖3。

圖3 查重示意圖

圖3中，表2是待處分的學生名單，包括K2、K4、K8。表1是已有處分的學生名單，包括K1-K5。如需查詢表2中的學生是否在表1名單中，可以使用本程序進行數據匹配處理以達到查重的目的。數據匹配后，表2中K2、K4所在單元格后將會被附加新的有效數據，表明K2和K4是有重復的，也即有處分記錄；而K8所在單元格后將不會新增任何有效數據，表明K8無處分記錄。總之，通過一些簡單的設置和操作，教務管理人員可以快速獲得兩個工作表之間的重復信息，從而滿足數據查重的需求。

2.3 查漏

查漏，與查重相反，它指的是對比兩批數據并從中找出兩者的不同之處。例如，在進行學生選課處理時，本著人性化管理的原則，需要教務管理人員適時統計應選課但未選課的學生名單，以便及時、精準地通知到漏選學生參與選課。問題在于，目前一般的教務管理系統很難自動統計出未選課學生名單，需要學生自覺按選課要求參與選課。此時，合理使用本程序來進行查漏處理，可以快速得到漏選課學生名單。查漏的示意圖見圖4。

圖4 查漏示意圖

圖4中，表2為應選課學生名單，包括K1-K5。表1為從選課系統中導出的已選課學生名單，包括K1、K3、K5。使用本程序進行數據匹配處理后，表2中K1、K3、K5所在單元格后都將添加新的有效數據，而K2、K4所在單元格后則沒有添加任何有效數據。這就意味著K2、K4是表1與表2的不同之處，也即K2和K4漏選課。

2.4 查錯

查錯，指的是對比兩批數據，并以其中一批數據(一般是權威、準確的數據)為基準，找出另一批數據中的錯誤或不規范之處。這里的權威數據一般來源于教務系統中的學生數據。例如，教務管理過程中，在統計和上報各類技能競賽報名名單時，不可避免地會遇到學生的姓名與學號不對應、班級名稱或院系名稱不規范等各種問題。嚴謹起見，在對內發布、對外公開、對上上報前相關管理人員必須提前對這些名單進行查錯處理后方可進行下一步的工作。此時，可以利用本程序的數據匹配和等值判斷功能來實現數據的查錯處理，從而發現錯誤或者不規范之處以便及時更正。查錯示意圖見圖5。

圖5 查錯示意圖

圖5中，表1與表2中均存在有邏輯關系的“關聯字段”K，一般為學生姓名或者學號，且同時存在字段CDE。但是由于表1中數據權威度和準確度更高，因此現需驗證表2中的C是否與表1中的C一致并給出醒目提醒。如果不一致，說明表2中的C是錯誤的或者說是不規范的表達，需要更正。如果一致，則說明填寫正確無誤。

使用本程序進行數據匹配處理后，如果表2中的K沒有找到，則表明該學生姓名或學號在學生學籍庫中不存在，也說明報名表中的學生姓名或者學號填寫錯誤需要返回修改；如果表2中的K成功找到，程序則會判斷兩表中的字段C是否等值，并用“√”或“×”來反饋給用戶。其中，“√”表示填寫規范無需修改，“×”表示兩者不一致需要再修改。此時，用戶只需要重點關注打“×”的部分即可。

2.5 合并

合并，指的是根據一定的條件，將兩批或以上的數據進行有邏輯關系的合并。例如，一般來說，高校的教師教學質量考核成績是由多個分項成績組成的，比如常見的教師自評、同行評教、學生評教、督導評教等。對這些來自不同渠道的數據進行合并是一項相當麻煩的事情，尤其是考核對象比較多的時候。以馬鞍山師專為例，我校的教師教學質量考核成績由學生評教、同行評教和考核組評教三項組成，各自占有一定的比例。在統計每位教師的總評成績時，需要將三項得分按照一定的比例進行合成匯總。利用本程序的數據匹配功能可以快速將多個表格合并在一起且不會出錯。示意圖見圖6。

圖6 多表合并示意圖

如圖6所示，其實現方法是：將存儲分項成績的3個表格(上圖中的表1-1、1-2、1-3)均視為表1，將全校教師名單視為表2，以姓名或教工號K作為“關聯字段”，然后重復3次數據匹配操作，這樣就可以快速得到每位教師的各分項考評成績C1、C2和C3。之后，我們再使用Excel的計算公式，即可根據成績比例換算得到每位教師的最終考核成績。此外，利用此種方法還能快速觀察到是否有教師存在遺漏某分項成績的問題。

3 結語

本文闡述了Vlookup函數存在的四個問題，對教務管理過程中常見的數據匹配需求進行了分析和歸類，總結了五種常見的數據匹配需求，結合Excel多表數據匹配軟件和具體的應用場景對五種需求進行了解釋并給出具體的解決方案。該解決方案不僅適用于教務管理過程，我們還可以將其思路與方法遷移和運用到更多的數據匹配場景中，讓更多的數據處理人員從中受益。