Excel函数在中职教务管理中的应用技巧

王华伟++杨云

摘要：Excel函数是Excel预定义的内置公式，用户可通过设置函数参数的方法解决实际工作中有关数据计算和统计等问题。该文以中职学生学籍信息表和中职学生体能测试成绩统计表为例，详细介绍了Excel函数在解决实际问题中的应用技巧。

关键词： Excel函数；函数参数；应用技巧

中图分类号：TP37 文献标识码：A 文章编号：1009-3044（2015）25-0096-03

随着计算机的日益普及，办公自动化已深入到各行各业，多数办公人员掌握了制作表格的基本操作，如表格内容录入、编辑打印及简单常见函数的使用等，但很少涉及函数库中功能更强大的函数，如“逻辑”类、“数学”类、“文本”类函数等。本文通过中职学校教务管理中的两个案例来介绍相关函数的应用技巧。

1 中职学生学籍信息表案例

每年新生入学，学校都要采集新生信息，制作学生学籍信息表。利用Excel函数从身份证号和家庭住址中提取信息，大大减少了信息录入人员的工作量。

1.1 利用函数提取身份证号中的相关信息

身份证号是一组特征组合码，由17位数字本体码和1位数字校验码组成。排列顺序从左至右依次为：6位地址码、8位出生日期码、3位顺序码和1位校验码。当每个学生的身份证号逐个录入后，学生的出生日期、性别和年龄等信息就可以从身份证号中提取。中职学生学籍信息表如图1所示。

1）提取出生日期

从身份证号中提取出生日期，需要用到两个文本类函数：MID与TEXT。MID函数，其语法格式为：MID（文本字符串，开始位置，字符个数），功能是返回文本字符串中从指定位置开始的指定字符个数的子串。TEXT函数，语法格式为：TEXT（数值，用引号括起的文本字符串的数字格式），功能是将数值内容转换为带格式的文本，而其结果将不再作为数字参与计算。

从身份证号中提取出生日期的方法是：从身份证号的第7位开始连续取8个数字的子串。操作步骤：先将光标定位到图1所示F4结果单元格中，输入公式：=TEXT（MID（C4，7，8），"0000-00-00"），回车确认，在F4单元格中就显示出YYYY-MM-DD格式的数字文本，然后拖动填充柄填充F列中其余学生的出生日期单元格，即可实现所有学生出生日期的自动填充，结果如图2所示。

2）填充性别

从身份证号中提取性别，需要用到三个函数：逻辑函数IF、文本函数MID和数学函数MOD。IF函数，其语法格式为：IF（条件表达式，满足条件的结果，不满足条件的结果），其功能是用来判断是否满足某个条件，满足时返回一个值，不满足时返回另一个值。它还可以和许多其他函数进行嵌套，在Excel 2010中你可以在一个 IF 函数内最多嵌套 64 个 IF 函数。而MOD函数，其语法格式为：MOD（被除数，除数），其功能是用于返回两数相除的余数，返回结果的符号与除数的符号相同。

由于身份证号的第17位和性别有关，如果第17位为偶数，则性别为女，否则为男，因此先利用MID函数提取出身份证号的第17位，再利用MOD函数判断是否为偶数，最后用IF函数根据第17位数的奇偶性填充不同的性别。

提取性别的操作步骤是：先将光标定位到图1所示E4结果单元格中，输入公式：=IF（MOD（MID（C4，17，1），2）=0，"女"，"男"），回车确认，在E4单元格中就显示出性别，然后拖动填充柄填充E列中其余学生的性别单元格，实现所有学生性别的自动填充。

3）计算年龄

利用出生日期的年份减去当前日期的年份，即可得到该学生的年龄。完成这一操作要用到三个函数：日期与时间函数NOW、YEAR和文本函数MID。函数NOW（）返回当前的日期和时间；函数YEAR（日期）返回日期时间参数所对应的年份。因此YEAR（NOW（））返回的即是当前日期的年份。从身份证号中利用MID函数可提取出生日期的年份。

计算年龄的操作步骤是：先将光标定位到图1所示G4结果单元格中，输入公式：=YEAR（NOW（））-MID（C4，7，4），回车确认，在G4单元格中就显示年龄的数值，然后拖动填充柄填充G列中其余学生的年龄单元格，实现所有学生年龄的自动填充。

1.2 从家庭住址中提取新生生源所在省、市、县等信息

