excel 如何使用函數(shù)計(jì)算階梯水價(jià) 電價(jià)與績(jī)效

現(xiàn)在很多城市都是按照階梯制在計(jì)算水價(jià)或者電價(jià)，階梯意思就是使用得越多，價(jià)格就越貴；其實(shí)在很多單位里面，績(jī)效也是這樣計(jì)算的，以銷(xiāo)售為例，銷(xiāo)售越多，返利越高，那么如何在Excel中用函數(shù)來(lái)解決這個(gè)問(wèn)題呢？

先看階梯水價(jià)的例子，然后我們?cè)儆煤瘮?shù)來(lái)編寫(xiě)就可以了。

一 階梯水價(jià)例子的第一種計(jì)算方法

在很多地方規(guī)定年用水量的階梯，然后按照月來(lái)執(zhí)行，按月繳納水費(fèi)，這里為了簡(jiǎn)單起見(jiàn)，就以年為單位來(lái)測(cè)算水價(jià)，假設(shè)我們年末一次性繳納水費(fèi)，和月計(jì)算過(guò)程都是一樣的。

假設(shè)某地區(qū)的階梯水價(jià)標(biāo)準(zhǔn)如下表所示：

意思就是說(shuō)根據(jù)用量，按照三個(gè)階梯分別計(jì)算，若每戶年用水量不超過(guò)180（含）立方米，按照5元/立方米；180立方米以上的部分至260（含）立方米，按照7元/立方米；260立方米以上的部分按照9元/立方米來(lái)算。這種計(jì)算的方法，是按照分段的思想來(lái)計(jì)算的，把用水量按照不同的價(jià)格區(qū)間分段，然后用用水量×單價(jià)計(jì)算出總金額。

假設(shè)菜販子家里面今年用水量為200立方米，那么其中有180立方米是按照5元/立方米來(lái)計(jì)算的，超過(guò)180立方米的另外20立方米的用水，就按7元/立方米計(jì)算了；所以菜販子家里面應(yīng)該繳納的水費(fèi)為180×5+20×7=1040（元）。

假設(shè)云淡風(fēng)輕家里面，用水量為300立方米，那么就有180立方米是按照5元/立方米算，有80立方米（超過(guò)180到260的部分）按照7元/立方米算，40立方米（超過(guò)260的部分）按照9元/立方米方算，180×5+80×7+40×9=1820（元）。

二 階梯水價(jià)的另外一種計(jì)算方法

除了上面分段計(jì)算的思想以外，其實(shí)我們也可以按照這樣的思路來(lái)考慮，以云淡風(fēng)輕家為例，水價(jià)最少都是5元/立方米，所以我先把300方都按照這價(jià)格算，那么就應(yīng)該是300×5=1500（元），然后超過(guò)5元的部分，我先按照7元的補(bǔ)，我先第一次補(bǔ)差價(jià)，那么就有120方，補(bǔ)第一次，所以就補(bǔ)120×（7-5）=240（元）；但是有一部分是按照9元/立方米來(lái)收的，到目前為止，還只是補(bǔ)夠了7元的一部分，那么我們還要繼續(xù)補(bǔ)，算一下超過(guò)260方，還有40方要再繳納9-7=2元/立方米，所以就再補(bǔ)差價(jià)，2×40=80（元），所以一共就應(yīng)該是300×5+120×2+40×2=1820，可以看到這種計(jì)算方法，得到的結(jié)果也是一樣的,我們可以用一個(gè)圖來(lái)表示；

三 如何用Excel來(lái)實(shí)現(xiàn)階梯水價(jià)

其實(shí)發(fā)現(xiàn)，第二種計(jì)算方法，雖然不太好理解，但是很好編函數(shù)（至少不用分段來(lái)寫(xiě)函數(shù)），于是乎我們就利用上面的第二種計(jì)算方法來(lái)編寫(xiě)了計(jì)算的函數(shù)，假設(shè)用水量填寫(xiě)在B8單元格中，那么我們?cè)贐9單元格中，輸入函數(shù) =SUMPRODUCT(TEXT(B8-{0,180,260},"0;!0")*{5,2,2})

函數(shù)很簡(jiǎn)單，主要就是兩個(gè)，其中外層的SUMPRODUCT是用來(lái)把兩個(gè)參數(shù)乘積再相加的，比如說(shuō)，菜販子今天去買(mǎi)菜，白菜2元/公斤，買(mǎi)了2公斤，豬肉20元/公斤，買(mǎi)了1公斤，蘋(píng)果10元/公斤，買(mǎi)了3公斤，問(wèn)用了多少錢(qián)？我們就可以使用單價(jià)乘以數(shù)量再相加，可得2×2+20×1+10×3=54（元），也可以使用SUMPRODUCT函數(shù)來(lái)計(jì)算，如下圖所示：

B7單元格中輸入的函數(shù)為 =SUMPRODUCT(B2:B4,C2:C4)，意思就是說(shuō)B2*C2+B3*C3+B4*C4，即把對(duì)應(yīng)位置上面的數(shù)據(jù)相乘，然后再相加。

而B(niǎo)8-{0,180,260}中，有一個(gè)常量數(shù)組，相減就代表的是實(shí)際的用水量300分別減去0,180,260，得到的結(jié)果為300,120,40;這種相減都是正數(shù)，倒好計(jì)算，但是有可能用水量不到180或者是不到260，那么后面相減的就可能出現(xiàn)負(fù)數(shù)，為了避免相減后出現(xiàn)負(fù)數(shù)，就使用TEXT(B8-{0,180,260},"0;!0")，把負(fù)數(shù)強(qiáng)制性的轉(zhuǎn)換成0；

其中TEXT函數(shù)的第二個(gè)參數(shù)"0;!0"，是格式代碼，一般來(lái)講格式代碼，是分成四部分，有下面的形式，”A;B;C;D”，記得四部分之間是采用半角狀態(tài)下的分號(hào)進(jìn)行分隔的；其表示如果是正數(shù)用A這種格式顯示，負(fù)數(shù)用B這種格式；0用C這種格式顯示；文本用D這種格式顯示；我們這個(gè)例子中是采用兩段式的，就是正數(shù)和負(fù)數(shù)用什么樣的格式來(lái)顯示，"0;!0"，意思就是說(shuō)正數(shù)就用原來(lái)格式顯示，負(fù)數(shù)的話，就讓顯示成0；

這樣0乘任何數(shù)也是0，所以就解決了小于180或者260的出現(xiàn)負(fù)數(shù)的情況了，我們只需要在外面套用一個(gè)SUMPRODUCT函數(shù)，其中函數(shù)的參數(shù){5,2,2}表示，先把所有的都按照5元算，然后用量大于180的，再補(bǔ)兩元，大于260的，又再補(bǔ)兩元，和前面方法二的思路是一樣的；

如果沒(méi)有看明白的話，可以進(jìn)入我們的視頻課堂(關(guān)注公眾號(hào)后，在往期回顧中，點(diǎn)擊函數(shù)，就可以看到了）去觀看視頻解釋，效果要比文字形象生動(dòng)得~~，為了節(jié)約大家時(shí)間，我視頻講得很快，相信你看了文章再看視頻的話，更加容易理解；