先學完這個技巧,再理解Vlookup函數一對多查詢就簡單了

2021-01-09 Excel自學成才

我們模擬一個一對多查詢的例子：左邊是部門及員工姓名數據，我們需要根據部門，來查找出所有的員工姓名

我們在使用VLOOKUP函數進行查找匹配的時候，如果源數據中有多個值時，它只會查找出第一個值，比如我們在E2輸入的公式：

=VLOOKUP(D2,A:B,2,0)

那如何才能進行一對多查詢呢？如果直接給你一個公式，你可能很難理解為什麼這麼做。那麼在給出公式之前，我們先轉化一下思路，如果說，我們左邊的數據是不同的，然後我們查找的數據也是不同的，那麼用上面的vlookup公式就可以很容易匹配出來

那麼一對多查詢的問題，就轉化成了，如何將最原始的錶轉換成這個表了

首先是左邊的原始數據源處理，我們插入一個輔助列，然後輸入的公式是：

=B2&COUNTIF($B$2:B2,B2)，COUNTIF函數是用來累計計數的，從上至下累計出現的次數是多少

然後就是查找的值進行處理了，我們使用的公式是：

=$E$2&COLUMN(A1)，COLUMN(A1)表示的是A1在第幾列，就是第1列了，向右填充就得到了2，3，...

所以我們整體使用的公式是：

=VLOOKUP($E$2&COLUMN(A1),$A:$C,3,0)

向右填充

如果我們下方還需要查找財務部的時候，我們可以注意一下查找值E2的相對引用，以及錯誤值的屏蔽，使用公式是：

=IFERROR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0),"")

關於一對多的查詢，你學會了麼？動手試一下~

