問題情景
累進銷售提成：
按照規定的銷售額分級區間，以相應的提成率計算各區間中的提成額，最後進行彙總。
比如銷售額為4750，則累進提成計算式為：=1000*1%+1000*1.2%+1000*1.4%+1000*1.6%+750*1.8%=65.5。
舉例數據如下：
此示例的問題，只用IF的多重嵌套是解決不了的，因為IF最多隻允許有9重嵌套。即使區間沒那麼多，但IF寫出的函數太長太囉嗦，也不是首選。
我們只能另尋他法。
視頻講解：
關鍵操作
第一步：添加輔助列
增加“區間最低值”與“區間提成”兩個輔助列，區間提成是用1000乘以提成率得來的。如下圖：
第二步：逐步分析
以B12單元格4750對應的累進提成為例，4750對應的銷售額區間為F7的4000～4999，所以對應是提成應該為：
C12=(B12－G7)*H7+SUM(I3:I6)
解決三個問題：
- 查找B12對應的區間最低值G7: LOOKUP(B12,$G$3:$G$13)
- 查找G7對應的提成率H7:LOOKUP(B12,$G$3:$G$13,$H$3:$H$13)
- 計算SUM(I3:I6)，I3:I6區域的表示：OFFSET(I3,,,MATCH(B12,$G$3:$G$13,1)-1,)
其中MATCH(B12,$G$3:$G$13,1)，是指在G3:G13區域內查找比B12小的最大值的位次。
C12處的公式可以寫為：
=(B12-LOOKUP(B12,$G$3:$G$12))*LOOKUP(B12,$G$3:$G$12,$H$3:$H$12)+SUM(OFFSET($I$3,,,MATCH(B12,$G$3:$G$13,1)-1,))
但，公式向上和向下填充時，會出現錯誤：
原因是：如果銷售額已經小於1000，MATCH(B12,$G$3:$G$13,1)-1的返回值是0。
銷售額已經小於1000時，前面再無分級區間對應的提成累進，所以，公式再進一步完善為：
=(B3-LOOKUP(B3,$G$3:$G$13))*LOOKUP(B3,$G$3:$G$13,$H$3:$H$13)+IF(B3<1000,0,SUM(OFFSET($I$3,,,MATCH(B3,$G$3:$G$13,1)-1,)))
至此，累進提成計算完畢：
擴展
此種方法可適用於各種分區間累計的問題，比如累進稅率等。
閱讀更多 韓老師講office 的文章
