index+small+if函數實現一對多查詢,或許有點難,但真的很實用

2020-12-15 Excel從零到一

Hello，大家好，當我們使用vlookup函數查找數據遇到重複值得時候，函數僅僅會返回第一個查找到的結果，但是在日常的工作中我們經常要根據一個值來查找到多個結果，這個時候vlookup函數就不能滿足我們需求了，今天就跟大家分享下在excel中如何實現一對多查詢

如下圖，我們想要根據部門，將相同部門的人都放在一行中，只需要點擊表格中的第一個單元格，輸入公式

=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=$F2,ROW($A:$A)),COLUMN(A1))),"")&""

按ctrl+shift+回車向右向下拖拉公式即可快速完成，在這裡我們就是通過查找部門來快速的返回多個結果

下面跟大家詳細的講解下這個公式是如何理解在，在這裡我們利用數組公式來完成查找的，函數是以index函數為主體來返回結果，

Index函數

第一參數：$B:$B，就是我們要返回的結果列

第二參數：SMALL(IF($A:$A=$F2,ROW($A:$A)),COLUMN(A1)))，使用small函數來獲取查找值的具體位置

在這裡我們主要來理解下small函數，small函數的作用是返回一組數據中從小到大的第n個值，他的參數一共有兩個，第一參數：數據區域，第二參數：數據區域的位置（從小到大）

在這裡Small函數在公式中的參數為

第一參數：IF($A:$A=$F2,ROW($A:$A))

第二參數：COLUMN(A1))

在IF($A:$A=$F2,ROW($A:$A))中，我們使用if函數來判斷A列的數據是不是等於f2單元格，也就是行政部，如果a列的單元格等於行政部就讓函數返回等於行政部位置的行號，在這裡我們省略了if函數的第三參數，當條件不正確的時候函數就會返回false，他的結算結果如下圖

因為small函數會自動將false這個結果忽略掉，所以現在small函數的第一參數僅僅包括3，5，7，9，17，這5個數據，因為我們在行方向上拖動數據的，所以我們使用COLUMN(A1))來構建一個從1開始的序列，這樣的話我們就能分別將這5個列號提取出來，將提取出來的列號作為index的第二參數來返回對應的結果

然後我們使用IFERROR來將錯誤值屏蔽掉錯誤值，最後在公式的最後使用連接符號連接上一個空值，是為了避免當我們的查找值為空的時候函數返回0

以上就是這個index+small+if函數的計算過程，怎麼樣，你學會了嗎？

我是excel從零到一，關注我持續分享更多excel技巧

