跳到主要內容

在 Excel 中篩選資料 – 簡單且全面

Excel篩選器命令可以幫助篩選範圍或表中的數據,以僅顯示所需的數據並隱藏其餘數據。 您可以使用其內置的運算符輕鬆過濾數字,文本或日期,例如過濾所有大於或等於某個數字的數字,過濾文本的開頭,結尾或包含特定字符或單詞,或僅顯示以下行:截止日期在某個日期之前或之後,依此類推。 篩選範圍或表中的數據後,如果數據發生更改,則可以重新應用篩選器以獲取新數據,也可以清除篩選器以顯示所有數據。

在本教程中,我們將演示如何在Excel中添加,使用或刪除過濾器。 除此之外,我們還將指導您如何增強過濾器功能以處理更複雜的Excel問題。

目錄: [ 隱藏 ]

(單擊下面目錄中的任何標題或在右側導航到相應的章節。)

1.如何在Excel中添加過濾器

要過濾範圍或表中的數據,首先需要在數據中添加過濾器。 本節提供3種在Excel中添加過濾器的方法。

1.1數據選項卡上的過濾器命令

選擇要添加過濾器的區域或表中的任何單元格,單擊 數據 > 過濾。

1.2“主頁”選項卡上的“過濾器”命令

選擇要添加過濾器的區域或表中的任何單元格,單擊 首頁 > 排序和過濾 > 過濾。

1.3使用快捷方式添加過濾器

選擇要添加篩選器的區域或表中的任何單元格,然後按 按Ctrl + 轉移 + L 鍵。

應用上述操作之一後,您可以看到在所選單元格的列標題中添加了下拉箭頭。


2.如何在Excel中應用過濾器(一個或多個條件)

添加過濾器後,您需要手動應用它。 本節將向您展示如何在Excel的一個或多個列中應用過濾器。

2.1將過濾器應用於一列(一項條件)

如果您只想將過濾器應用於一列,例如C列中的過濾器數據,則如下所示。 請轉到該列,然後執行以下操作。

  1. 1)單擊列標題中的下拉箭頭。
  2. 2) 根據需要指定過濾條件。
  3. 3)點擊 OK 按鈕開始過濾。 看截圖:

現在過濾器應用於 C 列。所有滿足過濾條件的資料將被顯示,其餘資料將被隱藏。

應用過濾器後,您可以看到下拉箭頭變為過濾器圖標 .

當您將光標懸停在過濾器圖標上方時,非常體貼,您指定的過濾條件將顯示為屏幕提示,如下面的屏幕截圖所示。 因此,如果您忘記了為過濾器指定的條件,只需將光標懸停在過濾器圖標上即可。

2.2將具有多個條件的過濾器應用於多個列(多個條件)

2.2.1將具有多個條件的過濾器一一應用於多列

如果要對具有多個條件的多列應用過濾器,只需將上述方法一個接一個地重複到多列。

將過濾器應用於多列後,您可以看到已過濾列中的下拉箭頭變為過濾器圖標。

2.2.2同時將具有多個條件的過濾器應用於多個列

使用上述方法,您需要將過濾器逐一應用於列,最重要的一點是該方法僅支持 標準。 在此介紹的方法不僅可以將過濾器同時應用於多個列,還可以同時應用於 OR 標準。

假設您有一個數據表,如下面的屏幕快照所示,並且想要基於多個條件從多個列中過濾數據: 產品= AAA-1,訂單> 80, or 總價> 10000。 請嘗試以下方法之一來完成它。

2.2.2.1使用高級過濾器功能將過濾器應用於多列

高級過濾器功能可以幫助您解決此問題,請按以下步驟進行操作。

1.首先,在工作表中創建條件,如下圖所示。

注意: 對於AND條件,請將條件值放在同一行的不同單元格中。 並將OR條件值放在另一行。

2。 點擊 數據 > 高級 打開 進階篩選 的功能。

3。 在裡面 進階篩選 對話框,請進行以下配置。

3.1)在 行動 部分,選擇 就地過濾列表 選項;
3.2)在 清單範圍 框,選擇要過濾的原始數據范圍或表(此處選擇A1:D9);
3.3)在 條件範圍 框,選擇包含您在步驟1中創建的標準值的範圍;
3.4)點擊 OK 按鈕。

現在,將根據給定的標準同時過濾列,如下面的屏幕截圖所示。

2.2.2.2使用出色的工具輕鬆將過濾器應用於多列

作為 OR 過濾條件在上述方法中不易管理,因此強烈建議您使用 超級濾鏡 的特點 Excel的Kutools。 使用此功能,您可以在Excel中輕鬆將過濾器應用於具有AND和OR條件的多個列。

1.安裝後 Excel的Kutools點擊此處成為Trail Hunter Kutools 加 > 超級濾鏡.

然後, 超級濾鏡 窗格顯示在工作表的右側。

默認情況下,會添加兩個空白條件組,並且在它們之間存在OR關係 超級濾鏡 窗格。 並且同一組中的條件之間的關係是AND。 您可以根據需要更改不同組之間的關係。

