編按：
在繁忙的都市工作中，快遞已然成了公司傳遞信件和物件的首要選擇。所以，在眼光繚亂的報價數(shù)據(jù)中做各種查詢工作也已然成了打工人的日常。這種日常又瑣碎的工作，有時肉眼可以輕松搞定，但是更多時候，肉眼卻只會帶來低效率和失誤。那么，如何快速查詢快遞價格，并秒速對比后做出最優(yōu)判斷呢？其實可以用Excel這樣做……

現(xiàn)在各個快遞公司間的競爭非常厲害，都為了不同的服務(wù)對象制定了不同的收價服務(wù)，試圖搶占更多的用戶。

所以，當你選擇快遞公司時，面對眼前這樣一份報價單，眼花就是一定的了。

那么，如何快速精準的找到今日份最佳合作公司呢？這事當然是交給Excel了！

高效地應(yīng)用Excel查詢一件貨物的運費，只需要動動鼠標，輸入幾個數(shù)據(jù)，就可以得到費用總和。效果如下。

下面是兩家著名快遞公司，順豐速運（EXPRESS）和DHL的價格清單和分區(qū)。

大家一起來看看，如何在繁雜的數(shù)據(jù)中精確查詢吧！

注意：EXPRESS（順豐）快遞公司有兩個不同業(yè)務(wù)，數(shù)據(jù)分別在“國際標快”和“國際特惠”兩個Excel工作簿中；DHL快遞公司只有一個業(yè)務(wù)，數(shù)據(jù)在名為“暫1”的Excel工作簿中。

一、數(shù)據(jù)整理

1.  設(shè)計格式，創(chuàng)建名稱和數(shù)據(jù)驗證

①  首先需要新建一個Excel工作簿，整理一下收貨地區(qū)。過程就不詳述了，最后的結(jié)果如下，格式可以隨個人喜好調(diào)整。

②  按照下圖所示，點擊菜單欄上的“公式”，打開“名稱管理器”，建立名稱。

③  如下圖所示，創(chuàng)建數(shù)據(jù)驗證。其中E列、F列和G列是三級下拉菜單。

④  在H列創(chuàng)建數(shù)據(jù)驗證。

2.整理源數(shù)據(jù)的結(jié)構(gòu)

為了確保數(shù)據(jù)計算的一致性，大家還需要對源數(shù)據(jù)的結(jié)構(gòu)做一些調(diào)整。

比如下圖中右側(cè)公司的清單上，文件和包裹的最小計費重量是2公斤，而左側(cè)公司的清單上，最小計費重量是2.5公斤。由于不同公司的報價單在細節(jié)上有些許差異，對源數(shù)據(jù)的結(jié)構(gòu)調(diào)整就是必要的前期操作。在這里，作者把右側(cè)清單上的最小計費重量調(diào)整為2.5公斤。

另外，兩家公司對于超重部分的重量規(guī)定也不一樣，因此大家要把這部分的數(shù)據(jù)調(diào)整到相同的起始行，并給他們定好相同的屬于區(qū)域。

3.整理分區(qū)

把各自快遞業(yè)務(wù)的分區(qū)整理出來，如下圖中的M列和N列。

二、設(shè)置計算公式

運費計算規(guī)則：

貨物沒有超重時，直接查詢重量和目的地，即可查到快遞費用；貨物超重時，用重量乘以對應(yīng)的價格來計算出快遞費用。

1.在單元格K15中輸入公式：

=IFERROR(ROUND(IF(I14<=2.5,IF(H14="文件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)),IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))),2),"")

函數(shù)解析：

①     這么長的一串函數(shù)，剔除最外層的IFERROR函數(shù)和ROUND函數(shù)后，其實就是由一個IF函數(shù)構(gòu)成的。

②     IF(H14="文件",VLOOKUP(I14,INDIRECT(F14&"!$B$8:$K$12"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE),VLOOKUP(I14,INDIRECT(F14&"!$B$13:$K$17"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE))這一部分是當判斷條件 “I14<=2.5” 為真時，函數(shù)的運算部分。這部分整體上是一個IF函數(shù)。如果H14是文件，那么就到“$B$8:$K$12”這個地方去查找運費；否則，就到包裹“$B$13:$K$17”那里去查找運 費。

③    確定跳轉(zhuǎn)到相應(yīng)的工作表。需要利用INDIRECT函數(shù)根據(jù)單元格F14中的內(nèi)容跳轉(zhuǎn)到對應(yīng)的工作表中去。

④     這部分中的INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0))組合，其作用是由MATCH函數(shù)確定單元格G14中的國別在N列中的位置，再由INDEX返回分區(qū)（M列）的信息。

⑤     MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1部分是由MATCH函數(shù)返回數(shù)據(jù)區(qū)域的列數(shù)信息，最后由VLOOKUP函數(shù)模糊查找數(shù)據(jù)。

⑥     IF(IF(E14="EXPRESS",I14>=20,I14>=31),VLOOKUP(I14,INDIRECT(F14&"!$B$74:$K$80"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)*I14,VLOOKUP(I14,INDIRECT(F14&"!$B$18:$K$72"),MATCH(INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0)),INDIRECT(F14&"!$C$6:$K$6"),0)+1,TRUE)))這一部分，是當判斷條件“I14<=2.5”為假時，函數(shù)的運算部分。這部分也是一個IF函數(shù)，它可以進行條件判斷。當貨物超重時，通過INDIRECT函數(shù)跳轉(zhuǎn)到超重價格區(qū)域“$B$74:$K$80”，計算運費；當貨物未超重時，則直接在$B$18:$K$72中查找價格。

2.  在單元格K15中輸入公式：=IFERROR(ROUND(K14*(1+J14),2),"")

這是很簡單的數(shù)學計算公式，就不再贅述了。

總結(jié)：

今天的教程本身并沒有什么難度，不要被那么長的公式嚇住了。其實它就是IF函數(shù)配合INDIRECT函數(shù)，根據(jù)不同的條件在不同的工作表區(qū)域中抓取數(shù)據(jù)的常規(guī)操作。

對于今天的教程，大家可以下載課件，多多練習哦~

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

IF函數(shù)，剝洋蔥

用Excel制作一個實現(xiàn)自動提醒補貨和動態(tài)查詢補貨數(shù)量的表

INDEX:函數(shù)中的精確制導導彈，最強大的瘸子

還不會做Excel三級下拉菜單？其實它跟復(fù)制粘貼一樣簡單

版權(quán)申明：

本文作者EXCEL應(yīng)用之家；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。