函数公式、职场模板 、财务应用、分析图表、练习题、财务机器人、快捷键、软件工具、表格合并、图表及可视化、Office 365、Power Query、表格美化、符号作用、生成序列、条件格式、学会骗、一本不正经、避坑指南、数据整理、筛选技巧、日期时间、偷懒宝典、漂亮3D、WPS技巧、PPT技巧、Word技巧
👆上面是分类专题👆
👇下面是最新文章👇
练习题110:
计算员工各年份的在职天数【函数公式】
注:
是计算自然日天数,不是计算工作日
F2:H2单元格区域是标准日期,已用自定义显示为XXXX年
公式其实很简单:
=MAX(MIN(F$2,$D3)-$C3+1-SUM($E3:E3),0)
公式思路:
第一步,
先整简单点,用本年末减入职日期,计算累计入职天数
F3单元格公式:
=F2-C3
将其往右往下拖动填充。
往后面拖动填充时公式出错:
得完善一下,使用正确的引用类型:
相关的知识以前写过文章介绍,新手朋友请先充一下电:
第二步,
有些人没到年底就辞职了,得修正一下。在本年年底和离职日之间取小的那个,与入职日相减。
F3单元格公式:
=MIN(F$2,$D3)-$C3
将其往右往下拖动填充(下同,不再重复本句)。
第三步:
将计算出的天数减掉前面年的合计天数,就是本年的在职天数,
F3单元格公式:
=MIN(F$2,$D3)-$C3-SUM($E3:E3)
从上图可以看出,有些单元格的结果是负数,这肯定是错的。
这些负数应该都为0。其他大于零的单元格还是保持原数。
所以,得在前面的公式最外面用IF函数或最大值函数MAX处理一下,IF有点啰嗦,用MAX简洁些:
F3单元格公式:
=MAX(MIN(F$2,$D3)-$C3-SUM($E3:E3),0)
扩展知识点:
《“偷懒”的技术2:财务Excel表格轻松做》第二章:
根据公司的实际情况,在其基础上加一天:,
=MAX(MIN(F$2,$D3)-$C3-SUM($E3:E3),0)+1
-----------------------------------
如果2022年是计算到今天为止,而不是计算到年底,需要修正完善一下。到今天为止,那些已经离职了的,用原来的公式计算没问题,在职的,只需将今天的日期“替代”离职日期即可。
公式:
=IF($D3<>0,$D3,TODAY())
"<>"在Excel公式中表示“不等于”
将其简写一下:
=IF($D3,$D3,TODAY())
简写的原理:
用IF判断时,第一参数,0为False,非0为True。
将其代入,计算到今天为止的在职天数,其完整公式为:
=MAX(MIN(F$2,IF($D3,$D3,TODAY()))-$C3-SUM($E3:E3),0)+1
本练习题收录于Excel偷懒练习题合集:
本练习题的示例数据,见文末表格。
联系客服