2。 在裡面 超級濾鏡 窗格中,請按以下方式配置過濾條件。

2.1)檢查 規定 框,單擊 按鈕以選擇要過濾的原始範圍或表格;
2.2)在 關係 下拉列表中選擇 或者;
3.3)單擊第一組中的第一個空白行,然後根據需要指定條件;

提示: 第一個下拉列表用於列標題,第二個下拉列表用於過濾器類型(您可以選擇 文字,數字,日期,年份,文字格式 等等),第三個是用於條件類型,最後一個文本框是用於條件值。

作為我們上面提到的示例,在這裡我選擇 產品 > 文本 > 等於 與三個下拉列表分開,然後鍵入 AAA級1 進入文本框。 看截圖:

2.4)繼續創建其餘標準,並且 Or 標準需要在新組中創建。 如下面的屏幕截圖所示,所有條件都已創建。 您可以從組中刪除空白條件。
2.5)點擊 篩選 按鈕開始過濾。

現在,只有匹配的數據才會顯示在原始數據范圍內,其餘的數據將被隱藏。 看截圖:

提示: 使用此方便的功能,您可以在組中添加更多條件,添加更多組,將當前過濾器設置保存為方案以備將來使用等。 它是必不可少的工具,可以節省大量工作時間並提高工作效率。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

單擊以了解有關此功能的更多信息。


3.如何在Excel中使用過濾器

在本節中,您將學習如何使用filter命令來過濾不同類型的數據類型,例如文本,數字,日期和格式。

3.1過濾文本值

3.1.1過濾具有特定條件的文本單元格(開頭,結尾,包含等)

實際上,內置的過濾器運算符- 文字過濾器 提供了許多有用的條件供您輕鬆過濾文本。 假設您要過濾以特定字符(例如J)開頭的單元格,請執行以下操作以完成此操作。

1.將過濾器添加到原始數據范圍的列標題。 點擊了解如何.

2.單擊標題單元格中的下拉箭頭以展開過濾器菜單。

3。 點擊 文字過濾器 > 開始於。

4。 在裡面 自定義自動篩選 對話框中,在文本框中輸入特定字符(在此我輸入一個J),然後單擊 確定。

提示: 你可以添加另一個 or Or 您需要的關係標準。

現在,所有以字符J開頭的單元格都顯示在D列中,如下圖所示。

3.1.2區分大小寫的過濾器

似乎很容易使用內置的篩選器運算符根據特定條件來篩選文本單元格。 但是,由於過濾器功能不支持使用區分大小寫的文本進行過濾,我們如何在Excel中進行區分大小寫的過濾器? 本節將向您展示實現它的方法。

3.1.2.1通過公式和“過濾器”命令過濾區分大小寫的某些文本

假設您要過濾某個文本的所有大寫字母,例如B列中的“ TEXT TOOLS”,請執行以下操作。

1.在原始數據范圍之外創建一個幫助器列(在這裡,我將列D選擇為幫助器列)。 在第二個單元格中輸入以下公式,然後按Enter鍵。 選擇結果單元格,拖動其 自動填充句柄 下來以獲得其他結果。

= EXACT(B2,UPPER(B2))

注意: 此公式有助於識別大寫和小寫的單元格。 如果一個單元格包含所有大寫字符,則結果將是 真正, 否則,您將得到如下結果 假。

2.選擇B和D列(將選擇C列,沒關係),單擊Data> 篩選 向他們添加過濾器。

3.轉到B列(該列包含要過濾的文本),然後進行如下配置。

3.1)單擊B列中的下拉箭頭;
3.2)取消選中 選擇全部 取消選擇所有項目的框;
3.3)選中旁邊的複選框 文字工具;
3.4)點擊 OK 按鈕。 看截圖:

現在,B欄中僅顯示大寫和小寫的“文本工具”。

4.單擊D列中的下拉箭頭,取消選中 選擇全部 複選框,選中旁邊的複選框 TRUE 項,然後單擊 OK 按鈕。

然後,將B列中所有大寫的文本“文本工具”過濾掉,如下圖所示。

3.1.2.2使用出色的工具輕鬆區分大小寫的單元格

如果您只想過濾一列中的所有大寫或小寫文本,請在此處推薦 特殊過濾器 的特點 Excel的Kutools。 使用此功能,只需單擊幾下即可輕鬆過濾所有大寫或小寫文本。

1.選擇要在其中過濾文本的列範圍,然後單擊 Kutools 加 > 特殊過濾器 > 特殊過濾器.

2。 在裡面 特殊過濾器 對話框,請進行以下設置。

2.1)在 範圍 框,您可以看到所選範圍已列出。 您可以根據需要更改範圍。
2.2)在 過濾規則 部分中,選擇“文本”選項,然後選擇 大寫文字 or 小寫文字 從下面的下拉列表中;
2.3)點擊 確定。

3.然後 Excel的Kutools 彈出對話框,告訴您找到了多少個並將被過濾的單元格,請單擊 OK 按鈕。

現在,所有大寫或小寫單元格都將立即被過濾,如下面的屏幕截圖所示。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

