柳雪環(huán)

（登封市中等專業(yè)學(xué)校 河南省登封市 452477）

Excel 是辦公自動(dòng)化軟件中重要的一項(xiàng)工具。很多行業(yè)利用Excel 進(jìn)行資料和數(shù)據(jù)的整理。目前，很多人對(duì)Excel 函數(shù)應(yīng)用始終停留在求和、求平均值等比較簡(jiǎn)單的用途上，對(duì)Excel 自帶的數(shù)據(jù)自動(dòng)處理和計(jì)算功能還不夠了解。本文通過(guò)Excel在學(xué)生信息管理的應(yīng)用具體舉例，表明Excel 函數(shù)的強(qiáng)大功能，以便各位教育者可以在日常工作處理中，利用其功能增加工作效率，減少不必要的人為失誤。

1 Excel基本概況

Excel 是Microsoft office system 中的電子表格程序，具備創(chuàng)建、設(shè)置等功能，以便幫助做出更加明智的決策和分析。Excel 還具備跟蹤數(shù)據(jù)功能，生成數(shù)據(jù)分析模型，利用公式對(duì)數(shù)據(jù)進(jìn)行計(jì)算，以多種方式將數(shù)據(jù)顯示出來(lái)?？傊?，Excel 使用，可以使數(shù)據(jù)和辦公處理更加方便、快捷。Excel 一般用于會(huì)計(jì)、預(yù)算、銷售、報(bào)表、計(jì)劃、使用日歷等，但是目前越來(lái)越多的運(yùn)用到辦公室領(lǐng)域。學(xué)校利用Excel 軟件進(jìn)行學(xué)生信息管理，分析學(xué)生的數(shù)據(jù)，幫助教師們提高教學(xué)效率和教學(xué)決策。

2 Excel應(yīng)用于學(xué)生基本信息管理中

2.1 解讀身份證號(hào)碼獲取學(xué)生信息

新生入學(xué)時(shí)，其身份證號(hào)碼就是一個(gè)非常重要的信息數(shù)據(jù)。眾所周知，身份證號(hào)碼是18 位特征組合碼，由數(shù)字碼和校驗(yàn)碼組成，排列順序前六位是地址碼，表示學(xué)生戶口所在地（市、級(jí)、縣）和行政區(qū)代碼，之后的八位數(shù)字是出生日期，表示學(xué)生出生的年、月、日，后三位數(shù)字序碼是地址碼所標(biāo)識(shí)的區(qū)域范圍碼，同年、月、日出生的人員編定不同的序列號(hào)，其中第17 位數(shù)字奇數(shù)是男生，偶數(shù)是女生。根據(jù)身份證的編碼規(guī)則，利用Excel 軟件分別獲取新生的戶籍、出生年月、性別等信息，又可以根據(jù)這些校驗(yàn)碼判斷身份證號(hào)碼輸入是否出現(xiàn)錯(cuò)誤。這樣，不僅使查詢更加的簡(jiǎn)便和快捷，而且準(zhǔn)確率高。核對(duì)時(shí)只需要對(duì)身份證號(hào)碼檢查即可，使辦公室管理工作的效率大大提高。表1 為學(xué)生基本信息表操作示例。

其中，身份證號(hào)碼獲取出生日期可以利用mid（）函數(shù)完成，這個(gè)函數(shù)可以從文字的指定位置開(kāi)始提取相應(yīng)字符串。其公式為：CONCATENATE(MID(M2,7,8),"年",MID(M2,11,2),"月",MID(M2,13,2),"日")[1]。從身份證的七位數(shù)字開(kāi)始截取8 位出生日期數(shù)字，將鼠標(biāo)移動(dòng)到填充處，下拉完成其他各個(gè)日期的提取工作。

獲取性別的公式為：IF(MOD(MID(M2,17,1),2)="0","女","男")。

計(jì)算學(xué)生年齡的公式為：=YEAR(NOW( ))- MID(F2,7,4)，這種計(jì)算方法可以看作從目前的年份減去身份證出生日期的年份所得到的數(shù)值，通過(guò)這樣的公式拉動(dòng)鼠標(biāo)至填充格，完成其他成員的年齡。

統(tǒng)計(jì)男女生人數(shù)可以通過(guò)COUNTIF（）函數(shù)進(jìn)行，公式為：：COUNTIF(range，criteria)[2]。