学生的家庭住址一般是根据户口本首页或身份证上的信息录入的，是一个准确详实的信息，从中提取新生生源所在省、市、县等信息，既减少了重复录入的过程，又保证了信息的一致性。

1）提取省名

从家庭住址中提取省名，需要用到文本函数LEFT。LEFT函数，其语法格式为：LEFT（文本字符串，从左开始截取的字符个数），功能是返回文本字符串中从左边第1个字符开始截取指定长度的字符子串。利用LEFT函数从家庭住址字符串中，从左第1个字符起截取3个字符即得到新生生源所在省的名称。

提取省名的操作步骤是：先将光标定位到图1所示I4结果单元格中，输入公式：=LEFT（H4，3），回车确认，在I4单元格中就显示出新生生源所在省的名称，然后拖动填充柄填充I列中其余学生的生源所在省单元格，即可实现所有学生生源所在省的自动填充， 结果如图2所示。

2）提取市名、县/区名及详细地址

利用文本函数MID可从家庭住址中提取市名、县/区名及详细地址。从家庭住址字符串中第4个字符起截取3个字符即得到新生生源所在的市名，从第7个字符起截取3个字符即得到新生生源所在的县/区名，而从第10个字符起截提取7个字符即得到新生生源的详细地址名称。

其操作步骤是：先将光标定位到图1所示J4结果单元格中，输入公式：=MID（H4，4，3），回车确认；再将光标定位到K4结果单元格中，输入公式：=MID（H4，7，3），回车确认；最后将光标定位到L4结果单元格中，输入公式：=MID（H4，10，16），回车确认，则在J4、K4、L4单元格中就显示新生的市名、县/区名及详细地址。然后选中J4、K4、L4单元格，拖动填充柄填充J、L、K列中其余学生的相关信息单元格，结果如图2所示。

2 中职学生体能测试成绩统计表案例

为全面推进素质教育，国家对在校学生体质健康方面提出了基本要求，要求各类学校对在校学生进行体能测试，测试成绩由测试人员汇总，按要求录入到电子表格并按照《国家学生体质健康标准》进行等级转换，得出测评结果，这些数据最终通过“中国学生体质健康网”按时上报至国家学生体质健康标准数据管理系统。测试人员录入测试原始数据后，逐人逐项目填写健康等级，工作量极大。如图3所示，利用IF逻辑函数转换各测试项目的健康等级，极大地减少录入工作量，提高正确率。

1）体重指数（BMI）及体重健康等级填充

体重指数（BMI）是用体重公斤数除以身高米数平方得出的数字。体重的健康等级由体重指数来决定，中职学生体重健康标准如表1所示。

体重指数填充的操作是：先将光标定位到图3所示G3结果单元格中，输入公式：=F3/（E3/100*E3/100），回车确认，在G3单元格中就显示出体重指数，再根据体重指数填充体重健康等级，将光标定位到H3结果单元格中，输入公式：=IF（C3=1，IF（G3<=23.8，IF（G3>=17.2，"正常"，"低体重"），IF（G3>=27.4，"肥胖"， IF（G3>=23.9，"超重"））），IF（G3<=23.4，IF（G3>=17，"正常"，"低体重"），IF（G3>=25.8，"肥胖"，IF（G3>=23.4，"超重"）））），回车确认，在H3单元格中就显示出体重健康等级，然后选中G3、H3单元格拖动填充柄填充G、H列中其余学生的体重指数及体重健康等级单元格，实现所有学生体重指数及体重健康等级的自动填充，操作结果如图4所示。

2）肺活量等测试项目的健康等级填充

根据《国家学生体质健康标准》，使用同样的操作步骤，对肺活量、50米跑、立定跳远、坐位体前屈等测试项目的健康等级使用IF函数嵌套的方法进行填充，操作结果如图4所示。

3 结束语

通过两个案例的实现，详细介绍了Excel函数在解决实际问题中的应用技巧。Excel函数在解决较难的实际问题时往往需要多个函数嵌套使用才能实现，在公式中所有的符号一律使用英文符号，如果使用了中文符号，系统会给出错误信息，导致无法完成操作。工作中，我们可通过查看Excel函数帮助进行学习，积累解决实际问题的能力，达到了事半功倍，提高工作效率的目的。

参考文献：

[1] 李斌. Excel 2010应用大全[M].机械工业出版社，2010.

[2] 张玉鑫. Excel函数在教务管理中的应用实例[J].中国商界，2010（5）.