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

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

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

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

現(xiàn)在，我們就通過表2來實(shí)現(xiàn)快遞費(fèi)用自動(dòng)計(jì)算，最終實(shí)現(xiàn)圖中這樣的效果：

第一步、制作查詢表格

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

表格有2個(gè)條件：目的地和重量。

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

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

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

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

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

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

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

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

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

我們先來看一個(gè)示例：

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

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

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

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

圖中案例公式為

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

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

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

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

表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目的地的首重價(jià)格 （如果B8重量<目的地的首重重量，返回0）=B7目的地的首重價(jià)格

第二種條件下

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

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

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

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

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

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

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

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

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

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

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

最終公式為

=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)算出快遞費(fèi)用了。

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

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

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

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

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

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