巧用ＥＸＣＥＬ函数建新生学籍

叶建阳

新生入校后,学校的一项重要工作就是建立新生学籍。随着学生学籍的数字化、电子化、网络化、信息化,对我们的学生学籍管理提出了更高要求。大多数学校建立学生电子学籍是在电子表格下完成的,即便使用数据库,多数也是先建立电子表格,然后导入数据库。因为电子表格具有操作简便直观,易于打印校对,不需要定义字段等优点,且普及和应用远远胜于数据库。但输入的信息多数情况下是简单重复且有规律性的,为了避免重复无意义的劳动,在实际工作中,输入方法的科学与否,直接影响输入的成效。电子表格中巧用函数,是解决这一问题最直接、最有效的方法。

一、首位带零的长学号生成方法:

学校为学生建立学籍往往要编辑学号。例如:某校的学号编辑规律是录取年份两位数、学校代码五位数、院系代码一位数、学历层次两位数、专业代码两位数、班级一位数、顺序号两位数,共计十五位数。例如:07年录取的某专业新生,编辑的学号为“071698042034012”。其中,07是录取年份,16980是学校代码,4是院系代码,20是学历层次编码,34是专业编码,0表示是单班,12是学生的顺序号。

在电子表格中生成学号时,学籍人员往往将输入学号一列定义为“文本”,否则不能保留首位零,然后一一输入,效率极低;如果使用“常规”或“数值(小数位数定义为零)”,因学号编码太长,当超过十一位时,就自动采用科学记数法,不能正常显示学号编码,且首位“零”不能保留。

而事实上只要合理利用文本合并函数“CONCATENATE”,就可轻松解决这一问题。

语法:CONCATENATE (text1,text2,...)

Text1,text2,...为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。

在实际操作中,text1、text2、……分别指电子表格中的某个单元格中的字符串,常用单元格名称代替。

假定:在A列A3单元格以下生成新生学号。先在C列C3、C4分别输入“34001”、“34002”,选中此二格,利用填充柄向下生成所需顺序号数值。然后选择B列,定义B列为“常规”,再选中B3格,在英文输入法状态下,输入“=concatenate(‘07169804203,C3)”,敲回车键,即可在B3单元格产生十五位学号。然后用填充柄在B列向下拖拽可产生所需学号,快速简便,效率极高。生成的学号会因删除B、C两列无用数值而产生错误,所以在B列生成十五位学号后,必须全部选中,单击鼠标右键,在弹出的快捷菜单中选择“复制”,再选中A3单元格,单击鼠标右键,在弹出的快捷菜单中选择“选择性粘贴”,在弹出的“选择性粘贴”对话框中,选择“数值”,单击“确定”按键,即可将B列产生的学号全部复制到A列。然后全部删除B、C列的数值。

二、巧用身份证号生成出生年月:

建立学生学籍时,往往既要输入出生年月也要输入身份证号,输入耗时且易出错,有时往往出现身份证号与出生年月不一致的问题,给校对带来很大的麻烦。

在实际输入中,只要输入正确的身份证号,经校对无误后,可用MID函数自动生成出生年月。

MID 返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

语法:MID(text,start_num,num_chars)

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num为1,以此类推。

Num_chars 指定希望MID从文本中返回字符的个数。

学生的身份证号全部为18位,几乎没有15位,前六为省、市、县区代码,中间8位为出生年月日,后4位为该生编码。而我们要用的就是中间这8位数值。

我们可用MID函数从身份证号中巧用此8位出生年月,而不用一一输入,即保证了数值的准确性、与身份证号的一致性,且简单快捷。

假定,已在F列F3单元格以下输入了学生的身份证号,现要在D列D3以下输入学生的出生年月,只要选中D3单元格,在其中输入“=MID(F3,7,8)”,意为从F3单元格的字符串中从7位起向后共选取8位数值(含第7位)返回其值在D3单元格。然后敲回车键,即可在D3单元格产生8位出生年月数值。余下的工作就是用填充柄向下填充了。

也可用下式生成日期格式出生年月,且可在身份证号单元格为空时,不返回错误信息,而返回空值。

=IF(F3=“”,“”,MID(F3,7,4)&-MID(F3,9,2)&-MID(F3,11,2))

不论哪一种输入法,输入男、女性别,都是多次敲击键盘的重复录入。有没有只需敲击一次键盘即可输入性别的方法呢?

IF函数巧用可成就性别快速输入。

IF函数执行真假值判断,根据逻辑计算的真假值,返回不同结果。

语法:IF(logical_test,value_if_true,value_if_false)

Logical_test:表示计算结果为 TRUE 或FALSE的任意值或表达式。例如,A10=100就是一个逻辑表达式,如果单元格A10中的值等于100,表达式即为TRUE,否则为FALSE。本参数可使用任何比较运算符。

Value_if_true:logical_test为TRUE时返回的值。例如,如果本参数为文本字符串“男”而且logical_test参数值为 TRUE,则IF函数将显示文本“男”。如果logical_test为TRUE而value_if_true为空,则本参数返回0(零)。如果要显示TRUE,则请为本参数使用逻辑值TRUE。Value_if_true也可以是其他公式。

Value_if_false:logical_test为 FALSE 时返回的值。例如,如果本参数为文本字符串“女”而且logical_test参数值为 FALSE,则IF函数将显示文本“女”。如果 logical_test为FALSE 且忽略了Value_if_false(即value_if_true后没有逗号),则会返回逻辑值FALSE。如果logical_test 为FALSE且Value_if_false为空(即 value_if_true后有逗号,并紧跟着右括号),则本参数返回0(零)。Value_if_false也可以是其他公式。

函数IF可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂的检测条件。

假定:在B列B3开始输入姓名,C列C3开始输入性别。先在D列D3单元格输入“=IF(C3=0,“男”,“女”)”,然后用填充柄拖拽至输入完成的最后一行,会在D列看到性别全部为“男”。在B列输入姓名完成后,当该生性别为“男”时在C列输入“0”或不输入任何内容,当为该生性别“女”时,可在C列输入除“0”以外的任何一字符或数字,D列对应的性别会立即变为“女”,连输入法都不用切换。输入完成后,只要将D列性别“复制”并以“选择性粘贴”、“数值”粘贴在C列即告完成。

身份证号第十七位为单数表示“男”性,为偶数时表示“女性”。只要使用MID函数从身份证号中提取第17位数,再使用求余函数MOD对提取值进行求余运算,当余数为“1”时,说明是单数,返回函数值“男”,否则返回函数值“女”。假设从F3单元格及以下录入了身份证号,可用下列式在公式所在单元格自动产生性别。

=IF(F3=“”,“”,IF(MOD(MID(F3,17,1),2)=1,“男”,“女”)

Excel中函数功能极其强大,应用得当可以起到事半功倍的作用,是电子表格处理事务的强有力助手。函数应用的能力强弱,将直接影响到电子表格的应用效率。电子表格不仅仅是学生学籍电子管理、学生成绩管理的手段,同时也为我们提供了强大的开发应用价值,我们应在实践中不断学习和提高应用能力,发挥好这一工具的作用。

作者单位:延安职业技术学院(学士、副教授)