在生活中有很多需要分段、階梯式計算的情景，如階梯電價、銷售人員提成、個人所得稅等。這些計算有一個共同點：需要分段計算，超過某個范圍后需適用另外一個比例且該比例逐漸遞增。本經(jīng)驗以階梯電價的計算為例，利用Excel函數(shù)公式來介紹這種計算方法。

應(yīng)用背景和數(shù)據(jù)介紹

1、如下圖所示，A1單元格存儲本月所用電量數(shù)（單元格實際輸入的數(shù)據(jù)是633，通過自定義單元格格式顯示成如圖效果），需根據(jù)階梯價格表計算本月應(yīng)交電費金額。

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")*{0.68,0.05,0.25})

=SUMPRODUCT(TEXT(A1%-{0,2.6,6},"0%;\0")*{68,5,25})

 一、將電價標準轉(zhuǎn)化成階梯圖形

1、如下圖，藍色區(qū)域表示的是每一檔標準的電費單價（C23，E22和G21）。其中酒紅色單元格表示的是每一檔電費單價與上一檔單價之差（E23和G22）。中間部分表示的是用電度數(shù)（A1單元格的值）在各階梯中的分布。

1）假設(shè)當月用電量低于或等于260度，那么該月電費為A1*0.68。

2）假設(shè)當月用電量大于260度且小于等于600度，那么該月電費計算為：260度*0.68+(A1-260)度*0.73。整理得到：A1*0.68+(A1-260度)*0.05。理解起來實際意義是這樣的：當月所用電量每度先支付0.68元，超出260度的每度再支付0.05元。

3）和步驟2推斷類似，當月用電量大于600度時電費計算結(jié)果為：A1度*0.68+(A1-260)度*0.05+(A1-600)度*0.25。實際意義為：所有的電量每度先支付0.68元，超過260度的每度先支付0.05元，最后超過600度的部分每度再額外支付0.25元。

二、用面積圖的方法解釋一個例子

1、假設(shè)當月用電量為678度，那么總電費金額=678度*0.68+(678-260)度*0.05+(678-600)度*0.25，也就是下圖中棕色、黃色和藍色三個區(qū)域面積之和。

三、將上述計算方法用數(shù)組方式表達

1、選中C70:C72，輸入=(A1-{0;260;600})*{0.68;0.05;0.25}，按Ctrl+Shift+Enter運行公式即可直觀在單元格中看到步驟2中三個顏色塊代表的計算結(jié)果。

上述公式參數(shù)在步驟二面積圖中的意義如下：A1-{0;260;600}代表各色塊矩形的長，{0.68;0.05;0.25}代表各色塊矩形的寬。

2、如果A1的值小于分段點，比如說是A1=576度，那么A1-{0;260;600}={576;316;-24}，其中的負數(shù)說明該分段點所在的顏色塊面積不應(yīng)算在結(jié)果之內(nèi)。因此外層嵌套個IF函數(shù)，如果返回值小于0則返回0，也就是：IF(A1-{0,260,600}>0,A1-{0,260,600},)。

上述返回結(jié)果再乘以{0.68,0.05,0.25}并求和就得到了應(yīng)交電費總數(shù)，完整公式為：=SUMPRODUCT(IF(A1-{0,260,600}>0,A1-{0,260,600},)*{0.68,0.05,0.25})

相關(guān)推薦：

Excel表格怎么計算工資所得稅？

EXCEL技巧：國地稅表格的合并技巧

最新評論