浅析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.