四个典型案例的函数运用

一、统计收费时间

要求不足一小时的按一小时收费，超过一小时的，不足半小时按半小时算。

数据

公式设置：

小时数        计时
   0.25           1.00
   1.07           1.50
  13.18          13.50
  12.05          12.50
小时数=(C5-B5)*24   注释：时间相减，得到的是按以天为单位的数值所以乘以24才能得到正确的小时数。
计时公式=MAX(1,ROUNDUP(F5/0.5,0)*0.5)  注释：除以0.5是统计有多少个半小时，roundup是把小数位收上来，最后乘以0.5则是换算为小时数。max巧妙解决了不足一小时按一小时计算的问题。


二、计算年终奖月份数

很快到年底了，明年1月初就会讨论年终奖计提的问题了。再次分享年终奖月份计算的公式。

出勤月数公式：

=MIN(12,DATEDIF($D7,DATE($C$4,12,31)+1,"M")+IF(DAY($D7)<=15,1,0))
注释：
1、datedif，把结束日期+1，是为了解决函数本身的bug，这里不详述。
2、if函数把日期里的天取出来，15号前入职的当月算一个月，16号及以后入职当月不算【这个相对公平】；
3、用min函数把工作年限超过12个月的，按12个月算。

请假扣除月数：=ROUNDUP(INT($F7/15)/2,0)
注释：不足15天的按半月，超过15天不足一个月按一个月计算休假月份数。
年终奖月数公式=$E7-$G7


三、计算一个日期段的数据和

设置按员工计算11月份的得分：

员工        得分
A        23
B        23
C        18
D        21
求和公式：
=SUMPRODUCT(($B$3: $B$10=$G3)*($C$3: $ C$10>=VALUE("2019-11-1"))*($C$3: $C$10<=VALUE("2019-11-30"))*$D$3: $D$10)
在公式中输入日期是一个比较麻烦的事情，用value函数即可把文本格式的日期进行转化。



四、计算增长率和达成率


1、增长率计算公式
增减幅度公式：=(C7-D7)/ABS(D7)
IF公式：=IF(D7>0,C7/D7-1,1-C7/D7)

2、达成率计算公式
达成率公式：=1+(C15-D15)/ABS(D15)
IF公式：=IF(D15>0,C15/D15,2-C15/D15)