特别复杂的年休假-请教!

sunya_0529 · 发表于 2009-6-10 12:34

原帖由曦妈于 2009-6-9 16:45 发表
另外，请问30楼的高手，那个单元格如何输入可以使它显示的时候是“2008年休假”字样，但单元格的值是“2008”？

能否解释一下你那个超美的表里面如下公式的意思？没有完全看明白。。。用evaluate分析了一下，还是 ...

想偷懒都不行呐，原以为“wangtx218”兄和“Jackeroo”版主会详细解释一下这个公式的，呵呵^_^

公式原理：
假设A员工于日期X刚好工作满1年，那么X所在年份全部按照12天来算年假的话，从1月1日起至X的这些日子Y天里，实际多算了（12－6）×Y／365天。因此，只要用12－［（12－6）×Y／365］就可以得出X所在年份可以享受的年休假是多少天了。

计算步骤：
一、最外层是一个IF()
嵌套，用来判断入职年份是否在E$1年度之后，如果是的话，年假为0，否则计算应该享受的年休假。
=IF(YEAR($D2)>E$1,0,CEILING(LOOKUP(E$1-YEAR($D2),{0,1,10},{6,12,15})-TEXT(E$1-YEAR($D2),"[<2]6;[=10]3;!0")*(DATE(E$1,MONTH($D2),DAY($D2))-DATE(E$1,1,1))/365,0.5))

二、用Lookup()函数来找出E$1年度可以享受年休假数的上限，要么6天，要么12天，要么15天。
LOOKUP(E$1-YEAR($D2),{0,1,10},{6,12,15})

三、算出需要折算年假天数的年份里多算的年假天数，如上述“公式原理”中的“（12－6）×Y／365天”
TEXT(E$1-YEAR($D2),"[<2]6;[=10]3;!0")*(DATE(E$1,MONTH($D2),DAY($D2))-DATE(E$1,1,1))/365
TEXT(E$1-YEAR($D2),"[<2]6;[=10]3;!0")，这个函数跟下面这个是等同的：
IF(E$1-YEAR($D2)<2,6,IF(E$1-YEAR($D2)=10,3,0))
计算的是如“（12－6）”的常数，入职不满一年的是（6-0）＝6，满一年是（12-6）＝6，满十年的是（15-12）＝3
TEXT()函数的作用是按照第二个参数中的格式化条件去格式化第一个参数，其作用等同于设置单元格格式。只不过TEXT()处理过的数据还可以直接参加运算。第二个参数里的完整条件应该包括：“正数;负数;零;文本”四块，用“;”分隔，分别表示当第一个参数是“正数”、“负数”、“零”、“文本”时显示的格式。利用这个原理，可以来简化IF()函数，尤其是IF()函数的第一个参数是一个很长的表达式的时候，能有效缩短公式字符，简洁易维护。
在Text()函数的第二个参数里，还可以用表达式来表示条件，上面的公式就用到了这个："[<2]6;[=10]3;!0"，意思就是第一个参数小于2时，TEXT()函数返回6，等于10时返回3，其余的情况就返回0（因为0在格式文本字符串中表示一个数字的占位，所以要显示“0”这个值，就要用“!”界定符来特别标志一下），和上面的这个IF()公式是不是一个意思呢？!

四、计算多数年休假部分的天数，也就是上述“公式原理”中的“Y”，这个比较简单，就不作说明了。
(DATE(E$1,MONTH($D2),DAY($D2))-DATE(E$1,1,1))/365

wangtx218 · 发表于 2009-6-10 14:48

原帖由 sunya_0529 于 2009-6-5 15:31 发表
楼主想要统计多个年度的，用一个公式可能更好一点，看看附件：

交流一下

带薪年假天数090610.rar (6.63 KB, 下载次数: 136)

曦妈 · 发表于 2009-6-10 17:22

谢谢sunya,拜读中。。。真的学到了不少东东啊，太感谢啦！

sunya_0529 · 发表于 2009-6-10 17:54

原帖由曦妈于 2009-6-10 12:18 发表
今天汇总的时候发现还有两种情况的年假没有计算在内。。。晕！是否能整合一下公式把下面三种情况都算进去？或者分开也行，情款3的公式已经有了，本想改一下的，但是没有成功，现在需要各位高手帮忙指点情况1和2的，谢 ...

“wangtx218”兄的数组公式实在是经典，学习了~~

在此启发之下，变换一下数组公式的内核为：

{=SUM(IF(E$1+ROW(INDIRECT("1:365"))>D2,LOOKUP(DATEDIF(D2,E$1+ROW(INDIRECT("1:365")),"Y"),{0,1,10},{6,12,15})/365,))}

复制代码

这样楼主的三个问题可以照搬套用了，其他以上下限来计算年假的公式也可以套用。

第一个问题的公式为：
=SUM(IF(E$1+ROW(INDIRECT("1:365"))>D2,LOOKUP(DATEDIF(D2,E$1+ROW(INDIRECT("1:365")),"Y"),{0,1,5,10},{0,15,20,25})/365,))
第二个问题的公式为：
=SUM(IF(E$1+ROW(INDIRECT("1:365"))>D2,LOOKUP(DATEDIF(D2,E$1+ROW(INDIRECT("1:365")),"Y"),{0,1,2,10},{0,6,12,15})/365,))

