劉沈

摘要：針對全國中等職業(yè)學(xué)校學(xué)生管理信息系統(tǒng)中數(shù)據(jù)模板檢測功能的不足，本研究探討使用Excel內(nèi)置函數(shù)來檢查有關(guān)字段，解決了數(shù)據(jù)補錄及照片文件批量更名等問題，避免了等待服務(wù)器端反饋錯誤的環(huán)節(jié)，促進了學(xué)籍信息管理的自動化和信息化，有助于學(xué)籍管理工作效率的提高。

關(guān)鍵詞：Excel；函數(shù)；學(xué)籍管理

中圖分類號：TP311 文獻標(biāo)識碼：A 文章編號：1009-3044（2015）29-0112-02

The Application of EXCEL in the Management Information System of Middle Vocational School Students in China

LIU Shen

（Yangzhou Business School of Business Administration， Yangzhou 225127， China）

Abstract： Aiming at the shortage of the secondary vocational school student management information system data template detection function， this study explore using Excel's built-in function to check the field， to solve the problems of additional data recording and photo file batch rename， etc.， to avoid waiting for server feedback error link， promote the automation and information technology in the management of student information， help to improve the student management work efficiency.

Key words： Excel； function； school management

為全面實現(xiàn)全國學(xué)生學(xué)籍“一人一號、終身使用”，杜絕跨教育階段學(xué)籍重復(fù)，新版全國中等職業(yè)學(xué)校學(xué)生管理信息系統(tǒng)已于2014年12月28日在江蘇正式上線。該系統(tǒng)針對新生信息錄入、在校生信息維護等功能均依托于EXCEL來完成，并提供了“招生導(dǎo)入模板”、“新生數(shù)據(jù)批量處理”、“在校生檔案管理數(shù)據(jù)批量處理”等一系列的EXCEL模板，基于模板內(nèi)的VBA宏，用戶可實現(xiàn)對學(xué)生數(shù)據(jù)的批量生成與檢查。盡管系統(tǒng)提供的模板已經(jīng)較好地提高了工作效率，避免了一些可能會出現(xiàn)的人工錯誤，但在實際操作中仍有不少可以改進的空間。筆者結(jié)合自身從事學(xué)籍管理工作的經(jīng)驗，以學(xué)生數(shù)據(jù)導(dǎo)入、數(shù)據(jù)補錄、照片文件命名等實際問題為例，介紹EXCEL部分函數(shù)的功能與應(yīng)用。

1 EXCEL在學(xué)生數(shù)據(jù)處理中的應(yīng)用

1.1 檢查行政區(qū)劃碼在字典中是否存在

全國中等職業(yè)學(xué)校學(xué)生管理信息系統(tǒng)（以下簡稱全國中職系統(tǒng)）在錄入新生學(xué)籍信息時，先要通過“招生導(dǎo)入模板”來完成招生信息管理模塊，該模板中有兩個必填字段“戶口所在地行政區(qū)劃碼”與“生源地行政區(qū)劃碼”，可以通過模板中的“批量自動生成”按鈕來根據(jù)身份證號前6位實現(xiàn)自動填充。然而筆者發(fā)現(xiàn)在點擊模板中的“開始檢查”按鈕之后，EXCEL并不會檢查自動生成的行政區(qū)劃碼是否在字典表內(nèi)存在，當(dāng)用戶在“數(shù)據(jù)校驗通過，可以上傳”的對話框彈出后上傳該模板至系統(tǒng)時，系統(tǒng)會在服務(wù)器端對數(shù)據(jù)再度進行處理，將沒有錯誤的新生數(shù)據(jù)導(dǎo)入系統(tǒng)，錯誤的數(shù)據(jù)系統(tǒng)會生成錯誤文件，用戶下載后可查看具體錯誤信息。然而服務(wù)器端對用戶導(dǎo)入的數(shù)據(jù)處理并不是實時的，而是需要等待5分鐘后方可查看處理結(jié)果，并且絕大多數(shù)的錯誤都是“系統(tǒng)不存在該戶口所在地行政區(qū)劃碼”，即由身份證號自動生成的行政區(qū)劃碼在模板的字典表內(nèi)并不存在，這時就需要用戶在更改行政區(qū)劃碼后再度上傳，再在5分鐘后查看處理結(jié)果。如果事先可以對行政區(qū)劃碼進行檢查，就可以避免上述的重復(fù)性勞動。因而在“招生導(dǎo)入模板”的字典表內(nèi)，筆者新增了一列，用MID和FIND函數(shù)取出字典表內(nèi)行政區(qū)劃碼的數(shù)字。如圖1所示，M2單元格內(nèi)的公式填寫如下：=MID（A2，F(xiàn)IND（"（"，A2）+1，12），用自動填充柄完成下拉填充。然后到模板的學(xué)生基礎(chǔ)信息表內(nèi)新增一列，在Z4單元格輸入公式：=VLOOKUP（I4，字典！M：M，1，0），如果結(jié)果顯示為“#N/A”，則表明該生通過身份證號自動生成的行政區(qū)劃碼在系統(tǒng)內(nèi)不存在，此時可用篩選功能將所有此類學(xué)生篩選出來，并逐一處理后再上傳。

