淺析EXCEL函數(shù)在學(xué)生信息管理中的應(yīng)用

潘皎

摘要：Excel是Office辦公軟件中的重要一員，被廣泛應(yīng)用于各個(gè)行業(yè)，Excel電子表格以操作簡單、方便實(shí)用等特點(diǎn)在學(xué)生信息管理中亦被青睞有加，靈活運(yùn)用Excel函數(shù)可以大大提高工作效率，達(dá)到事半功倍的效果。以學(xué)生信息管理中常見問題為例，介紹Excel中有關(guān)函數(shù)的具體應(yīng)用。

關(guān)鍵詞：Excel;函數(shù);學(xué)生信息管理

中圖分類號：G434文獻(xiàn)標(biāo)志碼：A文章編號：1001?7836（2019）10?0148?03

在學(xué)生信息管理工作中，我們經(jīng)常會存儲和處理大量的學(xué)生信息，借助Excel電子表格軟件管理這些信息既方便又快捷。目前越來越多的人能夠熟練地使用Excel的查詢、排序、數(shù)據(jù)篩選等功能，亦可以使用Excel中的“∑”自動求和函數(shù)進(jìn)行簡單計(jì)算，而對Excel復(fù)雜一些的函數(shù)卻不甚了解。本文結(jié)合學(xué)生信息管理工作中的具體案例，為大家介紹Excel函數(shù)在學(xué)生信息管理中的應(yīng)用。

一、從身份證號中提取信息

身份證號碼由18位數(shù)字組成，其中包含了性別和出生日期等信息，第7—14位數(shù)字表示公民的出生年、月、日，第17位數(shù)字表示公民的性別，單數(shù)為男性，雙數(shù)為女性。我們可以通過有關(guān)函數(shù)，將出生日期和性別等信息提取出來。

1.提取出生日期

通過MID函數(shù)可以從身份證號碼中提取出生日期。MID 函數(shù)的功能是從文本字符串中指定的位置開始提取指定長度的字符串。MID函數(shù)的語法結(jié)構(gòu)為MID（Text， Start_num， Num_chars），其中Text是需要查找的字符串文本，Start_num是準(zhǔn)備提取的第一個(gè)字符的位置，Num_chars指定所要提取的字符串長度。在B2單元格中插入函數(shù)“=MID（A2，7，8）”，確定后出生日期就提取出來了，如圖1所示。MID（A2，7，8）表示將A2單元格中的身份證號碼，從第7位開始截取8位，從而提取出出生日期。將鼠標(biāo)移動到B2單元格右下角，向下拖動填充柄完成其他出生日期的提取。

2.提取性別

在C2單元格中插入函數(shù)“=IF（MOD（MID（A2，17，1），2）=0，"女"，"男"）”，確定后性別就提取出來了。其中函數(shù)MID（A2，17，1）提取的是身份證號的第17位數(shù)字，MOD是求余函數(shù)，函數(shù)MOD（MID（A2，17，1）求取身份證號的第17位數(shù)字除以2的余數(shù);IF函數(shù)是邏輯函數(shù)用來判斷是身份證號第17位數(shù)字除以2余數(shù)是否為0，是則返回“女”，不是則返回“男”，如圖2所示。

3.計(jì)算年齡

在D2單元格中輸入公式“=YEAR（TODAY（））-MID（A2，7，4）”，確定后年齡就計(jì)算出來了，如圖3所示。其中函數(shù)TODAY（）返回當(dāng)前日期，函數(shù)YEAR（TODAY（））求出當(dāng)前日期對應(yīng)的年份，函數(shù)MID（A2，7，4）從身份證號中提取出生年份，當(dāng)前年份減去出生年份就可以計(jì)算出年齡。

二、長數(shù)字編碼的自動填充

有許多編號例如畢業(yè)證書編號通常號碼位數(shù)過多（超過11位），對于這種長數(shù)字編碼我們無法以序列方式自動填充。對于這種情況可以有以下兩種方式解決。

