FILTER函数用得好，年终奖金真不少

[admin]

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(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对应的整行记录。