導(dǎo)讀在上一篇文章里面，我們講了如何整理完成一個規(guī)范化的表格，以便于下一步的函數(shù)計算。最初的信息內(nèi)容如圖所示。經(jīng)過整理，我們得到了表2這樣的規(guī)范化表格。現(xiàn)在，我們....

在上一篇文章里面，我們講了如何整理完成一個規(guī)范化的表格，以便于下一步的函數(shù)計算。

最初的信息內(nèi)容如圖所示。

經(jīng)過整理，我們得到了表2這樣的規(guī)范化表格。

現(xiàn)在，我們就通過表2來實現(xiàn)快遞費用自動計算，最終實現(xiàn)圖中這樣的效果：

第一步、制作查詢表格

首先，在表1里面制作好查詢表格。

表格有2個條件：目的地和重量。

為了避免使用的時候出錯，我們先將2個條件分別設(shè)置數(shù)據(jù)驗證。

選中B7單元格，點擊“菜單欄-數(shù)據(jù)-數(shù)據(jù)驗證”，在“序列”里面去選取來源，來源在表2里面的B列對應(yīng)區(qū)域。

這樣，B7單元格的目的地就實現(xiàn)了下拉菜單選取。

然后，B8單元格要填入重量，就必須為數(shù)字，通過數(shù)據(jù)驗證，能夠禁止別人輸入非數(shù)字格式。

選中B7單元格，點擊“菜單欄-數(shù)據(jù)-數(shù)據(jù)驗證”，在“小數(shù)”里面選中“大于”，填入“0”。

這樣，只要在B8單元格輸入文字，就會彈出提示框，并且要求重填。

第二步、寫函數(shù)公式

一個快遞的重量，需要用if函數(shù)做個判斷，判斷重量是否超過首重，如果沒超過，就直接是首重費用；如果超過了，就應(yīng)該是首重費用 續(xù)重費用。

比較麻煩的是續(xù)重費用！

我們先來看一個示例：

假如快遞重量為1.5KG，那么續(xù)重的重量是1.5KG-首重1KG=0.5KG。

用int函數(shù)對0.5除以1的值進行取整（這里1是續(xù)重的標(biāo)準(zhǔn)1KG），得到0。

因此續(xù)重費用應(yīng)該是（0 1）*6=6（這里6是續(xù)重的價格6元）,得到6。

但另外還有個問題，這些10、1、6、1數(shù)據(jù)都是變化的，是根據(jù)目的地不同而不同，而且以后也可能進行修改，所以都需要用vlookup函數(shù)進行查詢引用。

圖中案例公式為

=VLOOKUP("上海",B2:F32,4,0)

代表著在B列到F列的第2行和第32行這個區(qū)域內(nèi)，在B列查找“上海”，返回右邊第4列，也就是續(xù)重價格這一列的值，因此結(jié)果為6。末尾的參數(shù)0表示精準(zhǔn)查詢。

將這些內(nèi)容綜合起來，我們可以寫出一個完整的函數(shù)公式。

注意：雖然這里數(shù)據(jù)很多用的是1，但考慮到這些價格標(biāo)準(zhǔn)隨時可能會調(diào)整，因此不應(yīng)該在函數(shù)公式里直接用1來做計算。否則下一次修改了價格標(biāo)準(zhǔn)，這個函數(shù)公式結(jié)果就出錯了。

表1里的B9單元格公式為：

=VLOOKUP(B7,Sheet2!B2:F32,2,0) IF(B8<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0))/VLOOKUP(B7,Sheet2!B2:F32,5,0)) 1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))

看上去很長，別怕，我們來分解一下。

先看一下文字版的：

第一種條件下

=B7目的地的首重價格 （如果B8重量<目的地的首重重量，返回0）=B7目的地的首重價格

第二種條件下

=B7目的地的首重價格 （如果B8重量>=目的地的首重重量，返回續(xù)重費用）=B7目的地的首重價格 B7目的地續(xù)重費用

續(xù)重費用的公式就是將數(shù)據(jù)全部用vlookup函數(shù)進行查詢獲得。

INT((B8重量-目的地的首重重量)/目的地的續(xù)重標(biāo)準(zhǔn) 1)*目的地的續(xù)重價格

不過到這一步，還沒有結(jié)束。

對于數(shù)學(xué)計算這一類的問題，一定要注意各種臨界值的驗證。

當(dāng)B8輸入1.5的時候，結(jié)果為10 6=16是正確的。

但是當(dāng)B8輸入2的時候，結(jié)果為10 12=22，是錯誤的。

因為2KG，其續(xù)重為1KG，應(yīng)該還是10 6才對。

這里就是臨界值出了問題，檢查會發(fā)現(xiàn)，只有重量為2、3、4、5這些續(xù)重1KG的整數(shù)倍數(shù)時候，會出現(xiàn)多增加1個續(xù)重單位的問題。

那么，這種情況怎么辦呢？

在int函數(shù)部分，我們將B8重量-首重重量這里，再減去一個極小的數(shù)字，比如-0.00001，這樣int后的結(jié)果就不是0，而是小于0，結(jié)果就不會出錯了。

最終公式為

=VLOOKUP(B7,Sheet2!B2:F32,2,0) IF(B8<VLOOKUP(B7,Sheet2!B2:F32,3,0),0,(INT((B8-VLOOKUP(B7,Sheet2!B2:F32,3,0)-0.00001)/VLOOKUP(B7,Sheet2!B2:F32,5,0)) 1)*VLOOKUP(B7,Sheet2!B2:F32,4,0))

最終，我們只需要在B7里選擇目的地，在B8里輸入重量，就能自動算出快遞費用了。

當(dāng)然，如果目的地還要精確到市區(qū)縣，只要有相應(yīng)的數(shù)據(jù)，制作為多級下拉菜單就可以了。

總結(jié)：這個案例的函數(shù)雖然只用到了if、vlookup、int三個函數(shù)，但由于涉及到多個查詢引用及計算轉(zhuǎn)換，也還是比較考驗綜合應(yīng)用能力的，大家可以多多練習(xí)，理順邏輯思路，提高函數(shù)處理能力。

《excel天天訓(xùn)練營》

《Excel天天訓(xùn)練營》是加薪學(xué)院專為職場人士研發(fā)的excel課程，根據(jù)常見辦公需求精選案例，從此辦公不求人。

目前，課程2.0圖文版本已升級完畢，體系更完整，講解更到位，學(xué)員已突破1000人。課程分為三個篇章：第1章-提高效率（15節(jié)課）、第2章-精通函數(shù)（25節(jié)課）、第3章-美化圖表（10節(jié)課），共50節(jié)內(nèi)容。同時，課程2.0視頻版正在更新中。

注意：購買課程之后，私信發(fā)送“333”，獲取課程配套的excel案例文件，同步實操練習(xí)，學(xué)習(xí)效果更佳！另外，視頻課程現(xiàn)已提供電腦端播放~