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

牟雨婷

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

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

中圖分類號：TP315 文獻標志碼：A 文章編號：1673-8454（2015）17-0050-03

一、引言

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

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

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

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

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

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

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

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

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

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

（1）語法結構：VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）。

（2）主要功能：

在數(shù)據(jù)表的首列查找指定的數(shù)值，并返回數(shù)據(jù)表當前行中指定列中的數(shù)值。

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

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

lookup_value： 所需要查找的值。

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

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

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

（1）語法結構：

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

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

（2）主要功能：

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

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

Array1，Array2，Array2，...為2到30個數(shù)組，其相應元素需要進行相乘并求和。

三、應用案例解析

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

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

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

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

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

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

求解步驟如下：

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

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

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

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

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

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

2.案例二，“SUMPRODUCT函數(shù)”在高基報表學生數(shù)統(tǒng)計中的應用

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

求解步驟如下：

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

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

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

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

四、結束語

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

參考文獻：

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

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

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

[4]錢德鳳.SUMPRODUCT函數(shù)在高校崗位設置統(tǒng)計中的運用[J].鹽城工學院學報（社會科學版）， 2011（1）：88-90.

（編輯：王天鵬）