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

俞木發(fā)

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

對于數(shù)據(jù)類型很多的文檔，比如下表是某公司的產(chǎn)品型號庫存統(tǒng)計(jì)表，現(xiàn)在因?yàn)樯嫌尾牧先必?，其中fjgx—13GWG20、fjgx—16GWL08、fjgx—18GWL09三個型號停產(chǎn)，需要將這三個型號的數(shù)據(jù)剔除，即只選擇除這三個型號外的所有數(shù)據(jù)（圖1）。

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

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

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

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

可以看到，借助輔助列的方法，我們可以將原來很多型號的數(shù)據(jù)變?yōu)楦唵蔚姆诸?。使用同樣的方法，大家可以對類似的?shù)據(jù)進(jìn)行更多的處理。

用好函數(shù)反向篩選更簡單

上述方法雖然可以很方便地將原來很多型號的數(shù)據(jù)變?yōu)楦傩吞柕倪x擇，不過由于全部是手動操作，這樣在篩選要求發(fā)生變化，比如新增fjgx-14GWZ 25型號停產(chǎn)時，操作又需要重復(fù)一遍，工作效率并不高。對于篩選要求經(jīng)常變化的數(shù)據(jù)，我們還可以借助函數(shù)進(jìn)行更高效的反向篩選。

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

公式解釋：

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

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

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

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

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