電子表格函數(shù)在高校學(xué)籍管理中的應(yīng)用研究

牟雨婷

（南京特殊教育師范學(xué)院 教務(wù)處，江蘇 南京 210038）

一、引言

近年來，隨著高等教育的發(fā)展，高校教務(wù)信息化建設(shè)的步伐也在加快，針對(duì)學(xué)籍信息的管理工作也越來越復(fù)雜，傳統(tǒng)的人工和半人工的學(xué)籍管理模式使學(xué)籍管理的工作長期處于一個(gè)低效率狀態(tài)，給學(xué)籍信息的查找、維護(hù)帶來眾多不便。

根據(jù)教育部《高等教育學(xué)歷證書電子注冊管理暫行規(guī)定》要求，從2001年起對(duì)普通高等教育、成人高等教育等高校畢業(yè)證書實(shí)行電子注冊，并且建立全國統(tǒng)一的學(xué)歷查詢系統(tǒng)，畢業(yè)生可以通過“學(xué)信網(wǎng)”進(jìn)行在線查詢，使各高校之間的學(xué)籍管理平臺(tái)得以統(tǒng)一。[1]但是，學(xué)籍管理信息化是一項(xiàng)非常復(fù)雜的系統(tǒng)工程，“學(xué)信網(wǎng)”雖然使各高校學(xué)籍管理信息化平臺(tái)得到了統(tǒng)一，但是其功能并不能解決各高校在學(xué)籍管理中的所有問題，如學(xué)籍信息統(tǒng)一維護(hù)、各項(xiàng)學(xué)籍報(bào)表統(tǒng)計(jì)等。

各高校在信息化建設(shè)過程中通常購買或者聯(lián)合開發(fā)適合本校的教務(wù)管理系統(tǒng)，其中學(xué)籍管理是教務(wù)管理系統(tǒng)中的重要組成部分。此時(shí)一般高校學(xué)籍管理也就形成了兩個(gè)信息化平臺(tái)——“學(xué)信網(wǎng)”和“學(xué)籍管理系統(tǒng)”。

但是作為高校的學(xué)籍管理人員，報(bào)表通常使用電子表格（Excel）進(jìn)行統(tǒng)計(jì)和分析，最終將準(zhǔn)確的學(xué)籍信息按照模板上傳至“學(xué)信網(wǎng)”，這樣就需要反復(fù)對(duì)“學(xué)籍管理系統(tǒng)”和“學(xué)信網(wǎng)”進(jìn)行轉(zhuǎn)換、數(shù)據(jù)一致性校驗(yàn)。如何在規(guī)定的時(shí)間內(nèi)將大量的學(xué)生信息進(jìn)行整理、上報(bào)注冊、統(tǒng)計(jì)，同時(shí)又要保證數(shù)據(jù)的準(zhǔn)確成為難點(diǎn)。使用電子表格自帶的一些函數(shù)，通?？梢赃_(dá)到事半功倍的效果。

二、電子表格函數(shù)簡介

電子表格是微軟公司開發(fā)的辦公套裝軟件（Office）的一個(gè)重要組成部分，它可以對(duì)各種數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析、處理和輔助決策，從上世紀(jì)90年代中期開始便廣泛地在辦公、管理、金融等眾多領(lǐng)域進(jìn)行使用。[2]而金山公司開發(fā)的WPS辦公軟件套裝也同樣有電子表格處理軟件，大體功能與Excel類似，市場使用率僅次于微軟Excel。

在電子表格中除了一般性的數(shù)據(jù)統(tǒng)計(jì)和處理功能外，還有大量的內(nèi)置函數(shù)可以進(jìn)行選用，可以進(jìn)行復(fù)雜的數(shù)據(jù)處理，這些函數(shù)既可以單獨(dú)使用，也可嵌套使用。通常情況每個(gè)函數(shù)都通過公式來標(biāo)識(shí)，公式中涉及各類輸入?yún)?shù)，輸入?yún)?shù)可以是數(shù)字、文本、常量、公式、自身或其他函數(shù)，也可以是數(shù)組、單元格引用等，最終通過函數(shù)計(jì)算的方式給用戶返回一個(gè)或多個(gè)結(jié)果值。

