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

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

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

2、為解決上述問題提供下列3個(gè)公式，本次講解第一個(gè)公式：

=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})

 一、將電價(jià)標(biāo)準(zhǔn)轉(zhuǎn)化成階梯圖形

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

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

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

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

二、用面積圖的方法解釋一個(gè)例子

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

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

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

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

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

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

相關(guān)推薦：

Excel表格怎么計(jì)算工資所得稅？

EXCEL技巧：國(guó)地稅表格的合并技巧

最新評(píng)論