转眼又到了一年调整工资的时候了,作为公司会计的小宏,这天却在办公室时忙得团团转,几百人的员工,要按年龄和工龄对工资进行调整,但从人事部转过来的员工信息表(如下图所示),只有员工的身份证号和入厂时间,却没有年龄和工龄,怎样处理呢?小宏请教同事,同事一条公式就解决了,看得小宏只怪自己的EXCEL没好好学。

利用mid函数计算出生年月日(MID函数原来这样用)(1)

【学习内容】

  1. MID函数的用法;
  2. 文本查找、提取的操作实例;

【学习目标】

1、会观察数据的结构,能从数据信息中提取需要的数据。

2、掌握MID函数的用法。

【学习过程】

1、MID函数

(1)功能:从任意位置提取指定数目的字符。

(2)描述:用于返回文本字符串中从指定位置开始的特定数目的字符,该数目由参数指定。

(3)格式:MID(text, start_num, num_chars)

(4)参数解释:

 text:表示包含要提取字符的文本字符串。 

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

num_chars:表示指定希望MID从文本中返回字符的个数。

2、实例讲解

要调整员工的工资,年龄、工龄和性别是必不可少的,根据上面的表格,可以将所需的信息补充完整,设置后的基本信息表如下图所示:

利用mid函数计算出生年月日(MID函数原来这样用)(2)

(1)补充员工的性别

大家知道,身份证的第17位是判断性别的数,奇数表示“男”,偶数表示“女”,这个可以用MOD函数来解决(MOD函数的具体用法请查看笔者往期的文章),提取数字,就可用到MID函数,再加上IF判断语句,就可以实现员工性别的判断了。

1)选中D3单元格,输入以下公式:

=IF(MOD(MID(C3,17,1),2)=1,"男","女")

2)填充公式,就可以得到所有员工的性别了。

3)公式解释

利用mid函数计算出生年月日(MID函数原来这样用)(3)

(2)获得员工出生年月日

身份证的出生年份开始的位数是在第7位,后面4位就是年份,跟着两位就是月份,随后两位就是日期,重复使用MID函数,提取年份、月份和日期。

1)选中E3单元格,输入如下公式:

=MID(C3,7,4)&"/"&MID(C3,11,2)&"/"&MID(C3,13,2)

2)往下拖拉填充公式,就可以得到所有员工的出生年月日。

(3)计算员工年龄

得到了员工的出生年份,要计算年龄就比较简单了,先提取现在时间的年份,然后减去员工出生日期的年份,就得到年龄。

1)选中F3单元格,输入如下公式,按回车,就可以算出员工的年龄了,E3是员工的出生日期。

=YEAR(TODAY())-YEAR(E3)

2)往下拖拉,得到所有员工的年龄。

(4)同(3)的操作方式一样,可以计算出员工的工龄。

1)选中H3单元格,输入如下公式,G3是员工入厂时间。

=YEAR(TODAY())-YEAR(G3)

2)往下填充公式,就可以计算出所有员工的工龄了。

3)最后,其员工信息表如下图所示。

利用mid函数计算出生年月日(MID函数原来这样用)(4)

【小结】

本例通过提取身份证的信息,从而得到员工的出生年月日、年龄、工龄等基本的信息,MID函数的应用是非常多的,本例只是举例说明,更多的技巧还有待读者们在实际的工作中进行实践操作。本例只是为了说明MID的操作,信息里的数据均是虚构,比如年龄和工龄等,不可跟实际的员工年龄相吻合。

,