當前位置: 妍妍網 > 辦公

只會Vlookup就out了!工作最常用的8大Excel多條件尋找公式,都在這

2024-07-13辦公

編按

Hello小夥伴們~在表格中常見的單條件尋找目標值相信大家都會,用過VLOOKUP函式的小夥伴可以說是很輕松就能解決單條件尋找的問題,但是當單條件變成多條件的時,你還會用函式尋找嗎?今天小編整理了常見的二維數據中多條件尋找的公式大全送給大家。


今天我們來學習函式解決多條件尋找的多種方法,相信總有一種適合你的。
下圖效果是當條件①和條件②變化時,會自動在左邊的數據區域中尋找出業績。

這是一份隨機模擬的1~6月銷售人員業績數據表格,如下圖:

我們現在需要設定公式,透過條件①和條件②尋找出對應業績數據。第一眼看到這種問題,你的反應是使用什麽函式來解決?我們來個小互動,想到VLOOKUP函式的小夥伴在評論區留言,想到其他函式的聊一聊是為什麽。

在講解公式之前,我們先在I3、K3單元格分別輸入「姓名」和「月份」,選中I4單元格,點選【數據】索引標籤下的「數據驗證功能」。在彈出來的對話方塊中,在 「允許」的下拉選單中選擇「序列」。

在「來源」文字域中輸入=$C$3:$G$3,單擊「確定」按鈕。K4單元格中的月份操作相同,不同的只是需要在「來源」文字域中輸入=$B$4:$B$9。

返回工作表中,單擊設定了數據驗證單元格,在單元格右側會出現一個下拉按鈕,單擊該按鈕可選擇指定序列內容。這個很簡單,我們就不多贅述啦,現在正式進入 多條件尋找公式講解部份

1.VLOOKUP+MATCH多條件尋找

在K6單元格中輸入公式 =VLOOKUP(K4,B:G,MATCH(I4,B3:G3,0),0)

公式語法解釋:

VLOOKUP函式語法:(尋找值,尋找區域,返回值的列數,精確尋找或模糊尋找)

K4單元格是對應條件②的月份值,尋找區域為B:G列,尋找區域中的列數使用MATCH函式進行判斷。

MATCH函式的語法:(尋找物件,指定尋找的範圍或者陣列,尋找方式)

MATCH的物件就是條件①的姓名,尋找範圍就是B3:G3,尋找方式選擇0為精確尋找。

因為VLOOKUP第三參數返回的列數是根據條件①的姓名動態變化而變化,所以巢狀MATCH函式對條件①姓名進行尋找。

2.HLOOKUP+MATCH多條件尋找

在K6單元格中輸入公式 =HLOOKUP(I4,3:9,MATCH(K4,B3:B9,0),0)

公式語法解釋:

HLOOKUP函式語法:(尋找值,尋找範圍,返回值所在的行數,尋找模式)

MATCH函式語法前面解釋過了,這裏就不再重復解釋了。重點講一下HLOOKUP和VLOOKUP函式的區別,從名稱表面上看只有首字母的差異,V是Vertical的第一個字母,單詞意思是垂直方向,所以VLOOKUP函式代表著垂直方向尋找,H是Horizontal的第一個字母,單詞意思水平方向,HLOOKUP就表示水平方向尋找。

3.INDEX+MATCH+MATCH多條件尋找

在K6單元格中輸入公式 =INDEX(B3:G9,MATCH(K4,B3:B9,0),MATCH(I4,B3:G3,0))

公式語法解釋:

INDEX函式語法:(尋找區域或陣列常量,返回值所在區域的行號,返回值所在區域的列號)

因為INDEX函式中第二參數返回是行號,第三參數返回的是列號,行號和列號都是變量,這時我們可以使用MATCH函式分別對行號條件①「姓名」和條件②「月份」進行尋找。

4.SUMPRODUCT多條件尋找

在K6單元格中輸入公式 =SUMPRODUCT((B4:B9=K4)*(C3:G3=I4)*C4:G9)

公式語法解釋:

SUMPRODUCT函式語法:((條件1=條件區域1)*(條件2=條件區域2)*……*(求和區域))

該函式的語法結構很有規律,也便於記憶理解,所以基本不需要怎麽解釋相信大家看到語法的時候就懂了。

5.SUMIF+OFFSET+MATCH多條件尋找

在K6單元格中輸入公式 =SUMIF(B:B,K4,OFFSET(B:B,0,MATCH(I4,C3:G3,0)))

公式語法解釋:

SUMIF函式語法:(條件區域,指定的條件,需要求和的區域)

OFFSET函式語法:(起始單元格,移動的行數,移動的列數,高度,寬度)

這個方法從外觀上看上去巢狀了三個函式,實際底層邏輯只是透過SUMIF條件求和的原理搭配OFFSET和MATCH函式進行動態尋找。

6.DSUM多條件尋找

前面介紹的五種方法基本都使用了函式巢狀才完成了多條件尋找,DSUM函式對多條件尋找就不需要巢狀那麽麻煩了,在K6單元格中輸入公式 =DSUM(B3:G9,I4,K3:K4) 即可。

公式語法解釋

DSUM(數據區域,求和的列數,條件區域)

需要註意的是在選擇第三參數時,必須包含選擇區域欄位標題,不可以像別的函式一樣只選擇一個單元格。

7.XLOOKUP多條件尋找 (office365專屬函式)

在K6單元格中輸入公式 =XLOOKUP(K4,B4:B9,XLOOKUP(I4,C3:G3,C4:G9),0)

公式語法解釋

XLOOKUP函式語法:(要尋找的值,尋找的區域,返回的區域)

註意這個函式只在Office 365版本的Excel中才有的哦。

8.INDEX+FILTER+MATCH多條件尋找

在K6單元格中輸入公式: =INDEX(FILTER(B3:G9,B3:B9=K4),MATCH(I4,B3:G3,0))

FILTER也是Office 365專屬函式,INDEX和MATCH這兩個函式前面幾種方法已經出現很多次了,相信大家一定不陌生了,在條件尋找中MATCH函式具有「名配角」的美稱。

介紹一下FILTER函式,該函式語法結構:(資料來源,篩選條件,容錯值)

FILTER函式在單條件尋找的時候還可以動態溢位尋找結果,自動擴充套件填充尋找值。

最後,歡迎加入Excel函式訓練營,學習68個函式、配套練習課件、輔導答疑。