函數FILTER當前在Excel2021,Excl365,最新WPS版本中可用。
=FILTER(查找區域,條件,備用返回值)
它可以很靈活地實現各種條件下的數據查找,在數據查找領域稱王稱霸30年之久的VLOOKUP在FILTER面前簡直就是弱雞。
一對一
=FILTER(D:D,A:A=F2)
在D列查找指定姓名對應的薪資。
![](https://news.xinpengboligang.com/upload/keji/5f20d223d5e7aa9fa92c1e211a99ef63.jpeg)
一對一
一對多
查找銷售部的姓名清單,一個查找條件返回多個查找結果,以數組的形式溢出顯示:
=FILTER(A:A,C:C=F1)
![](https://news.xinpengboligang.com/upload/keji/e5e4eee4f617d283487e13beafb25f1b.jpeg)
一對多
一對整
查找銷售部所有人的姓名及所有信息,隻需把整體數據區域作為查找區域:
=FILTER(A:D,C:C="銷售部")
![](https://news.xinpengboligang.com/upload/keji/8bcb5834968493d32d4950f5d5a1d506.jpeg)
一對整
橫向
FILTER也可以支持橫向數據的查找:
=FILTER($4:$4,$1:$1=B7)
![](https://news.xinpengboligang.com/upload/keji/5fe090bc5feca73dcfae93b029894fa8.jpeg)
橫向
多條件(與)
實際工作中查找條件很可能不止一個,如果是多個條件同時滿足,統統放進FILTER的第二參數,用星號(*)將其連接即可:
=FILTER(A:A,(B:B="女")*(D:D>8000))
註意第二參數包含的2個條件:性別為女;薪資大於8000
![](https://news.xinpengboligang.com/upload/keji/b776c019fd2cbe72d693acee3c618cc5.jpeg)
多條件(與)
多條件(或)
有多個條件,但隻需滿足其中之一或幾個,同樣把所有條件設置到第二參數,用加號( )連接即可:
=FILTER(A:A,(B:B="女") (D:D>8000))
![](https://news.xinpengboligang.com/upload/keji/beb8ab823ef4c87c0aa77677270843c7.jpeg)
多條件(或)
備用返回值
FILTER第三參數用於指定查找失敗時的返回值,例如找不到安全部時返回“沒有這個部門”:
=FILTER(A:D,C:C="安全部","沒有這個部門")
第三參數也可以再次套用函數來實現更為復雜的功能。
![](https://news.xinpengboligang.com/upload/keji/7a48f6d6d9177d55cdab046951473a9b.jpeg)
備用返回值
轉置
搭配TRANSPOSE可以將FILTER的結果進行行列轉置:
=TRANSPOSE(FILTER(A:A,C:C=E2))
![](https://news.xinpengboligang.com/upload/keji/abb9af5e7441e51afd8fe0dc5dd2a793.jpeg)
轉置
FILTER嵌套FILTER
二維數據的查找是Excel中經久不衰的話題,主流的方法是VLOOKUP MATCH,INDEX MACH.
FILTER FILTER嵌套也能實現同樣的效果:
=FILTER(FILTER($A$2:$D$9,$A$1:$D$1=G$1),$A$2:$A$9=$F2)
![](https://news.xinpengboligang.com/upload/keji/8192a527bd23f6fd6b06ef08696da73b.jpeg)
FILTER嵌套FILTER
返回不連續的列
在Excel365中搭配函數CHOOSECOLS可以讓二維查找變得更加簡單,即便是查找項目的數量和順序與原數據都不一致的情況:
=CHOOSECOLS(FILTER(A:F,B:B=H2),5,3,1,6)
其邏輯是FILTER查找返回整體數據後,用CHOOSECOLS提取所需的列。
相對於FILTER FILTER嵌套,這種方式更容易理解,也避免了繁雜的相對引用和絕對引用設置,大大降低公式難度。
![](https://news.xinpengboligang.com/upload/keji/6f936f25f3d000561ba7c099927ff3ff.jpeg)
返回不連續的列
公式中CHOOSCOLS的參數3,5,1,6表示從FILTER返回的數據中提取第3,5,1,6列,如果要返回的列數更多,手動輸入難免有出錯的風險,於是再嵌套XMATCH來獲取:
=CHOOSECOLS(FILTER(A:F,B:B=H2),XMATCH($I$1:$L$1,$A$1:$F$1,0))
XMATCH也是Excel365函數,其作用是返回I1:L1在A1:F1中的相對位置。
![](https://news.xinpengboligang.com/upload/keji/62a2f2fb85dcacaee07690e10dba54af.jpeg)
返回不連續的列