如何在Excel中使用高級過濾器 - 公式的標準範圍示例

該教程顯示瞭如何在Excel中使用高級過濾器，並提供了許多非平凡的標準範圍示例來創建一個案例敏感的過濾器，查找兩列之間的匹配和差異，匹配較小列表的提取記錄等等。

在上一篇文章中，我們討論了Excel Advanced Filter的不同方面，以及如何使用它和或邏輯過濾行。現在您知道了基礎知識，讓我們來看看更複雜的標準範圍示例，可能對您的工作有幫助。

• 如何設置基於公式的標準範圍
• 高級過濾標準範圍示例
  • 案例敏感過濾器的文本值
  • 列高於或低於平均列的濾波器值
  • 用空白 /非藍色單元格過濾行
  • 提取頂部 /底部N記錄
  • 濾鏡匹配和2列之間的差異
  • 匹配或不匹配列表中的項目的過濾行
  • 過濾週末和工作日

設置基於公式的標準範圍

由於本教程中討論的大多數標準範圍示例將包括各種公式，因此讓我們從定義正確設置它們的基本規則開始。相信我，這個小的理論將為您節省大量時間，並節省對您的複雜標准進行故障排除的頭痛，其中包括基於公式的多個條件。

• 您在標準範圍內使用的公式必須評估為True或False 。
• 標準範圍應至少包含2個單元格：配方奶細胞和標頭單元。
• 基於公式的標準的標題小區應與任何表（列表範圍）標題的空白或不同。
• 對於要在列表範圍內評估每一行的公式，請使用諸如a1之類的相對參考的數據參考最高單元格。
• 對於僅針對特定單元格或範圍進行評估的公式，請參考該單元格或範圍使用絕對參考（例如$ a $ 1）。
• 在公式中引用列表範圍時，請務必使用絕對的單元格引用。
• 在提供多種條件時，請輸入同一行上的所有標準，與AN和操作員一起加入它們，並將每個標準放在單獨的行上，以與OR操作員一起加入它們。

Excel高級過濾標準範圍示例

以下示例將教您如何在Excel中創建自己的過濾器，以處理無法使用常規Excel Autofter執行的更複雜的任務。

案例敏感過濾器的文本值

除了Excel Autofter外，Advanced Filter工具本質上是對大小寫的不敏感的，這意味著它在過濾文本值時不會區分大寫和小寫字符。但是，您可以通過使用高級過濾器標準中的精確函數輕鬆地執行對案例敏感的搜索。

例如，要過濾包含香蕉的行，忽略香蕉和香蕉，請在標準範圍內輸入以下公式：

=EXACT(B5, "Banana")

其中b是包含項目名稱的列，而第5行是第一個數據行。

然後，通過單擊“數據”選項卡上的“高級”按鈕應用Excel高級過濾器，然後配置列表範圍和標準範圍，如下面的屏幕截圖所示。請注意，標準範圍包括2個單元格 -媒體電池和配方奶單元。

筆記。上面的圖像以及本教程中的所有進一步屏幕截圖僅出於清晰度而顯示標準範圍單元中的公式。在您的真實工作表中，公式單元格應返回對還是錯，具體取決於數據的第一行是否匹配標準：

列高於或低於平均列的濾波器值

在過濾數字值時，您通常只想僅顯示列中平均值以上或低於平均值的單元格。例如：

要濾波以高於平均水平的行濾波行，請在標準範圍內使用以下公式：

=F5>AVERAGE($F$5:$F$50)

要濾除以下平均水平的行，請使用以下公式：

=F5<average></average>

請注意，我們使用相對引用來指帶有數據（F5）的頂級電池，並且絕對引用以定義您要計算平均值的整個範圍，不包括列標題（$ f $ 5：$ f $ 50）。

以下屏幕截圖顯示了上述平均公式：

那些熟悉Excel號碼過濾器的人可能會想知道，為什麼有人會不願意使用高級過濾器，而內置號碼過濾器已經具有高於平均水平和低於平均水平的選項？是的，但是內置的Excel過濾器不能與邏輯一起使用！

