罗戈网
搜  索
登陆成功

登陆成功

积分  

技巧 | 用Excel解决物流和采购中阶梯价的费用核算

[罗戈导读]Excel其乐无穷!

有阵子没琢磨Excel的应用了。正巧,前两天有同学问我,能否在物流中的运输阶梯价情况下,根据线路、吨数查找对应的运价,并进一步核算出该票运输的运费?这个问题,同样会出现在采购场景,无论是物资采购,还是服务采购,供应市场也经常使用阶梯报价,按照采购物资的数量或服务的次数,给出阶梯递减的报价,还貌似优惠呢。

在Excel表格中,大致会出现如下图所示的情况,要求根据A列的运输线路和B列的该票运单的重量(吨数),在右侧阶梯价格表中,查出对应的运输单价,并在D列的核算金额中计算该票运输的运费。

阶梯价格表的解释如下:

  • F列是与A列相对应的线路名称,可采用固定的文字、字符串或编号表示;

  • G列是起步重量。在有些运输场景,例如空运,承运商报价表中会出现最低收费(Min. Charge)的情况,如H列,适用于重量特轻时,最少也得收取这个费用。这类似我们坐出租车,总不能一上车跟司机说:“哥就坐200米,给两块钱,好不啦?”起步重量,通常可采用最低收费除以下一档的单价而得。例如,表中北京-上海线路,起步重量=800/500=1.60吨。这意味着,在该线路中,如果某票运输重量小于1.60吨,则不管重量多轻,也要支付800元;

  • I列到M列代表着小于5吨(但大于起步重量)以及大于等于5吨、10吨、15吨和20吨时的运费单价(元/吨)。

Excel需要实现的功能

1、根据A列的线路名称或编码,在阶梯价格表中寻找对应的报价行;

2、根据B列的吨数,查找阶梯价格对应重量等级的单价;

3、在D列的核算金额中,计算运费。如果是低于起步重量,则为最低收费;否则,运费为单价与重量的乘积。

Excel的解决思路

1、采用MATCH函数,根据B列的吨数,输出在数组{0, 起步重量, 5, 10, 15, 20}中的档次归属。例如,6吨,属于第3档,大于等于5吨的那档;0.5吨,属于第1档,最低收费的那档。然而,由于不同线路的起步重量可能不同,如G列所示,我们需要构造一个由0吨、单元G4显示的重量与{5, 10, 15, 20}组合的一个数组,那么,可采用CHOOSE函数。

2、使用CHOOSE函数构造重量等级数组,公式可写为:=CHOOSE({1,2,3,4,5,6},0,VLOOKUP(A2,$F$4:$G$6,2),$J$3,$K$3,$L$3,$M$3)。其中,{1,2,3,4,5,6}是人为设置的6档序号。{ }之后的一串值为各档次重量下限,并借用了单元$J$3:$M$3里的重量值,以及VLOOKUP函数之前的0吨。CHOOSE函数里的VLOOKUP(A2,$F$4:$G$6,2),是起步重量,是根据线路名称(如A2)在阶梯价格表中对应线路查找的。以北京-上海线路为例,起步重量是1.60吨。如此,CHOOSE函数的作用就是构造了一个{0,1.6,5,10,15,20}的数组。同理,北京-天津线路,则构造的是{0,1.43,5,10,15,20}这样一个数组。谨记,CHOOSE函数是一个非常好用的、构造组合数组的函数,特别是跨区域组合,或计算值、手写值(常数)和区域值的组合!

3、CHOOSE和MATCH函数的组合应用,=MATCH(B2,CHOOSE({1,2,3,4,5,6},0,VLOOKUP(A2,$F$4:$G$6,2),$J$3,$K$3,$L$3,$M$3),1)。即查找B列吨数在数组{0,1.6,5,10,15,20}中的档次,例如,在0~1.6吨区间的重量MATCH输出值为1,而在1.6~5吨区间重量,输出值为2,依此类推。

4、基于上述MATCH函数输出的档次,进一步采用VLOOKUP函数,查找在表格F4:M6中的线路,以及该线路对应上述重量档次+2列的单价来。灵魂拷问:为毛要+2呢?因此,C列单价查询的公式可最终写为:=VLOOKUP(A2,$F$4:$M$6,MATCH(B2,CHOOSE({1,2,3,4,5,6},0,VLOOKUP(A2,$F$4:$G$6,2),$J$3,$K$3,$L$3,$M$3),1)+2)

5、在D列最终计算运费:=IF(C2=VLOOKUP(A2,$F$4:$M$6,3),C2,B2*C2)。这个就不需我解释了吧?

好啦,今儿先到这里。Excel其乐无穷!

免责声明:罗戈网对转载、分享、陈述、观点、图片、视频保持中立,目的仅在于传递更多信息,版权归原作者。如无意中侵犯了您的版权,请第一时间联系,核实后,我们将立即更正或删除有关内容,谢谢!
上一篇:一季度2600余家网络货运企业共上传运单3000余万单
下一篇:专家观点丨刘岱宗:道路交通是全球交通运输最大的温室气体最终活动
罗戈订阅
周报
1元 2元 5元 10元

感谢您的打赏

登录后才能发表评论

登录

相关文章

2024-12-10
2024-12-10
2024-12-09
2024-12-09
2024-12-09
2024-12-09
活动/直播 更多

12.21上海【线下公开课】仓储精细化管理:从混乱到有序

  • 时间:2024-12-07 ~ 2024-12-08
  • 主办方:冯银川
  • 协办方:罗戈网

¥:2580.0元起

报告 更多

2024年10月物流行业月报-个人版

  • 作者:罗戈研究

¥:9.9元