Excel在学生信息管理中的应用一例

【前言】Excel在学生信息管理中的应用一例由文秘帮小编整理而成，但愿对你的学习工作带来帮助。假定C列为性别、D列为出生年月、F列为政治面貌、L列为县区、M列为身份证号码、N列为家庭住址，以下从相关基本操作、数据有效性、获取身份证相关信息等方面进行介绍。 一、相关基本操作 1.数据填充。 在Excel中，填充有规律数据（如重复数据、等差序列、等比序列等），...

中图分类号：TP391.13 文献表示码：A 文章编号：1003-2738（2012）05-0273-01

摘要：excel电子表格功能强大，且易学易用，得到广泛的应用。在学生管理中，对学生基本信息进行处理，Excel具有很强的优势。恰当应用Excel电子表格，可以极大地提高管理效率，并尽可能地减少错误。

关键词：Excel；学生；管理；应用

Excel作为一款常用软件，简化了步骤、提高了效率、降低了强度，在学生成绩管理、信息管理等方面，扮演着重要角色。本文以学生信息管理为例，初步探讨Excel的强大功能。

在学生信息表中，主要涉及姓名、性别、出生日期、政治面貌、民族、户籍、家庭住址、专业班级、学号、联系方式等方面。其中，政治面貌、民族、专业班级可利用数据有效性进行限定，以便选择；性别、出生日期、户籍等可从身份证号码中获取，也可校验身份证号码的合格性。

假定C列为性别、D列为出生年月、F列为政治面貌、L列为县区、M列为身份证号码、N列为家庭住址，以下从相关基本操作、数据有效性、获取身份证相关信息等方面进行介绍。

一、相关基本操作

1.数据填充。

在Excel中，填充有规律数据（如重复数据、等差序列、等比序列等），逐个单元格手工填充费时费力且易出错，利用填充柄可避免或减少错误。所谓填充柄，就是选择单元格或区域后，选定的区域出现一个粗线型的矩形框，在该矩形框的右下角出现的小黑四方块。当把鼠标指向填充柄后，它变成一个黑十字，此时表示可以向上下左右四个方向拖曳并填充数据了。

在第一个单元格内输入第一个值后，拖曳填充柄至全部选中所需填充单元格，放开后在区域右下角出现的“自动填充选项”选择填充规则即可完成填充。学号的输入即可采用该方式。

2.单元格格式设置。

针对各类数据的格式要求，可对个别单元格或整行、整列进行设置，依次选择“格式”、“单元格”，在弹出的对话框里完成选择和设置，主要有数字分类、对齐方式、字体字形、边框及图案等。

以“0”开头的字符串（如学号、异地固定电话等），应设为文本格式。对于身份证号码等长度大于15位的数字，也应选为文本格式，否则将以科学计数法在单元格内显示，而在编辑区将正确显示前15位，超过15位的部分全部显示为“0”。

3.公式输入及运行。

对单独单元格可直接在编辑区输入“=公式”，同时按下Ctrl+Shift+Enter三键回车以运行并检查公式，然后拖曳复制公式。但应注意两点，一是公式的输入要在半角状态下进行；二是根据公式所引用单元格的具体情况，分析是相对引用还是对行或列的绝对引用，否则在公式复制后将会出现计算错误。如M4为相对引用，而$M$4是绝对引用了行和列，当公式复制到其他地方，也将引用M4单元格的值。使用F4键可对选中的公式段在相对引用、绝对引用行、绝对引用列及绝对引用单元格间进行切换。

二、根据数据有效性选择

学生信息中的民族、政治面貌、专业班级等相对固定的几类数据，可设置为序列方式，然后进行选择以减少工作量和错误。以输入政治面貌为例，介绍步骤如下：

1.在同一工作簿的另一工作表中，以列或行的形式，在每个单元格内分别输入如“党员”、“团员”、“派”、“群众”等数据。

2.选中有效数据单元格，在名称框内输入“政治面貌”或其他易记的名称后回车对序列进行命名；也可依次选择“插入”、“名称”、“定义”打开对话框进行命名。