相關焦點

  • Excel一對多萬能查詢公式index+small+if,理清思路就會了!
    工作中vlookup函數更多用於一對一的查詢，如果碰到了一對多的情況，經常會看到使用index+small+if函數公式例如左右是基礎信息，然後我們列出一個部門，需要把部門成員全部找出來，則可以使用這個一對多的萬金沒公式：=INDEX
  • 不藉助任何輔助列,VLOOKUP函數實現一對多查詢技巧
    舉一個一對多查詢匹配的工作實例，左邊是原始數據，一個部分有對應多個員工，我們現在需要根據部門把這個部門的員工姓名找出來藉助輔助列的方法VLOOKUP函數是一對一查詢的，查詢區域B列如果不是唯一的，那麼插入一個輔助列，輸入的公式是：=C2&
  • 用Small、If、Index、Indirect、Row、Char、Match實現一對多查找
    （3）如果直接把（2）的結果作為對列的引用會返回錯誤，利用Indirect函數可以正確地返回列的引用。經過這三步上述部分公式等價於直接對一列的引用。（5）利用small（步驟4構成的數組，row（A1））,分別提取第一、二、三小的數值。Row（A1）向下拖動時返回一個1、2、 3的數字序列，最終構成的是查找區域等於查找值的行和較大值10000構成的數組。（6）利用Index函數提取（3）對列的引用，（5）所返回行構成的數組所對應的值。
  • 供應鏈實踐中如何應用Excel函數的萬能組合:INDEX+SMALL+IF
    （專欄主要內容見上圖）供應鏈管理常用到的6個（組）查找函數（見下圖），前幾章，我分別講了VLOOKUP、HLOOKUP和LOOKUP查找函數三劍客，介紹了一對好基友INDEX+MATCH，以及甘當綠葉的隱形高手OFFSET，本文介紹最後一組查找函數，Excel函數中的萬能組合（也稱萬金油組合）：INDEX+SMALL+IF。
  • Excel中查找函數vlookup和index—match使用方法詳細介紹
    在Excel中，談到查找函數推薦最多的就是vlookup函數和index—match函數，很多人把兩個函數作為判斷Excel水平的重要指標，可想而知這兩個函數在Excel數據計算中的重要性，本文就和大家一起來看看這兩個函數的用法吧。
  • 一對多查詢,職場上的你真的會嗎?
    具體公式用法： INDEX函數 語法：=INDEX(區域,行數,列數) 比如在單元格中輸入公式=INDEX(A1:C11,7,2)就可以獲取查找「王曉光」在表格中的位置。
  • 非常實用的兩個大小位置函數:LARGE函數和SMALL函數
    今日繼續統計函數專題講解的第六講，講解統計中的數據大小排序問題，涉及到的函數是LARGE函數和SMALL函數。兩個函數今日我放在一起講解，並結合實例來分析，以便讀者能比較區別。一 LARGE函數：找出排在第幾大的數字 LARGE函數定義：返回數據集中第k個最大值.使用此函數可以根據相對標準來選擇數值。
  • VLOOKUP進階用法:一對多匹配查詢
    各位讀者朋友們大家好，今天來給大家說一下如何用VLOOKUP函數進行一對匹配。這個技巧在實際工作也是非常常用，通常問的朋友也比較多，今天就來給大家詳細介紹一下。首先來看一下一對多匹配是什麼意思，顧名思義就是一個查找值對應多個結果，如下圖所示，比如一個班級有多名同學，我們怎麼能夠一次性將二班的同學匹配出來？這就是我們今天要講解的用法。首先我們來分析一下，如果要將二班全部匹配出來，直接用二班匹配肯定是不行的，根據VLOOKUP的特性，只能匹配到第一次二班對應的同學。
  • 「微信小程序開發連載十七」小程序API實現物流查詢
    上一篇文章講解「小程序全國物流查詢」，但由於篇幅原因，沒有完全實現，本篇文章配合小程序API實現全國物流查詢。請與上一篇文章配合學習。6. 小程序API在小程序交互層進行調用接口時，需要使用小程序的API。
  • 「GCTT 出品」小函數可能是有害的(Small Functions considered...
    小函數可能是有害的（Small Functions considered Harmful）在這篇博文中，我的目標是：揭示小函數的一些似是而非的優點解釋為什麼我個人認為有點不像建議說的那麼好《Code Clean》被普遍認為是一本編程聖經，它有一章專門講述函數，文章的開始就是介紹一個非常長，令人頭疼的函數。該書認為該函數的最大問題是長度過長，並指出：它（函數）不僅長度太長，而且有多處重複的代碼，奇怪的字符串，許多奇怪和不明了的數據類型和 API。三分鐘的學習後，你能了解函數的功能嗎？也許不能。那裡有太多的抽象層次。
  • Python指南:控制結構與函數
    1、控制結構Python通過if語句實現了分支，通過while語句與for…in語句實現了循環，還有一種通過if實現的條件表達式（類似於C語言的三目運算符）。of "apple": 3index of 9: -1由輸出結果得知，我們想要的效果已經實現了。
  • VLOOKUP函數嵌套,讓您一步到位
    VLOOKUP函數簡介VLOOKUP函數是EXCEL中的一個縱向查找函數，在工作中的應用非常廣泛，核對數據可以用到這種函數，表格合併也可以用到它。此函數是按列查找的，最終返回的是該列所需查詢序列所對應的值；此外，VLOOKUP函數經常會和其它函數嵌套使用，效果會更好。
  • Excel中條件判斷只IF就out錯誤iferror函數實用
    Excel有一類函數是貫徹所有函數嵌套的時候，那就是條件判斷類函數，其中出現的最多的也就是我們的IF條件判斷函數。If條件函數可以運用的範圍也是非常的普遍，除了它還有個條件判斷類函數也是非常的實用，它就是iferror函數，在數據出現錯誤值的時候，iferror函數會比IF更加的好用。
  • VLOOKUP函數進階用法:模糊一對多匹配之匹配帶關鍵字的所有內容
    各位讀者朋友們大家好，進入今天主題之前先給大家講一個網絡上的小段子，關於一個小夥子買藥的故事：我們段子裡面小夥子說了一個膠囊屬實是把醫生給難住了，但是如果醫生能夠將包含膠囊二字的所有藥品名羅列出來給小夥看，小夥是不是更能容易確定他想要的是什麼藥呢
  • Excel函數公式:含金量超高的SUBSTITUTE函數實用技巧解讀
    Excel中，查找和替換功能應用的非常普遍，但我們都是通過【查找替換】功能來實現的……其實，除了【查找替換】功能外，SUBSTITUTE函數也可以實現替換功能。一、SUBSTITUTE函數作用及語法結構。作用：將字符串中的部分字符串以新字符串進行替換。
  • Angularjs+servlet+mysql實現表格
    ng-repeat相當於for循環，循環列印<tr>標籤中的內容，數據是將js中JSON格式的studentList對象傳給student對象，$index是循環的索引，從0開始。通過angularjs的表達式{{xxx}}列印相應的值，{{$index+1}}顯示序號，{{student.id}}顯示學號，{{student.name}}顯示姓名。
  • 高效的5個pandas函數,你都用過嗎?
    之前為大家介紹過10個高效的pandas函數，頗受歡迎，裡面的每一個函數都能幫我們在數據分析過程中節省時間。高效的10個Pandas函數，你都用過嗎？pandas還有很多讓人舒適的用法，這次再為大家介紹5個pandas函數，作為這個系列的第二篇。
  • Excel 中Vlookup函數更靈活的擴展用法
    如下圖所示：我們在左側黃色單元格分別輸入查詢編號，商場號，是否促銷來獲得左下的商品編號，商品名稱，價格在本例中主要使用VLOOKUP函數來實現具體步驟如下：數：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])VLOOKUP 函數語法具有下列參數 （參數
  • Excel工作表中的7個資料庫函數解讀,易懂易理解,方便且實用!
    2、上述功能也可以用Sum+Sumif數組組合公式來實現，公式為：=SUM(SUMIF(F3:F12,{"大本","大專","中專"},G3:G12))。（三）資料庫函數Dsum：多欄位單條件求和。2、Dsum函數的多欄位單條件求和相當於多條件求和函數Sumifs,公式為：=SUMIFS(G3:G12,D3:D12,I3,F3:F12,J3)。（四）資料庫函數Dsum：多欄位多條件求和。
  • 零基礎學電腦辦公,Excel表格中的vlookup函數,從入門到精通
    您好，本文介紹的是微軟公司的，電腦表格製作軟體Excel 中 VLOOKUP函數的功能、語法和注意事項。文尾還有視頻教程哦！此公式將搜索區域 A2:C10 的第一列中的值 38，然後返回該區域同一行中第三列包含的值作為查詢值。VLOOKUP 中的 V 表示垂直方向。注意區分 HLOOKUP（如您需要HLOOKUP函數的課程，請在文尾留言）。