2021-05-12 14:32:11
【Excel技巧】階梯電價、提成和個稅怎麼算
2019-12-06 19:27:33
在生活中有很多需要分段、階梯式計算的情景，如階梯電價、銷售人員提成、個人所得稅等。這些計算有一個共同點：需要分段計算，超過某個範圍後需適用另外一個比例且該比例逐漸遞增。
本經驗以階梯電價的計算為例，利用Excel函數公式來介紹這種計算方法。
1
如下圖所示，A1單元格儲存本月所用電量數（單元格實際輸入的資料是633，通過自定義單元格格式顯示成如圖效果），需根據階梯價格表計算本月應交電費金額。
2
為解決上述問題提供下列3個公式，本次講解第一個公式：=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1-{0,260,600},"0; ")*{0.68,0.05,0.25})=SUMPRODUCT(TEXT(A1%-{0,2.6,6},"0%; ")*{68,5,25})1
如下圖，藍色區域表示的是每一檔標準的電費單價（C23，E22和G21）。其中酒紅色單元格表示的是每一檔電費單價與上一檔單價之差（E23和G22）。中間部分表示的是用電度數（A1單元格的值）在各階梯中的分布。
2
1）假設當月用電量低於或等於260度，那麼該月電費為A1*0.68。2）假設當月用電量大於260度且小於等於600度，那麼該月電費計算為：260度*0.68+(A1-260)度*0.73。整理得到：A1*0.68+(A1-260度)*0.05。理解起來實際意義是這樣的：當月所用電量每度先支付0.68元，超出260度的每度再支付0.05元。3）和步驟2推斷類似，當月用電量大於600度時電費計算結果為：A1度*0.68+(A1-260)度*0.05+(A1-600)度*0.25。實際意義為：所有的電量每度先支付0.68元，超過260度的每度先支付0.05元，最後超過600度的部分每度再額外支付0.25元。1
假設當月用電量為678度，那麼總電費金額=678度*0.68+(678-260)度*0.05+(678-600)度*0.25，也就是下圖中棕色、黃色和藍色三個區域面積之和。
1
選中C70:C72，輸入=(A1-{0;260;600})*{0.68;0.05;0.25}，按Ctrl+Shift+Enter執行公式即可直觀在單元格中看到步驟2中三個顏色塊代表的計算結果。
上述公式引數在步驟二面積圖中的意義如下：
A1-{0;260;600}代表各色塊矩形的長，{0.68;0.05;0.25}代表各色塊矩形的寬。
2
如果A1的值小於分段點，比如說是A1=576度，那麼A1-{0;260;600}={576;316;-24}，其中的負數說明該分段點所在的顏色塊面積不應算在結果之內。因此外層巢狀個IF函數，如果返回值小於0則返回0，也就是：IF(A1-{0,260,600}>0,A1-{0,260,600},)。上述返回結果再乘以{0.68,0.05,0.25}並求和就得到了應交電費總數，完整公式為：=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})相關文章