因此，為了進一步以此示例，讓我們過濾行，該行子（列F）或9月銷售（E列）高於平均水平。為此，通過在單獨的行上輸入每個條件來設置使用或邏輯的標準範圍。結果，您將獲得E或F列中具有以上平均值的項目列表：

用空白或非空白的過濾行

眾所周知，Excel Filter具有過濾空白單元的內置選項。通過在“自動濾波器”菜單中選擇或取消選擇（空白）複選框，您只能在一個或多個列中顯示那些具有空單元或非空單元的行。問題在於，空白的內置Excel濾波器只能與邏輯一起使用。

如果您想用或邏輯過濾空白或非空白單元格，或將空白 /非空白條件與其他一些標準一起使用，請使用以下公式之一設置高級濾波器標準範圍：

過濾器空白：

top_cell =“”

濾波器非空白：

top_cell “”

用或邏輯過濾空白單元格

要過濾A或B列或兩列中具有空白單元格的行，請以這種方式配置高級濾波器標準範圍：

• =A6=""
• =B6=""

其中6是最高數據。

用或井以及邏輯過濾非空白的單元格

為了進一步了解Excel的高級過濾器如何使用多個標準工作，讓我們在樣本表中使用以下條件過濾行：

• 區域（A列）或項目（B列）應非空白，並且
• 亞類（C列）應大於900。

換句話說，我們想顯示符合以下條件的行：

（小計> 900和區域=非藍蘭）或（小計> 900 ， item =非藍蘭）

如您所知，在Excel高級濾波器標準範圍內，與邏輯相連的條件應在同一行中輸入，並且與OR邏輯相連的條件 - 在不同的行上：

因為此示例中的一個標準用一個公式（非空白）表示，另一個標準包括一個比較操作員（亞電> 900），請讓我提醒您：

• 由比較操作員形成的標準應具有完全等於桌子標題的標準，例如上面屏幕截圖中的次數標準。
• 基於公式的標準應具有空白的標題單元格或不匹配任何桌子標題的標題，例如上面屏幕截圖中的非空白標準。

如何提取頂部/底部N記錄

您可能知道，Build-In-In Excel號碼過濾器可以選擇顯示前10個或底部10個項目。但是，如果您需要過濾前3個或底部5個值怎麼辦？在這種情況下，具有以下公式的Excel高級過濾器派上用場：

提取頂級n個項目：

top_cell > =大（範圍，n）

提取底部N項目：

top_cell range ，n）

例如，要過濾前3個小計，請使用此公式創建標準範圍：

=F5>=LARGE($F$5:$F$50,3)

要提取底部3個小計，請使用此公式：

=F5>=SMALL($F$5:$F$50,3)

其中F5是最高的單元格，該單元格中的數據列（不包括列標題）。

以下屏幕截圖顯示了行動中的前三名公式：

筆記。如果列表範圍包含幾行，其值與落入頂部/底部n列表中的值相同，則將顯示所有這些行，如下面的屏幕截圖所示：

過濾匹配和兩列之間的差異

我們以前的文章之一解釋了比較Excel中兩列並找到匹配和差異的多種方法。除了Excel公式外，有條件的格式規則和上述教程中涵蓋的重複卸載工具還可以使用Excel的高級過濾器來提取在兩個或更多列中具有相同或不同值的行。為此，在標準範圍內輸入以下簡單公式之一：

• 匹配的過濾器（重複）2列： =B5=C5
• 在2列中的差異（唯一值）的過濾器： =B5C5

其中B5和C5是您要比較的兩列中具有數據的最佳單元格。

筆記。高級過濾器工具只能在同一行中搜索匹配和差異。要查找A列中的所有值，但B列中沒有任何地方，請使用此公式。

根據列表中的匹配項過濾行

