专栏名称: Excel之家ExcelHome

excel技巧原创教程每日推送，excel表格职场模板干货仓库，图文/视频/动画等多种教学方式分享excel操作技巧教程/excel函数公式教程/excel数据透视表教程/excel图表教程/Word教程，助您轻松提高办公效率!

根据条件查数据，FILTER函数最简单

Excel之家ExcelHome · 公众号 · Excel · 2024-12-13 07:45

正文

FILTER函数的作用是筛选符合条件的单元格，目前可以在Excel 2021以及最新版的WPS表格中使用。

常用写法为：

=FILTER(要返回内容的数据区域,指定的条件,[没有记录时返回的内容])

今天咱们就一起来分享一下这个函数的一些典型应用。

1、一对多查询

如下图所示，希望根据E2单元格中指定的品牌，提取出左侧列表中该品牌的所有商品。

E5单元格输入以下公式，按回车，公式结果会自动溢出到其他单元格。

=FILTER(A2:A13,B2:B13=E2)

公式中的A2:A13部分，是需要返回内容的单元格区域。

B2:B13=E2部分是指定的条件。

当指定条件返回逻辑值TRUE时，FILTER函数就返回A2:A13单元格区域中与之对应的整行记录。

2、多对多查询

如下图所示，希望提取出品牌为“松下”，并且库存数大于20的所有记录。

E5单元格输入以下公式，按回车。

=FILTER(A2:A13,(B2:B13=E2)*(C2:C13>20))

本例中要返回内容的单元格区域是A2:A13，指定的条件为(B2:B13=E2)*(C2:C13>20)。

两个条件之间使用乘号，表示同时符合。

(B2:B13=E2)和(C2:C13>20)分别返回一组由TRUE和FALSE构成的逻辑值的内存数组。

当两组逻辑值的对应位置都是TRUE时，说明两个条件同时符合，相乘后的结果为1，否则返回0。

如果相乘后的某项结果不等于0，FILTER函数就返回A2:A13单元格区域中对应位置的整行记录。

3、提取包含关键字的记录

如下图所示，希望提取包含关键字“音响”的所有商品名。

E5单元格输入以下公式，按回车。

=FILTER(A2:A13,ISNUMBER(FIND(E2,A2:A13)))

本例中指定的条件为ISNUMBER(FIND(E2,A2:A13))。

先使用FIND函数，获取E2单元格中的关键字在A2:A13每个单元格所处的位置。如果某个单元格里包含E2中的内容，FIND函数返回表示位置的数字，否则返回错误值。最终得到一组由数字和错误值构成的内存数组。

然后再使用ISNUMBER函数，判断FIND函数的结果是不是数字。如果某个单元格中包含了E2中的关键字，ISNUMBER函数返回逻辑值TRUE，否则返回FALSE。

最终FILTER函数返回A2:A13单元格区域中与TRUE对应的整行记录。

4、提取未售出商品

如下图所示，希望根据A列的商品列表和C列的已售商品列表，提取全部未售出的商品。

E2单元格输入以下公式，按回车。

=FILTER(A2:A13,COUNTIF(C2:C5,A2:A13)=0)

先使用COUNTIF函数统计A2:A13单元格中的每个元素在C2:C5中出现的次数，得到一组由1和0构成的内存数组。

结果为1的，表示在C2:C5中出现过（已售出）。结果为0的，则表示A列的姓名在C2:C5中没有出现过（未售出）。

接下来判断内存数组中的各个元素是否等于0，返回由TRUE或FALSE构成的内存数组。

FILTER函数最终返回A2:A13单元格区域中与TRUE对应的整行记录。

图文制作：祝洪忠

1、点击或搜索微信公众号【 Excel之家ExcelHome】到公众号首页。

2、点击右上角【···】，在下拉菜单中点击【设为星标】。

设置星标后，小伙伴们就不用担心错过咱们的推送了！

设为星标，精彩内容不错过