有陣子沒琢磨Excel的應(yīng)用了。正巧，前兩天有同學(xué)問我，能否在物流中的運(yùn)輸階梯價(jià)情況下，根據(jù)線路、噸數(shù)查找對(duì)應(yīng)的運(yùn)價(jià)，并進(jìn)一步核算出該票運(yùn)輸?shù)倪\(yùn)費(fèi)？這個(gè)問題，同樣會(huì)出現(xiàn)在采購場(chǎng)景，無論是物資采購，還是服務(wù)采購，供應(yīng)市場(chǎng)也經(jīng)常使用階梯報(bào)價(jià)，按照采購物資的數(shù)量或服務(wù)的次數(shù)，給出階梯遞減的報(bào)價(jià)，還貌似優(yōu)惠呢。

在Excel表格中，大致會(huì)出現(xiàn)如下圖所示的情況，要求根據(jù)A列的運(yùn)輸線路和B列的該票運(yùn)單的重量（噸數(shù)），在右側(cè)階梯價(jià)格表中，查出對(duì)應(yīng)的運(yùn)輸單價(jià)，并在D列的核算金額中計(jì)算該票運(yùn)輸?shù)倪\(yùn)費(fèi)。

階梯價(jià)格表的解釋如下：

• F列是與A列相對(duì)應(yīng)的線路名稱，可采用固定的文字、字符串或編號(hào)表示；

• G列是起步重量。在有些運(yùn)輸場(chǎng)景，例如空運(yùn)，承運(yùn)商報(bào)價(jià)表中會(huì)出現(xiàn)最低收費(fèi)（Min. Charge）的情況，如H列，適用于重量特輕時(shí)，最少也得收取這個(gè)費(fèi)用。這類似我們坐出租車，總不能一上車跟司機(jī)說：“哥就坐200米，給兩塊錢，好不啦？”起步重量，通?？刹捎米畹褪召M(fèi)除以下一檔的單價(jià)而得。例如，表中北京-上海線路，起步重量=800/500=1.60噸。這意味著，在該線路中，如果某票運(yùn)輸重量小于1.60噸，則不管重量多輕，也要支付800元；

• I列到M列代表著小于5噸（但大于起步重量）以及大于等于5噸、10噸、15噸和20噸時(shí)的運(yùn)費(fèi)單價(jià)（元/噸）。

Excel需要實(shí)現(xiàn)的功能

1、根據(jù)A列的線路名稱或編碼，在階梯價(jià)格表中尋找對(duì)應(yīng)的報(bào)價(jià)行；

2、根據(jù)B列的噸數(shù)，查找階梯價(jià)格對(duì)應(yīng)重量等級(jí)的單價(jià)；

3、在D列的核算金額中，計(jì)算運(yùn)費(fèi)。如果是低于起步重量，則為最低收費(fèi)；否則，運(yùn)費(fèi)為單價(jià)與重量的乘積。

Excel的解決思路

1、采用MATCH函數(shù)，根據(jù)B列的噸數(shù)，輸出在數(shù)組{0, 起步重量, 5, 10, 15, 20}中的檔次歸屬。例如，6噸，屬于第3檔，大于等于5噸的那檔；0.5噸，屬于第1檔，最低收費(fèi)的那檔。然而，由于不同線路的起步重量可能不同，如G列所示，我們需要構(gòu)造一個(gè)由0噸、單元G4顯示的重量與{5, 10, 15, 20}組合的一個(gè)數(shù)組，那么，可采用CHOOSE函數(shù)。

2、使用CHOOSE函數(shù)構(gòu)造重量等級(jí)數(shù)組，公式可寫為：=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}是人為設(shè)置的6檔序號(hào)。{ }之后的一串值為各檔次重量下限，并借用了單元$J$3:$M$3里的重量值，以及VLOOKUP函數(shù)之前的0噸。CHOOSE函數(shù)里的VLOOKUP(A2,$F$4:$G$6,2)，是起步重量，是根據(jù)線路名稱（如A2）在階梯價(jià)格表中對(duì)應(yīng)線路查找的。以北京-上海線路為例，起步重量是1.60噸。如此，CHOOSE函數(shù)的作用就是構(gòu)造了一個(gè){0,1.6,5,10,15,20}的數(shù)組。同理，北京-天津線路，則構(gòu)造的是{0,1.43,5,10,15,20}這樣一個(gè)數(shù)組。謹(jǐn)記，CHOOSE函數(shù)是一個(gè)非常好用的、構(gòu)造組合數(shù)組的函數(shù)，特別是跨區(qū)域組合，或計(jì)算值、手寫值（常數(shù)）和區(qū)域值的組合！

3、CHOOSE和MATCH函數(shù)的組合應(yīng)用，=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列噸數(shù)在數(shù)組{0,1.6,5,10,15,20}中的檔次，例如，在0~1.6噸區(qū)間的重量MATCH輸出值為1，而在1.6~5噸區(qū)間重量，輸出值為2，依此類推。

4、基于上述MATCH函數(shù)輸出的檔次，進(jìn)一步采用VLOOKUP函數(shù)，查找在表格F4:M6中的線路，以及該線路對(duì)應(yīng)上述重量檔次+2列的單價(jià)來。靈魂拷問：為毛要+2呢？因此，C列單價(jià)查詢的公式可最終寫為：=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列最終計(jì)算運(yùn)費(fèi)：=IF(C2=VLOOKUP(A2,$F$4:$M$6,3),C2,B2*C2)。這個(gè)就不需我解釋了吧？

好啦，今兒先到這里。Excel其樂無窮！