1.2 借助公式高效完成數(shù)據(jù)補錄工作

在新版的全國中職系統(tǒng)正式上線后，江蘇省各院校需對系統(tǒng)內(nèi)數(shù)據(jù)進行完善，對學(xué)生的生源地行政區(qū)劃碼、戶口所在地行政區(qū)劃碼、民族等字段完成補錄工作。民族字段可以依托于VLOOKUP函數(shù)在現(xiàn)有的省中職系統(tǒng)所導(dǎo)出的學(xué)生數(shù)據(jù)表中進行查找，但是行政區(qū)劃碼卻只能通過其生源地名稱、戶口所在地名稱在模板內(nèi)的字典表內(nèi)進行查找。字典表內(nèi)戶籍地址行政區(qū)劃碼的格式為名稱后跟括號，括號內(nèi)為代碼，例如：揚州市廣陵區(qū)（321002000000））。筆者根據(jù)此特點在補錄模板中新增了生源地行政區(qū)、戶口行政區(qū)兩列，通過VLOOKUP函數(shù)在省系統(tǒng)導(dǎo)出的數(shù)據(jù)表內(nèi)找到每名學(xué)生對應(yīng)的地區(qū)名稱，在M4單元格內(nèi)輸入公式：=IF（ISERROR（MID（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A），LEN（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A））-12，12）），""，MID（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A），LEN（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A））-12，12）），EXCEL便會完成名稱與代碼的匹配，結(jié)果如圖3所示。同理，在N4單元格只需將上述公式內(nèi)的K4更換為L4即可，在完成M列與N列的自動填充后，選中兩列數(shù)據(jù)復(fù)制，并在原處選擇粘貼“值”，完成此步后刪除K列與L列，即可導(dǎo)入全國中職系統(tǒng)，完成數(shù)據(jù)補錄工作。

1.3 運用公式提取監(jiān)護人聯(lián)系電話

全國中職系統(tǒng)內(nèi)需要填寫學(xué)生的監(jiān)護人電話，該字段不可超過20個字符，只能為手機號碼或固定電話，且若為固定電話，前面需要加上區(qū)號。由于學(xué)生在報名時，學(xué)校招辦會錄入學(xué)生的基本信息，其中包含了家長聯(lián)系電話，很多班級在填寫全國中職系統(tǒng)數(shù)據(jù)模板時，直接復(fù)制了招辦報名信息表內(nèi)的聯(lián)系電話列內(nèi)的內(nèi)容，但是招辦在填寫聯(lián)系電話時往往至少登記一個固定電話與一個手機號碼，中間用頓號隔開，且固定電話在前，揚州大市的還略去了區(qū)號。這樣造成模板中的監(jiān)護人電話這列超過了20個字符，無法導(dǎo)入系統(tǒng)。筆者在監(jiān)護人電話所在的P列右側(cè)新增一列，在該列數(shù)據(jù)所在的第一個單元格Q4內(nèi)輸入公式：=IF（ISERROR（FIND（"、"，P4）），P4，MID（P4，F(xiàn)IND（"、"，P4）+1，12）），這樣便會取出頓號后面的手機號碼，在完成Q列自動填充后再將該列數(shù)據(jù)復(fù)制，在P列選擇粘貼“值”，再刪除Q列，即對監(jiān)護人電話完成符合系統(tǒng)要求的修改。

