編按：

哈嘍，大家好！相信在看過前兩期區(qū)間查找的教程后，小伙伴們已經(jīng)大致掌握了6種關(guān)于區(qū)間查找的方法了，可以說在區(qū)間查找的問題上，已經(jīng)能沉著應(yīng)對(duì)了。但excel最大的魅力就是它的多元性，任何一道題都是一題多解的。本篇是區(qū)間查找系列的最后一篇教程——數(shù)組函數(shù)篇，同時(shí)它也是本次系列教程中最難的一篇?？旄【幰黄饋韺W(xué)習(xí)吧！

【引言】

通過前兩篇教程的內(nèi)容，我們了解了區(qū)間取值問題的常規(guī)解法，也了解了嵌套函數(shù)的解法，應(yīng)該說我們?nèi)粘９ぷ髦性儆龅酱祟悊栴}，已經(jīng)有6種方法可以快速統(tǒng)計(jì)數(shù)據(jù)了。那么，此篇的內(nèi)容，就讓我們來升華一下自己的Excel函數(shù)技能，看看數(shù)組函數(shù)是如何解決“區(qū)間取值”的！

【數(shù)據(jù)源】

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

【解題方案】

方法七：SUM函數(shù)的“數(shù)組函數(shù)用法”

圖例：

C2單元格函數(shù)：

{=SUM((B2>=$G$2:$G$6)*(B2<$G$3:$G$7)*($H$2:$H$6))}

輸入完成后，按數(shù)組函數(shù)的結(jié)束鍵CTRL+SHIFT+ENTER三鍵結(jié)束。

函數(shù)解析：

這個(gè)案例需要一個(gè)輔助單元格，就是G7單元格。在G7單元格輸入了一個(gè)903E7值，這是一個(gè)科學(xué)計(jì)數(shù)法，意思就是903*10的七次方，等于903*POWER(10,7)=9030000000 ，目的是為了找一個(gè)臨界值。

那么有的表友可能會(huì)問了，為什么要加這個(gè)值？

答：為了區(qū)域相等，錯(cuò)位找到區(qū)間極值！

由圖中不難看出G2:G6就是每個(gè)“條件”的最小極值，那么最大極值呢，是不是錯(cuò)位之后G3:G7區(qū)域呢？可是G7是空值，默認(rèn)為0，所以我們加了一個(gè)絕對(duì)大的值代替了∞。

這里也教大家一個(gè)學(xué)習(xí)數(shù)組函數(shù)的小竅門，就是如何看到那些看不到的內(nèi)存數(shù)據(jù)。以C2單元格為例，我們可以通過工具欄中公式——公式審核——公式求值來看到這些內(nèi)容。

當(dāng)我們選中C2單元格，然后鼠標(biāo)單擊“公式求值”按鈕，就會(huì)彈出公式求值窗口，此時(shí)就可以看到我們?cè)O(shè)置的函數(shù)內(nèi)容。接著我們一下一下的點(diǎn)擊“求值”按鈕，就會(huì)發(fā)現(xiàn)，函數(shù)按步驟顯示出了每個(gè)環(huán)節(jié)的運(yùn)算結(jié)果。

將兩個(gè)比較運(yùn)算的部分分別進(jìn)行數(shù)組運(yùn)算，比較值為真返回TRUE，比較值為假返回FALSE，這樣的運(yùn)算結(jié)果得到了兩個(gè)由TRUE和FALSE組成的數(shù)列，{TREU;TRUE;TRUE;FALSE;FALSE}和{FALSE;FALSE;TRUE;TRUE;TRUE}。

這兩個(gè)值在EXCLE中被叫做“邏輯值”，既然是“值”，就是可以參與計(jì)算的，TRUE是1，FALSE是0 。那么{TREU;TRUE;TRUE;FALSE;FALSE}乘以{FALSE;FALSE;TRUE;TRUE;TRUE}，就可以理解為{1;1;1;0;0}*{0;0;1;1;1}={0;0;1;0;0} ，藉此得到了我們計(jì)算的唯一值，再乘以區(qū)間系數(shù)，就得到如下圖顯示的內(nèi)容。

最后的結(jié)果也就很清楚了。

方法八：MAX函數(shù)的“數(shù)組函數(shù)用法”

圖例：

C2單元格函數(shù)：

{=MAX((B2>=$G$2:$G$6)*$H$2:$H$6)}

輸入完成后，按數(shù)組函數(shù)的結(jié)束鍵CTRL+SHIFT+ENTER三鍵結(jié)束。

函數(shù)解析：

看了方法七的用法，是不是感覺“太硬，不好下嘴”？那么本例就給大家介紹一個(gè)簡單的數(shù)組函數(shù)吧，雖然簡單，但是如果你不會(huì)原理，還是不能正常的應(yīng)用。看一下“公式求值”給出的運(yùn)算結(jié)果吧。

