不走尋常路Excel反向篩選更高效

俞木發

插入輔助列反向篩選更簡單

對于數據類型很多的文檔，比如下表是某公司的產品型號庫存統計表，現在因為上游材料缺貨，其中fjgx—13GWG20、fjgx—16GWL08、fjgx—18GWL09三個型號停產，需要將這三個型號的數據剔除，即只選擇除這三個型號外的所有數據（圖1）。

常規的方法是為A列添加篩選按鈕，然后取消需要剔除型號前的勾選。因為這里產品類型很多，而且很多型號的名稱非常相似，在手動選擇時容易出錯，造成錯選和漏選（圖2）。

此時就可以使用輔助列進行反向篩選。首先使用高級篩選將需要剔除的型號單獨篩選出來。新建工作表2，在D1單元格中輸入“型號”（內容要和A1的一致），在D2：D 4區域中輸入上述剔除型號的數據（注意輸入數據一定要和篩選要求的數據一致）。返回工作表1，依次點擊“數據→篩選→高級篩選”，列表區域選擇A列數據，條件區域選擇表2中的新建數據，即“Sheet2！$D$1：$D$4”，點擊“確定”（圖3）。

這樣在表1中就會將停產數據全部篩選出來，按提示在其數據后全部添加上“已停產”進行備注（圖4）。

完成上述操作后，取消A列的篩選，選中D列并再次添加篩選按鈕，這樣在篩選列表中就只有“已停產”和“空白”兩類數據，按提示取消“已停產”前的勾選（圖5），即可完成反向篩選。這樣篩選大量數據時就不容易出錯了。

可以看到，借助輔助列的方法，我們可以將原來很多型號的數據變為更簡單的分類。使用同樣的方法，大家可以對類似的數據進行更多的處理。

用好函數反向篩選更簡單

上述方法雖然可以很方便地將原來很多型號的數據變為更少型號的選擇，不過由于全部是手動操作，這樣在篩選要求發生變化，比如新增fjgx-14GWZ 25型號停產時，操作又需要重復一遍，工作效率并不高。對于篩選要求經常變化的數據，我們還可以借助函數進行更高效的反向篩選。

從篩選要求可以看到，這個篩選的目的實際上是要找出符合要求的停產型號，這類字符通過FIND（）函數就可以找到，然后再結合IF（）函數進行判斷即可。定位到E2單元格并輸入公式“=IF（OR（ISNUMBER（FIND（{"fjgx—13GWG20"，"fjgx—16GWL08"，"fjgx—18GWL09"}，A2））），"已停產"，""）”，下拉填充E列后，所有已停產的型號的數據會自動在該列添加“已停產”的標記（圖6）。

公式解釋：

這里先使用FIND（ ）函數在A 2單元格開始查找字符，篩選字符輸入在{}中，如果有多個字符，使用半角逗號進行分隔。然后將查找結果通過ISNUMBER（ ）函數轉換為數字，接著通過OR（）函數（表示只要包含{}中的字符即滿足條件）進行選擇，使用OR（）函數可以輕松地進行多個并列條件的判斷，如果設置的是多個包含條件字符，則使用AND（ ）函數進行選擇。最后通過I F（ ）函數進行判斷，如果找到符合篩選要求的字符，那么就顯示“已停產”，否則顯示為空。

為了更方便地對篩選的數據進行標識，還可以為其添加條件格式。選中A 2：E116 區域，依次點擊“開始→ 條件格式→文本包含”，然后將文本包含“已停產”的單元格設置為自動填充“黃填充色深黃色文本”，這樣指定篩選條件的數據會填充為黃色（圖7）。

為了使篩選操作更方便，我們還可以執行下面的操作，將其錄制為“宏1”，并為其分配一個快捷鍵“Ctrl +W”。

選中E列，依次點擊“數據→篩選→按顏色篩選→按單元格顏色篩選→無填充”，完成錄制（圖8）。

因為這里我們是使用函數的方法進行篩選，因此可以將上述文件保存為模板。以后如果需要更改篩選條件，那么只要更改FIND（）函數中的字符即可。之后按“Ctrl+W”就可以完成反向篩選了。