該教程顯示瞭如何在Excel中使用高級(jí)過濾器，並提供了許多非平凡的標(biāo)準(zhǔn)範(fàn)圍示例來創(chuàng)建一個(gè)案例敏感的過濾器，查找兩列之間的匹配和差異，匹配較小列表的提取記錄等等。

在上一篇文章中，我們討論了Excel Advanced Filter的不同方面，以及如何使用它和或邏輯過濾行。現(xiàn)在您知道了基礎(chǔ)知識(shí)，讓我們來看看更複雜的標(biāo)準(zhǔn)範(fàn)圍示例，可能對(duì)您的工作有幫助。

設(shè)置基於公式的標(biāo)準(zhǔn)範(fàn)圍

由於本教程中討論的大多數(shù)標(biāo)準(zhǔn)範(fàn)圍示例將包括各種公式，因此讓我們從定義正確設(shè)置它們的基本規(guī)則開始。相信我，這個(gè)小的理論將為您節(jié)省大量時(shí)間，並節(jié)省對(duì)您的複雜標(biāo)準(zhǔn)進(jìn)行故障排除的頭痛，其中包括基於公式的多個(gè)條件。

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

Excel高級(jí)過濾標(biāo)準(zhǔn)範(fàn)圍示例

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

案例敏感過濾器的文本值

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

例如，要過濾包含香蕉的行，忽略香蕉和香蕉，請(qǐng)?jiān)跇?biāo)準(zhǔn)範(fàn)圍內(nèi)輸入以下公式：

=EXACT(B5, "Banana")

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

然後，通過單擊“數(shù)據(jù)”選項(xiàng)卡上的“高級(jí)”按鈕應(yīng)用Excel高級(jí)過濾器，然後配置列表範(fàn)圍和標(biāo)準(zhǔn)範(fàn)圍，如下面的屏幕截圖所示。請(qǐng)注意，標(biāo)準(zhǔn)範(fàn)圍包括2個(gè)單元格 -媒體電池和配方奶單元。

筆記。上面的圖像以及本教程中的所有進(jìn)一步屏幕截圖僅出於清晰度而顯示標(biāo)準(zhǔn)範(fàn)圍單元中的公式。在您的真實(shí)工作表中，公式單元格應(yīng)返回對(duì)還是錯(cuò)，具體取決於數(shù)據(jù)的第一行是否匹配標(biāo)準(zhǔn)：

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

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

要濾波以高於平均水平的行濾波行，請(qǐng)?jiān)跇?biāo)準(zhǔn)範(fàn)圍內(nèi)使用以下公式：

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

要濾除以下平均水平的行，請(qǐng)使用以下公式：

=F5<average></average>

請(qǐng)注意，我們使用相對(duì)引用來指帶有數(shù)據(jù)（F5）的頂級(jí)電池，並且絕對(duì)引用以定義您要計(jì)算平均值的整個(gè)範(fàn)圍，不包括列標(biāo)題（$ f $ 5：$ f $ 50）。

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

那些熟悉Excel號(hào)碼過濾器的人可能會(huì)想知道，為什麼有人會(huì)不願(yuàn)意使用高級(jí)過濾器，而內(nèi)置號(hào)碼過濾器已經(jīng)具有高於平均水平和低於平均水平的選項(xiàng)？是的，但是內(nèi)置的Excel過濾器不能與邏輯一起使用！

因此，為了進(jìn)一步以此示例，讓我們過濾行，該行子（列F）或9月銷售（E列）高於平均水平。為此，通過在單獨(dú)的行上輸入每個(gè)條件來設(shè)置使用或邏輯的標(biāo)準(zhǔn)範(fàn)圍。結(jié)果，您將獲得E或F列中具有以上平均值的項(xiàng)目列表：

用空白或非空白的過濾行

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

如果您想用或邏輯過濾空白或非空白單元格，或?qū)⒖瞻?/非空白條件與其他一些標(biāo)準(zhǔn)一起使用，請(qǐng)使用以下公式之一設(shè)置高級(jí)濾波器標(biāo)準(zhǔn)範(fàn)圍：