圖1：利用Excel 條件格式查找錯(cuò)誤

統(tǒng)計(jì)男生人數(shù)可以在D9中輸入公式：=COUNTIF(C2:C7,"男")；統(tǒng)計(jì)女生時(shí)可以在D10 單元格內(nèi)輸公式：=COUNTIF(C2:C7,"女")。

統(tǒng)計(jì)團(tuán)員、黨員人數(shù)可分別在在單元格D11、D12 中輸入公式：=COUNTIF(E2:E7,"團(tuán)員")；=COUNTIF(E2:E7,"中共黨員")。

統(tǒng)計(jì)少數(shù)民族學(xué)生個(gè)數(shù)可在D13 單元格內(nèi)輸入公式：=COUNTIF(D2:D7,"<>漢族")，其中，<>表示不等于[3]。

根據(jù)身份證號(hào)碼的最后一位，可以運(yùn)用公式算出正確的校驗(yàn)碼。然后用最后一位數(shù)進(jìn)行對(duì)照，檢查身份證號(hào)碼是否合格。具體操作方法如下：

輸入公式：=(RIGHT(M1,1)=MID("10X98765432",MOD(SUM(MID(M1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1))=FALSE[4]，并設(shè)置單元格，若身份證號(hào)碼不符合時(shí)，其所在的單元格內(nèi)字體會(huì)加粗，也可設(shè)置改變字體顏色，這可以根據(jù)制表者的個(gè)人習(xí)慣和愛(ài)好進(jìn)行設(shè)置，利用條件驗(yàn)證住址和戶籍是否一致，便于在錄入的過(guò)程中及時(shí)發(fā)現(xiàn)信息錯(cuò)誤，增加工作效率和正確率。如圖1所示。

2.2 Excel應(yīng)用于學(xué)生成績(jī)管理

教師在進(jìn)行學(xué)生信息管理工作時(shí)，經(jīng)常要對(duì)學(xué)生成績(jī)進(jìn)行數(shù)據(jù)處理。除了運(yùn)用簡(jiǎn)單的求和、求平均值外，教師們也要了解更多的公式，幫助處理更多的信息，有效地提高工作效率。學(xué)生成績(jī)示例表如表2所示。

排名次序可利用RANK（）函數(shù)進(jìn)行，他可以在一系列數(shù)據(jù)中對(duì)數(shù)值的大小進(jìn)行比對(duì)，計(jì)算出排名的次序，其公式為：RANK(number,ref,order)[5]。例如，選擇G2 單元格，輸入公式：=RANK(F2,SFS2:SFS7,0)，然后按下回車鍵，張梁同學(xué)的名次就會(huì)顯現(xiàn)在G2 單元格中，利用填充柄下拉，就可以將后面同學(xué)的成績(jī)名次排列好。公式中F2：F7 表示全體同學(xué)。

通過(guò)COUNTIF（）函數(shù)可以將各個(gè)分?jǐn)?shù)段的學(xué)生人數(shù)統(tǒng)計(jì)出來(lái)，以英語(yǔ)科目的成績(jī)?yōu)槔?，選中下面的空白表格（2 個(gè)）輸入公式：=COUNTIF(E2:E7,">=60")和=COUNTIF(E2:E7,"<60")就可以有效的統(tǒng)計(jì)在E 列中大于60 分和小于60 分的人，以此類推可以計(jì)算出60-70 分的學(xué)生人數(shù)、70-80 分的學(xué)生人數(shù)、80-90 分的學(xué)生人數(shù)等等。例如，算出數(shù)學(xué)科目80-90 分的人數(shù)，就可以套用COUNFIT（）函數(shù)公式，公式為：=COUNTIF(D2:D7,">=80")和=COUNTIF(D2:D7,"<90")。同理可得出不及格的學(xué)生人數(shù)，可以在H 列輸入公式：=COUNTIF(C2:E2,"<60")，利用鼠標(biāo)移動(dòng)到填充柄下拉完成整個(gè)列的填充和統(tǒng)計(jì)。

表1：學(xué)生基本信息示例表

表2：學(xué)生成績(jī)示例表

通過(guò)“格式”菜單下的“條件格式”運(yùn)用，也可以將學(xué)生的成績(jī)分別類的顯示。例如，可以設(shè)置條件A“>=90”，設(shè)置顏色為藍(lán)色，或設(shè)置字體變換等類型；設(shè)置條件B“<60”，顏色為紅色，將學(xué)生的成績(jī)分類，優(yōu)秀的成績(jī)和不及格的成績(jī)都會(huì)有明顯的顯示，有助于教師們進(jìn)行成績(jī)的查詢和比對(duì)分析。

