Excel 可否根据职员编号计算职员能享受的年假天数?

某公司的职员编号中包含部门名称缩写、进厂日期和序号,编号效果如图7.128所示。

图7.128 职工信息表

其中,部门编号与部门名称的对应关系见表7.4。

表7.4 编号与部门对应关系表

现要求利用编号中的入厂日期计算每个职工的年假天数。年假天数的计算方法见表7.5。

表7.5 年假计算办法

解题步骤

本例的问题相当复杂,因此可以分多个步骤来书写公式,将一个公式拆分成多个公式书写更容易理解公式的计算过程,同时也不易出错。具体步骤如下。

1.在E1:H1区域分别输入“进厂日期”、“完善日期”、“进厂月数”、“年假天数”。

2.在E2单元格输入以下公式,此公式用于提取两个“-”之间的字符,提取结果见图7.129。

图7.129 提取进厂日期

职员编号中的日期没有完整的年份,因此需要通过公式来完善日期。

3.在F2单元格输入以下公式:

有了完整的入职日期后就可以计算职工的入职时间了,单位为月。

4.在G2单元格输入以下公式:

有了入职时间,接下来就可以通过IF函数计算职工可享用年假的天数。

5.在H2单元格输入以下公式:

在以上4个公式的协同工作下,职工的年假天数会自动产生,效果如图7.130所示。

图7.130 分四步计算出年假天数

6.选择E2:H2,然后按下填充柄向下拖到第12行,从而让公式计算所有职工的年假天数。

假设只用一个公式计算年假天数,那么公式会相当长,不管是新手还是老手都不建议直接一步完成。如果要求只能在单列中完成,那么可以定义名称,通过名称简化公式,操作步骤如下。

1.删除E:H区域,然后在E1中输入“年假天数”。

2.选中E2单元格,按组合键<Ctrl+F3>打开“名称管理器”。

3.单击“新建”按钮,弹出“新建名称”对话框,然后将名称设置为“时间”,将引用位置设置为以下公式:

4.单击“确定”按钮保存设置,然后返回工作表界面。

5.在E2单元格输入以下公式:

6.双击E2单元格的填充柄,将公式向下填充到E12,最终效果如图7.131所示。

图7.131 单个公式计算年假天数

知识扩展

1.公式“=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)),99,99))”的含义是将职员编号中的“-”替换成99个空格,然后用MID函数从替换后的字符串中的第99位开始提取99个字符,从而得到空格与日期组成的字符串,最后用Trim函数去除空格,只保留日期。

公式中的REPT函数用于将指定的字符重复显示若干次,第一参数是要重复显示的字符,第二参数用于控制重复次数。例如“=REPT("AB",3)”的计算结果为“ABABAB”。

SUBSTITUTE函数用于将字符串中的部分字符替换成新字符,在本例中的作用是将“-”替换成99个空格。

由于替换后的日期前后各有99个空格,因此使用MID函数从替换后的字符串中第99位开始提取99个字符只能提取到空格和日期,不包含前面的部门编号和后面的序号,此时去除所有空格就只剩下日期了。

2.职工编号中的日期包含两种:一种是2000年以后的,第一个数字是0或1;另一种是2000年以前的,第一个数字是9,基于此规律,使用IF函数判断第一个数字是否等于9即可,如果等于9则在日期前添加19,否则添加20。

此处要注意的一点是LEFT函数提取出来的数字是文本格式的,因此和数字9比较时需要将数值9也转换成文本,在9的前后添加半角引号

3.DATEDIF函数计算两个日期的时间差时,两个日期都必须是日期格式的,“20150314”这种格式的数值不是日期格式,因此需要使用TEXT函数转换成“0000-00-00”这种格式后再参与计算。

4.当IF函数要求两个条件同时成立才算满足条件时,需要使用AND函数限制这两个条件。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

邮箱地址不会被公开。 必填项已用*标注