相關焦點

  • vlookup一對多查詢的4種解法
    職場快與慢-第18期前幾天，財務突然QQ上找我說有要事相商，當時我內心恐懼，小劇場一幕幕上演，難道這個月要拖欠工資了，難道我的報銷填寫錯誤了，我懷揣著一顆不安定的心，來到財務辦公室，原來財務再處理一個髮票問題時遇到了麻煩，於是我協助財務一起解決了整個問題；那麼今天我們要講的職場小技巧就是Excel查詢1對多的4種解法關注：公眾號/職場快與慢
  • Excel表格vlookup函數搭配match函數詳解
    vlookup用法上面的表格是右邊的數據，根據名稱在左邊數據裡面查詢對應的數據，=iferror(vlookup($f2,$a$1:$d$6,2,0),"")首先我們先來分析一下這個函數match函數我們再看下另一個輔助函數match，這個函數返回當前查找的值在區域中的位置，第一個參數為查找的值，如果是中文要加英文雙引號，第二個參數是查找區域，第三個為精確匹配，如上圖
  • Vlookup函數除了查詢引用,它的VBA使用你會嗎
    在工作中，我們在處理大量數據時，會有這樣一種需求，即查詢某個人或某個商品的一個信息。如果我們通過滾動用肉眼去看難免非常的痛苦，畢竟數據太多，那麼聰明的人是怎麼做的呢？對，就是用vlookup函數來實現。本章就圍繞vlookup的查詢引用以及通過VBA返回多個值的方法進行講解。
  • vlookup常用套路,自動生成數據原來這麼簡單,一會百會
    今天分享一下在Excel中幾種常用的vlookup套路，自動生成數據快捷迅猛，再不會我才不信呢-。-有個好消息！為了方便大家更快的掌握技巧，尋找捷徑。幫幫準備了海量模板素材免費下載。百度「模板終結者」，三個W點2xx點vip，我等著你噢(老熟人都懂得)^^我們來看範例圖片，根據學號快速查詢成績，咱們記住vlookup函數查詢值（學號），一定要在被查詢區域的首列。
  • 4個vlookup函數的高階用法,估計你一種都沒用過
    Hello，大家好，vlookup函數相信大家都非常的熟悉，今天就跟大家分享4個vlookup函數的高階用法，學會它們，你就是公司的大神了。話不多說，讓我們直接開始吧一、提取固定長度的數字如下圖，我們想要提取工號，只需要將函數設置為：=VLOOKUP(0,{0,1}*MID(A2,ROW($1:$30),6),2,0)，因為這個一個數組公式所以我們要按Ctrl+shift+回車三鍵填充公式，然後向下拖動即可，這樣的話我們就提取到了工號
  • 學會VLOOKUP函數的一個隱藏參數 告別 N/A錯誤值
    一、VLOOKUP犯錯根據函數語法 VLOOKUP（查找值，數據表，列序數，[匹配條件]），直接寫函數 =VLOOKUP(H3,A:E,4,0) 返回#N/A，#N/A 是 Not Applicable（查找值不適用，即沒有找到查找值）的意思。所以要學好vlookup，需要了解這個函數的來源。
  • INDEX+MATCH函數真的能替代VLOOKUP函數嗎?我看未必!
    閒言少敘，切入正題，在比較之前，先來回憶一下，這兩組函數的基本語法結構和參數的含義。,這裡用簡單1和-1代表；1：要求查詢列或行排序為升序,省略時默認模式；-1：要求查詢列或行為降序排列；這些就是VLOOKUP和INDEX和MATCH組合的參數說明，因為兩者都支持精準和模糊查找兩種模式，哪我就先從對比精準模式的用法開始吧。
  • 這篇通俗實用的Vlookup函數教程,5分鐘就可以包你一學就會
    如何利用Vlookup函數獲取學號中的班級信息。換言之，咱們源數據中放著姓名性別學號班級等信息，而在另一張表格中一定有學號信息，但其他信息就未必有，這需要我們將缺失的信息自動同步過去。使用vlookup函數的確非常簡單，今天我就再次來剖析一下這個函數吧。
  • 查找總是出錯:別為難vlookup函數了,還是讓lookup函數來吧
    有朋友提到在使用vlookup函數查找的時候結果為什麼是錯的？明明查找規格都是一樣的，並且沒有任何格式差別，怎麼會出錯了呢？看到朋友發來的文件才知道原來是通配符惹的禍？在excel中星號（*）是有特別意義的，如果你需要查找的內容中包含星號，那麼星號的本身是（~*），這樣才能正確查找！非通配符：~加在通配符*或？前，此時*或？
  • 查詢函數Choose、Lookup、Hlookup、Vlookup應用技巧解讀
    Excel中的查找和引用函數主要用於查找工作表中的所需內容，還可以獲得工作表中的單元格位置或表格大小等信息，如果將查找和引用函數配合其他的Excel函數使用，將會發揮更強大的功能。常用的查詢表中的數據函數有：引用表中數據的函數有：一、Choose函數。
  • 財務辦公常用Excel公式釋義手冊,及各類函數用法,全部整理齊了
    財務人在日常處理工作的時候，大多都會用到Excel，而99%的財務人都會用到的一個操作就是，指定數據查詢，但是有很多的財務人大多都不會，這樣就大大加重了財務人的工作負擔。今天小編就和大家匯總了一套《Excel 公式釋義及示例大全》手冊，裡面包含了各類函數用法及全部函數操作，共435頁。（文末有福利）好了，現在先給大家展示一下vlookup函數的七個經典查詢操作。大家一起來學習一下吧！
  • EXCEL表格詳解vlookup第五彈-VBA自己寫myLookup函數
    我們在前四彈詳細介紹了vlookup函數，以及vlookup函數實現不了後，使用index為主函數加match，small，if等函數的組合拳來實現複雜的條件查找。由於大家從事的工作領域不同，有時候還需要實現更加複雜的查找，這時往往在幾個函數之間相互切換，相互套用，難免給一些相對固定的查找帶來不必要的麻煩。下面我就手把手地帶大家寫一個自己的函數myLookup() ，使用這一個函數來實現前四彈的所有功能。
  • EXCEL公式函數系列 之 查找與引用函數VLOOKUP
    上圖中EXCEL告訴我們該函數用於搜索表區域首列滿足條件的元素，確定待檢測單元格在區域中的行序號，再進一步返回選定單元格的值。這個解釋裡面的重點已經用粗體標註出來，等下會集中解釋。這一點很多地方講這個函數都沒有說清楚，也是新手用這個函數容易出錯的地方。這個地方是可選項，不填默認為true，即近似值。大部人都是在一類的一個數據區域內查找精確的值，如果在一堆訂單號中查找相應的訂單，使兩個表對應起來。訂單號格式都是一樣的，默認或者填了TRUE，系統查找近似結果，訂單格式一樣所以很容易就被近似值取代了，然後這個公式就用錯了。
  • 表格排序那些不一樣的使用技巧 配合函數還可以這樣玩
    今天我們來講講排序的一些技巧，可能你平時用的技巧是正常的排個升序降序。請先看下圖，行列順序都不一致，我需要左邊的跟右邊的排序一致：你還在複製粘貼或者shift+滑鼠拉動嗎？如何行列足夠多的話，這個工作量不是一般的大。
  • Excel中Vlookup函數不能做的,lookup函數輕輕鬆鬆完成!
    工作中，vlookup函數是大眾情人，但也有它完成不了的工作，這個時候用lookup函數便可以輕鬆的完成！1、查找最後一條記錄例如公司會持續進不同的物品，不同的數量，現在我們需要求出最後一次進貨的數量是多少？
  • Excel函數公式:含金量超高的VLOOKUP、LOOKUP實用技巧解讀
    實際工作中，凡是提到查找引用的，90%以上的人第一時間想到的肯定是VLOOKUP函數，但其實VLOOKUP函數有很多缺點啊，例如不能直接逆向查詢等……但對於LOOKUP函數來說，都是小菜一碟。一、LOOKUP函數功能及語法結構。作用：從單行或單列或數組中查找對應的值。
  • 一文講解vlookup函數的基本使用方法,正向逆向,多種方法教給你
    記得在初學Excel的時候，遇到兩大頭疼的問題，一是vlookup函數學不會，二是透視表不會用，而這兩大數據處理利器，在工作當中十分實用，可以說不會這兩個，那就不算會用Excel。今天就來說說如何快速入門vlookup函數！
  • VLOOKUP 函數的終結者來了- XLOOKUP 函數使用介紹(一)
    自從office 365推出Xlookup函數，大家都在津津樂道這個函數的強大，直言不諱指出這個函數將是Vlookup函數和Hlookup函數的替代者。今天我們就來看一下這個函數到底有多麼強大可以替代Excel中的Vlookup函數呢？
  • Excel vlookup函數的多條件操作實例及if{1,0}數組組合剖析
    Excel 中的 vlookup函數可結合 if 多條件查找，並且可用數組作為 if 的條件，這樣可以同時查找多個欄位，例如查找服裝銷量表中分類為襯衫且價格為85元的服裝。文章先列舉了四個vlookup函數的多條件操作實例，然後對它們逐一剖析，主要剖析if{1,0}數組條件；四個操作實例分別為：實例1：條件用 IF{1,0}、實例2：條件用 IF{0,1}、實例3：兩列連接查找，條件用 IF{1,0}與用 & 連接查找區域和實例4：兩列連接查找，條件用 IF{0,1}與用 & 連接查找區域。實例中操作所用版本均為 Excel 2016。
  • Excel函數公式:LOOKUP函數單條件、多條件查詢公式技巧解讀
    LOOKUP函數是我們常用的查找函數之一，其語法決定，想要得到正確的查詢結果，必須對查詢的數據進行升序排序，但是一般情況下我們都不會先排序在查詢，而是採用：=LOOKUP(1,0/(B3:B9=H3),C3:C9)類似結構的語法來完成查詢。