日期列是年月日格式,如图。不改变原始数据日期格式的情况下,问题1,如何用公式统计2018/4-2018/6入社男生的人数。
问题2 如何统计2018.4入职的新员工,在未来3个月内的离职总数。也就是2018.4入职,2018.4、2018.5、2018.6 三个月的离职总数。
因为你的工作表中有合并单元格,给编写增加了难度和复杂程度,建议工作表中尽量不用或少用合并单元格。假设数据到第100行,先选择H列,点合并单元格工具按钮,取消H列的合并单元格,然后在H3中输入公式:
H3=IF(B3="","",TEXT(SUMPRODUCT(OFFSET(F3,,,IFERROR(MATCH(,0/(B4:B$100<>""),),COUNT(C3:C$100)),)*(DATEDIF(OFFSET(C3,,,IFERROR(MATCH(,0/(B4:B$100<>""),),COUNT(C3:C$100)),),TODAY()+1,"m")<3)),"[h]:mm"))
同时按Ctrl+Shift+Enter三个键,完成数组公式的输入。下拉到第100行。
再单击B列列标号B选择B列,单击格式刷工具图标后,单击H列列标号H,将B列的合并单元格格式刷到H列。
PS:F列条件格式设置,选择F列的数据,开始选项卡——条件格式——新建规则——使用公式确定要设置格式单元格,在为符合此公式的值设置格式(O)下面的框中输入:
=DATEDIF(C3+1,TODAY(),"m")>2
格式:填充,选择一种偏好的颜色后确定,再确定。
H列的公式和本条件格式的公式中都有一个加1(条件格式公式中加班日期加1)或减1(H列的公式中是当前日期减1),效果一样,目的都是正好3个月的日期,如今天7月30日与4月30日正好是3个月,加班天数还有效。
附:F列的公式:
F3=(E3-D3)*(ROUND((E3-D3)*24,0)>=1)
因为浮点误差,实测需要用round函数才能得到正确的结果。
问题2 如何统计2018.4入职的新员工,在未来3个月内的离职总数。也就是2018.4入职,2018.4、2018.5、2018.6 三个月的离职总数。
追答=COUNTIFS(H:H,">="&DATE(2018,4,1),H:H,"="&DATE(2018,4,1),I:I,"<="&DATE(2018,6,30))
本回答被提问者采纳如何统计2018.4入职的新员工,在未来3个月内的离职总数。也就是2018.4入职,2018.4、2018.5、2018.6 三个月的离职总数。