ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: 曦妈

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

[复制链接]

TA的精华主题

TA的得分主题

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

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



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

公式原理:
假设A员工于日期X刚好工作满1年,那么X所在年份全部按照12天来算年假的话,从11日起X的这些日子Y天里,实际多算了(126)×Y365天。因此,只要用12-[(126)×Y365]就可以得出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})

三、算出需要折算年假天数的年份里多算的年假天数,如上述“公式原理”中的“(126)×Y365天”
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))
计算的是如“(126)”的常数,入职不满一年的是(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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2009-6-10 14:48 | 显示全部楼层
原帖由 sunya_0529 于 2009-6-5 15:31 发表
楼主想要统计多个年度的,用一个公式可能更好一点,看看附件:


交流一下
带薪年假天数090610.rar (6.63 KB, 下载次数: 135)

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-10 17:22 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
谢谢sunya,拜读中。。。真的学到了不少东东啊,太感谢啦!

TA的精华主题

TA的得分主题

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


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

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


  1. {=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,))

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

TA的精华主题

TA的得分主题

发表于 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, 下载次数: 65)

短一点!

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2009-6-11 16:25 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2009-6-12 10:39 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
原帖由 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个字符:

  1. =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”,这样一来又省三个字符,变成:

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


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

带薪年假天数090610.rar

7.63 KB, 下载次数: 67

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 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在列出各种巧妙公式的时候,最好能做个解释,方便一些初学者理解,谢谢。

TA的精华主题

TA的得分主题

发表于 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}))

学习,写得太好了!

TA的精华主题

TA的得分主题

发表于 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()查找对应的每一天可以享受的年假天数的上限。

不知道说清楚了没有,呵呵~~
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-6-16 22:14 , Processed in 0.037342 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表