Excel函数应用篇：强大的函数FILTER

我们都清楚，Excel函数的功能特别强大，比如我们经常使用sum函数进行求和操作，使用count函数进行计数，使用if函数进行判断，这些都是比较基础的Excel函数，就是这些基础的Excel函数也给我们工作带来了很大的便利。

这次我们要分享一个精选的Excel函数，这个函数是filter函数，filter函数一般用于嵌套使用，我们可以利用filter函数的嵌套动态提取数据源。

函数参数：=FILTER(array,include,[if_empty])
参数详解：
Array：要筛选的数组或区域（必需）
Include：筛选的条件，必须是一组true或false的数组。用条件列 = 条件值，就行了。（必需）
if_empty：所含数组中的所有值都为空（筛选器不返回任何内容）时返回的值（可选）

说明：

array 可以一行值、一列值，也可以是几行值和几列值的组合。

FILTER 函数会返回一个数组，如果该数组是公式的最终结果，将会溢出。也就是说，按下回车键后，Excel 将动态创建相应大小的数组范围。如果支持数据位于 Excel 表格中，若使用结构化引用，则从数组范围中添加或删除数据时，数组将自动重设大小。

如果数据集可能返回空值，需要使用第三个参数 ([if_empty])。否则将导致 #CALC! 错误 ，因为 Excel 当前不支持空数组。

如果 include 参数的所有值都是个错误值（#N/A、#VALUE 等）或无法转换为布尔值，则 FILTER 函数将返回一个错误。

如果数据在不同的工作簿中，只有所有工作簿时都处于打开状态时才能返回正确的结果。否则将返回 #REF! 错误 。

1、单条件查找

查找“爱知趣”的数学成绩：

输入公式：

=FILTER(D2:D6,B2:B6=G2)

2、查找不到返回值

根据姓名查找其对应的数学成绩：

姓名中没有“土豆哥”，使用函数VLOOKUP查找不到时返回错误值#N/A。

如果不想显示错误值，要将错误值显示为空，需要借助函数IFERROR。

=IFERROR(VLOOKUP(G2,B:E,3,0),"")

而使用函数FILTER，它的第三参数可以直接设置：

=FILTER(D$2:D$6,B$2:B$6=G2,"")

3、逆向查找

查找“爱知趣”的学号：

=VLOOKUP(G2,IF(,B2:B6,A2:A6),2,0)

使用函数VLOOKUP不能直接进行逆向查找，需要借助IF或者CHOOSE等等结构将逆序转换为顺序，从而实现查找。

逆向查找，使用函数FILTER非常简单：

=FILTER(A2:A6,B2:B6=G2)

4、查找返回多列数据

使用函数VLOOKUP，需要借助函数COLUMN来实现返回多列。

=VLOOKUP($G2,$B2:$E6,COLUMN(B1),0)，向右填充。

使用函数FILTER：

在H2单元格输入公式：

=FILTER(C2:E6,B2:B6=G2)，直接返回多列数据。

5、筛选数据

根据品名筛选其对应的所有数据：

在E5单元格输入公式：

=FILTER(A2:C8,B2:B8=E2)

6、一对多查找

根据业务员查找对应销售的品名：

=FILTER(B2:B8,A2:A8=E2)

7、与或的条件筛选

根据E2、E3单元格中的条件，筛选出对应的数据：

在E7单元格输入公式：

=FILTER(A2:C8,(A2:A8=E2)+(A2:A8=E3))，不需要再向右向下填充公式。

使用高级筛选进行多条件筛选，这是一次性的，当条件变动时不能随之而变；使用函数FILTER进行筛选时，筛选条件变动，其结果区域会随之更新。

8、与且的条件筛选

筛选出品名为“榴莲”且销售数量大于等于500的数据：

=FILTER(A2:C8,(B2:B8=E2)*(C2:C8>=500))