连续讲了几期 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)),4)
公式释义：
- SMALL(D2:D25,4)：D2:D25 区域中数值最小的 4 个单元格
- D2:D25<...：比末 4 名还小，即末 3 名
- 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 单元格就会返回“查无此人”。
很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。
现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。