病毒安全知识,电脑网络技术,手工杀毒方法,答疑解决笔记

导航

« 4G手机耗电的不完全思考Windows删除多余的网络 »

Excel阶梯计费的公式

  阶梯计费方式,随举一例,费率表如下,按费目按天数阶梯计费,前面第一阶梯(1-10)天免费,费目类型后面还有,比如C、D、E……,但我没列出来,用省略号代替了:

天数/费目 A B ……
1-10 0 0 ……
11-20 90 100 ……
21-30 100 150 ……
31- 200 400 ……

在我们玩之前为方便后面说明,再把excel例表与要写公式的地方明确一下,如图,现在要在D列的单元格填写公式计算费用,其中“免费天数”是一个变态的设定,并不是指前面费率表中的1-10天这个公共免费天,可能出于某种理由给予的大于10天的免费天数,一般包含10天的免费天(即大于等于10),但阶梯仍然按总天数计,而不是扣除免费天数后再计阶梯。

Excel阶梯计费的公式

  现在可以开始玩了,以D2单元格为例(A2是费目,B2是总天数,C2是免费天数,D2是计算出的费用结果),怎样在这个Excel表中列出计算公式,如果费率与费目简单的话,当然可以用vlookup函数,但这里费目各类不止一个,而且阶梯计价,一般最简单的是用IF函数:

D2=IF(A2="A",IF(AND(B2>10,B2<21),90*(B2-C2),IF(B2>30,IF(C2<21,90*(20-C2)+100*10+200*(B2-30),IF(C2>30,200*(B2-C2),100*(30-C2)+200*(B2-30))),IF(C2<21,90*(20-C2)+100*(B2-20),100*(B2-C2)))),0)
        +IF(A2="B",IF(AND(B2>10,B2<21),100*(B2-C2),IF(B2>30,IF(C2<21,100*(20-C2)+150*10+400*(B2-30),IF(C2>30,400*(B2-C2),150*(30-C2)+400*(B2-30))),IF(C2<21,100*(20-C2)+150*(B2-20),150*(B2-C2)))),0)
                + ……

有多少费目后面就加多少个,这样写简单明了,以后修改也方便,为了优化,减少AND函数的使用,所以公式里先写了11-20天(这个AND没办法省了),再写了大于30天,这样就不用写AND(B2>20,B2<31),能缩短些公式,对于免费天数的长度的判断也是基于这种考虑。但是这种缩短是有限的,特别是费目多时,一个一个加上去,还是很容易就超出一个excel单元格只能容纳1024个字符的限制,出现公式太长的提示,在Excel2010里会提示:“无法输入指定的公式,因为它包含太多数值、引用和/或名称,超出了当前文件格式的允许值”。

Excel无法输入指定的公式

要缩短长度,要么分单元格引用,把公式分成几个部分分到几个单元格去,要么使用自定义名称(在excel2010里,定义名称在公式-名称管理器),把公式里一些长的引用定义成名称,缩短公式长度,但是定义名称不具有灵活性,要移植到其它excel文档或工作表就不方便,哪怕是按序列复制公式也不会自动变化。所以,我用了下面一种方法,改写公式:

D2=IF(A2="A",90*MAX(MIN(20,B2)-MAX(C2,10),0)+100*MAX(MIN(30,B2)-MAX(C2,20),0)+200*MAX(B2-MAX(C2,30),0),0)
        +IF(A2="B",100*MAX(MIN(20,B2)-MAX(C2,10),0)+150*MAX(MIN(30,B2)-MAX(C2,20),0)+400*MAX(B2-MAX(C2,30),0),0)
                +……

这个公式漂亮多了,每一阶梯都很清晰,用MIN函数判断落在哪个阶梯,用MAX(结果,0)来保证负数为0,即将计算免费阶段,虽然随着费目增加,长度也会增加到可能超出允许值,但是从简洁上来看已经算不错了,不错到什么程度,到了我都不想拿出去让那个当初叫我做这种阶梯计费公式的人用,反正他不懂得欣赏,给他用也是暴殄天物。

  如果要再进一步整理公式,可以用CHOOSE和MATCH函数,用CHOOSE匹配对应费目的计算式,用MATCH查找费目名称,

D2=CHOOSE(MATCH(A2,{"A","B",……},0),90*MAX(MIN(20,B2)-MAX(C2,10),0)+100*MAX(MIN(30,B2)-MAX(C2,20),0)+200*MAX(B2-MAX(C2,30),0),100*MAX(MIN(20,B2)-MAX(C2,10),0)+150*MAX(MIN(30,B2)-MAX(C2,20),0)+400*MAX(B2-MAX(C2,30),0),……)

省略号处可以加除A、B以外的费目及费用计算,此处略。

  如果允许工作表内单独存有费率表内容或可以到对应的excel文档中查到费率表的话,以上公式还可以精简,直接使用LOOKUP等(包括LOOKUP\VLOOKUP\HLOOKUP)函数代入费率,只要短短的一行,

(LOOKUP1)*MAX(MIN(20,B2)-MAX(C2,10),0)+(LOOKUP2)*MAX(MIN(30,B2)-MAX(C2,20),0)+(LOOKUP3)*MAX(B2-MAX(C2,30),0)

就不详写LOOKUP怎么查了,只用LOOKUP1、LOOKUP2、LOOKUP3意思一下,可不是公式就写这样。注意VLOOKUP是查列的,HLOOKUP是查行的,LOOKUP可以查行,也可以查列,不过要查询的值必须按升序排列,而且没有找到完全相等的值时,会选择一个最接近但小于查找值的数据。实际要怎么写得看费率表是什么样式的。如果不同费目阶梯天数不同,那么上面的天数也可以用LOOKUP等函数公式来代替。


>> 除非说明均为原创,如转载请注明来源于http://www.stormcn.cn/post/1715.html

  • 微信订阅号
    微信订阅

最新发表

本站出现的所有广告均不代表本人及本站观点立场 | 关于我 | 网站地图 | 联系邮箱 | 返回顶部
Copyright 2008-2016 www.stormcn.cn. All Rights Reserved. Powered By Z-Blog. 闽ICP备09000343号

闽公网安备 35010202000133号