有阵子没琢磨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吨时的运费单价(元/吨)。
1、根据A列的线路名称或编码,在阶梯价格表中寻找对应的报价行;
2、根据B列的吨数,查找阶梯价格对应重量等级的单价;
3、在D列的核算金额中,计算运费。如果是低于起步重量,则为最低收费;否则,运费为单价与重量的乘积。
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其乐无穷!
跨境物流公司社招仓库操作经理
4655 阅读美团社招物流产品运营、计划专家、驻仓品控、水产品控负责人、供应链品控负责人等
3493 阅读SHEIN社招资深/高级物资管理专员、资深/高级计划岗位、仓储经理(英语)、资深物流运营专员、海外仓项目经理
3455 阅读九毛九集团2025秋季校园招聘供应链类岗位
3349 阅读中国区精益物流专员招聘!梅特勒托利多测量设备(上海)有限公司
3216 阅读吉利社招海外供应链规划研究岗、海外仓储管理岗、物流单证岗
3191 阅读【招聘】联宇物流,职等你来!最新岗位来袭
3141 阅读宇通2025届秋招补录物流类岗位
2838 阅读顺丰社招储备人力资源部负责人
2820 阅读淘天物流部社招商务采购、商业数据分析、履约产品、产品经理-杭州
2653 阅读