在上一篇文章裡面，我們講了如何整理完成一個規範化的表格，以便于下一步的函數計算。

最初的信息内容如圖所示。

經過整理，我們得到了表2這樣的規範化表格。

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

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

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

為了避免使用的時候出錯，我們先将2個條件分别設置數據驗證。

選中B7單元格，點擊“菜單欄-數據-數據驗證”，在“序列”裡面去選取來源，來源在表2裡面的B列對應區域。

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

然後，B8單元格要填入重量，就必須為數字，通過數據驗證，能夠禁止别人輸入非數字格式。

選中B7單元格，點擊“菜單欄-數據-數據驗證”，在“小數”裡面選中“大于”，填入“0”。

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

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

比較麻煩的是續重費用！

我們先來看一個示例：

假如快遞重量為1.5KG，那麼續重的重量是1.5KG-首重1KG=0.5KG。

用int函數對0.5除以1的值進行取整（這裡1是續重的标準1KG），得到0。

因此續重費用應該是（0 1）*6=6（這裡6是續重的價格6元）,得到6。

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

圖中案例公式為

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

代表着在B列到F列的第2行和第32行這個區域内，在B列查找“上海”，返回右邊第4列，也就是續重價格這一列的值，因此結果為6。末尾的參數0表示精準查詢。

将這些内容綜合起來，我們可以寫出一個完整的函數公式。

注意：雖然這裡數據很多用的是1，但考慮到這些價格标準随時可能會調整，因此不應該在函數公式裡直接用1來做計算。否則下一次修改了價格标準，這個函數公式結果就出錯了。

表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重量>=目的地的首重重量，返回續重費用）=B7目的地的首重價格 B7目的地續重費用

續重費用的公式就是将數據全部用vlookup函數進行查詢獲得。

INT((B8重量-目的地的首重重量)/目的地的續重标準 1)*目的地的續重價格

不過到這一步，還沒有結束。

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

當B8輸入1.5的時候，結果為10 6=16是正确的。

但是當B8輸入2的時候，結果為10 12=22，是錯誤的。

因為2KG，其續重為1KG，應該還是10 6才對。

這裡就是臨界值出了問題，檢查會發現，隻有重量為2、3、4、5這些續重1KG的整數倍數時候，會出現多增加1個續重單位的問題。

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

在int函數部分，我們将B8重量-首重重量這裡，再減去一個極小的數字，比如-0.00001，這樣int後的結果就不是0，而是小于0，結果就不會出錯了。

最終公式為

=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裡輸入重量，就能自動算出快遞費用了。

當然，如果目的地還要精确到市區縣，隻要有相應的數據，制作為多級下拉菜單就可以了。

總結：這個案例的函數雖然隻用到了if、vlookup、int三個函數，但由于涉及到多個查詢引用及計算轉換，也還是比較考驗綜合應用能力的，大家可以多多練習，理順邏輯思路，提高函數處理能力。

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

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

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

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!