Question

excel分段计算的函数及公式

我想计算分值，如在完成销售目标的基础上，每超过销售目标的0%-30%部分，加1分，每超过销售目标的30%-50%部分，加2分，超过50%部分，加5分，求公式如何设置，谢谢。

Answer 1

excel分段计算的函数及公式？在使用EXCEL制作表格时，经常会遇到阶梯分段计算的情况，比如阶梯电费、阶梯提成等等，本篇介绍几种阶梯计算公式的设计思路，加深了解下几种函数的使用方法。
工具原料电脑EXCEL软件
方法/步骤分步阅读
1
/13
在现实生活中，使用阶梯计算的事例还是比较多的，阶梯提成是常见情况之一，根据销售业绩的多少来计算提成，业绩越高，提成的比例越高，收入越可观。
2
/13
根据图中提成比例，来计算各业务员的提成情况，首先想到的是IF函数，根据销售额进行判断，在哪个范围用哪个比例。先设计第一层判断，=IF(B2<=10000,B2*2%,888)，这里使用下设计嵌套函数的技巧，先给出第一层的返回值，后面的暂时还没想好怎么设计，先假定一个数字或其它内容，然后再进行替换，这样打勾或按回车后，已经设计好的部分就不会失去了，详细情况可参见“EXCEL中嵌套函数的设计思路”。
3
/13
再进行第二层函数的设计，为了方便，现在不再在C2中修改公式，而是将C2公式向下填充到C3中，在C3中设计公式，等所有公式都设计完成后，再反向填充即可。当销售额超过第一档次，但没超过第二档时，就要开始分段计算，只有超过第一档的部分，才按第二段的提成比例算，第一档部分仍按第一档的比例提成，这样公式框架为：=IF(B3<=10000,B3*2%,IF(B3<=30000,10000*2%+(B3-10000)*3%,888))，写公式时，也可以将第一段的直接算出来，公式改为：=IF(B3<=10000,B3*2%,IF(B3<=30000,200+(B3-10000)*3%,888))。
4
/13
依此思路，三层嵌套公式框架为：=IF(B4<=10000,B4*2%,IF(B4<=30000,200+(B4-10000)*3%,IF(B4<=50000,800+(B4-30000)*4%,888)))。
5
/13
经过层层嵌套，最终公式为：=IF(B6<=10000,B6*2%,IF(B6<=30000,200+(B6-10000)*3%,IF(B6<=50000,800+(B6-30000)*4%,IF(B6<=80000,1600+(B6-50000)*6%,3400+(B6-80000)*8%))))，再向上回拖，C2的公式为：=IF(B2<=10000,B2*2%,IF(B2<=30000,200+(B2-10000)*3%,IF(B2<=50000,800+(B2-30000)*4%,IF(B2<=80000,1600+(B2-50000)*6%,3400+(B2-80000)*8%))))。
6
/13
可以进一步化简，去掉内部的括号：=IF(B2<=10000,B2*2%,IF(B2<=30000,B2*3%-100,IF(B2<=50000,B2*4%-400,IF(B2<=80000,B2*6%-1400,B2*8%-3000))))。
7
/13
使用IF函数是最基本的思路，但公式比较长。通过观察比较发现，相当于每个档次，直接用总额乘以该档比例，再减去相应档次的扣除数。
8
/13
因此，可以使用LOOKUP函数，根据不同档次，使用不同的计算方法：=LOOKUP(B2,{0,10000,30000,50000,80000},B2*{2,3,4,6,8}%-{0,100,400,1400,3000})。
9
/13
选中公式中相减的后半部分，并按F9功能键，计算出此部分结果，经过比较，可以发现最终结果总是这部分运算结果的最大值，这是因为提成比例是逐步增加的，后档总比前档结果大，但当不足以达到后档时，扣除数也就相应的多扣了，所以达到的本档结果就能取最大值，因此公式可以简化为：=MAX(B2*{2,3,4,6,8}%-{0,100,400,1400,3000})。
10
/13
这个公式作为阶梯计算公式比使用IF函数嵌套公式要简化得多了，但此公式要预先算出扣除数。如果能不预先算扣除数，就省事多了。为此将总额拆解，与各档限额相比较，只有与各档限额相减差为正值的部分才参与运算，但此法是前面各档包含了后面各档的低比例部分，后面只要再增加比例的增值部分即可。
11
/13
因此，只要与各档额度相减，正数取用，负数剔除。文本格式函数TEXT就可以帮上大忙，通过使用不同的格式，可以将负数转化为0，相当于不参与运算。=TEXT(B2-{0,10000,30000,50000,80000},"0;!0")，通过选中并按F9计算出中间结果，可以看到不足部分会按0算。
12
/13
再将此公式的各因数与各自比例相乘，再累加，就得到最终结果，也就是再用一个SUMPRODUCT乘积和函数：=SUMPRODUCT(TEXT(B2-{0,10000,30000,50000,80000},"0;!0")*{2,1,1,2,2}%)，注意一下，最后的比例是依次增加比例，而不是原来的比例，因为在计算高档次时，低档比例已经计算进去了。
13
/13
因此，阶梯公式比较好用的就是后两个，前者要先算出扣除数，后者只要算下增值比例，相对来讲，后者好用些，尤其是在比例逐步下降或有升有降时都可使用，只要计算下相对增幅就可以了，而这种情况下，最大值公式是不适用的。
内容仅供参考并受版权保护