目標(biāo)值大于條件值，則為TRUE，否則為FALSE，得到了一個(gè)數(shù)列，再乘以區(qū)間系數(shù)H2:H6區(qū)域，就得到了{0;0.01;0.03;0;0}的數(shù)列。

最后用MAX函數(shù)取值，就完成了我們區(qū)間取值的要求。

方法九：INDEX+MAX函數(shù)的“數(shù)組函數(shù)用法”

圖例：

C2單元格函數(shù)：

{=INDEX($H$2:$H$6,MAX(IF(B2>=$G$2:$G$6,ROW($1:$5),0)))}

輸入完成后，按數(shù)組函數(shù)的結(jié)束鍵CTRL+SHIFT+ENTER三鍵結(jié)束。

函數(shù)解析：

這個(gè)函數(shù)的思路，就是“傳說中的萬金油”函數(shù)了。這種函數(shù)基本來說分為三步走：

1.條件賦值

通過IF函數(shù)的判斷，給每一個(gè)值都對(duì)應(yīng)上一個(gè)序號(hào)。正常的序號(hào)部分我們經(jīng)常使用ROW函數(shù)或者COLUMN函數(shù)，因?yàn)樾刑?hào)和列號(hào)一般都是等差排列的1、2、3…這個(gè)形式，如果不滿足條件的話，我們往往給這個(gè)位置設(shè)置0或者99^99，意思就是“相對(duì)最小”或者“相對(duì)最大”。

那么我們本例中的IF函數(shù)部分，返回了什么呢？我們通過“公式求值”的方式，就可以很輕松的得到答案，如下圖所示：

通過這個(gè)過程我們看到IF函數(shù)的運(yùn)算結(jié)果是{1,2,3,0,0}。

2.按需要取序號(hào)

因?yàn)槲覀兩厦娴?span>IF部分是做出想要的序號(hào)，那么第二步就是按要求取出我們需要的序號(hào)了。取出最后一次滿足條件的值，也就是最大值，所以我們使用了MAX函數(shù)。

在萬金油函數(shù)中，我們經(jīng)常會(huì)看到SMALL或者LARGR函數(shù)，這也是一種提取序號(hào)的過程，只不過是逐個(gè)從小到大或者從大到小的取值（不是取一次值），有興趣的同學(xué)可以看下我們往期的教程《Excel萬金油公式INDEX-SMALL-IF-ROW篩選函數(shù)公式解讀》，和今天我們的主題偏離較大就不多介紹了。

3.回歸到INDEX函數(shù)區(qū)間取值

取到了我們需要的序號(hào)，第三步就順理成章的又回歸到了INDEX函數(shù)上了，只不過之前我們使用的是MATCH函數(shù)提取的序號(hào)，這次我們用的是MAX+IF函數(shù)的方式。有沒有學(xué)會(huì)呢？

【編后語】

數(shù)組函數(shù)并不難，只是大家可能還沒有找到竅門。其實(shí)數(shù)組函數(shù)也挺“有趣”的，它能在你不會(huì)使用VBA的情況下，解決一些比較復(fù)雜的運(yùn)行效果。所以學(xué)無止境，有的技能可以不用，但還是要會(huì)的。

EXCEL最大的魅力是它的多元化，任何一道題，都是一題多解的，關(guān)鍵還是思路。這篇文章寫得很長，分了上、中、下三篇，但是依然不敢說已經(jīng)收錄齊了，只是可能邏輯上有重復(fù)的，就沒有收錄。

會(huì)一兩種方法可以解決問題就可以了，列出如此多的方案，只是希望大家能從中學(xué)到每個(gè)方法的知識(shí)點(diǎn)：比如VLOOKUP函數(shù)對(duì)于條件區(qū)域需要“升序排列”；比如“邏輯值”是如何參與計(jì)算的；比如“萬金油”公式的三步走等等。哪怕你只學(xué)到了規(guī)范的區(qū)間書寫方式，也算是不虛看此篇。

作者E圖表述——不只寫基礎(chǔ)教程，還會(huì)考慮你的Excel進(jìn)階，期待下次“部落窩”再見。

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

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

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

相關(guān)推薦：

區(qū)間查找（基礎(chǔ)函數(shù)篇）《老是加班還沒加班費(fèi)？誰讓你不會(huì)excel區(qū)間查詢的三大套路！》

區(qū)間查找（嵌套函數(shù)篇）《比VLOOKUP重要，更容易讓你晉升高手的函數(shù)，就包含在這三大經(jīng)典嵌套公式中》

MAX函數(shù)的特殊應(yīng)用《查詢界黑馬——MAX，竟讓查詢之王VLOOKUP也甘拜下風(fēng)？》