1.將長數(shù)字編碼分成兩部分

如“50311520190100001”將其分成“503115201901”和“00001”兩部分，后面的部分可以使用自動填充，然后再將兩部分連接起來，連接可以使用&連接符或者字符串連接函數(shù)CONCATENATE。

使用&連接符。通常將2個(gè)字符串連接在一起，最方便的就是使用&連接符。在D1單元格輸入公式“=B$1&C1”，如圖4所示，之后可以使用自動填充序列功能。

使用字符串連接函數(shù)CONCATENATE。在D1單元格輸入公式“=CONCATENATE（B$1，C1）”，之后可以使用自動填充序列功能。

2.可將前面重復(fù)出現(xiàn)的數(shù)字設(shè)置為固定的附加字符

選定D1單元格，單擊右鍵在快捷菜單中選定“設(shè)置單元格格式”，打開“設(shè)置單元格格式”對話框，在“數(shù)字”標(biāo)簽的分類里選擇“自定義”，右邊的“類型”框里用英文狀態(tài)的雙引號框起重復(fù)出現(xiàn)的數(shù)字，在后面補(bǔ)充n個(gè)“0”，作為n 個(gè)變動數(shù)字的位置：例如：（″503115201901″00000），設(shè)置好之后在單元格里只輸入最后的n位數(shù)如“00001”，單元格里會出現(xiàn)你要的完整數(shù)字“50311520190100001”，之后可以使用自動填充序列功能。

三、VLOOKUP函數(shù)在學(xué)生信息管理中的應(yīng)用

VLOOKUP函數(shù)是Excel中非常有用的一個(gè)查詢函數(shù)，主要功能是在表格中某指定區(qū)域的首列查找指定的數(shù)值，并返回該數(shù)值所在行中指定列處的數(shù)值。

1.快速查詢

在學(xué)生信息管理中經(jīng)常會在總的名單中查詢學(xué)生的信息，如果查詢的數(shù)量較多，通過一一查找，就會浪費(fèi)大量時(shí)間。而VLOOKUP函數(shù)可以很好地解決這個(gè)問題。

例：在表1中提供了要查詢的n個(gè)學(xué)生的身份證號，如圖5所示，表2是總名單，包含學(xué)號、姓名、身份證號、專業(yè)等信息，如圖6所示，我們要在表2中找到這n個(gè)學(xué)生并做出標(biāo)記，然后進(jìn)行排序，最后通過復(fù)制粘貼將它們提取出來。

VLOOKUP函數(shù)的語法結(jié)構(gòu)為VLOOKUP（lookup_value， table_array， col_index_num， [range_lookup]）。中文表述就是VLOOKUP（查找值，查找范圍，查找列數(shù)，精確匹配或者近似匹配）。

（1）在J2單元格中插入函數(shù)“=VLOOKUP（E2，表1！A：A，1，0）”，確定后J2單元格的內(nèi)容變成了“231004199410011430”，如圖7所示，用拖放的方式填充到表的最后一行，如果找到，則返回對應(yīng)的身份證號，如果找不到，則返回錯誤值“#N/A”。

（2）以“標(biāo)記”列為關(guān)鍵字進(jìn)行升序排序。

（3）排序后將有身份證號的行復(fù)制粘貼到新表中即可。

2.信息提取

在學(xué)生信息管理工作中，VLOOKUP函數(shù)最常用的實(shí)例就是可以關(guān)聯(lián)一個(gè)或多個(gè)數(shù)據(jù)表，對存在不同工作簿或工作表中的信息進(jìn)行整合。

例1：圖8所示表中有學(xué)號、姓名和身份證號字段，我們要根據(jù)已知學(xué)號信息到圖9 “學(xué)生基本信息匯總表”中提取學(xué)號對應(yīng)的姓名和身份證號信息，可以通過VLOOKUP函數(shù)進(jìn)行提取。