1.4 檢查數(shù)據(jù)填寫的完整性

在上傳學(xué)生信息至系統(tǒng)時，會發(fā)現(xiàn)有些通過模板內(nèi)“批量自動生成”按鈕填寫的數(shù)據(jù)會發(fā)生缺漏的情況，導(dǎo)致部分數(shù)據(jù)無法上傳。為了避免在上傳后才發(fā)現(xiàn)此類問題，筆者通過COUNTA函數(shù)來完成數(shù)據(jù)完整性的檢驗。以招生導(dǎo)入模板為例，在最后一列的Z4單元格輸入公式：=IF（COUNTA（$A4：$Y4）=COUNTA（$A$3：$Y$3），""，"存在缺項"），統(tǒng)計當(dāng)前行中的字段填寫個數(shù)，若非空單元格個數(shù)與第三行中的行標(biāo)題個數(shù)相同則返回空值，表明數(shù)據(jù)填寫完整，如果有缺漏則返回“存在缺項”，再去對相關(guān)數(shù)據(jù)進行完善。

2 EXCEL在照片文件處理中的應(yīng)用

在完成全國中職系統(tǒng)內(nèi)學(xué)生數(shù)據(jù)上報的工作后，還需要在系統(tǒng)內(nèi)上傳學(xué)生的照片。所有的照片文件都必須以在校生的身份證號命名。根據(jù)以往的流程，是請各班主任將以身份證號命名的學(xué)生電子照片統(tǒng)一交至教務(wù)處，再由學(xué)籍管理員上傳至系統(tǒng)。然而筆者發(fā)現(xiàn)各班級在用學(xué)生身份證號命名照片文件時很容易產(chǎn)生錯誤，比如漏了一位或者數(shù)字前后顛倒等等，對這些錯誤進行糾正會耗費大量的時間與精力。因而筆者要求各班在交照片時均以學(xué)號+姓名的方式命名，將照片統(tǒng)一放在一個文件夾內(nèi)。之后筆者利用全國中職系統(tǒng)內(nèi)的導(dǎo)出功能，將新生數(shù)據(jù)導(dǎo)出，僅保留學(xué)號、姓名、身份證號三列，然后在D2單元格輸入公式：=A2&B2，用填充柄自動填充D列，再在E2單元格內(nèi)輸入公式：="ren "&D2&".jpg "&C2&".jpg"，并用填充柄自動填充E列（如圖4）。在照片所在文件夾內(nèi)新建一記事本文件，并將E列數(shù)據(jù)復(fù)制到記事本內(nèi)，另存為擴展名為BAT的文件之后，在文件夾內(nèi)運行該BAT文件，將會自動將所有照片文件用身份證號碼完成批量更名操作。此時照片文件名稱會與系統(tǒng)內(nèi)身份證號完全一致，在導(dǎo)入過程中便不會出現(xiàn)任何錯誤。

3 結(jié)束語

本研究基于對EXCEL的部分函數(shù)應(yīng)用，實現(xiàn)對全國中職系統(tǒng)的數(shù)據(jù)模板部分優(yōu)化，補充了對相關(guān)字段的檢查，避免了等待服務(wù)器檢查反饋錯誤信息的環(huán)節(jié)，通過對照片文件的批量重命名，保證了文件名的正確性，較好地提高了工作效率，也為從事學(xué)籍管理相關(guān)工作的人員能夠從繁雜的數(shù)據(jù)處理與核對中解放出來提供了一定的幫助。

參考文獻：

[1] 劉艷慧.EXCEL函數(shù)在學(xué)生信息管理中的應(yīng)用[J].價值工程，2014（6）：195-196.

[2] 李仙花.淺議Excel函數(shù)在學(xué)籍管理中的應(yīng)用[J].職業(yè)，2014（3）：141-142.

[3] 肖章林，梁爽.Excel在學(xué)生管理工作中的應(yīng)用[J].電腦知識與技術(shù)，2014（34）：8179-8193.

[4] 韓輝珍.Excel在學(xué)生成績管理中的應(yīng)用[J].中國科技信息，2008（12）：39-41.