二維碼 購物車
部落窩在線教育歡迎您!

多條件判斷,勸你用VLOOKUP函數(shù)模糊查找取代IF函數(shù)的一長串公式!

?

作者:阿碩來源:部落窩教育發(fā)布時間:2021-07-02 11:13:39點擊:4843

分享到:
0
收藏    收藏人氣:0人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。

編按:

IF函數(shù)常常作為工作中處理條件判斷問題的首選!可是當面對需要判斷的條件越多時,用IF函數(shù)寫的公式就越長;越長的IF公式,寫的時候需要注意的小細節(jié)就越多。這讓很多小伙伴常常被困在研究長串嵌套公式的細節(jié)里,拖延了下班時間。其實,你早該淘汰這種笨拙的方法了!用VLOOKUP函數(shù)模糊查找,就能讓你輕松百倍……

 

工作半天,快到午休的時間了,公司同事們要么計劃著中午吃什么好吃的,要么商量著吃完飯去哪兒逛一逛,只有小張一個人愁眉不展,完全沒有心思想這些。只因為上午領(lǐng)導(dǎo)給他布置了一項任務(wù)——完成公司全體員工的統(tǒng)計,需要將員工的年齡劃分成不同的年齡段,然后再按年齡段進行統(tǒng)計。領(lǐng)導(dǎo)說下午上班的時候就要用這個數(shù)據(jù),可是直到同事們都出去了,小張還沒把它搞定?,F(xiàn)在的小張,已經(jīng)處于崩潰的邊緣了。

 

究竟是一張怎么樣的統(tǒng)計表,大家一起來幫小張看看!

 

數(shù)據(jù)如下圖所示,A列為員工姓名,B列為員工年齡,C列為要計算的員工年齡段。公司全部員工的年齡范圍是18歲至60歲,根據(jù)公司要求,需要將員工的年齡段具體劃分為:18-20歲,21-25歲,26-30,31-35歲,36-40歲,41-45歲,46-50歲,51-55歲,56-60歲,一共9個年齡段。

 

 

小張是怎么操做的呢?

他的辦法就是用IF函數(shù)一層一層地嵌套,對年齡區(qū)間依次進行判斷。

 

先判斷員工年齡是否在18-20歲這個區(qū)間,如果在,則返回“18-20歲”,如果不在18-20歲這個區(qū)間,則判斷員工年齡是否在21-25歲這個區(qū)間,如果在,則返回“21-25歲”,如果不在21-25歲這個區(qū)間,則判斷員工年齡是否在26-30歲這個區(qū)間……然后就這樣一個區(qū)間一個區(qū)間判斷下去,直到完成所有年齡段的判斷。

 

IF函數(shù)是Excel中的基礎(chǔ)函數(shù)了,小張原以為這是一個簡單的事兒。可是,小張在寫IF函數(shù)的過程中,他感覺簡直不能更崩潰了。

 

IF函數(shù)的難點:

- 因為IF函數(shù)嵌套的層數(shù)太多,經(jīng)常寫著寫著,就忘記寫到哪一層了;

- 隨著嵌套層數(shù)的增加,函數(shù)中的括號越來越多,一不小心少寫或多寫一個,就出錯;

- 年齡段的返回值需要用引號括起來,引號中還要漢字,所以就要在漢字和英文輸入法間來回切換,也很麻煩;

- 還有就是,沒寫完的函數(shù),在Excel中不能進行保存,如果一不小心碰到其他的單元格,則前面辛辛苦苦寫下的嵌套函數(shù)就白寫了。

 

總之,種種意想不到的狀況,導(dǎo)致小張一上午就把屁股“粘貼”在工位上了,心塞!

 

臨近下午上班時間,同事們陸續(xù)回來后,悲催的小張才終于把公式寫出來了,大家感受一下:

 

=IF(AND(B2>=18,B2<=20),"18-20",IF(AND(B2>=21,B2<=25),"21-25",IF(AND(B2>=26,B2<=30),"26-30",IF(AND(B2>=31,B2<=35),"31-35",IF(AND(B2>=36,B2<=40),"36-40",IF(AND(B2>=41,B2<=45),"41-45",IF(AND(B2>=46,B2<=50),"46-50",IF(AND(B2>=51,B2<=55),"51-55","56-60"))))))))

 

別說寫這個公式,是不是看到它,就讓人絕望?

 

 

那么,有沒有什么函數(shù),能夠高效地解決小張面臨的這個問題呢?當然有了!答案就是那個人見人愛、花見花開的函數(shù)界的“大眾情人”——VLOOKUP函數(shù)。不過,更確切地說,應(yīng)該是VLOOKUP函數(shù)的模糊查詢功能。

 

只需要在C2中輸入 “=VLOOKUP(B2,E:F,2,1)”即可。

 

 

下面,大家就來具體學(xué)習一下公式是如何通過VLOOKUP模糊查詢功能來實現(xiàn)小張需求的。

 

關(guān)于模糊查詢,有一個很簡單但是很重要的概念:查詢小于等于某值的最大值。

 

