Excel – 各种带条件花式查找，用filter函数就对了

公众号平台最新的推送规则对技术类文章不太友善，如果不想错过干货，请务必 “设为星标”哦！！！

点击上方蓝字 --> 点击“...”--> 选择“设为星标”

连续讲了几期 O365 函数，非常受读者欢迎。

O365 与之前的版本相较算是一个质的飞越，函数语法变得更加简洁易懂，还简化了数组公式的使用，新增的函数没有鸡肋，每一个都解决了之前版本的痛点。

所以今天再教一个同样让我期待已久的 filter 函数。

函数说明：

作用：

基于定义的条件筛选区域内的数据。

语法：

FILTER(array,include,[if_empty])

参数：

array：必需，要筛选的区域或数组。

include：必需，布尔值数组，其高度或宽度与 array 相同。

[if_empty]：可选，include 参数的数组中所有值都为空（筛选器不返回任何内容）时返回的值。

说明：

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

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

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

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

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

案例：

下图 1 中的表格为某公司员工一季度的收入和业绩完成情况明细，需要在右边区域按以下要求查找：

“诸葛钢铁”的所有记录

“诸葛钢铁”1月的记录

“诸葛钢铁”和“赵铁锤”的所有记录

业绩完成率超过 100% 的所有记录

业绩完成率末 3 名，从低到高排

姓名中有“大”字的所有记录

效果如下图 2 至 7 所示。

解决方案 1：查找“诸葛钢铁”的所有记录

在 G2 单元格中输入以下公式：

=FILTER(A2:E25,B2:B25="诸葛钢铁")

公式释义：

A2:E25：在 A2:E25 区域筛选

B2:B25="诸葛钢铁"：筛选条件为 B 列的单元格内容等于"诸葛钢铁"

解决方案 2：查找“诸葛钢铁”1月的记录

在 G2 单元格中输入以下公式：

=FILTER(A2:E25,(B2:B25="诸葛钢铁")*(A2:A25="1月"))

公式释义：

A2:E25：在 A2:E25 区域筛选

(B2:B25="诸葛钢铁")*(A2:A25="1月")：筛选条件为 B 列等于"诸葛钢铁"并且 A 列等于“1月”

* 表示“与”条件

解决方案 3：查找“诸葛钢铁”和“赵铁锤”的所有记录

在 G2 单元格中输入以下公式：

=FILTER(A2:E25,(B2:B25="诸葛钢铁")+(B2:B25="赵铁锤"))

公式释义：

A2:E25：在 A2:E25 区域筛选

(B2:B25="诸葛钢铁")+(B2:B25="赵铁锤")：筛选条件为 B 列等于"诸葛钢铁"或“赵铁锤”

+ 表示“或”条件

解决方案 4：查找业绩完成率超过 100% 的所有记录

在 G2 单元格中输入以下公式：

=FILTER(A2:E25,D2:D25>1)

公式释义：

筛选区域相同，不再复述

D2:D25>1：筛选条件为 D 列的数值大于 1

解决方案 5：查找业绩完成率末 3 名，从低到高排

在 G2 单元格中输入以下公式：

=SORT(FILTER(A2:E25,D2:D25

公式释义：

SMALL(D2:D25,4)：D2:D25 区域中数值最小的 4 个单元格

D2:D25

FILTER(A2:E25,...)：根据末 3 名，筛选出区域内的行

SORT(...,4)：将筛选出的结果按第 4 列升序排序，即“业绩完成率”从低到高排

解决方案 6：查找姓名中有“大”字的所有记录

在 G2 单元格中输入以下公式：

=FILTER(A2:E25,ISNUMBER(FIND("大",B2:B25)),"查无此人")

公式释义：

FIND("大",B2:B25)：在 B2:B25 区域中查找“大”字，找到会返回一个数字，表示它在文本中的位置；找不到则返回错误值

ISNUMBER(...)：用 isnumber 判断 find 函数返回的是否是数字，产生一组结果为 true 或 false 的逻辑值

FILTER(A2:E25,...,"查无此人")：筛选出区域内条件为 true 的行；如果找不到，则返回“查无此人”

有关 find 函数的详解，请参阅 Excel – 困扰许久的多条件模糊查找，输出不同结果，用find函数终于搞定了。

如果把公式中的“大”替换为“中”，G2 单元格就会返回“查无此人”。