Excel多表數(shù)據(jù)匹配軟件在教務(wù)管理中的應(yīng)用

盧志剛,李 穎

(馬鞍山師范高等專(zhuān)科學(xué)校 安徽馬鞍山 243041)

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

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

該軟件已經(jīng)在我校教務(wù)、學(xué)籍、財(cái)務(wù)、資助等多個(gè)行政部門(mén)使用達(dá)8年以上，受到用戶(hù)廣泛的好評(píng)。總之，該軟件不僅能夠?qū)崿F(xiàn)Vlookup函數(shù)的全部功能，還能“傻瓜化”地操作和靈活地配置，解決Vlookup函數(shù)在易用性、靈活性和準(zhǔn)確性等方面的問(wèn)題，降低了技術(shù)門(mén)檻，大大地提高了數(shù)據(jù)匹配的效率和質(zhì)量。

1 現(xiàn)狀與需求

作為一名高校教務(wù)管理人員，筆者經(jīng)常要面對(duì)大量甚至海量的教學(xué)管理類(lèi)數(shù)據(jù)，如學(xué)生學(xué)籍信息、課程基本信息、選課記錄信息、成績(jī)表信息等，且多以Excel格式存儲(chǔ)。這些Excel工作表有的導(dǎo)出自教務(wù)管理信息系統(tǒng)，數(shù)據(jù)表達(dá)比較準(zhǔn)確和規(guī)范，但數(shù)據(jù)有的比較零散，需要進(jìn)行進(jìn)一步的加工處理；另一些數(shù)據(jù)則來(lái)自于教師或?qū)W生，由于人工處理的隨意性，大部分?jǐn)?shù)據(jù)不夠準(zhǔn)確或規(guī)范，許多工作表中的數(shù)據(jù)需要后期加工處理后方可使用。

經(jīng)過(guò)歸類(lèi)和分析，按照用戶(hù)對(duì)數(shù)據(jù)進(jìn)行加工處理的使用目的進(jìn)行分類(lèi)，至少有以下五種基本數(shù)據(jù)加工需求：①有的工作表數(shù)據(jù)不夠完整，需要獲取其它工作表中的數(shù)據(jù)進(jìn)行補(bǔ)全處理；②有的工作表需要與其它工作表中的數(shù)據(jù)進(jìn)行查重處理；③有的工作表需要與其它工作表中的數(shù)據(jù)進(jìn)行查漏處理；④有的工作表數(shù)據(jù)不準(zhǔn)確或者表達(dá)不規(guī)范，需要參照其它工作表中的權(quán)威數(shù)據(jù)進(jìn)行查錯(cuò)和規(guī)范化處理；⑤有的工作表還需要按照一定的條件與其它多個(gè)工作表進(jìn)行合并處理。

2 對(duì)策與應(yīng)用舉例

當(dāng)然，以上部分需求通過(guò)Excel自帶的Vlookup函數(shù)就可以完成。例如，使用Vlookup函數(shù)可以補(bǔ)全信息。但是，面對(duì)其它更復(fù)雜的需求，Vlookup函數(shù)就難以很好地勝任，效率低下且容易出錯(cuò)。

其實(shí)，以上五種數(shù)據(jù)加工處理的需求都可以通過(guò)前述的Excel多表數(shù)據(jù)匹配軟件來(lái)滿(mǎn)足。使用該軟件，用戶(hù)只需點(diǎn)擊鼠標(biāo)而不需要輸入任何函數(shù)代碼即可完成數(shù)據(jù)匹配任務(wù)。而且，用戶(hù)還可以自定義各種參數(shù)，可以滿(mǎn)足各種個(gè)性化的應(yīng)用需求。下面筆者結(jié)合教務(wù)管理中的數(shù)據(jù)匹配實(shí)例對(duì)上述五種需求進(jìn)行分析，并對(duì)該軟件的應(yīng)用方法進(jìn)行較為詳細(xì)的說(shuō)明。

2.1 補(bǔ)全

補(bǔ)全，指的是原始數(shù)據(jù)不夠完整或全面，需要補(bǔ)充完善其它更多信息。在教學(xué)運(yùn)行管理過(guò)程中，教務(wù)管理人員經(jīng)常需要對(duì)各種信息不全的學(xué)生名單進(jìn)行補(bǔ)全處理。例如，已有在校生學(xué)籍庫(kù)和部分信息不完整的學(xué)生名單，如果要獲取這些學(xué)生正確的身份證號(hào)、所在班級(jí)、學(xué)號(hào)等信息，使用傳統(tǒng)的手工方法將會(huì)既費(fèi)時(shí)又費(fèi)力。此時(shí)，可以使用此程序來(lái)完成信息補(bǔ)全的任務(wù)。示意圖見(jiàn)圖1。

圖1 補(bǔ)全示意圖

圖1中，表1與表2中均存在有邏輯關(guān)系的“關(guān)聯(lián)字段”K，現(xiàn)需將字段CDE添加到表2中。由于有三個(gè)字段，因此如果使用Vlookup函數(shù)則需要三次操作。而使用本程序，用戶(hù)可以將表1中的CDE一次性添加到表2中的相應(yīng)位置以實(shí)現(xiàn)信息補(bǔ)全，減少了重復(fù)操作。程序操作界面如圖2所示。

