excel如何實現一對多查詢?學會數組公式,老闆為你點讚

2020-12-11 加薪學堂

課程信息卡

課程：《Excel天天訓練營》2.0圖文版章節：第2章-精通函數內容：數組基礎（small\large）

在excel函數公式裡，大家最頭疼的莫過於數組公式。因為普通的函數相對好理解，就好像做一道題只有一個答案，比較好看懂。但數組公式就好像一道題有N個答案，比較複雜。

但正因為複雜，它的功能也就更強大，可以根據條件獲得多條結果。

為了更好的使用數組公式，我們需要先掌握數組裡常用的配套函數small和large。

如圖中案例表格，要根據月份查詢倒數前3名，這種一個條件要獲得多條結果的，就要使用數組函數。

如果你沒有學會這些函數，那麼就無法應對大量數據的表格。

現在，我們就來用函數公式實現excel自動化辦公。

一、掌握「small函數」的用法

small函數是取一組數字裡面的最小值。用法為：

=small（數字組，取第幾小的值）

當「取第幾小的值」為1時，就代表取最小值；為2時，代表取倒數第2的值。這個參數不能省略。

示例說明：

=SMALL(B3:B7,1)

代表在B3:B7的數字裡面，也就是10、12、20、8、35裡面取第1小的值，即倒數第一的8。

=SMALL(B3:B7,3)

代表在B3:B7的數字裡面，也就是10、12、20、8、35裡面取第3小的值，即倒數第三的12。

使用「small函數」進行數組取值

圖中案例要根據月份來查詢倒數3個值，由於月份是變化的，所以要用offset函數取出每個月份對應的區域，然後再用small函數取出3個值。

首先，這是一個數組公式，也就是一個公式有多個結果，所以要先選中結果單元格區域，即G3：G5區域，然後在編輯欄裡寫公式（這時定位在G3單元格），寫完公式後要按「Ctrl+Shift+回車」三鍵確認（所有數組公式都必須用三鍵確認）。

公式為

=SMALL(OFFSET(A2,1,MATCH(G2,B2:D2,0),8,1),ROW(A1:A3))