在Excel中函數(shù)共分為11類，有數(shù)據(jù)庫函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)、用戶自定義函數(shù)。[2]函數(shù)由函數(shù)名和參數(shù)構(gòu)成，一般格式為函數(shù)名（參數(shù)1，參數(shù)2，…），如基礎(chǔ)性函數(shù)，絕對(duì)值A(chǔ)BS(number)、平均數(shù)AVERAGE(number1,number2,…)、日期DATE(year,month,day)。

本文主要針對(duì)學(xué)籍管理中實(shí)際遇到的操作問題進(jìn)行總結(jié)，重點(diǎn)對(duì)VLOOKUP和SUMPRODUCT兩個(gè)使用頻繁的函數(shù)進(jìn)行說明，對(duì)其應(yīng)用環(huán)境進(jìn)行舉例分析，希望能給高校學(xué)籍管理人員提供一些幫助。

1.VLOOKUP函數(shù)[3]

（1）語法結(jié)構(gòu)：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。

（2）主要功能：

在數(shù)據(jù)表的首列查找指定的數(shù)值，并返回?cái)?shù)據(jù)表當(dāng)前行中指定列中的數(shù)值。

（3）參數(shù)說明：

VLOOKUP函數(shù)中有四個(gè)主要參數(shù)作為輸入。其中：

lookup_value:所需要查找的值。

table_array:需要查找的區(qū)域范圍。

range_lookup:邏輯值（TRUE：近似匹配，F(xiàn)ALSE：精確匹配）。

2.SUMPRODUCT函數(shù)[4]

（1）語法結(jié)構(gòu)：

SUMPRODUCT（Array1,Array2,Array3,...）

SUMPRODUCT((條件1)*(條件2)*(條件3)*…(條件n))

（2）主要功能：

基本功能是返回相應(yīng)的區(qū)域、數(shù)組乘積的和，還可以用于多條件計(jì)數(shù)、求和。

（3）參數(shù)說明：

Array1,Array2,Array2,...為2到30個(gè)數(shù)組，其相應(yīng)元素需要進(jìn)行相乘并求和。

三、應(yīng)用案例解析

1.案例一，“VLOOKUP函數(shù)”在學(xué)籍電子注冊及名單校驗(yàn)中的應(yīng)用

每年9月份，學(xué)校招生辦公室會(huì)提供教務(wù)處一份已錄取學(xué)生名單表格，名單中包含姓名、錄取專業(yè)、身份證號(hào)等信息。教務(wù)處則需要對(duì)已錄取學(xué)生進(jìn)行分班、編排學(xué)號(hào)，并將信息反饋到各個(gè)院系。新生報(bào)到后，各院系提交已報(bào)到的學(xué)生名單給教務(wù)處。教務(wù)處會(huì)將已報(bào)到名單在10月底整理上傳至學(xué)信網(wǎng)?！皩W(xué)信網(wǎng)”要求上傳的數(shù)據(jù)應(yīng)與學(xué)信網(wǎng)上錄取庫中的數(shù)據(jù)一致。以“學(xué)信網(wǎng)”錄取庫為基準(zhǔn)數(shù)據(jù)對(duì)照“學(xué)信網(wǎng)”的上傳數(shù)據(jù)要求根據(jù)我校教務(wù)系統(tǒng)數(shù)據(jù)進(jìn)行缺失數(shù)據(jù)、字段填充。

“學(xué)信網(wǎng)”錄取庫中的數(shù)據(jù)包括KSH考生號(hào)、SYSSDM生源省市代碼、XM姓名、XB性別、CSRQ出生日期、SFZH身份證號(hào)、ZZMM政治面貌、MZ民族、YXDM院校代碼、YXMC院校名稱、ZYDM專業(yè)代碼、ZYMC專業(yè)名稱、CC層次、XZ學(xué)制、XXXS學(xué)習(xí)形式、ZF總分、LQNF錄取年份等等。