Answer 2

1、首先在Excel表格中输入一组数据，需要根据分段条件设置函数计算结果。

2、在B1单元格中输入分段函数的公式，可以使用IF函数，具体函数可见下图单元格输入的公式。

3、然后点击回车即可得到计算结果，可以看到当对应的数据为小于1150的时候，为数字“0”。

4、然后下拉公式即可得到对应A列单元格数据的计算结果。

追问 : 变成#VALUE了,下拉后计算好像是不对的。 是我弄错了，这个函数是对的，我还更容易理解！ 可不可以写成下面这样： =IF(A1>6000,A1*15%,IF(A1>3000,A1*14%,IF(A1>2000,A1*12%,IF(A1>1150,A1*10%,IF(A1>0,0)))））

登录
【Excel神技】之 公式的分段计算 原创
2018-02-03 11:06:54

米斯特Zh

码龄6年

关注
Excel公式很强大，但有个问题是公式复杂的时候很难让人看懂逻辑。这时候，公式的分段计算功能就体现作用了。

分段计算，很像是软件开发时候的分步debug，断点excel给你设置好了而已。

下图的公式其实挺简单，这里只是用作简单演示一下分段计算的步骤。

选择套用公式的单元格-》选择【公式】菜单-》公式求值：

注意红色标识处的说明：

点击一次【求值】，就会对下划线处的公式求值，结果以斜体显示。下图分别进行了两次【求值】计算：

Answer 3

A 列： 设定的销售目标
B列 ： 实际销售
C列 ： 初始分值（如果没有基础分，则删掉）

(这里考虑低于销售目标的情况，设为0，不然当低于销售目标时会变成False)
=IF(AND(B1>=A1,B1<=1.3*A1),C1+1,IF(AND(B1>1.3*A1,B1<=1.5*A1),C1+2,IF(B1>1.5*A1,C1+5,C1+0)))
没有初始分值的公式：
=IF(AND(B1>=A1,B1<=1.3*A1),1,IF(AND(B1>1.3*A1,B1<=1.5*A1),2,IF(B1>1.5*A1,5,0)))

追问

您好，请问如果加入低于销售目标的话，公式里怎么加呢？
另外如果设置每超过销售目标的0%-30%部分，每超过10万，加1分，每超过销售目标的30%-50%部分，每超过20万，加2分，超过50%部分，每超过50万，加5分，这个又如何设置呢？万分感谢

追答

公式里已经考虑了低于销售目标的情况了，默认为0（或增加0），也可自行更改成别的数值。设置分数的条件要么纯百分比，要么纯数字，数字和百分比不能混用，会出现非预期情况。 销售目标是30万和50万是判断条件都不一样，公式会混乱，得不到正确的值。按增加固定数值的

下面两个公式，是按照固定值判断（50、20、10）的。从描述上看，两者的第一个条件没法对应，之前低于销售目标的条件与现在的不超过10万是等价的。（如果0%-30%改成30%，就等价了，但是前面的两个公式也都要重写了）

有初始分值（销售目标单位为万）：=IF(B1>A1+50),C1+5,IF(AND(B1>=A1+20,B1<=A1+50),C1+2,IF(AND(B1>A1+10,B1<A1+20),C1+1,C1+0)))

无初始分值（销售目标单位为万）

=IF(B1>A1+50),5,IF(AND(B1>=A1+20,B1<=A1+50),2,IF(AND(B1>A1+10,B1<A1+20),1,0)))

Answer 4

加分=lookup((b1-a1)/a1,{0,0.3,0.5},{1,2,5})假设a1是销售目标，b1是实际完成

追问

请问这个公式是测算超过30%-50%部分的加分吗？{1.23}这点没明白

追答

应该是=lookup((b1-a1)/a1,{0,0.3,0.5},{1,2,5})

追问

另外如果设置每超过销售目标的0%-30%部分，每超过10万，加1分，每超过销售目标的30%-50%部分，每超过20万，加2分，超过50%部分，每超过50万，加5分，这个又如何设置呢？万分感谢

追答

不大明白，把问题作为内容（邮件主题一定要包含“excel”,本人以此为依据辨别非垃圾邮件，以免误删）,excel样表文件（把现状和目标效果表示出）作为附件发到yqch134@163.com帮你看下

追问

好的，谢谢，我马上发

追答

已回