也就是将公式里的数组设定一下就可以了。

wangtx218 · 发表于 2009-6-11 08:34

sunya：LOOKUP用的不错，学习了！

=SUM(IF(E$1+ROW(INDIRECT("1:365"))>D5,LOOKUP(DATEDIF(D5,E$1+ROW(INDIRECT("1:365")),"Y"),{0,6;1,12;10,15})))/365
=IF(G$1>=YEAR(D2),LOOKUP(G$1-YEAR(D2),{0,6;1,12;10,15})-TEXT(G$1-YEAR(D2),"[<2]6;[=10]3;!0")*(D2-DATE(YEAR(D2),1,0))/365,)

带薪年假天数090610.rar (7.07 KB, 下载次数: 66)

短一点！

[ 本帖最后由 wangtx218 于 2009-6-11 14:48 编辑 ]

曦妈 · 发表于 2009-6-11 16:25

谢谢wangtx218和sunya的交流，真的学习啦！

sunya_0529 · 发表于 2009-6-12 10:39

原帖由 wangtx218 于 2009-6-11 08:34 发表
sunya：LOOKUP用的不错，学习了！

=SUM(IF(E$1+ROW(INDIRECT("1:365"))>D5,LOOKUP(DATEDIF(D5,E$1+ROW(INDIRECT("1:365")),"Y"),{0,6;1,12;10,15})))/365
=IF(G$1>=YEAR(D2),LOOKUP(G$1-YEAR(D2),{0,6;1,12;10,1 ...

多谢wangtx218 兄提点，这个问题真是越钻研越有趣，想到了用Average()函数，公式还可以更简短，只要97个字符：

=AVERAGE(LOOKUP(DATEDIF(D2,DATE(H$1+99,1,ROW(INDIRECT("1:365"))),"Y")-99,{-99,0;0,6;1,12;10,15}))

复制代码

其实在上面这个公式里，如果入职年份与统计年份相差不超过10的话，还可以把“99”改成“9”，这样一来又省三个字符，变成：

=AVERAGE(LOOKUP(DATEDIF(D2,DATE(H$1+9,1,ROW(INDIRECT("1:365"))),"Y")-9,{-9,0;0,6;1,12;10,15}))

复制代码

运算结果如附件中H列所示：

jackeroo · 发表于 2009-6-12 10:51

原帖由 sunya_0529 于 2009-6-12 10:39 发表

多谢wangtx218 兄提点，这个问题真是越钻研越有趣，想到了用Average()函数，公式还可以更简短，只要97个字符：

=AVERAGE(LOOKUP(DATEDIF(D2,DATE(H$1+99,1,ROW(INDIRECT("1:365"))),"Y")-99,{-99,0;0,6;1,12 ...

sunya在列出各种巧妙公式的时候，最好能做个解释，方便一些初学者理解，谢谢。

wangtx218 · 发表于 2009-6-12 12:36

AVERAGE(LOOKUP(DATEDIF(D2,DATE(G$1+9,1,ROW(INDIRECT("1:365"))),"Y")-9,{-9,0;1,5;10,10;20,15}))

学习，写得太好了！

sunya_0529 · 发表于 2009-6-12 13:31

原帖由 jackeroo 于 2009-6-12 10:51 发表

sunya在列出各种巧妙公式的时候，最好能做个解释，方便一些初学者理解，谢谢。

多谢版主提醒，下次写一些简单的思路和实现原理附在后面。

其实上面这个公式还是借鉴了前面你和wangtx218兄的思路，略作简化而已，看完通篇帖子的朋友相信应该可以完全理解公式的含义和运算原理。

这里简单介绍一下这个公式“=AVERAGE(LOOKUP(DATEDIF(D2,DATE(H$1+9,1,ROW(INDIRECT("1:365"))),"Y")-9,{-9,0;0,6;1,12;10,15}))”——

原理：
　　在统计年度里（比如说2009年），计算每一天可以享受的年假天数的上限（比如到2009-2-1日如果满1年的话，可以享受15天），算出全年的平均值即为全年可以享受的年假总数了。
　　这个公式和wangtx218兄在45楼的公式原理一样，只不过做了两处简化，一个是用Average()函数来代替SUM()/365，求平均的概念更直观；另一个是利用Dateif()里的第二个参数，通过加上一个“9”后，确保终止日期始终大于起始日期，就不会产生错误值，也就不需要通过IF()来排除错误值，省下不少的字符。

运算过程：
“DATE(H$1+9,1,ROW(INDIRECT("1:365")))”，会产生一个日期数组，相对于H$1后9年的全年日期列表，如果H$1为2009的话，产生的数组为：
2018-1-1，2018-1-2，……，2018-12-30，2018-12-31
“DATEDIF(D2,DATE(H$1+9,1,ROW(INDIRECT("1:365"))),"Y")-9”，返回365个从D2起计算的年间隔，通过“-9”返回与H$1同年度的日期年间隔，方便后面的Lookup()查找对应的每一天可以享受的年假天数的上限。

不知道说清楚了没有，呵呵~~

		自动登录	找回密码
密码			免费注册

[求助] 特别复杂的年休假-请教!

评分

评分

评分