“我校教務(wù)系統(tǒng)”導(dǎo)出的已報(bào)到學(xué)生名單中的信息包括學(xué)號(hào)、姓名、曾用名、性別、學(xué)院、專業(yè)名稱、行政班、年級(jí)、出生日期、政治面貌、民族、籍貫、戶口所在地、來源地區(qū)、出生地、入學(xué)日期、畢業(yè)中學(xué)、宿舍號(hào)、電子郵箱地址、身份證號(hào)等等。

“學(xué)信網(wǎng)”上傳數(shù)據(jù)字段有KSH考生號(hào)、XH學(xué)號(hào)、XM姓名、XB性別、CSRQ出生日期、SFZH身份證號(hào)、ZZMM政治面貌、MZ民族、ZYDM專業(yè)代碼、ZYMC專業(yè)名稱、FY分院、XSH系所函、BH班號(hào)、CC層次、XXXS學(xué)習(xí)形式、XZ學(xué)制、RXRQ入學(xué)日期、YJBYRQ預(yù)計(jì)畢業(yè)日期。

對(duì)比“我校教務(wù)系統(tǒng)”的數(shù)據(jù)和“學(xué)信網(wǎng)”錄取庫數(shù)據(jù)發(fā)現(xiàn)存在專業(yè)名稱數(shù)據(jù)填充不完整情況；對(duì)比“學(xué)信網(wǎng)”錄取庫數(shù)據(jù)和“學(xué)信網(wǎng)”上傳數(shù)據(jù)發(fā)現(xiàn)存在學(xué)號(hào)、班級(jí)等字段缺失情況。由于學(xué)生人數(shù)眾多，逐一檢查工作量比較大，而且容易出錯(cuò)，利用Excel中的VLOOKUP函數(shù)可以快速解決上述問題。如圖1、圖2所示，需要將“教務(wù)系統(tǒng)”數(shù)據(jù)中的學(xué)號(hào)、學(xué)院、行政班填充到“學(xué)信網(wǎng)”錄取庫新列。

圖1 “學(xué)信網(wǎng)”錄取庫數(shù)據(jù)

圖2 教務(wù)系統(tǒng)數(shù)據(jù)

求解步驟如下：

（1）在“學(xué)信網(wǎng)”錄取庫數(shù)據(jù)中新建“班號(hào)（bh）”和“學(xué)號(hào)（xh）”兩列，并且將“教務(wù)系統(tǒng)數(shù)據(jù)”拷貝到另一個(gè)表單。

（2）以學(xué)生身份證號(hào)作為唯一條件進(jìn)行查詢，將兩張表進(jìn)行統(tǒng)一。

（3）在表單“錄取庫”中“班號(hào)（bh）”首行單元格中插入“VLOOKUP函數(shù)”，公式為：=VLOOKUP（E2,教務(wù)系統(tǒng)數(shù)據(jù)!A$2:G$2130,7,FALSE），如圖3所示。

圖3 公式及填充結(jié)果

（4）在“學(xué)號(hào)（xh）”首行單元格插入“VLOOKUP函數(shù)”，公式為：=VLOOKUP（E2,教務(wù)系統(tǒng)數(shù)據(jù)!A$2:G$2130,2,FALSE）。

（5）分別向下填充。選擇這兩個(gè)數(shù)據(jù)右下角的“+”并拖動(dòng)至表單末尾，即可完成“班號(hào)（bh）”和“學(xué)號(hào)（xh）”信息填充，如圖3所示。

（6）將整理好的表格按要求字段長度轉(zhuǎn)換成DBF格式，上傳至學(xué)信網(wǎng)便可以完成學(xué)籍電子注冊的工作。

2.案例二，“SUMPRODUCT函數(shù)”在高基報(bào)表學(xué)生數(shù)統(tǒng)計(jì)中的應(yīng)用