單擊以了解有關此功能的更多信息。

3.1.3按文本長度過濾

如果要按文本長度過濾單元格,例如,要過濾文本長度等於10個字符的單元格,該怎麼辦? 在這裡,以下三種方法可以幫您一個忙。

3.1.3.1使用“過濾器”命令按文本長度過濾單元格

實際上,Filter命令具有內置的運算符來解決此問題,請執行以下操作。

1.選擇要過濾的單元格範圍(在這裡我選擇B1:B27),然後通過單擊將過濾器添加到此列範圍 數據 > 過濾。

2.單擊列標題中的下拉箭頭,然後單擊 文字過濾器 > 自定義過濾器。 看截圖:

3。 在裡面 自定義自動篩選 對話框中,選擇相等的條件,在文本框中鍵入10個問號(?)作為模式模式,然後單擊 OK 按鈕。

保養竅門:這10個問號表示它將與長度為10的文本字符串匹配。

現在,將立即過濾文本字符串長度為10(包括空格)的所有單元格。

3.1.3.2使用公式和Filter命令按文本長度過濾單元格

此外,您可以使用LEN函數來計算每個單元格的文本字符串長度,然後應用Filter命令根據計算結果來過濾所需的文本長度單元格。

1.在原始數據范圍附近創建一個幫助列。 在其中輸入以下公式,然後按Enter鍵。 選擇結果單元格,然後拖動其 自動填充句柄 下來以獲得其他結果。

= LEN(B2)

現在,您將獲得指定列中每個單元格的文本長度。

2.選擇幫助器列(包括標題),單擊 數據 > 篩選 給它添加一個過濾器。

3.單擊下拉箭頭,取消選中 選擇全部 框以取消選擇所有項目,然後僅選中數字10旁邊的框,最後單擊 OK 按鈕。

現在,將立即過濾文本字符串長度為10(包括空格)的所有單元格。

3.1.3.3使用出色的工具輕鬆按文本長度過濾單元格

在這裡推薦 特殊過濾器 的效用 Excel的Kutools 幫助您輕鬆地在Excel中按文本長度過濾單元格。

1.選擇您要根據特定文本長度過濾單元格的列範圍,單擊 Kutools 加 > 特殊過濾器 > 特殊過濾器。 看截圖:

2。 在裡面 特殊過濾器 對話框,請進行以下配置。

2.1)所選範圍顯示在 範圍 框,您可以根據需要進行更改;
2.2)在 過濾規則 部分,選擇 文本 選項;
2.3)選擇 文字長度 等於下拉列表中的選項,然後在文本框中輸入10。
2.4)點擊 確定。

3。 “ Excel的Kutools 彈出對話框,告訴您找到了多少個並將被過濾的單元格,單擊 OK 繼續前進。

然後,將過濾所有文本字符串長度等於10的單元格,如下面的屏幕截圖所示。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.2過濾器編號

在Excel中,使用“數字過濾器”命令過濾數字也非常容易。

假設您要過濾一列中數字在15000到20000之間的單元格(例如C,如下面的屏幕截圖所示),則可以執行以下操作來實現。

1.選擇包含要過濾的數字的列範圍,然後單擊 數據 > 篩選 添加過濾器。

2.添加過濾器後,請進行以下配置。

2.1)點擊下拉箭頭展開過濾器;
2.2)點擊 號碼過濾器 > 之間;

2.3)在 自定義自動篩選 對話框,輸入條件,然後單擊 確定。

提示: 因為我要過濾數字介於15000和20000之間的單元格,所以在這裡我分別在文本框中輸入15000和20000。

現在,將過濾數字介於15000和20000之間的單元格,如下面的屏幕截圖所示。

3.3過濾日期

默認情況下,內置功能“日期過濾器”提供了許多常見的日期過濾條件。 如您所見,沒有內置選項可以按星期,週末或工作日過濾日期。 本節將教您如何實現這些操作。

3.3.1按星期幾或週末過濾日期

假設您有一個數據表,如下面的屏幕快照所示,如果要按星期或週末過濾日期,請採用以下方法之一。

3.3.1.1使用公式和“過濾器”命令按星期幾或週末過濾日期

在本節中,您將應用WEEKDAY函數來計算每個日期的星期幾,然後根據需要應用過濾器來過濾星期幾或週末的某天。

1.在空白單元格(在這種情況下為D2)中,輸入以下公式,然後按 Enter 鍵。 選擇結果單元格,然後拖動 自動填充句柄 在以下單元格上應用此公式。

=星期(A2)

提示:

1)由於我們需要根據助手列值過濾日期單元格,因此結果值和原始日期單元格應位於同一行。
2)在公式中,A2是包含要過濾日期的第一個單元格。

注意: 如您所見,該公式從 17,表示從星期幾開始 星期日星期六 (1代表星期日,7代表星期六)。

2.選擇整個公式結果(包括標題單元格),單擊 數據 > 過濾。

3.單擊下拉箭頭,然後取消選中 選擇全部 複選框。

