作者:乔山办公网日期:
返回目录:excel表格制作
今天教大家如何用很短的函数计算阶梯式奖金系数,会涉及到一个新函数 int。
案例:
某公司销售的获客奖金规则如下,请按规则计算出销售当月实际奖金数。
很多人是不是会想到写很多 if 函数嵌套?当然不必这么麻烦。
奖金系数规则说明:
- 净增客户 3 个为合格标准,满足 3 个,奖金系数为 100%
- 标准以上,每多净增 3 个,系数上浮 10%,比如,6 个为 110%,9 个为 120%,上不封顶
- 净增低于 3 个的话,每少 1 个,系数下降 20%,比如,2 为 80%,1 为 60%,到 0% 截止
解决方案:
本案例中,>= 3 个的规则和 <3 个的规则标准完全不同,我们可以分两段公式来分别计算。
1. >= 3 个的公式:
=INT(B2/3)/10+0.9
公式释义:
- INT(number):将数字向下舍入到最接近的整数
- 以下就是 int(n/3) 的结果列表,根据其数值区间,我们得出符合案例需求的公式:INT(B2/3)/10,表示每个区间的值 *10% 上浮比例
- 因为 >=3 的时候,为 100%,不需要上浮,所以我们就在结果后面 +0.9(即 90%)
2. <3 个的公式:
=MAX(0,(B4-3)*0.2+1)
公式释义:
- (B4-3)*0.2:小于 3 的话,每少一个下降 20%,所以我们用实际数字减去 3,得到一个负数,*0.2(即 *20%)
- +1: 表示 +100%,即如果做到 3 的话,基数是 100%,做不到就在这基数上扣
- MAX(0,...):本例是扣到 0 即止,所以我们将 0 和实际数值取其大,如果扣到负数就为 0
3. 现在我们用个 if 函数将两个条件串起来:
=IF(B2>2,(INT(B2/3)/10+0.9),MAX(0,(B2-3)*0.2+1))
公式释义:
- 当 B 列 >2(即 >=3)时,用第一段公式,否则用第二段公式
4. 最后,我们用奖金基数乘以刚才算出来的奖金系数,就得到了实际奖金数