可能有一點繞,小伙伴們可以多讀幾遍這句話,并把它記牢。打一個形象的比方,如果有重要的賓客來公司訪問,在理論上應(yīng)由公司一把手接待,如果一把手在的話,則由他接待;如果偏巧一把手不在的話,那么應(yīng)由職位上小于等于一把手的那個領(lǐng)導(dǎo)——也就是公司二把手接待,說白了,就是“老大在家找老大,老大不在找老二”。

 

對模糊查詢的概念有了理解之后,接下來,就該研究研究如何操作了。

 

熟悉VLOOKUP函數(shù)的小伙伴們都知道,VLOOKUP函數(shù)的第一參數(shù),是查詢值,在本例中,即為B列中的員工年齡;

VLOOKUP的第四參數(shù),是查詢方式,其為0時,是精確查詢,其為1時,是模糊查詢,因為要本例中我們要使用的是VLOOKUP函數(shù)的模糊查詢功能,所以第四參數(shù)為1。

 

現(xiàn)在,四個參數(shù)就已經(jīng)確定了兩個。接下來,大家來看一下第二、第三參數(shù)應(yīng)該是什么?

大家知道,VLOOKUP函數(shù)的第二參數(shù)是查詢區(qū)域,而第三參數(shù)是返回值在查詢區(qū)域中的列序號,所以,只要第二參數(shù)(即查詢區(qū)域)確定了,第三參數(shù)就應(yīng)運而生了。所以,問題就轉(zhuǎn)化為,我們要構(gòu)建一個查詢區(qū)域來作為VLOOKUP函數(shù)的第二參數(shù)。

 

如果這個查詢區(qū)域構(gòu)建出來,就可以順利解決小張面臨的問題了。筆者構(gòu)建了一個查詢區(qū)域,如下圖中E列、F列所示。

 

 

E列中的數(shù)據(jù)為分段點,它的值,分別是各個年齡段的起始值。F列中的數(shù)據(jù)為分段點對應(yīng)的就是分段內(nèi)容,在本例中即為年齡段。這里面有一個邏輯,就是如何將分段點和分段內(nèi)容對應(yīng)起來。分段內(nèi)容是一種標識,它用來標識各分段點所對應(yīng)的區(qū)間段,一般來說,分段內(nèi)容標識的是分段點(注意:包含此分段點)至下一個分段點(注意:不包含此分段點)之間的數(shù)據(jù)。

 

18這個分段點為例,它對應(yīng)的是從18歲至下一個分段點21之間的所有年齡,即 18歲、19歲、20歲,亦即18-20歲。以此類推,21對應(yīng)的是21歲、22歲、23歲、24歲、25歲,亦即21-25……56對應(yīng)的即為56歲、57歲、58歲、59歲、60歲,亦即56-60歲。

 

下面,大家再從各個員工年齡的角度,來看一下構(gòu)造出的查詢區(qū)域和VLOOKUP函數(shù)之間的關(guān)系,如下圖所示。

 

假設(shè)大家要查找的員工年齡是18歲,那么查找的是小于等于18的最大值,即為18,亦即實際查找值為18,則對應(yīng)的年齡段是18-20歲;假設(shè)我們要查詢的員工年齡是19歲,那么查詢的是小于等于19的最大值,即為18,亦即實際查找值為18,則對應(yīng)的年齡段是18-20歲;其余以此類推。

 

 

好了,弄清模糊查詢的邏輯和查詢區(qū)域的構(gòu)建方法之后,接下來,大家只要寫一下VLOOKUP函數(shù)的公式就行了。

C2中輸入 “=VLOOKUP(B2,E:F,2,1)”,然后下拉復(fù)制填充,即可得到員工的年齡段,如下圖所示。

 

 

這樣,通過VLOOKUP函數(shù),就輕松地解決了小張面臨的問題。而且采用這種方式,不僅寫函數(shù)的時候輕松,而且函數(shù)的擴展性非常好。例如,假設(shè)公司中的員工有61-65歲的員工,如果想要對他進行年齡段判斷的話,只需要再加增加一個新的分段點和對應(yīng)的年齡段即可(函數(shù)不用動),如下圖所示。

 

 

VLOOKUP函數(shù)的模糊查詢功能是對數(shù)值型數(shù)據(jù)進行分段的一個非常重要的函數(shù),它廣泛地應(yīng)用于賬齡、工齡、年齡、成績、銷售數(shù)量、銷售金額、天數(shù)等數(shù)值型數(shù)據(jù)的分段問題中。

最后,再給大家留一個小小的練習作業(yè):請用VLOOKUP函數(shù)的模糊查詢,來計算月份對應(yīng)的季度。答案就提前公布在下方了,小伙伴們,趕快來練習一下吧!

 

 

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

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

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

IMG_256

相關(guān)推薦:

IF函數(shù):剝洋蔥

IF函數(shù)的新用法,早會早下班!

模糊查找不等于瞎子摸象,數(shù)值劃分等級和簡稱查全稱你該這么干

怎么用vlookup在兩個查找區(qū)域里查找?

版權(quán)申明:

本文作者阿碩;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。