我们，让Excel变简单

今天介绍一个关于数据筛选的技巧。这个技巧几乎是一个固定用法，在很多场景中都可以使用这个技巧来解决问题。

我们知道，Excel的数据筛选功能很强大，你可以很方便的进行各种筛选。但是这个筛选有个问题，那就是最多只能使用两个条件：

当然，如果使用高级筛选是可以筛选多个条件的记录的，但是高级筛选又比较复杂。

今天我们介绍的这个方法，使用了SUMPRODUCT函数和SEARCH函数添加了一个辅助列，使得我们可以筛选满足多个条件的记录。

1.使用公式添加辅助列

假设我们的数据和需要筛选的条件如下：

我们需要筛选所有至少满足右边多个条件之一的那些记录

我们可以添加一个辅助列：

其中，我们使用了公式：

=SUMPRODUCT(N(NOT(ISERROR(SEARCH($G$3:$G$6,C3)))))

将这个公式填充到整列：

可以看到，所有至少满足一个右边条件的记录，该辅助列的结果是1，因此，我们只要筛选所有辅助列大于0的记录就可以了。

这里我们要筛选大于0，而不是等于1，是因为这个公式实际计算的满足的条件个数，例如，如果右边的条件修改一下：

辅助列的结果有很多变成了2，因为他们满足两个条件。比如，第三行“健怡可乐CAN”，既包含了“CAN”，又包含了“可乐”。

这里要注意，如果你删了一个条件，会发现结果有问题了：

我们将右表的最后一个条件删掉，结果发现辅助列的结果都大于0了

这是因为我们的公式写的是固定的区域(G3:G6)，现在G6删掉了内容，该单元格变成了空，而所有的产品列都会包含“空”。

为了避免这种情况，最好的方式就是将右侧条件转换为超级表（Ctrl+T）：

在删除的时候用右键菜单删除表行。这样就变成了可以根据条件区域自行扩大的筛选了。

在条件区域还可以使用通配符：

2.公式解释

我们先来看这个嵌套的公式中最里层的部分：=SEARCH(表1[条件],C3)

这里的参数“表1[条件]是指右边的条件区域，C3是产品名称，

这个公式的结果是个错误值。在公式栏中点击鼠标进入编辑状态，按F9：

该公式的计算结果展现在公式栏中

可以看到，这个SEARCH公式的结果是一个数组。因为有5个条件，所以数组有5个元素，每个元素代表一个条件的结果，除了第三个是一个数值外，其他的结果都是错误值。

再来看外面一层：ISERROR(SEARCH(表1[条件],C3))，同样在公式栏中按F9看看展开的计算结果：

可以看到，原来错误值的结果对应变成TRUE，而原来数值的地方变成了FALSE。

现在我们给这个公式嵌套了一个NOT函数：NOT(ISERROR(SEARCH(表1[条件],C3)))，结果变成了：

NOT的作用就是将TRUE变成FALSE，FALSE变成TRUE。

倒数第二层是嵌套一个N函数，这个函数的作用是将一个内容变成数值，如果这个内容是TRUE，结果返回1，如果这个内容是FALSE，结果就返回0。于是，公式：N(NOT(ISERROR(SEARCH(表1[条件],C3))))的结果就是：

最后，SUMPRODUCT的作用就是将这个数组的所有结果加起来。

于是，这个公式就可以告诉我们究竟产品名称中包含了一个右表中的条件。

今天的分享就到这里了。相信你在工作中可以用到这个技巧。

END

关注ExcelEasy

关于Excel的一切问题，你都可以在这里找到答案