3.选中政治面貌所在列，依次选择“数据”、“有效性”，在弹出的对话框中设置有效性条件。在“设置”选项卡“允许”的内容选择为“序列”，在“来源”里输入“=政治面貌”。

设置数据有效性后，在相应的单元格右侧将出现下拉箭头，可点击箭头选取；也可直接输入，但若设置的序列不严格一致将不能完成输入。当连续单元格数据重复时，可拖曳填充柄完成填充。需注意的是，有效性的限定对粘贴的不同格式数据无效。

三、利用身份证号码获取相关信息

现在的公民身份证号码为十八位的特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，表示编码对象常住户口所在县(市、旗、区)的行政区划代码；八位数字出生日期码，表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符；三位数字顺序码，为同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。其中第十七位奇数分给男性，偶数分给女性；和一位数字校验码，根据相应规则计算得出。

根据身份证号码的编码规则，我们既可从身份证号码中获取性别、出生年月、户籍，又可根据校验码判断身份证号码是否合格，以免输入时出现错误。

1.获取性别公式为IF(MOD(MID(M2,17,1),2)="0","女","男")，式中M2为身份证号所在单元格。

2.获取出生日期公式为CONCATENATE(MID(M2,7,4),"年",MID(M2,11,2),"月",MID(M2,13,2),"日")。

3.获取身份证发证地（即户籍地）的方法与步骤：

（1）先在国家统计局网站上下载最新的城市代码表，通过整理，在EXCEL工作簿的另一工作表选取三列，分为身份证地市代码、对应的省辖市及县区（市），需要注意的是地市代码应与身份证号码格式一致，即文本格式；

（2）选中此三列，在名称框内输入“地市代码”后回车，如图1：

图1 定义地市代码

（3）在学生信息工作表单元格内输入“=VLOOKUP(LEFT(M2,6),地市代码,Y,)”，公式中Y为2时可获取所属省辖市，为3时可获取所在县区（市）。其余单元格利用填充柄填充即可。

4.验证户籍及家庭住址一致性：此时需增加辅助列，在辅助列单元格内输入公式=FIND(L2,N2)。公式运行后单元格内所显示的数值是县区（市）在家庭住址内查找到的位置，此时表明二者一致，否则信息可能有误。

为在数据录入过程中及时发现错误，可采用第二种方法。即设置条件格式。选中L列，依次选择“格式”、“条件格式”，将“条件1”选为“公式”并在右侧框内输入“=ISERROR(FIND(L1,N1))=TRUE”，点击“格式”，在弹出的对话框中确定单元格格式，然后确定，其结果如图2所示，县区与家庭住址中不一致的，其单元格底纹将为 。

图2 利用条件格式查找错误

5.校验身份证号码合格性

根据身份证号码最后一位校验码的计算方法，运用公式计算出正确校验码，然后与最后一位对照，若二者相等说明身份证号码是合格的。

利用条件格式，选中M列，将“条件1”的公式输入为

“=(RIGHT(M1,1)=MID("10X98765432",MOD(SUM(MID(M1,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1))=FALSE”，并根据个人习惯设置单元格格式，身份证号码不合格时其所在单元格将显示为条件格式的设置，如图2所示为加粗显示，也可改为醒目的红色。

利用条件格式验证户籍与家庭住址一致性及校验身份证号码合格性，便于在数据录入过程中及时发现错误，但难以排序和筛选。

使用Excel进行学生信息管理，对具有VB编程基础的使用者来说，通过编程可简化操作并验证错误，对多数使用者而言，可通过函数的应用在不需编程的情况下使工作简单易行。本文利用学生信息自身的特点，巧妙地利用Excel及其函数，利用身份证号码的信息获得学生的出生年月、性别和生源地市等信息，减少了信息的输入量，有效地保证了其正确性。希望对从事人事管理人员及相关人员有所帮助，增强其办事效果，提高工作能力。