本文為雲淡風輕微課堂(word_excel_ppt)原創文章，轉載請注明來源；

現在很多城市都是按照階梯制在計算水價或者電價，階梯意思就是使用得越多，價格就越貴；其實在很多單位裡面，績效也是這樣計算的，以銷售為例，銷售越多，返利越高，那麼如何在Excel中用函數來解決這個問題呢？

先看階梯水價的例子，然後我們再用函數來編寫就可以了。

一 階梯水價例子的第一種計算方法

在很多地方規定年用水量的階梯，然後按照月來執行，按月繳納水費，這裡為了簡單起見，就以年為單位來測算水價，假設我們年末一次性繳納水費，和月計算過程都是一樣的。

假設某地區的階梯水價标準如下表所示：

水價表

意思就是說根據用量，按照三個階梯分别計算，若每戶年用水量不超過180（含）立方米，按照5元/立方米；180立方米以上的部分至260（含）立方米，按照7元/立方米；260立方米以上的部分按照9元/立方米來算。這種計算的方法，是按照分段的思想來計算的，把用水量按照不同的價格區間分段，然後用用水量×單價計算出總金額。

假設菜販子家裡面今年用水量為200立方米，那麼其中有180立方米是按照5元/立方米來計算的，超過180立方米的另外20立方米的用水，就按7元/立方米計算了；所以菜販子家裡面應該繳納的水費為180×5 20×7=1040（元）。

假設雲淡風輕家裡面，用水量為300立方米，那麼就有180立方米是按照5元/立方米算，有80立方米（超過180到260的部分）按照7元/立方米算，40立方米（超過260的部分）按照9元/立方米方算，180×5 80×7 40×9=1820（元）。

計算方法

二 階梯水價的另外一種計算方法

除了上面分段計算的思想以外，其實我們也可以按照這樣的思路來考慮，以雲淡風輕家為例，水價最少都是5元/立方米，所以我先把300方都按照這價格算，那麼就應該是300×5=1500（元），然後超過5元的部分，我先按照7元的補，我先第一次補差價，那麼就有120方，補第一次，所以就補120×（7-5）=240（元）；但是有一部分是按照9元/立方米來收的，到目前為止，還隻是補夠了7元的一部分，那麼我們還要繼續補，算一下超過260方，還有40方要再繳納9-7=2元/立方米，所以就再補差價，2×40=80（元），所以一共就應該是00×5 120×2 40×2=1820，可以看到這種計算方法，得到的結果也是一樣的,我們可以用一個圖來表示；

第二種計算方法

三 如何用Excel來實現階梯水價

其實發現，第二種計算方法，雖然不太好理解，但是很好編函數（至少不用分段來寫函數），于是乎我們就利用上面的第二種計算方法來編寫了計算的函數，假設用水量填寫在B8單元格中，那麼我們在B9單元格中，輸入函數 =SUMPRODUCT(TEXT(B8-{0,180,260},"0;!0")*{5,2,2})

函數很簡單，主要就是兩個，其中外層的SUMPRODUCT是用來把兩個參數乘積再相加的，比如說，菜販子今天去買菜，白菜2元/公斤，買了2公斤，豬肉20元/公斤，買了1公斤，蘋果10元/公斤，買了3公斤，問用了多少錢？我們就可以使用單價乘以數量再相加，可得2×2 20×1 10×3=54（元），也可以使用SUMPRODUCT函數來計算，如下圖所示：

SUMPROUDCT

B7單元格中輸入的函數為 =SUMPRODUCT(B2:B4,C2:C4)，意思就是說B2*C2 B3*C3 B4*C4，即把對應位置上面的數據相乘，然後再相加。

而B8-{0,180,260}中，有一個常量數組，相減就代表的是實際的用水量300分别減去0,180,260，得到的結果為300,120,40;這種相減都是正數，倒好計算，但是有可能用水量不到180或者是不到260，那麼後面相減的就可能出現負數，為了避免相減後出現負數，就使用TEXT(B8-{0,180,260},"0;!0")，把負數強制性的轉換成0；

其中TEXT函數的第二個參數"0;!0"，是格式代碼，一般來講格式代碼，是分成四部分，有下面的形式，”A;B;C;D”，記得四部分之間是采用半角狀态下的分号進行分隔的；其表示如果是正數用A這種格式顯示，負數用B這種格式；0用C這種格式顯示；文本用D這種格式顯示；我們這個例子中是采用兩段式的，就是正數和負數用什麼樣的格式來顯示，"0;!0"，意思就是說正數就用原來格式顯示，負數的話，就讓顯示成0；

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

階梯電價

如果沒有看明白的話，可以進入我們的專欄課堂(就是下方的專欄卡片）去觀看視頻解釋，效果要比文字形象生動得~~，為了節約大家時間，我視頻講得很快，相信你看了文章再看視頻的話，更加容易理解；

函數的代碼，在上面複制就可以了，你還可以關注 word_excel_ppt 在裡面回複 階梯水價 系統會自動發送給你；