過濾器空白：

濾波器非空白：

用或邏輯過濾空白單元格

要過濾A或B列或兩列中具有空白單元格的行，請(qǐng)以這種方式配置高級(jí)濾波器標(biāo)準(zhǔn)範(fàn)圍：

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

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

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

為了進(jìn)一步了解Excel的高級(jí)過濾器如何使用多個(gè)標(biāo)準(zhǔn)工作，讓我們?cè)跇颖颈碇惺褂靡韵聴l件過濾行：

• 區(qū)域（A列）或項(xiàng)目（B列）應(yīng)非空白，並且
• 亞類（C列）應(yīng)大於900。

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

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

如您所知，在Excel高級(jí)濾波器標(biāo)準(zhǔn)範(fàn)圍內(nèi)，與邏輯相連的條件應(yīng)在同一行中輸入，並且與OR邏輯相連的條件 - 在不同的行上：

因?yàn)榇耸纠械囊粋€(gè)標(biāo)準(zhǔn)用一個(gè)公式（非空白）表示，另一個(gè)標(biāo)準(zhǔn)包括一個(gè)比較操作員（亞電> 900），請(qǐng)讓我提醒您：

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

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

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

提取頂級(jí)n個(gè)項(xiàng)目：

提取底部N項(xiàng)目：

例如，要過濾前3個(gè)小計(jì)，請(qǐng)使用此公式創(chuàng)建標(biāo)準(zhǔn)範(fàn)圍：

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

要提取底部3個(gè)小計(jì)，請(qǐng)使用此公式：

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

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

以下屏幕截圖顯示了行動(dòng)中的前三名公式：

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

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

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

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

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

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

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

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

符合列表中項(xiàng)目的過濾行

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

假設(shè)較小的列表在d2：d7的範(fàn)圍內(nèi)，並且要將表的項(xiàng)目與該列表進(jìn)行比較，則在第10行開始中，該公式如下（請(qǐng)注意，絕對(duì)和相對(duì)引用的使用）：

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

當(dāng)然，您不僅只有一個(gè)標(biāo)準(zhǔn)過濾桌子。

例如，要過濾與列表匹配的行，但僅對(duì)於北部地區(qū)，請(qǐng)?jiān)谕恍兄休斎雰蓚€(gè)條件，以便它們可以使用邏輯：

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

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

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

不匹配列表中項(xiàng)目的過濾行

要查找表中不在較小列表中的所有項(xiàng)目，請(qǐng)檢查我們的Countif公式的結(jié)果是否等於零：

例如，要過濾列表中確實(shí)出現(xiàn)的北區(qū)項(xiàng)目，請(qǐng)使用以下標(biāo)準(zhǔn)：

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

筆記：

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

週末和工作日的過濾器

到目前為止，我們的高級(jí)過濾條件範(fàn)圍示例主要與數(shù)字和文本值有關(guān)?，F(xiàn)在，是時(shí)候給那些在約會(huì)的人那裡提供一些線索了。

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

如您所知，Microsoft Excel提供了一個(gè)特殊的工作日功能，該功能返回一周中與給定日期相對(duì)應(yīng)的日期。正是我們將在Excel高級(jí)濾波器標(biāo)準(zhǔn)範(fàn)圍內(nèi)使用的功能。

如何在Excel中過濾週末

請(qǐng)記住，在工作日的時(shí)間裡，有1個(gè)代表週日和6個(gè)代表星期六，週末過濾的公式如下：

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

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

如何在Excel中過濾工作日

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

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

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

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

要以其他方式過濾工作表中的日期，只需找到相關(guān)的日期函數(shù)，然後隨時(shí)在高級(jí)過濾條件範(fàn)圍內(nèi)使用它。

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

練習(xí)工作簿

Excel高級(jí)過濾器示例（.xlsx文件）

以上是如何在Excel中使用高級(jí)過濾器 - 公式的標(biāo)準(zhǔn)範(fàn)圍示例的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！