浅谈Excel中常用函数在工作中的应用.docVIP

精品论文 参考文献 浅谈Excel中常用函数在工作中的应用 ◆ 陈钦　海南省东方市港务中学　572600 　　摘　要：在日常工作中，经常和数据打交道，统计学生的考试成绩，录入学生的学籍信息，有学籍号、身份证号码、性别、出生年月、年龄等，录入教职工的信息，有身份证号码、出生年月，年龄、教龄、退休时间等，这些信息要是一一手工录入的话，工作量无疑非常大，而且还容易出现错误。如何减少工作量，提高工作效率？本文列举工作中的几个事例，浅谈Excel中常用函数在工作中的应用。 　　关键词：Excel　函数　应用 　　在日常工作中经常需要对数据进行统计、分析，比如：需要统计学生考试成绩及“三率一分”，需要录入学生的身份证号码、性别、出生年月、年龄，教职工的身份证号码，出生年月、年龄、教龄、退休时间等，这些信息要一一录入电脑，工作量是非常大的，有没有什么方法减少工作量，提高工作效率？Excel电子表格成为办公人员的首选，而Excel中函数则是解决这些问题的利器。本文从几个事例谈谈函数在工作中的应用。 　　一、使用函数统计“三率一分” 　　“三率一分”指的是“优秀率”、“及格率”、“低分率”和平均分。优秀率=优秀人数divide;全班人数，而优秀人数如何计算？一个一个地算吗，这虽然也是一种办法，但每一科、每个班都这样算，显然不是好办法，可以在Excel表格中使用带条件的计数函数COUNTIF（range,criteria）自动算出某科中符合条件的人数，即算出大于等于76分的人数。函数中参数range是计算的范围（例如：B3:B65），criteria为条件，即”gt;=76”，在优秀率单元格中输入：=COUNTIF(B3:B65,”gt;=76”)/73,其中73为全班人数，按回车键即可出现结果，再用填充柄把公式复制到其他学科，自动算出优秀率，及格率和低分率的函数是类似的，而平均分可以使用AVERAGE(B3:B65)计算。 　　二、从身份证号码中提取相关信息 　　录入学生信息时除了身份证号码外，还有出生年月、性别、年龄等，如果要一一录入，一位学生的信息要录入几遍，这样的工作量是比较大的，有没有既简便又省事的方法？ 　　1.身份证号码规则 　　实际上，身份证号码中就是包含了出生年月、性别、年龄等信息，具体规则是：15位数字的身份证号码，1-6位是出生编码，7-8位是出生年份，9-10位是出生月份，11-12位是出生日期，13-14位是出生顺序编号，15位是性别标号（奇数为男性，偶数为女性）；18位数字的身份证号码类似，1-6位是出生编码，7-10位是出生年份，11-12位是出生月份，13-14位是出生日期，15-16位是出生顺序编号，17位是性别标号（奇数为男性，偶数为女性），18位是效验码。 　　2.从身份证号码提取出生日期 　　在D3中输入“=if（len（b3）=15,”19“amp;mid（b3,7,2）amp;”年“amp;mid（b3,9,2）amp;”月“amp;mid（b3,11,2）amp;”日,“if（len（b3）=18,mid（b3,7,4）amp;”年“amp;mid（b3,11,2）amp;”月“amp;mid（b3,13,2）amp;”日,“身份证错误”）），不含中文双引号，按回车即可提取出生年月，然后使用填充手柄即可自动获取出生年月，可以判断身份证是15位还是18位，除这两个外都是错误的身份证号。 　　 　　3.从身份证号码中提取性别 　　在C3中输入“=IF（LEN（B3）=15,IF（MOD（MID（B3,15,1）,2）=1,“男”,“女”）,IF（LEN（B3）=18,IF（MOD（MID（B3,17,1）,2）=1,“男”,“女”）,“身份证错误”））”，不含中文双引号，按回车键即可获取性别，再利用填充手柄自动获取其他人的性别。 　　4.根据出生年月算出年龄 　　根据上面从身份证中提取的出生年月可以算年龄。把出生年月一列改为“0000-00-00”的格式。在E3中输入“INT（（TODAY()-D3）/365）”,不含中文双引号，按回车键即可算出年龄，再利用填充手柄自动获取其他人的年龄。 　　三、使用日期计算工龄和退休时间 　　1.从参加工作时间计算工龄 　　假如从M列六行起为教职工参加工作时间，O列为工龄，则在O6单位格中输入：=INT（（TODAY（）-M6）/365），按回车键就计算出工龄。 　　公式的含义： 　　TODAY（）为系统当前的日期，即今天的日期 　　INT（）为取整函数，即把小数部分去掉。 　　2.从出生日期计算退休时间 　　退休时间按男60岁，女55岁退休来算，假如出生日期从F列6行起，退休时间在P列，则在F6单元格中输入：=IF（E6=“男”,DATE（YEAR（F6）+60,M