您現在的位置是:首頁 > 垂釣
Excel一對多查詢,vlookup不能解決的它能解決
- 由 職場聊聊天 發表于 垂釣
- 2022-11-02
函式為什麼不能一對多
說到查詢,是不是大家想到的就是vlookup。但是vlookup只能用於一對一的查詢。如果一個查詢值有對應多個目標值,這種一對多的查詢是vlookup解決不了的。今天給大家說說這種一對多的查詢要如何實現。
以下面的例子為例,一個產品有不同的有效期,我需要查找出來並放在對應的產品下面,這個時候就需要用到index+small+if的結合。
公式:
=IFERROR(INDEX($R$1:$R$12,SMALL(IF($Q$1:$Q$12=U$1,ROW($Q$1:$Q$12)),ROW(Q1))),“”)
公式解析:
①用Small+IF函式構成陣列來確定第幾個值:
IF($Q$1:$Q$12=U$1,ROW($Q$1:$Q$12))
==如果Q列的值等於U$1,就返回對應的行,如果不等於,就為FALSE
==即:{FALES,2,FALSE,FALSE,5,FALSE,FALSE,8,FALSE, FALSE, FALSE, FALSE }
SMALL(IF($Q$1:$Q$12=U$1,ROW($Q$1:$Q$12)),ROW(Q1))
Small(數值範圍,第幾個最小值)
==在IF函式構成的數組裡面,定位到第ROW(Q1)個的最小值,返回的結果即是行序號。比如在
{FALES,2,FALSE,FALSE,5,FALSE,FALSE,8,FALSE, FALSE, FALSE, FALSE }裡,第一個最小值是2,
Small
函式返回的是2,
第二個最小值是5,
Small
函式返回的是5……以此類推。
★
ROW(Q1)這裡的“相對引用”,也就是下拉的時候會變成ROW(Q2)/ROW(Q3)……目的是為了能定位到第1/2/3……個最小值
②用
INDEX來定位
INDEX($R$1:$R$12,SMALL(IF($Q$1:$Q$12=U$1,ROW($Q$1:$Q$12)),ROW(Q1)))
INDEX (資料範圍,行序號,列序號)
== INDEX(在$R$1:$R$12裡,定位到符合條件的行號)
這樣子出來的結果就是查詢到產品的不同有效期了~如果覺得難以理解的話可以直接複製公式,修改相關引數就好~
下面附上計算的過程更方便大家理解~
其實不用公式直接用資料透視表+vlookup也能輕鬆實現,大家想到這樣子操作的實現步驟嗎?