vlookup配合一個等號,輕鬆搞定一對多查詢,設計得太巧妙了
Hello,大家好,今天跟大家分享我們如何使用vlookup函數來解決一對多查詢的問題,相信很多人都聽過到一對多查詢,但是不明白具體是什麼意思,簡單來説一對多查詢就是通過查找一個值來返回多個結果,比如我們想要通過班級找到這個班級中的所有學生,説的再通俗一點其實就是篩選,只不過我們需要使用公式來獲得篩選的結果,這個的操作其實也非常的簡單,下面就讓我們來一起操作下吧
一、設置輔助列
在這裏我們想要通過查找4班,來找到4班所有學生的姓名。首先我們在數據的最前面插入一個空白列,在這一列中A1單元格什麼數據都不要輸入,在A2單元格中輸入公式:=(B2=$G$2)+A1隨後向下填充數據,這樣的話輔助列就構建完畢了,這個函數的作用是每遇到一個4班就會增加1
跟大家簡單的講解下公式的計算原理,公式為:=(B2=$G$2)+A1,B2=$G$2它的結果是一個邏輯值true或者false,我們可以將true看作是1,false看做是0。在A2單元格中條件是成立的,而A1單元格是空白值所以函數的結果就是1。向下填充公式的時候,在A3單元格中的公式為:(B3=$G$2)+A2,在這裏條件不成立結果是false也就是0,A2的結果是1所以函數的結果就是1,直到遇到下一個4班函數的結果才會增加1,這個就是函數的計算過程
二、一對多查詢
雖然我們構建的輔助列它是有重複值的,但是我們需要的結果他都是在重複值的第一個位置,這樣的話我們可以將從1開始的序列作為查找值,來找到準確的結果。在公式中可以利用row(A1)來獲取一個從1開始的序列,我們只需要將公式設置為:=VLOOKUP(ROW(A1),$A$1:$D$16,3,0)向下填充即可找到正確的結果,簡單的跟大家講解下他的計算原理
公式:=VLOOKUP(ROW(A1),$A$1:$D$16,3,0),這個函數其實就是vlookup的常規用法
第一參數:ROW(A1),查找值,利用row函數構建從1開始的序列
第二參數:$A$1:$D$16,查找的數據區域
第三參數:3,需要查找的結果在數據區域的第三列
第四參數:0,精確匹配
在這裏我們儘量將公式向下多拖動一些,因為如果姓名的個數大於公式的個數,我們就無法找到全部的姓名了,必須要保證公式的個數大於姓名的個數才行,但是多出的公式會被顯示為錯誤值,這個時候可以使用IFERROR函數來屏蔽下錯誤值,公式為:=IFERROR(VLOOKUP(ROW(A1),$A$1:$D$16,3,0),"")
以上就是今天分享的全部內容,怎麼樣?你學會了嗎?
我是Excel從零到一,關注我,持續分享更多Excel技巧