圖2 運(yùn)行界面及補(bǔ)全操作示例

如圖2所示，用戶(hù)可以一次性將表1中權(quán)威和準(zhǔn)確的學(xué)生性別、所在班級(jí)、所屬專(zhuān)業(yè)和所屬院系等信息匹配到表2中的相應(yīng)單元格中，實(shí)現(xiàn)對(duì)學(xué)生名單的補(bǔ)全處理。在該例中，表1中存儲(chǔ)的是在校生名單總庫(kù)，內(nèi)有學(xué)生各項(xiàng)基本學(xué)籍信息；表2為待匹配的部分在校學(xué)生名單，但只有姓名和學(xué)號(hào)字段。顯而易見(jiàn)，兩個(gè)工作表中都有“姓名”字段，故將“姓名”字段視為兩表間的“關(guān)聯(lián)字段”K并在下拉列表中選擇該字段名稱(chēng)。性別、所在班級(jí)、所屬專(zhuān)業(yè)和所屬院系等四個(gè)“字段”是需要從表1中返回的數(shù)據(jù)列CDEF，用戶(hù)勾選列表框中的這些“字段”名稱(chēng)，通過(guò)數(shù)據(jù)匹配操作即可將正確數(shù)據(jù)附加到表2中的相應(yīng)單元格中。

由圖2可見(jiàn)，用戶(hù)可以自由選擇基準(zhǔn)工作表(即表1)和待數(shù)據(jù)匹配的工作表(即表2)，并可以靈活地在工作簿中實(shí)時(shí)切換到不同的工作表，方便了用戶(hù)的操作；用戶(hù)還可以自由地選擇和指定兩個(gè)工作表之間存在邏輯關(guān)系的“關(guān)聯(lián)字段”，如圖2中的“姓名”字段；如果工作表中的第一行是工作表的標(biāo)題，用戶(hù)還可以勾選“首行標(biāo)題”復(fù)選框?？傊?，用戶(hù)操作起來(lái)非常方便，不需要寫(xiě)任何代碼即可完成數(shù)據(jù)匹配任務(wù)。

2.2 查重

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

圖3 查重示意圖

圖3中，表2是待處分的學(xué)生名單，包括K2、K4、K8。表1是已有處分的學(xué)生名單，包括K1-K5。如需查詢(xún)表2中的學(xué)生是否在表1名單中，可以使用本程序進(jìn)行數(shù)據(jù)匹配處理以達(dá)到查重的目的。數(shù)據(jù)匹配后，表2中K2、K4所在單元格后將會(huì)被附加新的有效數(shù)據(jù)，表明K2和K4是有重復(fù)的，也即有處分記錄；而K8所在單元格后將不會(huì)新增任何有效數(shù)據(jù)，表明K8無(wú)處分記錄?？傊?，通過(guò)一些簡(jiǎn)單的設(shè)置和操作，教務(wù)管理人員可以快速獲得兩個(gè)工作表之間的重復(fù)信息，從而滿(mǎn)足數(shù)據(jù)查重的需求。

2.3 查漏

查漏，與查重相反，它指的是對(duì)比兩批數(shù)據(jù)并從中找出兩者的不同之處。例如，在進(jìn)行學(xué)生選課處理時(shí)，本著人性化管理的原則，需要教務(wù)管理人員適時(shí)統(tǒng)計(jì)應(yīng)選課但未選課的學(xué)生名單，以便及時(shí)、精準(zhǔn)地通知到漏選學(xué)生參與選課。問(wèn)題在于，目前一般的教務(wù)管理系統(tǒng)很難自動(dòng)統(tǒng)計(jì)出未選課學(xué)生名單，需要學(xué)生自覺(jué)按選課要求參與選課。此時(shí)，合理使用本程序來(lái)進(jìn)行查漏處理，可以快速得到漏選課學(xué)生名單。查漏的示意圖見(jiàn)圖4。

圖4 查漏示意圖

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

2.4 查錯(cuò)

查錯(cuò)，指的是對(duì)比兩批數(shù)據(jù)，并以其中一批數(shù)據(jù)(一般是權(quán)威、準(zhǔn)確的數(shù)據(jù))為基準(zhǔn)，找出另一批數(shù)據(jù)中的錯(cuò)誤或不規(guī)范之處。這里的權(quán)威數(shù)據(jù)一般來(lái)源于教務(wù)系統(tǒng)中的學(xué)生數(shù)據(jù)。例如，教務(wù)管理過(guò)程中，在統(tǒng)計(jì)和上報(bào)各類(lèi)技能競(jìng)賽報(bào)名名單時(shí)，不可避免地會(huì)遇到學(xué)生的姓名與學(xué)號(hào)不對(duì)應(yīng)、班級(jí)名稱(chēng)或院系名稱(chēng)不規(guī)范等各種問(wèn)題。嚴(yán)謹(jǐn)起見(jiàn)，在對(duì)內(nèi)發(fā)布、對(duì)外公開(kāi)、對(duì)上上報(bào)前相關(guān)管理人員必須提前對(duì)這些名單進(jìn)行查錯(cuò)處理后方可進(jìn)行下一步的工作。此時(shí)，可以利用本程序的數(shù)據(jù)匹配和等值判斷功能來(lái)實(shí)現(xiàn)數(shù)據(jù)的查錯(cuò)處理，從而發(fā)現(xiàn)錯(cuò)誤或者不規(guī)范之處以便及時(shí)更正。查錯(cuò)示意圖見(jiàn)圖5。