相關焦點

  • excel查詢技巧：如何用數據透視表進行一對多查詢
    Excel一對多查詢，你能夠想到用什麼函數？excel一對多查詢不僅可以使用函數公式，還可以數據透視表。我承認我只是一個普通人或者是懶人，儘管高手的方法很多，但我只衷情於數據透視表進行一對多查詢，因為它快並且不用動腦筋！有這樣一份Excel一對多的查詢案例，需要返回對應的多個值。B、C列是數據源，根據E2的部分在F列返回對應的部門成員。
  • index+small+if函數實現一對多查詢,或許有點難,但真的很實用
    Hello，大家好，當我們使用vlookup函數查找數據遇到重複值得時候，函數僅僅會返回第一個查找到的結果，但是在日常的工作中我們經常要根據一個值來查找到多個結果，這個時候vlookup函數就不能滿足我們需求了，今天就跟大家分享下在excel中如何實現一對多查詢
  • Excel表格中一對多查詢的幾個公式，可以直接套用，閒公式複雜，用數據透視表
    今天分享一對多查詢的幾個公式，公式都比較複雜，用時直接複製粘貼，再修改單元格地址即可，不明白可以留言或私信我。另外，最後一種方法用數據透視表功能來完成，相信小夥伴們都能學會！當公式出現錯誤值時，查詢的結果就沒有了。
  • Excel一對多萬能查詢公式index+small+if,理清思路就會了!
    工作中vlookup函數更多用於一對一的查詢，如果碰到了一對多的情況，經常會看到使用index+small+if函數公式例如左右是基礎信息，然後我們列出一個部門，需要把部門成員全部找出來，則可以使用這個一對多的萬金沒公式：=INDEX
  • 一對多查詢,這組Excel公式太好用了
    如何按給定的條件查找數據中所有記錄？今天與大家分享一個非常強大的公式組合，具體是什麼？一起看看吧。在A16單元格中輸入公式=IFERROR(INDEX($A$1:$F$10,SMALL(IF($C$1:$C$10=$B$13,ROW($A$1:$A$10),4^8),ROW(A1)),COLUMN(A:A)),"")，然後按Ctrl+Shift+Enter組合鍵。
  • excel數組公式進階:按列查找的時候,記得要使用這個函數!
    課程信息卡課程：《Excel天天訓練營》2.0圖文版章節：第2章-精通函數內容：數組公式（列查找）用excel數組公式，如果遇上按列查找的時候，你會發現公式會出問題。如圖中案例表格，數據雖然是一行一行的記錄，但要查詢的內容都在一行的不同列，屬於按列查找，這時候就要用行列轉換函數TRANSPOSE。如果你沒有學會這些函數，那麼就無法應對大量數據的表格。現在，我們就來用函數公式實現excel自動化辦公。
  • 老闆給了條件,如何用excel取出數值?學會offset+match函數
    如圖中案例表格，除了我們之前講的index+match這一組函數，用offset+match這一組函數也很容易實現。而且，offset+match還有更高級的用法（比如製作多級聯動下拉菜單），我們會在後面的課程講到。如果你沒有學會這些函數，那麼就無法應對大量數據的表格。
  • Excel教程:3種Excel一對多查詢方法,任你挑選!
    Excel一對多查詢，你能夠想到用什麼函數？不僅可以使用函數公式，還可以使用數據透視表。 我承認我只是一個普通人或者是懶人，儘管高手的方法很多，但我只鍾情於數據透視表進行一對多查詢，因為它快並且不用動腦筋！有這樣一份Excel一對多的查詢案例，需要返回對應的多個值。B、C列是數據源，我們要根據E2的部門在F列返回對應的部門成員。
  • Excel | VBA(6)——一對多查詢，幾行代碼代替複雜公式
    經常讀我公眾文章的朋友一看就知道，那是兩篇關於一對多查找 的文章。兩種方式，不管是VLOOKUP還是 INDEX+SMALL，都用到了構建新的數組，一是公式寫起來比較麻煩，二是如果數據量過大，運行速度較慢。今天，韓老師給幾行代碼，可以輕鬆解決以上問題。
  • 一對多查詢的幾個公式,可以直接套用,閒公式複雜,用數據透視表
    今天分享一對多查詢的幾個公式，公式都比較複雜，用時直接複製粘貼，再修改單元格地址即可，不明白可以留言或私信我。另外，最後一種方法用數據透視表功能來完成，相信小夥伴們都能學會！當公式出現錯誤值時，查詢的結果就沒有了。
  • excel函數公式:ISERROR函數來幫你搞定錯誤,你學會了嗎?
    在前面的文章中介紹了excel的錯誤有8種，如果忘記了的夥伴可以參考下下面的表格，回憶一下excel中8種錯誤類型（ps：最後一種嚴格上來講並不是錯誤哦）我們知道了excel中會出現這些錯誤，當我們遇到這些錯誤怎麼處理呢，今天小編就給大家分享excel捕捉錯誤值的函數。
  • 批處理數據神器:Excel數組公式全解析丨免費微課
    ，讓你學會用數組公式批量處理一組或多組數據，追求更高的數據處理速度。，並返回一個或多個結果，比我每個函數處理一個數據快多了！同時，利用數組公式的原理，結合其他函數應用還可以實現非常高效率的效果，如圖例，使用Vlookup+數組公式，只需要一條公式，簡單幾步就能得出評級：如果你想追求更快的數據處理速度，那就來學習數組公式吧！我們為你請來@黃波藝 老師，帶你用一小時學會如何使用數組公式，從入門到進階，再也不怕處理大量數據了！
  • Excel公式與函數之美07:公式中的王者——數組公式
    為什麼要學習和使用數組公式使用數組公式，可以使Excel完成我們認為不可能的任務，或者說要使用VBA才能完成的任務，並且在有些情形下，數組公式可能是一個非常有效的解決方案。當然，好奇心也會驅使我們想要學習進階去創建更高級的公式，並且在學會數組公式的基本使用後想要創建更有效的數組公式。
  • 值得學習的excel操作小技巧,利用vlookup函數實現一對多查詢
    我們在實際工作中，我們經常使用excel表格對數據進行處理和分析，我們都清楚excel具有強大的excel函數和數據處理工具，我們可以憑藉這些工具盒函數對數據進行快速處理，這次我們還是要講解一下有關vlookup函數的相關內容，我們知道vlookup函數是一個查找函數，我們這次講解的是利用
  • vlookup函數不止是單條件查詢,特殊的數組查詢並求和才算高效
    vlookup函數相信對於大家來說都不陌生，大家都知道這個函數可以進行各式各樣的數據查詢操作。單條件查詢、多條件查詢、逆向查詢、一對多查詢等等，這些操作vlookup函數都可以實現。下面我們來學習一下vlookup數組嵌套的另外一種方式，如何對查詢出來的多個條件值進行快速求和。案例說明：利用vlookup函數查詢對應人員2、3兩個月數據並進行求和案例說明：如上圖所示，我們利用vlookup函數，需要查詢對應人員2月、3月兩個月的數據，查詢出來後進行數據求和。
  • Excel教程:Vlookup一對多查找大法
    SO：當遇上一對多查詢時很多小夥伴就不淡定了網上搜來的一對多查找Index+Small+If+Row公式實在太難理解，公式辣麼辣麼長還得三鍵結束。問題是我還看不懂啊！答案肯定是有的今天小編就來給大家分享一種不用數組公式的一對多查詢Vlookup+輔助列如下圖所示，根據姓名查找對應的每筆銷售明細
  • Excel教程:Vlookup一對多查找大法,EXCEL神技巧!
    SO：當遇上一對多查詢時很多小夥伴就不淡定了網上搜來的一對多查找Index+Small+If+Row公式實在太難理解，公式辣麼辣麼長還得三鍵結束。問題是我還看不懂啊！答案肯定是有的今天小編就來給大家分享一種不用數組公式的一對多查詢Vlookup+輔助列如下圖所示，根據姓名查找對應的每筆銷售明細
  • word中也能實現vlookup函數的一對多查詢,超級簡單
    excel實例分享戶籍管理：textjion函數簡直太好用啦1分鐘搞定首先我們需要對excel中家庭信息處理一下，提取每一家的戶主1、在E2中輸入公式=INDEX(A:A,LOOKUP(1,0/($B$2:B2="戶主"),ROW($B$2:B2)),)下拉公式即可公式解釋：index函數語法=index
  • Excel實用技巧分享:小括號常常用,但大括號你用過嗎?
    在excel中，用大括號{}包括起來的一組數據叫做數組數據，而其中的每一個數據叫做這個數組數據的元素。例如：數組{「甲」、「乙」、「丙」}，其中甲、乙、丙都是這個數組的一個元素。下面，我就用一個實例為大家講解大括號{}在excel中的使用方法。
  • Excel函數公式:含金量超高的「一對多」查詢實用技巧解讀
    那麼，該如何去實現呢？步驟：（一）、高亮度顯示該銷售員的銷售記錄。方法：1、選中目標單元格。2、【開始】-【條件格式】-【新建規則】，選中【選擇規則類型】中的【使用公式確定要設置格式的單元格】，並在【為符合此公式的值設置單元格格式】中輸入：=($H$4=$B3)。3、單擊右下角【格式】-【填充】，選取填充色，並【確定】-【確定】完成設置。