excel函数实战案例:如何通过公式,自动计算价格

在上一篇文章里面,我们讲了如何整理完成一个规范化的表格,以便于下一步的函数计算。

图片[1]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

最初的信息内容如图所示。

图片[2]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

经过整理,我们得到了表2这样的规范化表格。

现在,我们就通过表2来实现快递费用自动计算,最终实现图中这样的效果:

图片[3]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

第一步、制作查询表格

首先,在表1里面制作好查询表格。

表格有2个条件:目的地和重量。

为了避免使用的时候出错,我们先将2个条件分别设置数据验证。

图片[4]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

选中B7单元格,点击“菜单栏-数据-数据验证”,在“序列”里面去选取来源,来源在表2里面的B列对应区域。

图片[5]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

这样,B7单元格的目的地就实现了下拉菜单选取。

然后,B8单元格要填入重量,就必须为数字,通过数据验证,能够禁止别人输入非数字格式。

图片[6]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

选中B7单元格,点击“菜单栏-数据-数据验证”,在“小数”里面选中“大于”,填入“0”。

图片[7]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

这样,只要在B8单元格输入文字,就会弹出提示框,并且要求重填。

第二步、写函数公式

一个快递的重量,需要用if函数做个判断,判断重量是否超过首重,如果没超过,就直接是首重费用;如果超过了,就应该是首重费用 续重费用。

比较麻烦的是续重费用!

我们先来看一个示例:

图片[8]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

假如快递重量为1.5KG,那么续重的重量是1.5KG-首重1KG=0.5KG。

用int函数对0.5除以1的值进行取整(这里1是续重的标准1KG),得到0。

因此续重费用应该是(0 1)*6=6(这里6是续重的价格6元),得到6。

但另外还有个问题,这些10、1、6、1数据都是变化的,是根据目的地不同而不同,而且以后也可能进行修改,所以都需要用vlookup函数进行查询引用。

图片[9]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

图中案例公式为

=VLOOKUP(“上海”,B2:F32,4,0)

代表着在B列到F列的第2行和第32行这个区域内,在B列查找“上海”,返回右边第4列,也就是续重价格这一列的值,因此结果为6。末尾的参数0表示精准查询。

将这些内容综合起来,我们可以写出一个完整的函数公式。

注意:虽然这里数据很多用的是1,但考虑到这些价格标准随时可能会调整,因此不应该在函数公式里直接用1来做计算。否则下一次修改了价格标准,这个函数公式结果就出错了。

图片[10]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

表1里的B9单元格公式为:

=VLOOKUP(B7,Sheet2!B2:F32,2,0) IF(B8<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0))/VLOOKUP(B7,Sheet2!B2:F32,5,0)) 1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))

看上去很长,别怕,我们来分解一下。

先看一下文字版的:

第一种条件下

=B7目的地的首重价格 (如果B8重量<目的地的首重重量,返回0)=B7目的地的首重价格

第二种条件下

=B7目的地的首重价格 (如果B8重量>=目的地的首重重量,返回续重费用)=B7目的地的首重价格 B7目的地续重费用

续重费用的公式就是将数据全部用vlookup函数进行查询获得。

INT((B8重量-目的地的首重重量)/目的地的续重标准 1)*目的地的续重价格

不过到这一步,还没有结束。

对于数学计算这一类的问题,一定要注意各种临界值的验证。

图片[11]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

当B8输入1.5的时候,结果为10 6=16是正确的。

图片[12]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

但是当B8输入2的时候,结果为10 12=22,是错误的。

因为2KG,其续重为1KG,应该还是10 6才对。

这里就是临界值出了问题,检查会发现,只有重量为2、3、4、5这些续重1KG的整数倍数时候,会出现多增加1个续重单位的问题。

那么,这种情况怎么办呢?

图片[13]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

在int函数部分,我们将B8重量-首重重量这里,再减去一个极小的数字,比如-0.00001,这样int后的结果就不是0,而是小于0,结果就不会出错了。

最终公式为

=VLOOKUP(B7,Sheet2!B2:F32,2,0) IF(B8<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0)-0.00001)/VLOOKUP(B7,Sheet2!B2:F32,5,0)) 1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))

图片[14]-excel函数实战案例:如何通过公式,自动计算价格-职比牛办公资源网

最终,我们只需要在B7里选择目的地,在B8里输入重量,就能自动算出快递费用了。

当然,如果目的地还要精确到市区县,只要有相应的数据,制作为多级下拉菜单就可以了。

总结:

这个案例的函数虽然只用到了if、vlookup、int三个函数,但由于涉及到多个查询引用及计算转换,也还是比较考验综合应用能力的,大家可以多多练习,理顺逻辑思路,提高函数处理能力。

© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容