看看excel絕頂高手用的這些壓箱底查找公式,其實都是你熟悉但從沒深入用過的簡單東西……

2019-09-06 07:40:16

關注回覆[目錄]學習113篇Excel教程


全套Excel視頻教程,掃碼觀看

編按:
哈嘍,大家好!相信在看過前兩期區間查找的教程後,小夥伴們已經大致掌握了6種關於區間查找的方法了,可以説在區間查找的問題上,已經能沉着應對了。但excel最大的魅力就是它的多元性,任何一道題都是一題多解的。本篇是區間查找系列的最後一篇教程——數組函數篇,同時它也是本次系列教程中最難的一篇。快跟着小編一起來學習吧!
 
【引言】
 
通過前兩篇教程的內容,我們瞭解了區間取值問題的常規解法,也瞭解了嵌套函數的解法,應該説我們日常工作中再遇到此類問題,已經有6種方法可以快速統計數據了。那麼,此篇的內容,就讓我們來昇華一下自己的Excel函數技能,看看數組函數是如何解決“區間取值”的!
 
【數據源】
 
要求:根據B列的數值,在E列的範圍條件中找到對應範圍在H列的區間係數,並提取到C列計提係數中。
 
 
【解題方案】
 


7

SUM函數的“數組函數用法”


 
圖例:
 

 
C2單元格函數:
{=SUM((B2>=$G$2:$G$6)*(B2*($H$2:$H$6))}
 
輸入完成後,按數組函數的結束鍵CTRL+SHIFT+ENTER三鍵結束。
 
函數解析:
 
這個案例需要一個輔助單元格,就是G7單元格。在G7單元格輸入了一個903E7值,這是一個科學計數法,意思就是903*10的七次方,等於903*POWER(10,7)=9030000000 ,目的是為了找一個臨界值。
 
那麼有的表友可能會問了,為什麼要加這個值?
 
答:為了區域相等,錯位找到區間極值!
由圖中不難看出G2:G6就是每個“條件”的最小極值,那麼最大極值呢,是不是錯位之後G3:G7區域呢?可是G7是空值,默認為0,所以我們加了一個絕對大的值代替了∞。

温馨提示:加入下面QQ羣:362177791,下載教程配套的課件練習操作。

這裏也教大家一個學習數組函數的小竅門,就是如何看到那些看不到的內存數據。以C2單元格為例,我們可以通過工具欄中公式——公式審核——公式求值來看到這些內容。
 

 
當我們選中C2單元格,然後鼠標單擊“公式求值”按鈕,就會彈出公式求值窗口,此時就可以看到我們設置的函數內容。接着我們一下一下的點擊“求值”按鈕,就會發現,函數按步驟顯示出了每個環節的運算結果。
 

 
將兩個比較運算的部分分別進行數組運算,比較值為真返回TRUE,比較值為假返回FALSE,這樣的運算結果得到了兩個由TRUE和FALSE組成的數列,{TREU;TRUE;TRUE;FALSE;FALSE}和{FALSE;FALSE;TRUE;TRUE;TRUE}。
 
這兩個值在EXCLE中被叫做“邏輯值”,既然是“值”,就是可以參與計算的,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} ,藉此得到了我們計算的唯一值,再乘以區間係數,就得到如下圖顯示的內容。
 

 
最後的結果也就很清楚了。
 


8

MAX函數的“數組函數用法”



圖例:
 

 
C2單元格函數:
{=MAX((B2>=$G$2:$G$6)*$H$2:$H$6)}
 
輸入完成後,按數組函數的結束鍵CTRL+SHIFT+ENTER三鍵結束。
 
函數解析:
 
看了方法七的用法,是不是感覺“太硬,不好下嘴”?那麼本例就給大家介紹一個簡單的數組函數吧,雖然簡單,但是如果你不會原理,還是不能正常的應用。看一下“公式求值”給出的運算結果吧。
 

 
目標值大於條件值,則為TRUE,否則為FALSE,得到了一個數列,再乘以區間係數H2:H6區域,就得到了{0;0.01;0.03;0;0}的數列。
 

 
最後用MAX函數取值,就完成了我們區間取值的要求。
 


9

INDEX+MAX函數的“數組函數用法”



圖例:
 

 
C2單元格函數:
{=INDEX($H$2:$H$6,MAX(IF(B2>=$G$2:$G$6,ROW($1:$5),0)))}
 
輸入完成後,按數組函數的結束鍵CTRL+SHIFT+ENTER三鍵結束。
 
函數解析:
 
這個函數的思路,就是“傳説中的萬金油”函數了。這種函數基本來説分為三步走:
 
1.條件賦值


通過IF函數的判斷,給每一個值都對應上一個序號。正常的序號部分我們經常使用ROW函數或者COLUMN函數,因為行號和列號一般都是等差排列的1、2、3…這個形式,如果不滿足條件的話,我們往往給這個位置設置0或者99^99,意思就是“相對最小”或者“相對最大”。
 
那麼我們本例中的IF函數部分,返回了什麼呢?我們通過“公式求值”的方式,就可以很輕鬆的得到答案,如下圖所示:
 

 
通過這個過程我們看到IF函數的運算結果是{1,2,3,0,0}。
 
2.按需要取序號
 
因為我們上面的IF部分是做出想要的序號,那麼第二步就是按要求取出我們需要的序號了。取出最後一次滿足條件的值,也就是最大值,所以我們使用了MAX函數。
 
在萬金油函數中,我們經常會看到SMALL或者LARGR函數,這也是一種提取序號的過程,只不過是逐個從小到大或者從大到小的取值(不是取一次值),有興趣的同學可以看下我們往期的教程《熬夜加班髮際線後移?誰讓你不會Excel萬金油公式!》,和今天我們的主題偏離較大就不多介紹了。
 
3.迴歸到INDEX函數區間取值
 
取到了我們需要的序號,第三步就順理成章的又迴歸到了INDEX函數上了,只不過之前我們使用的是MATCH函數提取的序號,這次我們用的是MAX+IF函數的方式。有沒有學會呢?
 
【編後語】
 
數組函數並不難,只是大家可能還沒有找到竅門。其實數組函數也挺“有趣”的,它能在你不會使用VBA的情況下,解決一些比較複雜的運行效果。所以學無止境,有的技能可以不用,但還是要會的。
 
EXCEL最大的魅力是它的多元化,任何一道題,都是一題多解的,關鍵還是思路。這篇文章寫得很長,分了上、中、下三篇,但是依然不敢説已經收錄齊了,只是可能邏輯上有重複的,就沒有收錄。
 
會一兩種方法可以解決問題就可以了,列出如此多的方案,只是希望大家能從中學到每個方法的知識點:比如VLOOKUP函數對於條件區域需要“升序排列”;比如“邏輯值”是如何參與計算的;比如“萬金油”公式的三步走等等。哪怕你只學到了規範的區間書寫方式,也算是不虛看此篇。
 
作者E圖表述——不只寫基礎教程,還會考慮你的Excel進階,期待下次“部落窩”再見。

掃一掃添加老師微信


在線諮詢Excel課程


Excel教程相關推薦




老是加班還沒加班費?誰讓你不會excel區間查詢的三大套路!
比VLOOKUP重要,更容易讓你晉升高手的函數,就包含在這三大經典嵌套公式中
比VLOOKUP好用10倍,你卻只會用MAX求最大值?
已同步到看一看
在看



熱點新聞