【原】excel查找技巧：單個函數(shù)在區(qū)間查找中的應(yīng)用解析

部落窩教育BLW 2019-08-23

展開全文

excel函數(shù)公式：常用高頻公式應(yīng)用總結(jié)（上）

編按：哈嘍，大家好！說到加班這個話題，相信不少小伙伴們又開始頭疼了。其實加班并不可怕，無意義的加班才可怕。明明幾分鐘就可以完成的事，非折騰到晚上八九點。就拿excel中的區(qū)間查找來說，在我們的工作中隨時都會用到，比如等級評定，績效考核等等。所以我們將推出關(guān)于區(qū)間取值的系列教程，該系列教程共分為3篇，分別是常規(guī)函數(shù)篇、經(jīng)典嵌套函數(shù)篇、數(shù)組函數(shù)篇，將為小伙伴分享9種區(qū)間取值的方法，希望能豐富小伙伴們的excel知識。（本篇為常規(guī)函數(shù)篇）

【引言】區(qū)間取值的問題，在我們?nèi)粘９ぷ髦薪?jīng)常會遇到，比如：銷售提成、等級評定、生產(chǎn)標(biāo)準(zhǔn)核定、績效考核等等，都屬于此類問題，今天就給大家介紹幾種常用的方式方法，旨在豐富大家知識面的同時，也可以對函數(shù)的應(yīng)用拓寬一些眼界。

【數(shù)據(jù)源】先給出今天教學(xué)的源數(shù)據(jù)。建議大家在看后面解決方式之前，先思考一下你會用什么方法處理，會用幾種方法處理，然后再來印證。

數(shù)據(jù)本身沒有什么好說的，要求：根據(jù)B列的數(shù)值，在E列的范圍條件中找到對應(yīng)范圍在H列的區(qū)間系數(shù)，并提取到C列計提系數(shù)中。

（本身來說，此類問題更多的是計算計提金額，我們?yōu)榱烁油怀鎏崛∠禂?shù)的函數(shù)部分，就省略了此環(huán)節(jié)，大家記得下面的每個函數(shù)再乘以B列數(shù)值就可以得到計提金額。）

【附加知識】在給大家解決問題的方式之前，先說一點附加知識?？匆幌律蠄D中F列的表達方式，在日常工作中，我們看到此類問題的條件描述大部分都會寫成E列的格式。但區(qū)間的表示方式，規(guī)范的寫法應(yīng)該如F列的格式，由兩個值組成，以逗號隔開，左邊的值為最小值，右邊的值為最大值，“[ ]”為包含等于，“( )”為不包含等于。無窮符號是在插入符號中輸入，如下。

＊＊＊＊＊＊解題方案＊＊＊＊＊＊

方法一：IF函數(shù)

圖例：

C2單元格函數(shù)：

=IF(B2>=500,0.1,IF(B2>=300,5%,IF(B2>=150,3%,IF(B2>=50,1%,0))))

函數(shù)解析：

對于區(qū)間取值的問題，IF函數(shù)也許是我們最先想到的方式，也是很多同學(xué)用的最多的方式，同時它也確實是最好理解函數(shù)原理的一個。但是小函數(shù)卻有大智慧，對于IF函數(shù)，我們要知道多級IF嵌套的運算順序是從左向右進行的，第一級條件B2>=500為真（TRUE），則返回0.1，為假（FALSE）則進行第二級IF判斷B2>=300，為真（TRUE）則返回5%，為假（FALSE）則進行第三級判斷條件，以此類推。當(dāng)某一級條件為真（TRUE）返回某個值后，函數(shù)也就不再向后運行。

所以很多同學(xué)寫錯了IF嵌套，就是寫錯了這個邏輯關(guān)系，導(dǎo)致返回值不對。記住這個邏輯關(guān)系一定是要么全用>號，從大到小寫；要么全用<號，從小到大寫！

上圖就是全用<號寫的IF函數(shù)，大家注意到?jīng)]有，條件中的=號都是包含在次一級跳點區(qū)間中的，所以我們在函數(shù)中只使用了<號，沒有使用=號。

C2單元格函數(shù)：

=IF(B2<50,0,IF(B2<150,1%,IF(B2<300,3%,IF(B2<500,5%,10%))))

方法二：VLOOKUP函數(shù)

圖例：

C2單元格函數(shù)：

=VLOOKUP(B2,$G$2:$H$6,2,1)

函數(shù)解析：

VLOOKUP函數(shù)用于垂直查詢，一共有四個參數(shù)，前三個我們就不多介紹了，其中第四個參數(shù)是模糊查詢（TRUE）/精確查詢（FALSE）。

VLOOKUP函數(shù)是一個使用率很高的函數(shù)，再絕大多數(shù)的工作環(huán)境中我們都可以使用精確查詢，但是在區(qū)間取值的問題上，必須使用模糊查詢。

使用VLOOKUP函數(shù)區(qū)間取值時，我們的數(shù)據(jù)源必須像G、H列那樣，將數(shù)據(jù)按照“升序”的方式排列出來。當(dāng)然我們也可以使用數(shù)列，同樣數(shù)列的輸入也須按照升序來寫{0,0;50,1%;150,3%;300,5%;500,10%}。數(shù)列的問題不是今天的重點，我們以后寫數(shù)組函數(shù)內(nèi)容的時候再來說它。

方法三：LOOKUP函數(shù)

圖例：

C2單元格函數(shù)：

=LOOKUP(B2,$G$2:$G$6,$H$2:$H$6)

函數(shù)解析：

這里我們使用了LOOKUP函數(shù)的“向量”用法。即在第一個區(qū)域（第2參數(shù)）的查詢值中，返回第二個區(qū)域（第3參數(shù)）中對應(yīng)的值。這個是不是比VLOOKUP函數(shù)更好理解呢？同理，和VLOOKUP函數(shù)一樣，它的數(shù)據(jù)源也需要升序排列。

當(dāng)然我們也可以使用LOOKUP函數(shù)的數(shù)組用法，如下：