熬夜加班髮際線後移?誰讓你不會Excel萬金油公式!【Excel教程】

excel教程2019-07-11 21:37:35


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


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


總是聽到高手們説有個萬金油公式,可到底什麼是萬金油公式,這個公式又能幹什麼呢?不妨先看看下面這個效果圖:


加入QQ羣:264539405下載課件練習

這個例子就是一個典型的一對多查找,查找條件是部門,在數據源內每個部門對應的都是多個數據,萬金油公式最主要的用途就是用來解決一對多查找等一些相對複雜的問題。上面動畫中的公式為:

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

看到這個公式,或許很多朋友都會驚歎:這麼長的公式,看不懂哇!

今天老菜鳥就和大家一同破解這個看不懂但又很強悍的公式套路,耐心往下看哦……

上面這個公式一共用了六個函數:IFERROR、INDEX、SMALL、IF、ROW和MATCH,其中的IFERROR和MATCH是本例中輔助性的兩個函數,其餘的四個INDEX-SMALL-IF-ROW就是萬金油公式啦。

因此我們先來學習這個核心部分的原理:

F4單元格的公式為:

=INDEX($A$2:$A$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))


先從INDEX説起,這個函數基本功能是給出一個區域,然後根據對應的行列位置返回查找結果,上圖中INDEX查找的數據區域就是姓名所在的區域$A$2:$A$21。

INDEX函數的基本結構是:INDEX(查找區域,第幾行,第幾列),如果區域是單行或者單列的話,後面兩個參數可以省略一個。通俗點説,你拿着電影票去找座位,整個大廳的座位就是區域,第幾排第幾座就是公式中的後面兩個參數,通過這種方式可以準確找到目標位置。

在上面這個例子裏,區域是在一列,所以我們只需要確定每個數據在第幾行就行。

明白這一點的話,我們的重點就該放到INDEX的第二個參數了:

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))


注意看上面這個圖,銷售部一共有四條記錄,分別在數據區域的第5、8、9和16行(數據區域是從第二行開始)。

因此我們希望公式下拉的時候,INDEX的第二個參數分別是5、8、9和16這四個數字(這一點一定要想明白)。

注意,接下來我們即將接觸到萬金油最核心的部分,請保持高度集中的注意力……

SMALL函數的基本結構:SMALL(一組數,第幾小的數)

建議自己模擬個簡單的數據來充分理解這個函數,方法如下:


在A列輸入一些數字,公式的意思是這列數字中最小的一個,結果是2,很好理解對不對,將公式的第二個參數改成2,再看看結果:


倒數第二小的是4。

如果希望繼續得到第三小的數,該怎麼做我想大家都能想到,但是會有個問題,我們只能手動修改第二參數,並不能通過下拉來實現這個參數的變化,如果要想可以下拉的話,第二參數就需要用到ROW函數,也就是這樣修改:


ROW函數非常簡單,得到的就是參數的行號,通過這個公式,我們就把A列的數據從小到大排了個序,覺得有意思嗎?

回到我們的萬金油公式,5、8、9和16這四個數字代表什麼意思還記得吧,我們需要用SMALL函數依次得到這四個數字,思路是通過判斷C列是否與F2一致,如果一樣得到行號,如果不一樣,就得到一個比最大行號還大的數字(目的是為了防止被查找到):


要實現這個目的,就需要IF函數的介入,於是就有了:

IF($C$2:$C$21=$F$2,ROW($1:$20),99),用這一段來作為SMALL的第一個參數。

關於這段IF,就比較容易理解了,我們可以藉助F9來看看這段公式的結果:


因為我們的數據就20個,所以IF的第三個參數使用99就足夠了,如果數據量比較大的話,可以用9^9,表示9的9次方,反正足夠大就行。

搞清楚這個IF的話,再來看這段

SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1))是不是就沒那麼暈了。

關於SMALL這部分,一定要明白是隨着公式下拉的時候,逐個得到我們希望得到的那幾個數字,然後用這些數字作為INDEX的第二參數,就可以得到最終需要的結果。

萬金油的核心就是INDEX、SMALL、IF和ROW,請大家務必反覆琢磨,把這部分原理搞清楚。還有非常重要的一點需要強調,萬金油公式是一個數組公式,因此需要我們按着Ctrl和shift再回車。

至於一開始的公式,考慮到要查找多列的內容,所以INDEX的數據區域用的$A$2:$D$21,多列的時候,就需要提供列位置才能找到目標值,因此用MATCH(F$3,$A$1:$D$1,0)來確定數據在第幾列。

每個部門的數據都不一樣多,我們需要將公式多向下拉幾行,這時候就會產生一些錯誤值,在公式的最外層使用IFERROR函數屏蔽了錯誤值,使得查詢結果看起來非常乾淨。

今天只是使用了一對多查找這樣一個例子來解釋萬金油公式的原理,實際上萬金油的套路還有很多,大家喜歡的話以後繼續分享相關的實例,當然,如果看完本文的話能夠自己去解讀一些複雜的公式就更好了。

想要全面系統學習Excel,不妨關注《一週Excel直通車》視頻課或者《Excel極速貫通班》


《一週Excel直通車》視頻課

包含Excel技巧、函數公式、

數據透視表、圖表。

一次購買,永久學習。


最實用接地氣的Excel視頻課

《一週Excel直通車》

風趣易懂,快速高效,帶您7天學會Excel

38 節視頻大課

(已更新完畢,可永久學習)

理論+實操一應俱全


主講老師:滴答

 

Excel技術大神,資深培訓師;

課程粉絲100萬+;

開發有《Excel小白脱白系列課》

        《Excel極速貫通班》。

原價299元

限時特價 99 元,隨時漲價

少喝兩杯咖啡,少吃兩袋零食

就能習得受用一生的Excel職場技能!


  長按下面二維碼立即購買學習

購課後,加客服微信:603830039領取練習課件



爆文閲讀:

90後小姐姐面試想拿7K,老闆説:連批量插入空行都不會,只值3K!【Excel教程】

Excel教程:17集Excel函數公式,滴答老師主講!

108集全套Word視頻,入門到精通【限時立減30元】

https://hk.wxwenku.com/d/201123869