首页 > 文章中心 > 正文

高校班级管理EXCEL运用

高校班级管理EXCEL运用

1利用函数从身份证号码中提供有效信息

众所周知,身份证号码能如实的反映出一个人的性别、出生年月、籍贯等信息,并与之密不可分,因此,不论是15位还是18位的身份证号码,个人的此类信息都蕴含其中。以15位为例,出生年份(两位数)反映在第7、8位上,出生月份反映在第9、1O位上,出生日期反应在第11、12位上,It_-I:~U反映在第15位上,奇数为男,偶数为女。18位身份证号码稍有差别,出生年份(四位数)反映在第7、8、9、1O位上,出生月份反映在第11、第12位上,出生日期反映在第13、14位上,性别反映在第17位上,奇数为男,偶数为女。这里,我们需要使用IF、LEN、MOD、MlD、DATE等函数从身份证号码中提取个人信息

1.1出生年月信息的提取

由于报表格式的局限,只涉及到出生年月,并没有涉及出生日期,因此我们要通过身份证号码提取相应信息,即显示为“7208”这样的信息。在D2单元格中输入公式“=IF(LENfC2)=15,MJD(C2,7,4),MID(C2,9,4))”,其中:LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。MlD(C2,7,4):从C2单元格中字符串的第7位开始提取四位数、字,本例中表示提取15位身份证号码的第7、8、9、10位数字。MID(文本,开始字符,所取字符数)MlD(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、1O、11、12位数字。IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取白第9位开始的四位数字。如果需要显示为“7O年12月”这样的格式,请使用DATE格式,并在“单元格格式一日期”中进行设置。

1.2年月日信息的提取

从身份证号码中提取出表示出生年、月、日的数字,用文本函数MID()可以达到目的。MID()——从指定位置开始提取指定个数的字符(从左向右)。对一个身份证号码是15位或是18位进行判断,用逻辑判断函数lF()和字符个数计算函数LEN0辅助使用可以完成。

1.3性别信息的提取

由于报表中各位学生的序号编排是按照上级核定的编制进行的,因此不可能区分出男女性别,倘若依靠手工输入的话,不但麻烦而且容易出错。例如性别信息在B列反映出来,可以在B2单元格中输入公式“=lF(M0D(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男”,“女”)”,其中:LEN(C2)=15:检查身份证号码的长度是否是15位。MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字。MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。JF(MODfIF(LENfC2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“):如果除以2以后的余数是1,那么B2单元格显示为“男”,否则显示为“女”。点击回车键后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳。现在这份报表无论是提取信息或是核对,都方便多了。

2在学生成绩统计中的应用实例

2.1保持学号顺序不变的前提下进行成绩排名

在每年9月的奖学金评定中,最重要的一项就是学生成绩排名。特别要强调的是,为了保持学号顺序不变的情况下进行成绩名次的评定,最好使用RANK(X,Y,Z)函数。其中第一个参数×为某个学生的成绩所在单元格;第二个Y为整个班级成绩所在区域;第三个Z是可选的,表示统计方式,若省写或写0,则成绩高的名次靠前,一般默认此种方式,如果写1,则成绩高的名次靠后。例如=RANK(C2,$C$2:$C$13,0),然后利用自动填充将其复制到下方几个单元格内。

2.2将百分制转换成不同的等级分有多种不同的划分方法。①是将百分制的分数转换成A(90~100)、B(80—89)、C(70~79)、D(60—69)与E(低于60)五个等级;②是将百分制的分数转换成优(90—100)、良(75~89)、中(6O~74)与不及格(低于6O)四个等级。具体使用哪种等级划分方法可根据实际情况自己确定。在百分制转换成不同的等级分时,一般使用IF(X,Y,Z)函数。其中有三个参数,第一个参数X为条件,不能加引号:第二个参数为条件成立时的结果,如果是显示某个值,则要加引号:第三个参数为条件不成立时的结果,如果是显示某个值,同样要加引号。该函数可以嵌套,即在第二个或第三个参数处可以再写一个lF函数。为了得到“一”所要的等级结果,可以在D2单元格中输入公式:=IF(C2>=90,“A,IF(C2>=80,“B“,IF(C2>=70,”C“,IF(C2>=60,“D”,”))),然后,利用自动填充柄将其复制到下方的几个单元格。为了得到“二”列所要的等级结果,可以在E2单元格中输入公式:=IF(C2>=90,优“,IF(C2>=75,”良”,IF(C2>=60,“中”,“不及格”))),然后,利用自动填充柄将其复制到下方的几个单元格。

2.3不及格的分数用红色字体显示选择“格式”菜单中的“条件格式”命令。该命令会弹出一个要求确认条件与相应的格式的对话框。对于“成绩”列,可先选中C2:C13,然后使用“格式”菜单中的“条件格式”命令,在弹出的对话框中,左边使用默认的“单元格数值”,中间选“小于”,右边填写60,然后单击右边的“格式”按钮,从中选择红色,最后单击两次“确定”按钮。对于“等级1”列,可先选中D2:D13,然后使用“格式”菜单中的“条件格式”命令,在弹出的对话框中,左边使用默认的“单元格数值”,中间选“等于”,右边填写Ej然后单击右边的“格式”按钮,从中选择红色,最后单击两次“确定”按钮。“等级2”列类似。对于其他的一些统计计算要求,如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能,应该比较简单,本文此处不赘述。