1)如果要過濾所有周末,請選中數字1和7旁邊的複選框;
2)如果要過濾除週末之外的一周中的某一天,請選中數字1和7以外的複選框。例如,要過濾所有星期五,只需選中編號6旁邊的複選框即可。

然後,將過濾所有周末或一周中的某天。 看截圖:

3.3.1.2使用出色的工具輕鬆按星期或週末過濾日期

如果以上方法對您來說不方便,請在此處推薦 特殊過濾器 的效用 Excel的Kutools。 使用此功能,只需單擊幾下,您就可以輕鬆過濾包含星期幾的單元格。

1.選擇包含要基於星期幾過濾的日期的單元格。

2。 點擊 Kutools 加 > 特殊過濾器 > 特殊過濾器.

3。 在裡面 特殊過濾器 對話框,請進行以下配置。

3.1)所選範圍顯示在 範圍 框。 您可以根據需要更改範圍。
3.2)在 過濾規則 部分,選擇 日期 選項,然後從下拉列表中選擇一個選項。
       要過濾所有周末牢房,請選擇 週末 從下拉列表中
       要過濾除週末以外的一周中的任何一天,請選擇 週一,週二,週三,週四 or 星期五 從下拉菜單中按需選擇。
3.3)點擊 確定。 見截圖:

4.然後 Excel的Kutools 彈出對話框,其中找到了多少個並將被過濾的單元格,單擊 OK 繼續前進。

現在,所有周末或一周中的任何一天都將對單元進行過濾。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.3.2使用出色的工具按工作日過濾單元

除了按星期或週末過濾單元格外, 特殊過濾器 的效用 Excel的Kutools 還可以幫助您按工作日過濾單元格。

1.應用 與上述相同的步驟 啟用特殊過濾器實用程序。

2。 在裡面 特殊過濾器 對話框中,進行以下設置。

2.1)所選範圍顯示在 範圍 盒子。 您可以根據需要進行更改;
2.2)在 過濾規則 部分,選擇 日期 選項,然後選擇 工作日 從下拉列表中;
2.3)點擊 確定。 見截圖:

3.然後 Excel的Kutools 對話框彈出。 點擊 OK 繼續前進。

現在,所有工作日單元格都將被過濾。

3.4過濾格式

通常,Excel支持根據視覺標準(如字體顏色,單元格顏色或圖標集)過濾數據,如下面的屏幕截圖所示。

但是,如果要基於其他視覺條件(例如,字體樣式(粗體,斜體),字體效果(刪除線)或特殊單元格(包含公式))過濾數據,則Excel無法幫助您實現。 本節提供了可幫助您解決這些問題的方法。

3.4.1按粗體/斜體格式的文本過濾

假設您想通過粗體或斜體格式的文本過濾數據,如下面的屏幕截圖所示,以下方法可以幫您一個忙。 請按照以下步驟進行。

3.4.1.1使用公式和“過濾器”命令過濾粗體/斜體格式的文本

Get.Cell公式和“過濾器”命令的組合可以幫助過濾列範圍內的粗體格式文本。

1。 點擊 公式 > 定義名稱。

2。 在裡面 新名字 對話框,您需要:

2.1)在 姓名 框;
2.2)選擇 工作簿 來自 範圍 下拉列表;
2.3)將以下公式輸入到 框;
要過濾粗體文本單元格,請應用以下公式:
= GET.CELL(20,$ B2)
要過濾斜體文本單元格,請應用以下單元格:
= GET.CELL(21,$ B2)
2.4)點擊 確定。 見截圖:

公式語法:

=GET.CELL(type_num, reference)

公式參數

類型編號:是一個數字,用於指定所需的單元格信息類型;
在這裡,我們輸入數字20,如果該單元格具有粗體字體格式,則返回 真正, 否則返回 FLASE。
或者你可以 轉到此頁面 了解有關Type_num及其相應結果的更多信息。
參數支持:是您要分析的單元格引用。

3.在B2的同一行中選擇一個空白單元格,在其中鍵入以下公式,然後按 Enter 鍵。 選擇結果單元格,拖動其 自動填充句柄 在以下單元格上應用此公式。

= Filter_Bold_Cells

4.選擇整個結果單元格(包括標題),單擊 數據 > 過濾。

5.單擊下拉箭頭,僅選中旁邊的框。 TRUE 選項,然後單擊 確定。

然後,將過濾所有粗體或斜體文本單元格。 看截圖:

3.4.1.2使用“查找,替換和過濾”命令過濾粗體或斜體格式的文本

您也可以結合使用“查找,替換和過濾”命令來實現。

1.選擇包含要過濾的粗體或斜體文本單元格的列範圍,然後按 按Ctrl + F 鍵。

2。 在裡面 查找和替換 對話框,您需要配置如下。

2.1)點擊 選項 按鈕以展開對話框;
2.2)點擊 格式 按鈕;

2.3)在開頭 查找格式 對話框中,單擊 字體 標籤,選擇 斜體 or 膽大 ,在 字體樣式 框,然後單擊 好;

2.4)返回到 發現 並更換 對話框,單擊 找到所有;
2.5)然後,所有結果都列在對話框中,選擇其中之一,然後按 按Ctrl + A 選擇所有的鍵;
2.6)關閉 查找和替換 對話框。 看截圖:

