几个常用的函数案例分享

一、停车计时

要求：

1、不足一小时按1小时计算；

2、超过1小时，不足半小时按半小时计算。

进场时间B4	离场时间C4	组合公式D4
8:15	8:30	1.00
8:26	9:30	1.50
8:55	19:20	10.50
9:03	21:06	12.50

第一步，计算小时数

=(C5-B5)*24

第二步，不足半小时按半小时计算

公式逻辑：计算有多少个半小时，不足半小时的向上进位

=ROUNDUP((C5-B5)*24/0.5,0)*0.5

第三步，不足1小时按一小时处理

公式逻辑：当小时数小于0.5，向上进位结果为0.5，需要按1小时计

=MAX(1,ROUNDUP((C5-B5)*24/0.5,0)*0.5)

结果：

D5=MAX(1,ROUNDUP((C5-B5)*24/0.5,0)*0.5)

案例延伸：

如果算加班时的约定（提示：用ROUNDDOWN）：

1、不足半小时不算加班时，比如加班29分钟加班时为0；

2、超过半小时的，不足半小时的依然不算加班时，比如加班59分钟，加班时按0.5小时算。

二、计算当年出勤月份数

要求：

1、当年新入员工，如果15号前入职当月计算出勤月，16号及以后入职当月不算出勤月；

2、如果累计病事假不超过15天，不扣除出勤月数，如果超过15天（含），扣除一个月出勤。

工号	姓名	入职日期	出勤月    数E6	病事假/    旷工天数	请假扣    除月数	年终奖    月数H6
		2012/5/1	12	1	0	12
		2002/5/2	12	66	2	10
		2008/5/3	12	15	1	11
		1997/5/4	12	21	1	11
		2016/5/5	12	45	2	10
		2019/5/5	8	5	0	8
		2019/5/16	7	0	0	7

C4输入计算的年份

第一步、计算当年出勤月数

=DATEDIF($D7,DATE($C$4,12,31)+1,"M")

第二步、判断入职日时15号前还是之后

= IF(DAY($D7)<=15,1,0)

第三步、超过12个月的，按12个月算

E7=MIN(12,DATEDIF($D7,DATE($C$4,12,31)+1,"M")+IF(DAY($D7)<=15,1,0))

第四步、病事假扣除出勤月数

G7=ROUNDUP(INT($F7/15)/2,0)

第五步、年终奖月份数

H7 =$E7-$G7

三、一个公式计算达成率/增长率

要求：

1、基期有正也有负数，设置统一公式计算达成率和增长率；

2、不用IF函数。

项目    A6	2019年度    B6	2018年度    C6	增减幅度    D6
销售收入	24,137	18,527	30.28%
税前利润	-855	-778	-9.90%
权益利润	-529	-708	25.28%

一、计算增长率

第一步，用IF函数计算

=IF(D7>0,C7/D7-1,1-C7/D7)

第二步，推演公式

(C7-D7)/D7，当D7为正数，该公式保持现状;

如果D7为负数，公式1-C7/D7分子分母同时乘-1，则：

1-C7/D7=-1*(D7-C7) /ABS(D7)=(C7-D7)/ABS(D7)

所以，无论基期正数或者负数，都可以用公式(C7-D7)/ABS(D7)

项目	增减幅度	IF公式
销售收入	=(C7-D7)/ABS(D7)	=IF(D7>0,C7/D7-1,1-C7/D7)
税前利润	=(C8-D8)/ABS(D8)	=IF(D8>0,C8/D8-1,1-C8/D8)
权益利润	=(C9-D9)/ABS(D9)	=IF(D9>0,C9/D9-1,1-C9/D9)

第三步，推演达成率

达成率在增长率基础上+1即可，所以公式为

项目	达成率	IF公式
销售收入	=1+(C15-D15)/ABS(D15)	=IF(D15>0,C15/D15,2-C15/D15)
税前利润	=1+(C16-D16)/ABS(D16)	=IF(D16>0,C16/D16,2-C16/D16)
权益利润	=1+(C17-D17)/ABS(D17)	=IF(D17>0,C17/D17,2-C17/D17)