淺析EXCEL函數在學生信息管理中的應用

潘皎

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

關鍵詞：Excel;函數;學生信息管理

中圖分類號：G434文獻標志碼：A文章編號：1001?7836（2019）10?0148?03

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

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

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

1.提取出生日期

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

2.提取性別

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

3.計算年齡

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

二、長數字編碼的自動填充

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

1.將長數字編碼分成兩部分

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

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

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

2.可將前面重復出現的數字設置為固定的附加字符

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

三、VLOOKUP函數在學生信息管理中的應用

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

1.快速查詢

在學生信息管理中經常會在總的名單中查詢學生的信息，如果查詢的數量較多，通過一一查找，就會浪費大量時間。而VLOOKUP函數可以很好地解決這個問題。

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

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

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

（2）以“標記”列為關鍵字進行升序排序。

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

2.信息提取

在學生信息管理工作中，VLOOKUP函數最常用的實例就是可以關聯一個或多個數據表，對存在不同工作簿或工作表中的信息進行整合。

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

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

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

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

（1）提取學生基本信息

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

（2）提取學生的各門課程成績

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

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

INDEX函數的功能之一是返回指定行、列交叉處單元格的值。MATCH函數是匹配函數，功能是返回指定數值在指定單元格區域中的位置，這兩個函數組合使用功能強大。

MATCH函數的語法結構為MATCH（lookup_value， lookup_array， match_type）。中文表述就是MATCH（查找值，查找區域，查找模式）。

INDEX函數的語法結構為INDEX（array，row_num，column_num）。中文表述就是INDEX（單元格區域，行號，列號）。

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

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

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

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

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

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

五、總結

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

參考文獻：

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