excel 如何使用函数计算阶梯水价 电价和绩效-英雄云拓展知识分享

现在很多城市都是依照阶梯制在计算水价或电价，阶梯意思就是使用得越多，价格就越贵；其实在很多单位里面，绩效也是这样计算的，以销售为例，销售越多，返利越高，那末如何在Excel中用函数来解决这个问题呢？

先看阶梯水价的例子，然后我们再用函数来编写就能够了。

一 阶梯水价例子的第一种计算方法

在很多地方规定年用水量的阶梯，然后依照月来履行，按月缴纳水费，这里为了简单起见，就以年为单位来测算水价，假定我们年末一次性缴纳水费，和月计算进程都是一样的。

假定某地区的阶梯水价标准以下表所示：

意思就是说针对用量，依照3个阶梯分别计算，若每户年用水量不超过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（元）。

2 阶梯水价的另外一种计算方法

除上面分段计算的思想之外，其实我们也能够依照这样的思路来斟酌，以云淡风轻家为例，水价最少都是5元/立方米，所以我先把300方都依照这价格算，那末就应当是300×5=1500（元），然后超过5元的部份，我先依照7元的补，我先第一次补差价，那末就有120方，补第一次，所以就补120×（7⑸）=240（元）；但是有一部份是依照9元/立方米来收的，到至今为止为止，还只是补够了7元的一部份，那末我们还要继续补，算一下超过260方，还有40方要再缴纳9⑺=2元/立方米，所以就再补差价，2×40=80（元），所以一共就应当是300×5+120×2+40×2=1820，可以看到这类计算方法，得到的结果也是一样的,我们可以用一个图来表示；

3 如何用Excel来实现阶梯水价

其实发现，第2种计算方法，虽然不太好理解，但是很好编函数（最少不用分段来写函数），因而乎我们就利用上面的第2种计算方法来编写了计算的函数，假定用水量填写在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函数来计算，以下图所示：

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函数的第2个参数"0;!0"，是格式代码，一般来说格式代码，是分成4部份，有下面的情势，”A;B;C;D”，记得4部份之间是采取半角状态下的分号进行分隔的；其表示如果是正数用A这类格式显示，负数用B这类格式；0用C这类格式显示；文本用D这类格式显示；我们这个例子中是采取两段式的，就是正数和负数用甚么样的格式来显示，"0;!0"，意思就是说正数就用原来格式显示，负数的话，就让显示成0；

这样0乘任何数也是0，所以就解决了小于180或260的出现负数的情况了，我们只需要在外面套用一个SUMPRODUCT函数，其中函数的参数{5,2,2}表示，先把所有的都依照5元算，然后用量大于180的，再补两元，大于260的，又再补两元，和前面方法2的思路是一样的；

如果没有看明白的话，可以进入我们的视频课堂(关心公众号后，在往期回顾中，点击函数，就能够看到了）去观看视频解释，效果要比文字形象生动得~~，为了节俭大家时间，我视频讲得很快，相信你看了文章再看视频的话，更加容易理解；