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

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

所以，當(dāng)你選擇快遞公司時(shí)，面對(duì)眼前這樣一份報(bào)價(jià)單，眼花就是一定的了。

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

高效地應(yīng)用Excel查詢一件貨物的運(yùn)費(fèi)，只需要?jiǎng)觿?dòng)鼠標(biāo)，輸入幾個(gè)數(shù)據(jù)，就可以得到費(fèi)用總和。效果如下。

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

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

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

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

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

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

②  按照下圖所示，點(diǎn)擊菜單欄上的“公式”，打開(kāi)“名稱管理器”，建立名稱。

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

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

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

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

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

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

3.整理分區(qū)

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

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

運(yùn)費(fèi)計(jì)算規(guī)則：

貨物沒(méi)有超重時(shí)，直接查詢重量和目的地，即可查到快遞費(fèi)用；貨物超重時(shí)，用重量乘以對(duì)應(yīng)的價(jià)格來(lái)計(jì)算出快遞費(fèi)用。

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ù)解析：

①     這么長(zhǎng)的一串函數(shù)，剔除最外層的IFERROR函數(shù)和ROUND函數(shù)后，其實(shí)就是由一個(gè)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))這一部分是當(dāng)判斷條件 “I14<=2.5” 為真時(shí)，函數(shù)的運(yùn)算部分。這部分整體上是一個(gè)IF函數(shù)。如果H14是文件，那么就到“$B$8:$K$12”這個(gè)地方去查找運(yùn)費(fèi)；否則，就到包裹“$B$13:$K$17”那里去查找運(yùn) 費(fèi)。

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

④     這部分中的INDEX(INDIRECT(F14&"!$M:$M"),MATCH(G14,INDIRECT(F14&"!$N:$N"),0))組合，其作用是由MATCH函數(shù)確定單元格G14中的國(guó)別在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ù)返回?cái)?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)))這一部分，是當(dāng)判斷條件“I14<=2.5”為假時(shí)，函數(shù)的運(yùn)算部分。這部分也是一個(gè)IF函數(shù)，它可以進(jìn)行條件判斷。當(dāng)貨物超重時(shí)，通過(guò)INDIRECT函數(shù)跳轉(zhuǎn)到超重價(jià)格區(qū)域“$B$74:$K$80”，計(jì)算運(yùn)費(fèi)；當(dāng)貨物未超重時(shí)，則直接在$B$18:$K$72中查找價(jià)格。

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

這是很簡(jiǎn)單的數(shù)學(xué)計(jì)算公式，就不再贅述了。

總結(jié)：

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

對(duì)于今天的教程，大家可以下載課件，多多練習(xí)哦~

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

IF函數(shù)，剝洋蔥

用Excel制作一個(gè)實(shí)現(xiàn)自動(dòng)提醒補(bǔ)貨和動(dòng)態(tài)查詢補(bǔ)貨數(shù)量的表

INDEX:函數(shù)中的精確制導(dǎo)導(dǎo)彈，最強(qiáng)大的瘸子

還不會(huì)做Excel三級(jí)下拉菜單？其實(shí)它跟復(fù)制粘貼一樣簡(jiǎn)單

版權(quán)申明：

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