牟雨婷

摘 要：目前，高校在學(xué)籍管理方面通常有兩個(gè)信息化平臺(tái)——“學(xué)信網(wǎng)”和“學(xué)籍管理系統(tǒng)”。作為學(xué)籍管理人員，報(bào)表通常使用電子表格（Excel）進(jìn)行統(tǒng)計(jì)、分析和上傳。如何在較短的時(shí)間內(nèi)將大量的學(xué)生信息進(jìn)行處理，又要保證數(shù)據(jù)一致成為難點(diǎn)。本文主要針對(duì)學(xué)籍管理中實(shí)際遇到的操作問(wèn)題進(jìn)行描述，重點(diǎn)對(duì)電子表格中自帶的VLOOKUP和SUMPRODUCT兩個(gè)使用頻繁的函數(shù)進(jìn)行說(shuō)明，對(duì)其應(yīng)用環(huán)境進(jìn)行舉例分析，希望能給高校學(xué)籍管理人員提供一些幫助。

關(guān)鍵詞：電子表格函數(shù)；學(xué)籍管理；VLOOKUP；SUMPRODUCT

中圖分類(lèi)號(hào)：TP315 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：1673-8454（2015）17-0050-03

一、引言

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

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

各高校在信息化建設(shè)過(guò)程中通常購(gòu)買(mǎi)或者聯(lián)合開(kāi)發(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)注冊(cè)、統(tǒng)計(jì)，同時(shí)又要保證數(shù)據(jù)的準(zhǔn)確成為難點(diǎn)。使用電子表格自帶的一些函數(shù)，通?？梢赃_(dá)到事半功倍的效果。

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

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

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

在Excel中函數(shù)共分為11類(lèi)，有數(shù)據(jù)庫(kù)函數(shù)、日期與時(shí)間函數(shù)、工程函數(shù)、財(cái)務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢(xún)和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、文本函數(shù)、用戶(hù)自定義函數(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í)際遇到的操作問(wèn)題進(jìn)行總結(jié)，重點(diǎn)對(duì)VLOOKUP和SUMPRODUCT兩個(gè)使用頻繁的函數(shù)進(jìn)行說(shuō)明，對(duì)其應(yīng)用環(huán)境進(jìn)行舉例分析，希望能給高校學(xué)籍管理人員提供一些幫助。

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

（1）語(yǔ)法結(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ù)說(shuō)明：

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

lookup_value： 所需要查找的值。

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

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

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

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

SUMPRODUCT（Array1，Array2，Array3， ...）

SUMPRODUCT（（條件1）*（條件2）*（條件3）* …（條件n））

（2）主要功能：

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

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

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

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

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

每年9月份，學(xué)校招生辦公室會(huì)提供教務(wù)處一份已錄取學(xué)生名單表格，名單中包含姓名、錄取專(zhuān)業(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)上錄取庫(kù)中的數(shù)據(jù)一致。以“學(xué)信網(wǎng)”錄取庫(kù)為基準(zhǔn)數(shù)據(jù)對(duì)照“學(xué)信網(wǎng)”的上傳數(shù)據(jù)要求根據(jù)我校教務(wù)系統(tǒng)數(shù)據(jù)進(jìn)行缺失數(shù)據(jù)、字段填充。

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

“我校教務(wù)系統(tǒng)”導(dǎo)出的已報(bào)到學(xué)生名單中的信息包括學(xué)號(hào)、姓名、曾用名、性別、學(xué)院、專(zhuān)業(yè)名稱(chēng)、行政班、年級(jí)、出生日期、政治面貌、民族、籍貫、戶(hù)口所在地、來(lái)源地區(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專(zhuān)業(yè)代碼、ZYMC專(zhuān)業(yè)名稱(chēng)、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)”錄取庫(kù)數(shù)據(jù)發(fā)現(xiàn)存在專(zhuān)業(yè)名稱(chēng)數(shù)據(jù)填充不完整情況；對(duì)比“學(xué)信網(wǎng)”錄取庫(kù)數(shù)據(jù)和“學(xué)信網(wǎng)”上傳數(shù)據(jù)發(fā)現(xiàn)存在學(xué)號(hào)、班級(jí)等字段缺失情況。由于學(xué)生人數(shù)眾多，逐一檢查工作量比較大，而且容易出錯(cuò)，利用Excel中的VLOOKUP函數(shù)可以快速解決上述問(wèn)題。如圖1、圖2所示，需要將“教務(wù)系統(tǒng)”數(shù)據(jù)中的學(xué)號(hào)、學(xué)院、行政班填充到“學(xué)信網(wǎng)”錄取庫(kù)新列。

求解步驟如下：

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

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

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

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

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

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

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é)生名單整理出來(lái)，學(xué)生名單通常包含專(zhuān)業(yè)名稱(chēng)、年級(jí)、姓名、學(xué)號(hào)、出生日期、民族、生源地等信息。在計(jì)數(shù)統(tǒng)計(jì)過(guò)程中，需要用到多條件計(jì)數(shù)，而SUMPRODUCT函數(shù)提供了此功能。如圖4、圖5所示，需要將圖4中的“專(zhuān)業(yè)名稱(chēng)、年級(jí)”作為條件進(jìn)行查找、計(jì)數(shù)后把結(jié)果填充至圖5中的在校學(xué)生數(shù)相應(yīng)單元格中（本例中一年級(jí)學(xué)生為2013級(jí)）。

求解步驟如下：

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

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

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

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

四、結(jié)束語(yǔ)

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

參考文獻(xiàn)：

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

[2]劉祖萍，宋燕福.計(jì)算機(jī)文化及 MS Office 案例教程（Windows7+Office2010）[M].北京：中國(guó)水利水電出版社，2013.8.

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

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

（編輯：王天鵬）