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

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

設置基于公式的標準范圍

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

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

Excel高級過濾標準范圍示例

以下示例將教您如何在Excel中創(chuàng)建自己的過濾器，以處理無法使用常規(guī)Excel Autofter執(zhí)行的更復雜的任務。

案例敏感過濾器的文本值

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

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

=EXACT(B5, "Banana")

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

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

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

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

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

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

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

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

=F5<average></average>

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

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

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

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

用空白或非空白的過濾行

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

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

過濾器空白：

濾波器非空白：

用或邏輯過濾空白單元格

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

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

其中6是最高數(shù)據(jù)。

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

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

• 區(qū)域（A列）或項目（B列）應非空白，并且
• 亞類（C列）應大于900。

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

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

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

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

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

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

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

提取頂級n個項目：

提取底部N項目：

例如，要過濾前3個小計，請使用此公式創(chuàng)建標準范圍：

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

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

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

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

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

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

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

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

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

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

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

根據(jù)列表中的匹配項過濾行

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

符合列表中項目的過濾行

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

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

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

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

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

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

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

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

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

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

例如，要過濾列表中確實出現(xiàn)的北區(qū)項目，請使用以下標準：

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

筆記：

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

周末和工作日的過濾器

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

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

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

如何在Excel中過濾周末

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

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

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

如何在Excel中過濾工作日

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

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

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

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

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

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

練習工作簿

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

以上是如何在Excel中使用高級過濾器 - 公式的標準范圍示例的詳細內容。更多信息請關注PHP中文網(wǎng)其他相關文章！