3.現在,在原始範圍內選擇了所有粗體或斜體文本單元,單擊 首頁 > 填色, 然後為所選單元格選擇填充顏色。

4.再次選擇整個列範圍,單擊 數據 > 篩選 給它添加一個過濾器。

5.單擊下拉箭頭,選擇 通過彩色濾光片,然後單擊您剛才在下面指定的填充顏色 按單元格顏色過濾。 看截圖:

然後,將過濾所有粗體或斜體文本單元格。

3.4.1.3使用出色的工具輕鬆過濾粗體或斜體格式的文本

如上所述, 特殊過濾器 的效用 Excel的Kutools 有助於區分大小寫,按文本長度和日期過濾。 在這裡,我們將教您如何應用此功能在Excel中過濾粗體或斜體格式的文本單元格。

1.選擇包含要過濾的粗體或斜體格式的單元格的列範圍(包括標題)。

2。 點擊 Kutools 加 > 特殊過濾器 > 過濾粗體 / 過濾斜體。 看截圖:

3。 點擊 OK 在彈出 Excel的Kutools 對話框以繼續操作(此對話框彈出,告訴您有多少個單元格符合條件)。

現在,所有粗體或斜體格式的文本單元都將被過濾。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.4.2按刪除線格式的文本進行過濾

假設您收到了一個添加刪除線的人員名單,並且需要通過過濾來查找所有刪除線單元,則以下方法可以幫您一個忙。

3.4.2.1使用用戶定義的功能和Filter命令來過濾刪除線格式的文本

您可以應用用戶定義的函數來標識刪除線格式的文本單元格,然後使用“過濾器”命令根據結果過濾所有刪除線單元格。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊。 然後將下面的VBA代碼複製到 推薦碼 窗口。

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4.選擇一個空白單元格(此單元格應在要計算的單元格的同一行中),輸入以下公式,然後按 Enter 鍵。 選擇結果單元格,拖動其 自動填充句柄 在下面的單元格上應用此公式。

= HasStrike(B2)

注意: 如果相應的單元格具有刪除線字體效果,則返回 真正, 否則返回 假。

5.選擇整個結果單元格(包括標題單元格),單擊 數據 > 過濾。

6.然後單擊下拉箭頭>僅選中TRUE選項旁邊的框>單擊 確定。 請參閱顯示的屏幕截圖。

現在,您可以看到所有刪除線格式化的單元格都已被過濾。

3.4.2.2使用出色的工具輕鬆過濾刪除線格式化的文本

隨著 特殊過濾器 的效用 Excel的Kutools,所有刪除線格式的單元格都只能通過單擊幾下直接過濾。

1.選擇要過濾所有刪除線格式的單元格的列範圍,單擊 Kutools 加 > 特殊過濾器 > 過濾刪除線。

2.然後 Excel的Kutools 彈出對話框,告訴您有多少個單元格合格,單擊 OK 繼續前進。

然後,所有刪除線格式的單元格都會被過濾,如下圖所示。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.4.3按字體或背景顏色過濾

正如我們在此過濾器格式部分的開頭提到的那樣,Excel支持使用其內置功能根據視覺標準(例如字體顏色,單元格顏色或圖標集)過濾數據。 本節說明如何應用 通過彩色濾光片 此功能可按字體或背景顏色詳細過濾單元格。 同時,我們建議使用方便的第三方功能來幫助解決此問題。

3.4.3.1使用“過濾器”命令按一種字體或背景色進行過濾

您可以直接應用“過濾器”命令的“按顏色過濾”功能,以按Excel中的特定字體或背景色過濾單元格。

1.選擇您要按字體或背景顏色過濾單元格的列範圍,然後單擊 數據 > 過濾。

2.單擊下拉箭頭> 通過彩色濾光片。 然後,您可以看到列出了當前列範圍的所有單元格顏色和字體顏色。 單擊任何單元格顏色或字體顏色將基於其過濾所有單元格。

3.4.3.2使用用戶定義的功能和“過濾器”命令按多種背景色進行過濾

如果要按多種背景顏色過濾單元格,請應用以下方法。

假設您要過濾B列中具有橙色和藍色背景色的所有單元格,如下面的屏幕截圖所示。 首先,您需要計算這些單元的顏色指數。

1。 按 其他 + F11 鍵同時。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊。 然後將下面的VBA代碼複製到代碼窗口中。

VBA代碼:獲取單元格背景色索引

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4.在新列中,在第一個單元格中鍵入標題(此單元格應在原始範圍的標題的同一行上)。

5.選擇標題單元格旁邊的空白單元格(在這裡我選擇E2),在其中輸入以下公式,然後按 Enter 鑰匙。 選擇結果單元格,然後拖動其 自動填充句柄 在以下單元格上應用此公式。

= GetColor(B2)

注意: 如果單元格沒有填充顏色,則返回-4142。

6.選擇幫助程序列單元格(包括標題),單擊 數據 > 篩選 為該列添加過濾器。