3 Excel運(yùn)用到日常排序功能

Excel 還有助于實(shí)現(xiàn)自定義數(shù)據(jù)的快速排序。

（1）要輸入特定要求的序列，選擇“工具”菜單中的“選項(xiàng)”菜單，在對(duì)話框中填寫自己想要定義的詞語(yǔ)進(jìn)序列順序。例如，教授、副教授、講師、助教，然后再在“工具”菜單中“排序”菜單內(nèi)選擇“選項(xiàng)”按鈕，打開(kāi)自定義排序的對(duì)話框，選擇剛剛設(shè)置好的序列，然后點(diǎn)擊確定按鈕完成數(shù)據(jù)排序功能。

（2）此方法也可用于學(xué)生管理，進(jìn)行學(xué)生區(qū)域、成績(jī)、職務(wù)、民族、年齡等方面的自定義排序，使查找更加便捷，增加教師們的工作效率。在學(xué)生信息管理的實(shí)際過(guò)程中，教師需要對(duì)學(xué)生的基本信息內(nèi)容加以掌握，以保證學(xué)生管理工作的高質(zhì)量開(kāi)展。而通過(guò)Excel 應(yīng)用，就能夠在Excel 表格中對(duì)學(xué)生基本信息進(jìn)行匯總，在需要時(shí)，能夠通過(guò)快速檢索的方式，調(diào)取需要的學(xué)生信息，全面提升學(xué)生管理的效率。并且，通過(guò)Excel 對(duì)學(xué)生基本信息進(jìn)行管理時(shí)，可以通過(guò)不同的表頭制作，對(duì)學(xué)生信息加以區(qū)分。

（3）利用Excel 進(jìn)行學(xué)生學(xué)籍照片管理。在學(xué)生學(xué)籍照片管理實(shí)踐中，Excel 的應(yīng)用能夠有效提升學(xué)生學(xué)籍照片管理工作的質(zhì)量，避免傳統(tǒng)管理中學(xué)生照片遺失情況的發(fā)生。

4 文本快速轉(zhuǎn)化數(shù)值類型

由于學(xué)生的各項(xiàng)成績(jī)屬于數(shù)字信息，可以直接進(jìn)行數(shù)字之間的計(jì)算和轉(zhuǎn)化，如果在日常教師操作中，將數(shù)據(jù)類型轉(zhuǎn)化為文字類型，那么在失誤操作后，學(xué)生自身的成績(jī)有可能被全部轉(zhuǎn)化為文本類型，其具體Excel 文檔會(huì)在單元格中出現(xiàn)綠色三角標(biāo)識(shí)。由于數(shù)據(jù)已經(jīng)被轉(zhuǎn)化為文本，因此該單元格內(nèi)的數(shù)據(jù)無(wú)法進(jìn)行正常運(yùn)算，從而為后期的數(shù)據(jù)計(jì)算和編寫帶來(lái)了巨大的隱患和阻礙。此時(shí)教師需要針對(duì)此類文本問(wèn)題進(jìn)行相應(yīng)解決處理。首先，教師另外選擇空白無(wú)文字公式的單元格，在空白的單元格中輸入數(shù)字“1”，并且加以復(fù)制，隨后選擇已經(jīng)轉(zhuǎn)化為文本的區(qū)域，單擊鼠標(biāo)右鍵，在選擇欄中選擇“選擇性粘貼”最后在單元格內(nèi)部選擇運(yùn)算公式乘法，最終確定，即可完成文本轉(zhuǎn)化數(shù)據(jù)的相關(guān)運(yùn)算。

5 總結(jié)

Excel 具有強(qiáng)大的數(shù)據(jù)功能。利用Excel 函數(shù)對(duì)學(xué)生信息進(jìn)行管理，對(duì)于具有計(jì)算機(jī)編程基礎(chǔ)的教師來(lái)說(shuō)，可以通過(guò)簡(jiǎn)化操作驗(yàn)證很多錯(cuò)誤。但對(duì)于更多的教師而言，在不進(jìn)行編程的情況下，利用Excel 表格中的函數(shù)進(jìn)行工作更加簡(jiǎn)單和方便。