比VLOOKUP重要,更容易讓你晉升高手的函數(shù),就包含在這三大經(jīng)典嵌套公式中
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2019-08-26 15:04:51點(diǎn)擊:4930
編按:
哈嘍,大家好!在上一期區(qū)間查詢的教程中,我們掌握了IF、LOOKUP、VLOOKUP三種最基礎(chǔ)的函數(shù)解法,相信小伙伴們再次面對區(qū)間查詢的問題時也能沉著應(yīng)對啦。但僅僅三種基礎(chǔ)的函數(shù)解法怎么夠,今天我們要說的是比VLOOKUP函數(shù)更厲害的三大經(jīng)典嵌套組合。學(xué)會了這三種經(jīng)典嵌套組合,相信小伙伴們也能在職場上縱橫一番了。
【引言】
Excel函數(shù)是Excel重要的組成部分,400多個工作表函數(shù)(不包含宏表函數(shù)和VBA函數(shù)),每一個都有其實(shí)際存在的意義,只是我們沒有使用到而已。而將一個函數(shù)的返回值作為另外一個函數(shù)的參數(shù)參與運(yùn)算的方式就是函數(shù)的嵌套,此類寫法的函數(shù)過程就是“嵌套函數(shù)”。
我們繼續(xù)上次的“區(qū)間取值”,借此學(xué)習(xí)一些比較經(jīng)典的嵌套函數(shù)是如何解決此類問題的。(本篇為經(jīng)典嵌套函數(shù)篇)
【數(shù)據(jù)源】
要求:根據(jù)B列的數(shù)值,在E列的范圍條件中找到對應(yīng)范圍在H列的區(qū)間系數(shù),并提取到C列計提系數(shù)中。
【解題方案】
方法四:INDEX+MATCH函數(shù)
圖例:
C2單元格函數(shù):
=INDEX($H$2:$H$6,MATCH(B2,$G$2:$G$6,1))
函數(shù)解析:
INDEX+MATCH函數(shù)的方式,應(yīng)該也算是某些同學(xué)的解題思路之一。只要了解了函數(shù)的語法,這個方法并不難。
INDEX函數(shù),在使用上有兩種方式:數(shù)組形式、引用形式。我們今天用到的是數(shù)組形式。
INDEX函數(shù)語法:INDEX(array, row_num, [column_num]),其解釋可以理解為,在一個區(qū)域中,找到指定行號和列號的交叉點(diǎn),將其返回至單元格中。
因為我們是在H2:H6這一列區(qū)域中提取值,所以我們只用了row_num參數(shù),忽略了column_num。(反之亦然)
那么我們?nèi)绾蝸砼袛嘁祷氐趲仔械膮^(qū)間系數(shù)呢?那就需要MATCH函數(shù)來解決了。
MATCH函數(shù)語法:MATCH(lookup_value, lookup_array, [match_type]),其解釋可以理解為,返回在一個區(qū)域中第一次出現(xiàn)該數(shù)據(jù)的位置序號。
MATCH函數(shù)的第三參數(shù)和VLOOKUP的第四參數(shù),有著異曲同工之妙,都可以用作模糊查詢和精確查詢,不過MATCH函數(shù)的模糊查詢有兩個值1(小于),-1(大于)。利用MATCH函數(shù)找到數(shù)據(jù)在條件區(qū)域G列中所處的位置序號,再用INDEX函數(shù)找到對應(yīng)的區(qū)間系數(shù)就達(dá)到了我們的需求。
方法五:OFFSET+MATCH函數(shù)
圖例:
C2單元格函數(shù):
=OFFSET($G$1,MATCH(B2,$G$2:$G$6,1),1,1,1)
函數(shù)解析:
這個方法使用了EXCEL函數(shù)中的漂移函數(shù)——OFFSET函數(shù)。它可以根據(jù)我們給定的條件,從某一個單元格,移動到另一個單元格或者區(qū)域,并返回地址引用。
OFFSET函數(shù)的語法:OFFSET(reference, rows, cols, [height], [width]),其函數(shù)解釋可以理解為從某個基準(zhǔn)單元格開始,先上(下)移動,再左(右)移動,這樣就得到了一個新的基準(zhǔn)點(diǎn),以新的基準(zhǔn)單元格定出高度和寬度,形成的單元格或區(qū)域作為引用地址。(如果返回的是單獨(dú)的單元格,那么就會直接返回該單元格的值;如果是區(qū)域,那么它就可以參與其他函數(shù)的調(diào)用)
因為條件區(qū)域是G2:H6,那么我們就把基準(zhǔn)值設(shè)置成G1單元格,向下偏移量我們用MATCH函數(shù)來解決(用法參考【方法四】的內(nèi)容),向右偏移一行,最后得出公式。
方法六:CHOOSE+MATCH函數(shù)
圖例:
C2單元格函數(shù):
=CHOOSE(MATCH(B2,$G$2:$G$6,1),$H$2,$H$3,$H$4,$H$5,$H$6)
函數(shù)解析:
這個方法是用CHOOSE函數(shù)來處理區(qū)間取值的問題。
CHOOSE函數(shù)語法:CHOOSE(index_num, value1, [value2], ...),其函數(shù)作用可以理解為找出value1, [value2], ...中的第index_num位次上的值,并返回單元格。函數(shù)最多可以有254個value,同時index_num的值也必須是1~254之間的一個數(shù)字。
CHOOSE函數(shù)也是一個很強(qiáng)大的函數(shù),它不僅可以返回一個單元格的值,也可以返回一個區(qū)域的引用,作為其他函數(shù)運(yùn)算的參數(shù)。
本例中我們依然是使用了MATCH函數(shù)來找到對應(yīng)的區(qū)間,然后返回了H2到H6單元格的內(nèi)容。
【編后語】
作為區(qū)間取值的《中篇》內(nèi)容,主要講了三個比較常用的嵌套函數(shù),之所以稱之為“經(jīng)典嵌套函數(shù)”,是因為這些嵌套函數(shù),可以被應(yīng)用到很多的方面,不僅僅是區(qū)間問題。
當(dāng)然,我們也可以自己來對函數(shù)進(jìn)行多種組合,但是函數(shù)的嵌套使用,一定是基于對獨(dú)立函數(shù)的充分理解之后,才可以使用的,比如我們今天的案例,MATCH函數(shù)的返回值是數(shù)值型,那么就一定要把它放到一個數(shù)值型的參數(shù)位置上,否則函數(shù)就會報錯的。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
區(qū)間查找(基礎(chǔ)函數(shù)篇)《老是加班還沒加班費(fèi)?誰讓你不會excel區(qū)間查詢的三大套路!》
MATCH函數(shù)解析《MATCH:函數(shù)哲學(xué)家,找巨人做伴。新出道必學(xué)!》
CHOOSE函數(shù)解析《請也為Excel中的《無名之輩》choose獻(xiàn)上一份理解!》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!