7.單擊下拉箭頭以打開下拉列表,然後進行如下配置。

7.1)取消選中 選擇全部 取消選擇所有項目的項目;
7.2)僅選中您需要顯示的數字旁邊的框。 在這種情況下,我選中數字19和20旁邊的框,因為19是背景“橙色”的顏色索引,而20是背景“藍色”的顏色索引;
7.3)點擊 確定。 見截圖:

現在,單元格將通過指定的背景顏色進行過濾,如下面的屏幕截圖所示。

3.4.3.3使用出色的工具輕鬆按字體或背景色過濾

毫無疑問,很容易應用“按顏色過濾”內置功能來按字體或背景顏色過濾單元格。 但是,缺點是下拉列錶框覆蓋了原始數據的內容,因此我們無法隨時查看數據以正​​確選擇字體或背景色。 為避免此問題,在這裡推薦 特殊過濾器 的效用 Excel的Kutools.

1.選擇您要按字體或背景顏色過濾單元格的列範圍,然後單擊 Kutools 加 > 特殊過濾器 > 特殊過濾器.

2。 在裡面 特殊過濾器 對話框,請進行以下配置。

2.1)所選列範圍的單元格地址顯示在 範圍 框;
2.2)在 篩選規則 部分,選擇 格式 選項;
2.3)選擇 背景顏色 or 字體顏色 從下拉列表中;
2.4)單擊稻草圖標 ;

2.5)在開頭 篩選特殊單元格 對話框中,選擇一個包含要作為篩選依據的字體顏色或背景色的單元格,然後單擊“確定”。 確定。

2.6)返回到 特殊過濾器 對話框中,所選單元格的字體顏色或背景色顯示在文本框中(您可以根據需要修改顏色),單擊 OK 按鈕開始過濾單元格。

然後,將過濾具有選定字體顏色或背景顏色的選定範圍內的所有單元格。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.4.4包含公式的過濾器單元

如果您有一長串包含實際值和公式的數據,並且只需要過濾公式單元格,該怎麼辦? 本節提供了兩種方法來實現它。

3.4.4.1使用用戶定義的函數和Filter命令過濾公式單元格

首先,您需要使用用戶定義的函數查找列表中的所有公式單元格,然後應用“過濾器”命令根據結果對公式單元格進行過濾。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的VBA代碼複製到“代碼”窗口中。

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

4.選擇一個空白單元格,此單元格應在要檢查它是否為公式單元格的單元格的同一行中,在其中輸入以下公式,然後按 Enter 鍵。 選擇結果單元格,拖動其 自動填充句柄 在以下單元格上應用此公式。

= HasFormula(C2)

如上面的屏幕截圖所示,結果是 真正, 表示如果相應的單元格是公式單元格,則返回TRUE,否則返回FALSE。

5.選擇結果單元格(包括標題單元格),單擊 數據 > 篩選 給它添加一個過濾器。

6.單擊下拉箭頭,僅選中旁邊的框。 TRUE 框,然後單擊 確定。

然後,您可以看到所有公式單元格都已過濾。

3.4.4.2使用出色的工具輕鬆過濾公式單元格

在這裡演示Kutools for Excel的特殊過濾器實用程序,以幫助您僅單擊幾次即可輕鬆過濾列表中的公式單元格。

1.選擇要過濾所有公式單元格的列表,然後單擊 Kutools 加 > 特殊過濾器 > 篩選公式.

2。 “ Excel的Kutools 彈出對話框,告訴您有多少個單元格合格,單擊 OK 繼續前進。

然後將所有公式單元格過濾掉,如下圖所示。

此外,您可以使用 特殊過濾器 的特點 Excel的Kutools 以其他格式輕鬆過濾單元格,例如:

用評論過濾所有單元格, 點擊了解更多...

根據特定值過濾所有合併的單元格, 點擊了解更多...

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。

3.5使用通配符過濾

有時,您在過濾時可能會忘記確切的搜索條件。 在這種情況下,建議您使用通配符。

Excel中只有3個通配符:

通配符 產品描述
*(星號) 代表任意數量的字符 例如, *漿果 發現“黑莓“”草莓“”藍莓“ 等等
? (問號) 代表任何單個字符 例如, l?ck 發現““”“”缺乏“ 等等
〜(潮) 其次是 *, ?, 或者 ~ 代表真實 * 、? or ~ 字符 例如, 臉〜*店 出土文物 “臉*店”

讓我們看看如何在過濾中使用通配符。

假設您需要過濾B列中以Market結尾的所有單元格,如下面的屏幕快照所示,請執行以下操作。

1.首先,創建一個標準範圍。 輸入與原始列標題相同的標題,然後在下面的單元格中鍵入過濾條件。 看截圖:

2。 點擊 數據 > 高級。

3.在開幕 進階篩選 對話框,配置如下。

3.1)在 行動 部分,選擇 就地過濾列表 選項;
3.2)在 清單範圍 框,選擇要過濾的原始數據范圍;
3.3)在 條件範圍 框中,選擇包含您在步驟1中創建的標題和過濾條件的單元格;
3.4)點擊 確定。 見截圖:

然後,所有以Market結尾的單元格都會被過濾。 看截圖:

*和〜通配符在過濾中的用法與上述操作相同。

3.6使用內置搜索框過濾

如果您使用的是Excel 2010或更高版本,則可能會注意到Excel篩選器中有一個內置搜索框。 本節將演示如何使用此搜索框過濾Excel中的數據。

如下面的屏幕截圖所示,您要過濾包含“市場”的所有單元格,搜索框可以幫助您輕鬆完成此操作。

1.選擇要過濾數據的列範圍,然後單擊 數據 > 篩選 給它添加一個過濾器。

2.單擊下拉箭頭,輸入 “市場” 進入搜索框,然後單擊 確定。

您可以在搜索框中輸入數據的同時,實時列出所有符合條件的文本。

然後,將所有包含“市場”的單元格過濾掉,如下圖所示。


4.僅複製可見數據(忽略隱藏或過濾的數據)

默認情況下,Excel複製可見單元格和隱藏單元格。 如果只想在過濾後復制可見的單元格,則可以嘗試以下方法之一。
僅使用快捷鍵複製可見數據

您可以使用快捷鍵僅選擇可見的單元格,然後手動將其複制並粘貼到所需的位置。

1.選擇您要僅複製可見單元格的範圍。 在此步驟中,可見單元格和隱藏單元格均被選中。

2。 按 其他; 同時按鍵。 現在僅選擇可見的單元格。

3。 按 按Ctrl + C 鍵複製選定的單元格,然後按 按Ctrl + V 粘貼它們的鍵。

僅使用出色的工具即可輕鬆複製可見數據

這裡介紹 粘貼到可見 的特點 Excel的Kutools 讓您僅在Excel中輕鬆複製可見數據。 此外,使用此功能,您可以將值僅複製和粘貼到已過濾範圍內的可見單元格中。

1.選擇僅要復制可見單元格的過濾範圍,然後單擊 庫工具 > 範圍 > to可見 > 全部 / 僅粘貼值.

對於公式單元格,選擇 全部 複製結果和公式,選擇 僅粘貼值 僅複製實際值。

2.在彈出 粘貼到可見範圍 對話框中,選擇一個空白單元格以輸出複制的單元格,然後單擊“確定”。 OK.

然後,僅將選定過濾範圍內的可見單元格複製並粘貼到新位置。

注意: 如果已過濾目標範圍,則所選值將僅粘貼到可見單元格。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


5.過濾後刪除隱藏或可見的行

對於過濾列表,您可能需要刪除隱藏的行,以便僅保留可見數據。 在本節中,您將學習三種刪除Excel中篩選列表中隱藏或可見行的方法。

使用VBA代碼從當前工作表中刪除所有隱藏的行

下面的VBA代碼可以幫助刪除Excel當前工作表中的所有隱藏行。

注意: 此VBA不僅刪除篩選列表中的隱藏行,還刪除您手動隱藏的行。

1.在工作表中包含要刪除的隱藏行,請按 其他 + F11 同時打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的VBA代碼複製到“模塊”窗口中。

VBA代碼:從當前工作表中刪除所有隱藏的行

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3。 按 F5 鍵來運行代碼。

4.然後 Excel的Kutools 彈出對話框,告訴您已刪除了多少隱藏行,請單擊 OK 關閉它。

現在,所有隱藏行(包括自動隱藏行和手動隱藏行)都將被刪除。

使用“轉到”功能過濾後刪除可見行

如果您只想刪除特定範圍內的可見行,則 轉到 功能可以為您提供幫助。

1.選擇要刪除可見行的過濾範圍,然後按 F5 鍵打開 轉到 對話框。

2。 在裡面 轉到 對話框中,單擊 特別 按鈕。

3。 在裡面 去特別 對話框中,選擇 僅可見細胞 選項,然後單擊 OK 按鈕。

4.現在,所有可見的單元格都被選中。 右鍵單擊所選範圍,然後單擊 刪除行 在上下文菜單中。

然後刪除所有可見的單元格。

使用出色的工具進行過濾後,輕鬆刪除隱藏或可見的行

上述方法是繁瑣且費時的。 在這裡推薦 刪除隱藏(可見)行和列 的特點 Excel的Kutools。 使用此功能,您不僅可以輕鬆刪除選定範圍內的隱藏行或可見行,還可以輕鬆刪除當前工作表,多個選定工作表或整個工作簿中的行。 請按照以下步驟進行。

1.選擇要從中刪除所有隱藏或可見行的範圍。

筆記:

1)要從當前工作表或整個工作簿中刪除隱藏或可見的行,請忽略此步驟;
2)要同時從多個工作表中刪除隱藏或可見的行,您需要通過按住 按Ctrl 鍵。

2。 點擊 庫工具 > 刪除 > 刪除隱藏(可見)行和列.

3。 在裡面 刪除隱藏(可見)行和列 對話框,您需要配置如下。