假設您有一張數百或數千行的大桌子，並且您收到了一個較短的列表，其中僅包含在給定時刻相關的項目。問題是 - 您如何找到表中或不在較小列表中的所有條目？

符合列表中項目的過濾行

要使用以下countif公式在較小列表中查找源表中也存在的所有項目：

countif（ list_to_match ， top_data_cell）

假設較小的列表在d2：d7的範圍內，並且要將表的項目與該列表進行比較，則在第10行開始中，該公式如下（請注意，絕對和相對引用的使用）：

=COUNTIF($D$2:$D$7,B10)

當然，您不僅只有一個標準過濾桌子。

例如，要過濾與列表匹配的行，但僅對於北部地區，請在同一行中輸入兩個條件，以便它們可以使用邏輯：

• 區域： ="=North"
• 匹配項目： =COUNTIF($D$2:$D$7,B10)

正如您在下面的屏幕截圖中看到的那樣，表中只有兩個記錄符合這兩個條件：

筆記。在此示例中，我們使用文本值的確切匹配標準： <em>="=North</em> "僅找到與指定文本完全等於的單元格。如果您僅像北一樣進入區域標準（沒有同等標誌和雙引號），Microsoft Excel將找到所有以指定文本（例如東北或西北）開頭的項目。有關更多信息，請參閱Excel Advanced Filter有關文本值。

不匹配列表中項目的過濾行

要查找表中不在較小列表中的所有項目，請檢查我們的Countif公式的結果是否等於零：

countif（ list_to_match ， top_data_cell） = 0

例如，要過濾列表中確實出現的北區項目，請使用以下標準：

• 區域： ="=North"
• 非匹配項： =COUNTIF($D$2:$D$7,B10)=0

筆記：

• 如果要匹配的列表位於不同的工作表中，請確保在公式中包含表名稱，例如=COUNTIF(Sheet2!$A$2:$A$7,B10) 。
• 如果要將結果提取到其他表格，請從目標表啟動高級過濾器，如如何將過濾的行提取到另一個工作表中所述。

週末和工作日的過濾器

到目前為止，我們的高級過濾條件範圍示例主要與數字和文本值有關。現在，是時候給那些在約會的人那裡提供一些線索了。

內置的Excel日期過濾器提供了多種涵蓋許多方案的選項。很多，但不是全部！例如，如果您獲得了日期清單並要求過濾工作日和周末，那麼您將如何處理？

如您所知，Microsoft Excel提供了一個特殊的工作日功能，該功能返回一周中與給定日期相對應的日期。正是我們將在Excel高級濾波器標準範圍內使用的功能。

如何在Excel中過濾週末

請記住，在工作日的時間裡，有1個代表週日和6個代表星期六，週末過濾的公式如下：

或（工作日（日期）= 7，工作日（日期）= 1）

在此示例中，我們正在以第5行開始過濾B列中的日期，因此我們的周末公式採用以下形狀：

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

如何在Excel中過濾工作日

要過濾工作日，請修改上述公式，以便將1（星期日）和7（星期六）遺漏：

和（工作日（日期） 7，工作日（日期） 1）

對於我們的示例表，以下公式將有效：

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

此外，您可以添加更多條件來過濾空白單元格： =B5""

要以其他方式過濾工作表中的日期，只需找到相關的日期函數，然後隨時在高級過濾條件範圍內使用它。

好吧，這就是您在Excel中使用高級濾波器使用複雜標準的方式。當然，您的選擇不僅限於本教程中討論的示例，我們的目標只是為您提供一些鼓舞人心的想法，這些想法將使您走上正確的軌道。記住，練習的精通之路是用練習鋪成的，您可能需要使用以下鏈接下載我們的示例，並擴展或反向工程師，以更好地理解。我感謝您閱讀，並希望下週在我們的博客上見到您！

練習工作簿

Excel高級過濾器示例（.xlsx文件）

以上是如何在Excel中使用高級過濾器 - 公式的標準範圍示例的詳細內容。更多資訊請關注PHP中文網其他相關文章！