二维码 购物车
部落窝在线教育欢迎您!

Excel教程:如何计算工龄补助?给大家整理了20+个函数公式!

 

作者:老菜鸟来源:部落窝教育发布时间:2022-06-20 17:33:08点击:494

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

今天遇到一个工龄补助的问题,让人脑洞大开,于是一次性写出了20个函数公式,赶紧来学习一下吧!相信大家可以学到更多的函数思路,多角度去理解函数的应用方式,达到活学活用的境界!

 

 

曾经发表过一篇教程,因为一个简单问题,引发出一大波的公式函数用法。

可以戳链接查看:

一个四舍五入的问题竟然连LOOKUP都用上了,你们也太会玩了……

今天再来看一个计算工龄补助的问题,领略一题多解的乐趣。

注意:本篇不涉及函数的基本用法讲解。

 

我们的工龄补助计算规则为:

  工龄不足5年无补助;

  工龄满5年不足10年补助100元;

  工龄满10年不足15年补助200元;

  工龄满15年一律补助300元。

 

下图是模拟数据和结果。

 

 

对于这类问题,最容易想到的就是IF函数,所以先来看两个使用IF函数的公式。

IF解法1=IF(B2<5,0,IF(B2<10,100,IF(B2<15,200,300)))

 

 

一共有四种情况:无补助、补助100元、补助200元、补助300元,所以用了三个IF嵌套解决,公式的具体原理就不多说了。

将解法1的逻辑倒过来,就得到了解法2

IF解法2=IF(B2>=15,300,IF(B2>=10,200,IF(B2>=5,100,0)))

 

 

使用多个IF嵌套的时候,一定要理清逻辑顺序,对比这两个公式相信可以加深对IF函数的理解。

在实际应用中,经常会使用LOOKUP来取代IF函数处理这种区间匹配的问题,所以下面的几个公式都是用LOOKUP来解决问题的。

LOOKUP解法1=LOOKUP(B2,{0,0;5,100;10,200;15,300})

 

 

这种用法中LOOKUP用到了两个参数,第二参数{0,0;5,100;10,200;15,300}等于这样的一个42列的数组。

 

 

这样就把一个多次逻辑判断的问题变成了一个数据匹配的问题,不过这里用的是模糊匹配的二分法原理。

详情可以戳链接:二分法

也可以使用三个参数的用法,这就有了下面这个公式。

LOOKUP解法2=LOOKUP(B2,{0,5,10,15},{0,100,200,300})

 

 

接下来的三个lookup公式都是在数组的构造上玩起了花样。

LOOKUP解法3=LOOKUP(B2,{0,1,2,3}*5,{0,1,2,3}*100)

LOOKUP解法4=LOOKUP(B2,{0,5,10,15},{0,1,2,3}/1%)

LOOKUP解法5=LOOKUP(B2/5,{0;1;2;3})/1%

 

 

公式变得越来越简短,但是越来越难以理解,尤其是解法5的思路,确实值得玩味。

再来看一个VLOOKUP的公式,=VLOOKUP(B2,{0,0;5,100;10,200;15,300},2,1)

 

 

这个公式中VLOOKUP的第四参数使用了1,表示模糊匹配,而我们平时用的更多的则是精确匹配,当使用模糊匹配时,第四参数还可以直接省略,公式就变成了:

=VLOOKUP(B2,{0,0;5,100;10,200;15,300},2),注意,这种省略是连同第三参数后面的逗号一起省略的。

还可以将那个经典的INDEX-MATCH组合也用到这个例子里,公式是:

=INDEX({0;100;200;300},MATCH(B2,{0;5;10;15}))

 

 

更甚者可以直接使用MATCH函数来解决这个问题。

MATCH解法1=(MATCH(B2/5,{0,1,2,3})-1)*100

 

 

要注意的是,这个公式里MATCH只用了两个参数,省略第三参数是MATCH大致匹配的用法,具体原理可以参考之前的相关教程。公式还可以这样写=(MATCH(B2,{0,5,10,15})-1)/1%,结果同样正确,这就有点数字游戏的感觉了,有兴趣的同学可以自己琢磨一下两个公式的异同点。

下面这两个公式中的主角是一个我们平时用的不多的CHOOSE函数,当然需要和其他函数组合起来才好用。

CHOOSE解法1=CHOOSE(MATCH(B2,{0,5,10,15}),0,100,200,300)

 

 

还可以将这个公式中的MATCH(B2,{0,5,10,15})这部分换一个思路,就有了CHOOSE解法2

=CHOOSE(MIN(INT(B2/5)+1,4),0,100,200,300)

 

 

以上的这些公式中,都用了查找引用类的函数,但是在一些参数的构造中感觉开始玩数字游戏了。

下面的这几个公式之间就是数字逻辑,都是很基础的函数,不妨试试你能理解几个。

MIN-INT解法1=MIN(INT(B2/5)*100,300)

MIN-INT解法2=MIN(INT(B2/5),3)*100

MIN-INT解法3=MIN(INT(B2/5),3)/1%

MIN-FLOOR解法:=MIN(FLOOR(B2,5),15)*20

除此之外还有两个更烧脑的公式。

SUM-FREQUENCY解法:=SUM(FREQUENCY(B2,{4;9;14})*{0;1;2;3}/1%)

MID-MATCH解法:=MID("0123",MATCH(B2,{0,5,10,15}),1)*100

最后再来一波Excel365新增函数的解法。

XLOOKUP 解法:=XLOOKUP(B2,{0,5,10,15},{0,100,200,300},,-1)

SWITCH-MATCH解法:=SWITCH(MATCH(B2,{0,5,10,15}),1,0,2,100,3,200,4,300)

当然少不了IFS函数,IFS解法1=IFS(B2<5,0,B2<10,100,B2<15,200,B2>=15,300)

IFS解法2=IFS(B2<5,0,B2<10,100,B2<15,200,1,300)

IFS解法3=IFS(B2>=15,300,B2>=10,200,B2>=5,100,B2<5,0)

IFS解法4=IFS(B2>=15,300,B2>=10,200,B2>=5,100,1,0)

 

怎么样,看了这么多解法,你的脑洞开了吗?

以上就是今天的所有内容,感谢你的观看。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

八大查找函数公式,轻松搞定数据中的多条件查找

10种职场人最常用的excel多条件查找方法!(建议收藏)

别怕,VBA入门级教程来了,条件语句很简单!

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。