有陣子沒琢磨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其樂無窮！