階梯式計算問題，想必大家都不陌生。常見的情形，如計算綜合所得的個人所得稅、階梯式電價、水價等，都是比較常見的階梯式計算問題。本文以計算銷售提成為例，分享階梯式計算的兩種方法。

1

案例描述

如下圖所示，A1:C4區域為銷售提成表。當銷售額在1萬元以下時，按1%計算提成；當銷售額超過1萬元，不超過3萬元時，超過部分按2%計算提成；當銷售額超過3萬元時，超過部分按3%計算提成。

以計算“皮卡球”的提成為例，銷售額為38000，計算提成的第一種方法為：提成=10000*1% 20000*2% 8000*3%=740。這是計算銷售提成最直觀的方式。

計算提成的第二種方法為：提成=38000*1% (38000-10000)*(2%-1%) (38000-30000)*(3%-2%)=740。

第二個計算公式的邏輯是，首先38000全部按照第一階梯的提成比例1%計算提成；然後超過第一階梯銷售額部分（即38000-10000），按照第二階梯和第一階梯的提成比例差異（2%-1%），補計提銷售提成；最後超過第二階梯銷售額部分（即38000-30000），按照第三階梯和第二階梯的提成比例差異（3%-2%），補計提銷售提成。

理解第二個計算公式的邏輯，對于接下來理解使用Excel函數批量計算銷售提成非常重要。接下來分享的兩種方法，都是由第二個計算公式的計算邏輯構造。

2

MAX函數

計算階梯式提成的第一種方法是使用MAX函數。

如下圖所示，在G2單元格輸入公式：

=F2*1% MAX((F2-10000)*(2%-1%),0) MAX((F2-30000)*(3%-2%),0)

拖動G2單元格填充柄向下複制公式。

MAX函數用于獲取一組數值的最大值。以MAX((F2-10000)*(2%-1%),0)為例，當銷售額沒有超過第一階梯的銷售額上限10000時，(F2-10000)*(2%-1%)為負值。MAX((F2-10000)*(2%-1%),0)返回0。當銷售額超過第一階梯的銷售額上限10000時，(F2-10000)*(2%-1%)為整數，MAX((F2-10000)*(2%-1%),0)返回值為(F2-10000)*(2%-1%)。

3

SUMPRODUCT Text函數

計算階梯式提成的第一種方法是使用SUMPRODUCT TEXT函數。

在G2單元格輸入公式：

=SUMPRODUCT(TEXT(F2-{0,10000,30000},"0;!0")*{0.01,0.01,0.01})

拖動G2單元格填充柄，向下複制公式。

公式解析：

（1）F2-{0,10000,30000}，指F2單元格的銷售額依次減去第一階梯、第二階梯、第三階梯的銷售額下限分界點。當銷售額為38000時，返回的結果為{38000,28000,8000}；當銷售額為5000時，返回的結果為{5000,-5000,-25000}。

（2）TEXT(F2-{0,10000,30000},"0;!0")用于将F2-{0,10000,30000}返回的結果中複制設置為0。代碼“0;!0”，指當數值為正值時，返回數值本身，當數值小于0時，返回0。當銷售額為38000時，Text函數返回的結果為{38000,28000,8000}；當銷售額為5000時，Text函數返回的結果為{5000,0,0}。

（3）SUMPRODUCT函數則将Text函數返回的結果與每個階梯的提成相乘并求和。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!