技巧 | 用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其乐无穷！