Office 365 帶來了一些很棒的功能，例如 XLOOKUP、SORT 和 FILTER。
在 Excel 中過濾數據時，在 Office 365 之前的世界中，我們主要依賴 Excel 內置過濾器或最多高級過濾器或複雜的 SUMPRODUCT 公式。如果您必須過濾數據集的一部分，這通常是一個複雜的解決方法。
但是有了新的 FILTER 功能，現在真的很容易根據條件快速過濾部分數據集。
在本教程中，我將向您展示新的 FILTER 功能有多棒，以及您可以用它做的一些有用的事情。
但在我進入示例之前，讓我們快速了解一下 FILTER 函數的語法。
如果您想在 Excel 中獲得這些新功能，您可以升級到 Office 365（加入內部計劃以訪問所有功能/公式）
Contents
Excel 篩選函數 – 語法
下面是 FILTER 函數的語法：
=FILTER(array,include,[if_empty])
- array – 這是您擁有數據並希望從中過濾一些數據的單元格範圍
- include – 這是告訴函數要過濾哪些記錄的條件
- [if_empty] – 這是一個可選參數，您可以在其中指定在 FILTER 函數未找到結果時返回的內容。默認情況下（未指定時），它返回#CALC！錯誤
現在讓我們看看一些令人驚嘆的過濾器函數示例和它可以做的事情，這些事情在沒有它的情況下曾經非常複雜。
示例 1：根據一個條件（區域）過濾數據
假設您有一個如下所示的數據集，並且您只想過濾美國的所有記錄。
下面是執行此操作的 FILTER 公式：
=FILTER($A$2:$C$11,$B$2:$B$11="US")
上式以數據集為數組，條件為$B$2:$B$11=”US”
此條件將使 FILTER 函數檢查 B 列中的每個單元格（具有該區域的單元格），並且僅過濾與此條件匹配的那些記錄。
此外，在此示例中，我將原始數據和過濾後的數據放在同一張工作表上，但您也可以將它們放在單獨的工作表甚至工作簿中。
過濾函數返回一個動態數組的結果（這意味著它不是返回一個值，而是返回一個溢出到其他單元格的數組）。
為此，您需要有一個結果為空的區域。在該區域中的任何單元格（本例中的 E2:G5）中已經有一些東西，該函數會給你 #SPILL 錯誤。
此外，由於這是一個動態數組，您不能更改結果的一部分。您可以刪除具有結果的整個區域或單元格 E2（輸入公式的位置）。這兩個都會刪除整個結果數組。但是您不能更改任何單個單元格（或刪除它）。
在上面的公式中，我對區域值進行了硬編碼，但您也可以將它放在一個單元格中，然後引用具有該區域值的那個單元格。
例如，在下面的示例中，我在單元格 I2 中有區域值，然後在公式中引用它：
=FILTER($A$2:$C$11,$B$2:$B$11=I1)
這使公式更加有用，現在您只需更改單元格 I2 中的區域值，過濾器就會自動更改。
您還可以在單元格 I2 中有一個下拉菜單，您可以在其中簡單地進行選擇，它會立即更新過濾的數據。
示例 2：根據一個標準（大於或小於）過濾數據
您還可以在過濾器函數中使用比較運算符並提取大於或小於特定值的所有記錄。
例如，假設您有如下所示的數據集，並且您想要過濾所有銷售額超過 10000 的記錄。
下面的公式可以做到這一點：
=FILTER($A$2:$C$11,($C$2:$C$11>10000))
數組參數指的是整個數據集，在這種情況下，條件是 ($C$2:$C$11>10000)。
該公式檢查 C 列中的值的每條記錄。如果該值大於 10000，則將其過濾，否則將其忽略。
如果您想獲取所有小於 10000 的記錄，可以使用以下公式：
=FILTER($A$2:$C$11,($C$2:$C$11<10000))
您還可以使用 FILTER 公式獲得更多創意。例如，如果要根據銷售額篩選前三名記錄，可以使用以下公式：
=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))
上面的公式使用 LARGE 函數得到數據集中第三大的值。然後在 FILTER 函數條件中使用該值來獲取銷售額大於或等於第三大值的所有記錄。
示例 3：使用多個條件 (AND) 過濾數據
假設您有以下數據集，並且您想要過濾銷售價值超過 10000 的美國的所有記錄。
這是一個 AND 條件，您需要檢查兩件事 – 地區需要到美國，銷售額需要超過 10000。如果只滿足一個條件，則不應過濾結果。
下面是過濾器公式，它將過濾以美國為地區且銷售額超過 10000 的記錄：
=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))
請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)
由於我使用了兩個條件並且我需要兩者都為真，因此我使用了乘法運算符來組合這兩個條件。這將返回一個由 0 和 1 組成的數組，其中只有兩個條件都滿足時才返回 1。
如果沒有符合條件的記錄，該函數將返回#CALC！錯誤。
如果你想返回一些有意義的東西（而不是錯誤），你可以使用如下所示的公式：
=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")
在這裡，我使用了“未找到”作為第三個參數，當沒有找到符合條件的記錄時使用它。
示例 4：使用多個標準 (OR) 過濾數據
您還可以修改 FILTER 函數中的“包含”參數以檢查 OR 條件（其中任何一個給定條件都可以為真）。
例如，假設您有如下所示的數據集，並且您想要過濾國家/地區為美國或加拿大的記錄。
下面是執行此操作的公式：
=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))
請注意，在上面的公式中，我只是使用加法運算符將兩個條件相加。由於這些條件中的每一個都返回一個 TRUE 和 FALSE 數組，因此我可以添加一個組合數組，如果滿足任何一個條件，它就為 TRUE。
另一個示例可能是當您想要過濾國家/地區為美國或銷售價值超過 10000 的所有記錄時。
下面的公式將做到這一點：
=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))
注意：在 FILTER 函數中使用 AND 條件時，使用乘法運算符 (*)，在使用 OR 條件時，使用加法運算符 (+)。
示例 5：過濾數據以獲取高於/低於平均水平的記錄
您可以使用 FILTER 函數中的公式來過濾和提取值高於或低於平均值的記錄。
例如，假設您有如下所示的數據集，並且您想要過濾銷售價值高於平均水平的所有記錄。
您可以使用以下公式執行此操作：
=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))
同樣，對於低於平均水平，您可以使用以下公式：
=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))
示例 6：僅過濾偶數記錄（或奇數記錄）
如果您需要快速過濾和提取偶數行或奇數行中的所有記錄，您可以使用 FILTER 函數來完成。
為此，您需要檢查 FILTER 函數中的行號，並且只過濾符合行號條件的行號。
假設您有如下所示的數據集，並且我只想從該數據集中提取偶數記錄。
下面是執行此操作的公式：
=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)
上面的公式使用 MOD 函數來檢查每條記錄的行號（由 ROW 函數給出）。
公式 MOD(ROW(A2:A11)-1,2)=0 在行號為偶數時返回 TRUE，在奇數時返回 FALSE。請注意，我已從 ROW(A2:A11) 部分中減去 1，因為第一條記錄位於第二行中，這會調整行號以將第二行視為第一條記錄。
同樣，您可以使用以下公式過濾所有奇數記錄：
=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)
示例 7：使用公式對過濾後的數據進行排序
將 FILTER 函數與其他函數一起使用可以讓我們完成更多工作。
例如，如果您使用 FILTER 函數過濾數據集，則可以使用 SORT 函數來獲取已排序的結果。
假設您有一個如下所示的數據集，並且您想要過濾所有銷售額超過 10000 的記錄。您可以將 SORT 函數與該函數一起使用，以確保根據銷售額對結果數據進行排序。
下面的公式將做到這一點：
=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)
上面的函數使用 FILTER 函數來獲取 C 列中銷售額大於 10000 的數據。然後在 SORT 函數中使用 FILTER 函數返回的這個數組，根據銷售額對這些數據進行排序。
SORT 函數中的第二個參數是 3，即根據第三列進行排序。第四個參數是-1，即按降序對這些數據進行排序。
所以這些是在 Excel 中使用 FILTER 函數的 7 個示例。
希望您發現本教程有用。
