Excel数据处理与分析之累加区间计算

继续参照8.3.1小节的实例，要求按累加区间的方法对销售提成做出计算。累加区间的计算条件如下：销售额1万元以下的部分，提成比例按0.5%计算；销售额1万～3万元的部分，提成比例按1%计算；销售额3万～5万元的部分，提成比例按1.5%计算；销售额5万～7万元的部分，提成比例按2%计算；销售额7万～9万元的部分，提成比例按2.5%计算；销售额9万～10万元的部分，提成比例按3%计算；销售额10万元以上的部分，提成比例按3.5%计算。区间规则包含下限，不包含上限。例如，销售额85360元，销售提成为10000*0.5%+20000*1%+20000*1.5%+20000*2%+15360*2.5%=1334（元）。

选择C2单元格，输入公式“=SUM（IF（B2-{0；10000；30000；50000；70000；90000；100000}＞0，（B2-{0；10000；30000；50000；70000；90000；100000}）*{0.005；0.005；0.005；0.005；0.005；0.005；0.005}））”，输入完毕后按Enter键结束，并将公式向下填充，即可按要求计算出累加区间的销售提成，结果如图8.3.2-1所示。

在理解的基础上，公式还可以进一步优化，将公式优化为“=SUM（IF（B2*1%%-{0；1；3；5；7；9；10}＞0，（B2*1%%-{0；1；3；5；7；9；10}）/1%%*{0.005；0.005；0.005；0.005；0.005；0.005；0.005}））”，同样可以得到相同的计算结果，如图8.3.2-2所示。

图8.3.2-1　输入计算提成的公式（方法1）

图8.3.2-2　优化后的公式(www.xing528.com)

此公式利用销售额减去计算条件中的全部区间的起始值，结果为一个数组，数组中大于0的，返回其差额分别乘以0.5%，最后使用SUM函数对数组进行求和，乘以0.5%是因为需要补充前面计算所缺少的部分。例如，首先（65360-0=65360）*0.5%，在计算下一个区间（65360-10000=55360）*0.5%时，因为55360已经在上一个区间乘以0.5%了，因此只需要补充前面计算缺少的部分1%-0.5%=0.5%即可。

选择D2单元格，输入公式“=MAX（B2*{0.005；0.01；0.015；0.02；0.025；0.03；0.035}-{0；50；200；450；800；1250；1750}）”，输入完毕后按Enter键结束，并将公式向下填充，计算的结果如图8.3.2-3所示，与C列的计算结果完全一致，如图8.3.2-3所示。

图8.3.2-3　输入计算提成的公式（方法2）

此方法的思路是将销售额分别乘以全部的区间比例，然后减去相应的速算扣除数，在得到的数值中取最大值，即是我们需要的结果。此方法与方法1的思路相反，其中速算扣除数是减去上一步多算的部分。