3.1)在 在看 下拉列表中,根據需要選擇一個選項;
有4選項: 在選定範圍內, 在活動表中, 在選定的工作表中, 在所有工作表中.
3.2)在 刪除類型 部分,選擇 選項;
3.3)在 詳細類型 部分中,選擇 可見行 or 隱藏的行 根據您的需求;
3.4)點擊 確定。 見截圖:

4.然後立即刪除所有可見或隱藏的行。 同時,會彈出一個對話框,告訴您已刪除的行數,單擊 OK 完成整個操作。

  如果您想免費試用(30天)此實用程序, 請點擊下載,然後按照上述步驟進行操作。


6.跨多張紙過濾

通常,很容易在工作表中過濾數據。 在本節中,您將學習如何在具有通用數據結構的多個工作表中使用相同條件過濾數據。

假設一個工作簿包含三個工作表,如下面的屏幕截圖所示,現在您要使用相同的條件同時過濾這三個工作表中的數據,產品= KTE”,下面的VBA代碼可以幫您一個忙。

1。 按 其他 + F11 鍵打開 Microsoft Visual Basic for Applications 窗口。

2。 在裡面 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的VBA代碼複製到模塊窗口中。

VBA代碼:一次跨多個工作表過濾數據

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

注意: 該行“Xws.Range(“ A1” .AutoFilter 1,“ = KTE”)代碼中的“”表示您將使用條件= KTE過濾A列中的數據,數字1是A列的列號。您可以根據需要進行更改。 例如,如果您要過濾B列中所有大於500的數字,則可以將此行更改為“Xws.Range(“ B1” .AutoFilter 2,“> 500”)“。

3。 按 F5 鍵來運行代碼。

然後,在當前工作簿的所有工作表中同時過濾指定的列。 請參閱以下結果。


7.更改數據後重新應用過濾器

有時您可能已對過濾範圍進行了更改。 但是,無論您對范圍進行什麼更改,過濾器結果均保持不變(請參見下面的屏幕截圖)。 在本節中,我們將向您展示兩種方法,以手動或自動在當前範圍上重新應用過濾器,以便包括您所做的更改。

使用“重新應用”命令手動重新應用過濾器

Excel具有內置 重新申請 功能可幫助您手動重新應用過濾器。 您可以按以下方式申請。

點擊 數據 > 重新申請 重新應用當前工作表中的過濾器。

然後,您將看到重新應用過濾後的範圍以包括所做的更改。

使用VBA代碼自動重新應用過濾器

如果需要頻繁更改過濾列表,則必須反复單擊以應用此重新應用功能。 在此提供VBA代碼,以幫助在數據更改時實時自動自動重新應用過濾器。

1.在工作表中包含您要自動重新應用的過濾器,右鍵單擊工作表選項卡,然後選擇 查看代碼。

2.在開幕 Microsoft Visual Basic for Applications 窗口,將下面的VBA代碼複製到“代碼”窗口中。

VBA代碼:更改數據時自動重新應用過濾器

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

注意: 在代碼中 “ Sheet2” 是當前工作表的名稱。 您可以根據需要進行更改。

3。 按 其他 + Q 關閉鍵 Microsoft Visual Basic for Applications 窗口。

從現在開始,當更改過濾列表中的數據時,將動態調整過濾結果。 請參見下面的gif圖片。


8.清除或移除過濾器

我們已經了解瞭如何在上述內容中添加,應用和使用過濾器。 在這裡,我們將學習如何在Excel中清除或刪除過濾器。

8.1清除列中的過濾器

在列中應用過濾器後,如果要清除它,請單擊過濾的圖標,然後單擊 清除“標題名稱”中的過濾器 從下拉菜單中選擇。

8.2清除工作表中的所有篩選器

如果您已將過濾器應用於多個列,並希望同時清除所有列,請單擊 數據 > 明確。

然後清除所有過濾器,如下圖所示。

8.3清除當前工作簿中所有工作表中的篩選器

假設您已在工作簿中的多個工作表上應用了篩選器,並希望立即清除這些篩選器。 下面的VBA代碼可以幫您一個忙。

1.打開工作簿,您將清除其中的所有篩選器,然後按 其他 + F11 鍵同時。

2.在開幕 Microsoft Visual Basic for Applications 窗口中,單擊 插入 > 模塊 然後將下面的VBA代碼複製到模塊窗口中。

VBA代碼:清除當前工作簿中所有工作表中的篩選器

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3。 按 F5 鍵來運行代碼。 然後,從當前工作簿中的所有工作表中清除所有篩選器。

8.4刪除工作表中的所有過濾器

以上方法僅有助於清除篩選狀態,並且篩選器仍保留在工作表中。 如果要從工作表中刪除所有篩選器,請嘗試以下方法。

通過關閉過濾器來刪除工作表中的所有過濾器

點擊 數據 > 篩選 關閉功能(“篩選器”按鈕未處於突出顯示狀態)。

通過快捷鍵刪除工作表中的所有篩選器

此外,您可以應用快捷鍵來刪除工作表中的所有過濾器。

在工作表中包含要刪除的過濾器,請按 按Ctrl + 轉移 + L 鍵同時。

然後,立即刪除當前工作表中的所有篩選器。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations