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

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

經過整理，我們得到了表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

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

先看一下文字版的：

第一種條件下

=B7目的地的首重價格+(如果B8重量

第二種條件下

=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

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

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

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

《Excel天天訓練營》

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

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

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