返回列表 发帖

几个常用的函数案例分享

一、停车计时

要求:

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)

  
收藏 分享
分享到: QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
jinwa/金蛙

返回列表