每年10月，教育廳會(huì)通知各高校進(jìn)行高基報(bào)表填報(bào)，其中學(xué)生數(shù)統(tǒng)計(jì)占了很大一部分，數(shù)據(jù)量也較大。在進(jìn)行統(tǒng)計(jì)時(shí)，先要把參與統(tǒng)計(jì)的學(xué)生名單整理出來，學(xué)生名單通常包含專業(yè)名稱、年級(jí)、姓名、學(xué)號(hào)、出生日期、民族、生源地等信息。在計(jì)數(shù)統(tǒng)計(jì)過程中，需要用到多條件計(jì)數(shù)，而SUMPRODUCT函數(shù)提供了此功能。如圖4、圖5所示，需要將圖4中的“專業(yè)名稱、年級(jí)”作為條件進(jìn)行查找、計(jì)數(shù)后把結(jié)果填充至圖5中的在校學(xué)生數(shù)相應(yīng)單元格中（本例中一年級(jí)學(xué)生為2013級(jí)）。

圖4 在校生名單

圖5 高基2-1-2表分專業(yè)學(xué)生數(shù)

求解步驟如下：

（1）在“一年級(jí)”單元格（G3）中插入SUMPRODUCT函數(shù)，公式為：=SUMPRODUCT（（在校學(xué)生名單!$A$1:$A$5160=分專業(yè)學(xué)生數(shù)!A3）*（在校學(xué)生名單!$B$1:$B$5160="2013"）），此時(shí)函數(shù)會(huì)進(jìn)行計(jì)算，最終返回“特殊教育專業(yè)”2013級(jí)學(xué)生人數(shù)。

（2）在“二年級(jí)”單元格（H3）中插入SUMPRODUCT函數(shù)，公式為：=SUMPRODUCT（（在校學(xué)生名單!$A$1:$A$5160=分專業(yè)學(xué)生數(shù)!A3）*（在校學(xué)生名單!$B$1:$B$5160="2012"）），計(jì)算不同年級(jí)的學(xué)生人數(shù)，只需要將步驟（1）公式中的“2013”改成“2012”即可，如圖6所示。

圖6 公式及統(tǒng)計(jì)結(jié)果

（3）分別向下填充。選擇數(shù)據(jù)右下角的“+”并拖動(dòng)至表單末尾，即可分別統(tǒng)計(jì)其他專業(yè)在校學(xué)生人數(shù)。注：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，SUMPRODUCT函數(shù)會(huì)返回錯(cuò)誤值“#VALUE!”。

通過以上兩個(gè)電子表格函數(shù)在學(xué)籍管理中的應(yīng)用，可以明顯地體會(huì)到電子表格函數(shù)的優(yōu)勢，不僅提高了工作效率，還提高了數(shù)據(jù)的準(zhǔn)確性，為學(xué)籍管理工作中涉及的數(shù)據(jù)處理工作提供了便利。

四、結(jié)束語

高校學(xué)籍?dāng)?shù)據(jù)庫一般體量都比較大，如果靠人工篩選、核對(duì)，需要花費(fèi)很長時(shí)間，還不能保證數(shù)據(jù)正確。巧妙地利用Excel函數(shù)，可以將復(fù)雜的工作變得簡單，大幅度提高工作的效率，在利用計(jì)算機(jī)處理數(shù)據(jù)時(shí)，前期準(zhǔn)備工作一定要扎實(shí)，尤其要保證基礎(chǔ)數(shù)據(jù)準(zhǔn)確，對(duì)于強(qiáng)大的Excel處理軟件，本文只應(yīng)用了冰山一角，需要不斷地學(xué)習(xí)與探索。

[1]袁敏.基于學(xué)信網(wǎng)平臺(tái)的高職院校學(xué)籍學(xué)歷管理探討與研究[J].中國管理信息化,2014(10):106.

[2]劉祖萍,宋燕福.計(jì)算機(jī)文化及MSO ffice案例教程(Windows7+O ffice2010)[M].北京:中國水利水電出版社,2013.8.

[3]張建成.VLOOKUP函數(shù)在信息鏈接中的應(yīng)用技巧[J].中國教育信息化(基礎(chǔ)教育版),2008(8):31-32.

[4]錢德鳳.SUMPRODUCT函數(shù)在高校崗位設(shè)置統(tǒng)計(jì)中的運(yùn)用[J].鹽城工學(xué)院學(xué)報(bào)(社會(huì)科學(xué)版),2011(1):88-90.