圖5 查錯(cuò)示意圖

圖5中，表1與表2中均存在有邏輯關(guān)系的“關(guān)聯(lián)字段”K，一般為學(xué)生姓名或者學(xué)號(hào)，且同時(shí)存在字段CDE。但是由于表1中數(shù)據(jù)權(quán)威度和準(zhǔn)確度更高，因此現(xiàn)需驗(yàn)證表2中的C是否與表1中的C一致并給出醒目提醒。如果不一致，說(shuō)明表2中的C是錯(cuò)誤的或者說(shuō)是不規(guī)范的表達(dá)，需要更正。如果一致，則說(shuō)明填寫(xiě)正確無(wú)誤。

使用本程序進(jìn)行數(shù)據(jù)匹配處理后，如果表2中的K沒(méi)有找到，則表明該學(xué)生姓名或?qū)W號(hào)在學(xué)生學(xué)籍庫(kù)中不存在，也說(shuō)明報(bào)名表中的學(xué)生姓名或者學(xué)號(hào)填寫(xiě)錯(cuò)誤需要返回修改；如果表2中的K成功找到，程序則會(huì)判斷兩表中的字段C是否等值，并用“√”或“×”來(lái)反饋給用戶(hù)。其中，“√”表示填寫(xiě)規(guī)范無(wú)需修改，“×”表示兩者不一致需要再修改。此時(shí)，用戶(hù)只需要重點(diǎn)關(guān)注打“×”的部分即可。

2.5 合并

合并，指的是根據(jù)一定的條件，將兩批或以上的數(shù)據(jù)進(jìn)行有邏輯關(guān)系的合并。例如，一般來(lái)說(shuō)，高校的教師教學(xué)質(zhì)量考核成績(jī)是由多個(gè)分項(xiàng)成績(jī)組成的，比如常見(jiàn)的教師自評(píng)、同行評(píng)教、學(xué)生評(píng)教、督導(dǎo)評(píng)教等。對(duì)這些來(lái)自不同渠道的數(shù)據(jù)進(jìn)行合并是一項(xiàng)相當(dāng)麻煩的事情，尤其是考核對(duì)象比較多的時(shí)候。以馬鞍山師專(zhuān)為例，我校的教師教學(xué)質(zhì)量考核成績(jī)由學(xué)生評(píng)教、同行評(píng)教和考核組評(píng)教三項(xiàng)組成，各自占有一定的比例。在統(tǒng)計(jì)每位教師的總評(píng)成績(jī)時(shí)，需要將三項(xiàng)得分按照一定的比例進(jìn)行合成匯總。利用本程序的數(shù)據(jù)匹配功能可以快速將多個(gè)表格合并在一起且不會(huì)出錯(cuò)。示意圖見(jiàn)圖6。

圖6 多表合并示意圖

如圖6所示，其實(shí)現(xiàn)方法是：將存儲(chǔ)分項(xiàng)成績(jī)的3個(gè)表格(上圖中的表1-1、1-2、1-3)均視為表1，將全校教師名單視為表2，以姓名或教工號(hào)K作為“關(guān)聯(lián)字段”，然后重復(fù)3次數(shù)據(jù)匹配操作，這樣就可以快速得到每位教師的各分項(xiàng)考評(píng)成績(jī)C1、C2和C3。之后，我們?cè)偈褂肊xcel的計(jì)算公式，即可根據(jù)成績(jī)比例換算得到每位教師的最終考核成績(jī)。此外，利用此種方法還能快速觀察到是否有教師存在遺漏某分項(xiàng)成績(jī)的問(wèn)題。

3 結(jié)語(yǔ)

本文闡述了Vlookup函數(shù)存在的四個(gè)問(wèn)題，對(duì)教務(wù)管理過(guò)程中常見(jiàn)的數(shù)據(jù)匹配需求進(jìn)行了分析和歸類(lèi)，總結(jié)了五種常見(jiàn)的數(shù)據(jù)匹配需求，結(jié)合Excel多表數(shù)據(jù)匹配軟件和具體的應(yīng)用場(chǎng)景對(duì)五種需求進(jìn)行了解釋并給出具體的解決方案。該解決方案不僅適用于教務(wù)管理過(guò)程，我們還可以將其思路與方法遷移和運(yùn)用到更多的數(shù)據(jù)匹配場(chǎng)景中，讓更多的數(shù)據(jù)處理人員從中受益。