在 Office365 和 Excel2021 版本中，有一個非常好用的函數(shù)——Filter。

它基本上可以代替之前在函數(shù)界稱霸武林十幾年之久的萬金油函數(shù)組合（Index+small+if+row）。

我們在之前的文章中也介紹過該函數(shù)的基本用法和它的厲害之處。

但是，今天要介紹的這兩個神秘用法，一般人還真不一定知道！

有點好奇？那就趕緊上車，跟我一起來看看吧！

實現(xiàn)二維方向查找

在之前我們介紹該函數(shù)的用法中，一般都是在一維方向?qū)崿F(xiàn)單條件或者多條件查找引用。

如下圖，是一張銷售明細表，需要查找客戶名稱為：AA 的商品名稱有多少種。

我們可以在【I4】單元格輸入如下公式：

• =FILTER(C3:C9,B3:B9=J2)

公式的意思是：

在 B 列的客戶名稱中，如果有與客戶名稱 AA 相同的，就返回顯示 C 列中對應(yīng)的商品名稱。

如果需要查看下單日期，那我們就必須把公式的第一參數(shù)引用區(qū)域更改成 A 列。

公式如下：

• =FILTER(A3:A9,B3:B9=J2)

那么再想查看規(guī)格型號，就要再次更改第一參數(shù)的引用區(qū)域。

這樣操作是不是很麻煩？

那么如何才能在一個二維的區(qū)域中，快速查找并顯示我們想要的數(shù)據(jù)，而不需要每次更改公式的引用區(qū)域呢？

這時我想起了那樣一句經(jīng)典的話：

沒有什么問題是用一個函數(shù)解決不了的。

如果有，那就再來一個。

好吧，那我們就再來一個 Fliter 函數(shù)吧，

把上面的公式稍微改動下，如下圖：

公式如下：

• =FILTER(FILTER(A3:G9,B3:B9=J2),A2:G2=K2)

公式解析：

? 先用第一個 FILTER 函數(shù)（FILTER(A3:G9,B3:B9=J2)）篩選出【A3:G9】單元格的符合條件的全部數(shù)據(jù)區(qū)域。

如下圖：

? 然后在這個結(jié)果的基礎(chǔ)上，再進一步篩選：=FILTER(?,A2:G2=K2)

第二參數(shù)的條件設(shè)置為：【A2:G2】單元格的內(nèi)容如果等于【K2】單元格的內(nèi)容，就返回結(jié)果 TRUE，其他返回 FALSE。

如果結(jié)果為 true 的就顯示所在的那一列全部內(nèi)容。

其他返回 FALSE 的，將不顯示。

此時【K2】單元格內(nèi)容是下單日期，所以只顯示下單日期：

如果，我們再想查看商品名稱，就只需要把【K2】單元格的內(nèi)容更改成商品名稱即可。

想看哪個就顯示哪個，再也不用一個一個更改引用區(qū)域了。

是不是方便的不要不要的呢！

顯示指定列內(nèi)容


在某些工作場景下，需要根據(jù)銷售明細表，來制作指定格式的客戶對賬單。

如下圖：需要根據(jù)上面的銷售明細表中的數(shù)據(jù)，提取其中的【下單日期】、【商品名稱】、【規(guī)格型號】、【金額】等這四列，其他列則不需要顯示出來。

你可能會像下面這樣寫出公式。

公式如下：

• =FILTER(明細!$A$3:$A$9,明細!$B$3:$B$9=B2)

這個就是最基本的用法。

但是每換一列，就需要更改單元格區(qū)域。這里有四列，那就需要寫四個公式。無法自動化。

如何解決這個問題呢？這里有兩種方法。

▋方法一

還是用和上面類似的方法，使用兩個 Filter 函數(shù)來實現(xiàn)。

公式如下：

• =FILTER(FILTER(明細!$A$3:$G$9,明細!$B$3:$B$9=$B$2),明細!$A$2:$G$2=A3)

公式解析：

? 第一個 FILTER(明細!$A$3:$G$9,明細!$B$3:$B$9=$B$2)；

顯示全部符合條件的數(shù)據(jù)。

? 第二個FILTER(?,明細!$A$2:$G$2=A3)；

在第一步的基礎(chǔ)上，將明細表中的 A2:G2 單元格中的等于 A3 的內(nèi)容顯示出來。

這里的與上面的原理相同。只是把【A3】變成了可以變動的單元格。然后向右拖動就可以顯示相對應(yīng)的數(shù)據(jù)了。

一直拖動到【D4】單元格。

這樣所有列就全部顯示出來了！

▋方法二

也可以用下面這個公式解決，如下圖：

公式如下：

• =FILTER(FILTER(明細!$A$3:$G$9,明細!$B$3:$B$9=$B$2),{1,0,1,1,0,0,1})

公式解析：

這里是利用 1 和 0 就可以解決多列顯示的問題。

因為在 Excel 中，1 代表 True,0 代表 False。

明細表中，【$A$3:$G$9】一共有 7 列，那么第二個 filter 的第二參數(shù)，也由 7 個 1 和 0 組成。

如果需要顯示的列就寫 1，不需要顯示的列就寫 0。

最后就完美解決上面的問題啦！

PS.?一定要記住的是：第二參數(shù)中 1 和 0 之間的分隔符號要用逗號，而不能用分號噢。

如果用分號將返回錯誤值。

寫在最后


今天，我們介紹了 filter 函數(shù)的兩個神秘用法。

??用兩個 filter 函數(shù)實現(xiàn)二維方向查找，突破之前僅僅用于一維方向的查找引用

。
? 顯示指定列內(nèi)容。利用數(shù)字 1 和 0 組成的常量數(shù)組，輕松實現(xiàn)想要顯示的列內(nèi)容。這種方法無論是指定列在數(shù)據(jù)源中的排列順序是什么，都可以輕松實現(xiàn)。

好了，今天的分享就到這里，如果喜歡此文，歡迎點贊&轉(zhuǎn)發(fā)！

如果你想系統(tǒng)性學習 Excel。

正好，我們秋葉家的《秋葉 Excel 3 天集訓營》專為職場人準備，全部基于職場真實表格案例設(shè)計，還有很多超實用 Excel 技巧教學。?

每天學習大概?30 分鐘，從日常的功能出發(fā)，全程演示，一課一練，夯實進階每一步。

秋葉 Excel 3 天集訓營

原價?99 元??

現(xiàn)在?只需 1?元

?每天學習?30 分鐘

你也有可能成為 Excel 高手！

現(xiàn)在就掃碼報名吧！

??????

▲

報名成功后將自動彈出班主任二維碼

切勿提前退出

??