首先，在圖8所示表的B2單元格中插入函數(shù)“=VLOOKUP（A2，學(xué)生基本信息匯總表！A：B，2，0）” ，確定后B2單元格的內(nèi)容變成了“林元濤”，然后用自動填充柄下拉填充到表的最后一行;同理，B3單元格中插入函數(shù)“=VLOOKUP（A2，學(xué)生基本信息匯總表！A：E，5，0）”確定后B3單元格的內(nèi)容變成了“330327199408100057”，然后用自動填充柄下拉填充到表的最后一行。

例2：多表整合，學(xué)生畢業(yè)前要為每名學(xué)生打印成績單，成績單中要求記錄學(xué)生的基本信息和成績情況，如圖10所示，圖8 “學(xué)生基本信息匯總表”中包含所有學(xué)生的基礎(chǔ)信息，圖11 “學(xué)生成績匯總表”中包含所有學(xué)生的成績情況，成績單中的各項(xiàng)信息可以使用VLOOKUP函數(shù)分別從上述的兩張表中提取。

在成績單所在表的B2單元格中輸入學(xué)號，然后從“學(xué)生基本信息匯總表”中一一提取姓名、專業(yè)、層次、學(xué)制、學(xué)習(xí)形式、入學(xué)時(shí)間及畢業(yè)設(shè)計(jì)等學(xué)生基本信息;從“學(xué)生成績匯總表”中一一提取學(xué)生的各門課程的成績。

（1）提取學(xué)生基本信息

在成績單所在表的B2單元格中輸入學(xué)號“20190301010102”，在D4單元格中插入函數(shù)“=VLOOKUP（B2，學(xué)生基本信息匯總表！A：M，2，0）”，確定后得到學(xué)生姓名“林元濤”;在F4單元格中插入函數(shù)“=VLOOKUP（B2，學(xué)生基本信息匯總表！A：M，8，0）”，確定后得到學(xué)生專業(yè)“學(xué)前教育”;可以發(fā)現(xiàn)兩次輸入的VLOOKUP函數(shù)中的參數(shù)僅第三項(xiàng)不同，即“查找列數(shù)”不同，姓名對應(yīng)所選區(qū)域的第二列，專業(yè)對應(yīng)所選區(qū)域的第八列，以此類推，“層次”列在第九列，在H2單元格中插入函數(shù)“=VLOOKUP（B2，學(xué)生基本信息匯總表！A：M，9，0）”，確定后得到學(xué)生層次“專升本”，其他基本信息也使用此法，在對應(yīng)單元格中一一插入對應(yīng)項(xiàng)的VLOOKUP函數(shù)即可。

（2）提取學(xué)生的各門課程成績

在C5單元格中插入函數(shù)“=VLOOKUP（B2，學(xué)生成績信息匯總表！A：Q，3，0）”，確定后得到“中特社會主義理論體系”課程成績“79”;在C6單元格中插入函數(shù)“=VLOOKUP（B2，學(xué)生成績信息匯總表！A：Q，4，0）”，確定后得到“現(xiàn)代教育理論”課程成績“78”;我們發(fā)現(xiàn)，兩次輸入的VLOOKUP函數(shù)中的參數(shù)還是第三項(xiàng)不同，“中特社會主義理論體系”對應(yīng)所選區(qū)域的第三列，“現(xiàn)代教育理論”對應(yīng)所選區(qū)域的第四列，以此類推，其他課程成績也使用此法，在對應(yīng)單元格中一一插入對應(yīng)項(xiàng)的VLOOKUP函數(shù)即可。

四、INDEX+MATCH函數(shù)組合在學(xué)生信息管理中的應(yīng)用我們剛剛介紹過的VLOOKUP函數(shù)是非常高效的一個(gè)查詢函數(shù)，但是遇到反向查找、雙向查找等比較復(fù)雜的操作時(shí)VLOOKUP函數(shù)也顯出明顯的劣勢，此時(shí)我們就可以使用INDEX+MATCH函數(shù)組合。

