年假计算
本篇适合：表格公式基础较强者，小白请绕道。
需求：根据逻辑，自动计算出，员工年假天数，如图-1：
图-1丨年假计算逻辑
基础数据字段：姓名，入职时间，社会工龄计算时间。
01逻辑分析
1、辅助列
根据文字描述，要想得到最终的年假天数，我们需要计算出：
1、司龄年假；
2、社龄年假；
3、临界年假*2。
四者最大值，即为最终年假天数。
如图-2，首先添加6列辅助列：
图-2丨年假计算辅助列
2、辅助单元格
例如我们在2018年，采集2019年的员工年假天数，那么计算截点是2019年01月01日和2019年12月31日：
图-3丨计算截点
02公式设置
先来简单的：司龄和社龄。
E2处公式：=($B$5-B2)/365，向下填充。
H2处公式：=($B$5-C2)/365，向下填充。
☆直接相减，注意B5绝对引用，再除以365转换为年，结果保留1位小数，如图-4：
图-4丨司龄、社龄的计算
接下来，匹配对应年假。F2处数组公式：
=VLOOKUP(1,IF({1,0},(E2>$A$9:$A$12)*(E2<$B$9:$B$12),$E$9:$E$12),2,0)，向下填充。
图-5丨司龄年假匹配公式
数组公式，一般都较难读懂，为方便理解，我们添加一个辅助区域，解其原理，如图-6：
图-6丨司龄、社龄的计算
然后，如图-7，C9处公式：
=($E$2>A9)*($E$2<=B9)，向下填充。
图-7丨演示列公式
☆牢记：逻辑值参与运算时，TRUE=1，FALSE=0。
到这里，例如在C5处输入公式：
=VLOOKUP(1,C9:E12,3,0)，即可匹配出司龄为E2=1.6时，对应的5天年假。
图-8丨司龄VLOOKUP匹配示例
把刚才讲解内容，糅合一下，也就是上文的数组公式。
同理，我们将社龄年假公式设置好，如图-9：
图-9丨社龄VLOOKUP匹配示例
接下来，我们处理临界值，也就是刚好满5、10、15、20工龄临界年的情况。
图-10丨临界年假计算逻辑
处理临界值时，需要先计算出两个百分比，可以利用YEARFRAC函数，如图-11，
E5处公式：
=YEARFRAC(--TEXT($B$5,"mm-dd"),--TEXT(B2,"mm-dd"),3)
E6处公式：
=YEARFRAC(--TEXT($B$6,"mm-dd"),--TEXT(B2,"mm-dd"),3)
图-11丨司龄、社龄的计算
☆TEXT公式作用：统一年份，并提取出月日，以便计算百分比。
例如张四14年8月1日入职，那么在19月8月1日满5年，属于满5年临界情况，其临界年假=前百分比*5+后百分比*10。
OK，接下来放大招，司龄临界年假计算公式，G2处公式：
=ROUNDDOWN(IF(AND(E2<5,E2>4),YEARFRAC(--TEXT($B$5,"mm-dd"),--TEXT(B2,"mm-dd"),3)*5+YEARFRAC(--TEXT($B$6,"mm-dd"),--TEXT(B2,"mm-dd"),3)*10,IF(AND(E2<15,E2>10),YEARFRAC(--TEXT($B$5,"mm-dd"),--TEXT(B2,"mm-dd"),3)*10+YEARFRAC(--TEXT($B$6,"mm-dd"),--TEXT(B2,"mm-dd"),3)*15,F2)),0)，向下填充。
（怕怕~）
图-12丨司龄临界年假计算
不知道能看懂不，反正我是看不懂的......
这里采用了两层嵌套的if函数，直接读公式是困难的，我们可以借助WPS流程图，理一下公式流程，如图-13：
图-13丨公式流程图
同理，修改部分参数，即为社龄临界年假计算公式。（临界年假的计算结果，向下舍入）
最终结果，取四者最大值。如图-14，K2处公式：
=MAX(F2:G2,I2:J2)，向下填充。
图-14丨结果取四者最大
03总结
年假计算逻辑，一张图完事儿。
而要实现自动计算，密密麻麻的公式，光看看都觉得可怕......
群主原创，手打不易。
更多实战分享，请关注微信公众号，“闲钓宇哥”，谢谢！
网友评论