不走寻常路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”就可以完成反向筛选了。