INDEX函數(shù)的功能之一是返回指定行、列交叉處單元格的值。MATCH函數(shù)是匹配函數(shù)，功能是返回指定數(shù)值在指定單元格區(qū)域中的位置，這兩個(gè)函數(shù)組合使用功能強(qiáng)大。

MATCH函數(shù)的語法結(jié)構(gòu)為MATCH（lookup_value， lookup_array， match_type）。中文表述就是MATCH（查找值，查找區(qū)域，查找模式）。

INDEX函數(shù)的語法結(jié)構(gòu)為INDEX（array，row_num，column_num）。中文表述就是INDEX（單元格區(qū)域，行號，列號）。

例1：我們需要根據(jù)身份證號查找對應(yīng)的姓名，如圖12所示，圖8“學(xué)生基本信息匯總表”中包含姓名和身份證號等信息，我們通過INDEX+MATCH函數(shù)組合進(jìn)行提取。

這個(gè)問題使用VLOOKUP函數(shù)也可以解決，但是必須將源數(shù)據(jù)區(qū)域中身份證號列調(diào)到姓名列的前面。如果我們使用INDEX+MATCH函數(shù)組合進(jìn)行操作，則更加方便。

在B2單元格中插入函數(shù)“=INDEX（學(xué)生基本信息匯總表！B：B，MATCH（A2，學(xué)生基本信息匯總表！E：E，0））”，確定后即可看到查找結(jié)果為“林元濤”。其中函數(shù)MATCH（A2，學(xué)生基本信息匯總表！E：E，0）是從學(xué)生基本信息匯總表的身份證號列中查找“330327199408100057”的位置，之后再使用Index函數(shù)根據(jù)查找到的位置從姓名列取值。

例2：在圖9的成績單中，我們使用VLOOKUP函數(shù)實(shí)現(xiàn)了從圖10“學(xué)生成績匯總表”中提取學(xué)生的成績，這項(xiàng)操作使用INDEX+MATCH函數(shù)組合則更加簡單。

這個(gè)問題我們使用VLOOKUP函數(shù)可以解決，但是這種方法存在一些問題，如果學(xué)生成績信息匯總表的結(jié)構(gòu)發(fā)生變化，我們的VLOOKUP函數(shù)中的參數(shù)可能需要改動，這樣不但加大了工作量而且容易出錯，如果使用INDEX+MATCH函數(shù)組合進(jìn)行操作，表結(jié)構(gòu)發(fā)生變化對我們的參數(shù)影響不大，這樣會大大提高效率。

在成績單所在表的B2單元格中輸入學(xué)號“20190301010102”，在C5單元格中插入函數(shù)“=INDEX（學(xué)生成績信息匯總表！A：Q，MATCH（$B$2，學(xué)生成績信息匯總表！A：A，0），MATCH（A5，學(xué)生成績信息匯總表！$1：$1，0））”，確認(rèn)后得到“中特社會主義理論體系”課程成績“79”。其中函數(shù)MATCH（$B$2，學(xué)生成績信息匯總表！A：A，0）是從學(xué)生成績信息匯總表的學(xué)號列中查找“20190301010102”的行號，函數(shù)MATCH（A5，學(xué)生成績信息匯總表！$1：$1，0）是從學(xué)生成績信息匯總表的第一行中查找A5單元格中“中特社會主義理論體系”的列標(biāo)，之后再使用INDEX函數(shù)返回指定行、列交叉處單元格中的成績。

五、總結(jié)

以上是筆者在學(xué)生信息管理工作中遇到的實(shí)際問題，使用Excel函數(shù)不但能夠巧妙地解決問題，而且事半功倍，不易出錯。Excel軟件是一款功能強(qiáng)大、操作簡單的辦公軟件，熟練運(yùn)用Excel的函數(shù)功能可以讓我們的數(shù)據(jù)處理工作變得更加輕松便捷。

參考文獻(xiàn)：

[1][美]沃肯巴赫.中文版Excel2003寶典[M].北京：電子工業(yè)出版社，2004.