头部广告

Excel筛选函数FILTER太强大了,一对多、多对多查询轻松搞定!

日常工作中,我们经常需要对Excel表格进行数据筛选,熟练使用函数公式可以快速提高工作效率,达到事半功倍的效果。今天就跟大家分享一下WPS中强大的筛选函数FILTER,轻松实现一对多查询、多对多查询。

一、FILTER函数介绍

FILTER是基于定义的条件筛选一系列数据的函数,它由数组,包括,空值三个参数所构成。

使用语法=FILTER(数组,包括,空值)

第一个参数【数组】:就是筛选区域

第二个参数【包括】:就是筛选列=筛选条件

第三个参数【空值】:可以忽略,这个参数就是如果出现错误值可以设置返回信息

备注:FILTER函数需更新至WPS Office最新版本使用

二、FILTER函数实现一对多查询

如下图所示,左侧是员工考核信息表,右侧根据姓名查询出员工的信息,横向返回查询结果。

1-1.jpg

1、首先在右侧的查询结果表格中,先选中G3:I3这几列(因为在WPS中暂时没有溢出功能,据说今年出这个功能),也就是说想要横向返回几列数据,就要先选择几列单元格,如下图所示

1.gif

2、然后在上面的公式位置输入公式=FILTER(B:D,A:A=F3)

输入完公式之后按「Ctrl+Shift+Enter」三键组合,获取所有的查询结果,如下图所示

2.gif

公式解释:

3.jpg

①公式中B:D就是要返回的筛选数据列,A:A=F3就是判断条件,查询表格中的姓名等于员工考核信息表中的A列姓名,然后返回查询数据。

②需要注意,WPS Office暂不支持动态数组(据说今年会支持动态数组),故必须使用「Ctrl+Shift+Enter」键快捷设置为数组形式,其他方式均仅返回为单个数值。

三、FILTER函数实现多对多查询

如下图所示,还是用上面的实例,只是右侧查询表格是根据“姓名”和“部门”两个条件查询,横向返回查询结果。

4-1.jpg

1、同样先在右侧的查询结果表格中,先选中H3:I3这几列(因为在WPS中暂时没有溢出功能),也就是说想要横向返回几列数据,就要先选择几列单元格,如下图所示

4.gif

2、然后在上面的公式位置输入公式

=FILTER(B2:C10,(A2:A10=F3)*(D2:D10=G3),"无数据")

输入完公式之后按「Ctrl+Shift+Enter」三键组合,获取所有的查询结果,如下图所示

5.gif

公式解释:

6.jpg

①公式中第二参数:多条件筛选使用的是(A2:A10=F3)*(D2:D10=G3),有几个条件就用括号()和星号*链接,星号*的意思就是AND且的意义,会筛选出同时满足这几个条件的查询结果。如果查询的空值就返回第三参数:"无数据"。

②需要注意,WPS Office暂不支持动态数组(据说今年会支持动态数组),故必须使用「Ctrl+Shift+Enter」键快捷设置为数组形式,其他方式均仅返回为单个数值。

四、FILTER函数实现多对多查询(同时包含且和或条件逻辑)

如下图所示,上面表格是学生成绩单,下面表格根据姓名、班级、数学分数大于等于90,来查询对应学生的信息。

7-1.jpg

1、先在右侧的查询结果表格中,先选中E12:J12这几列(因为在WPS中暂时没有溢出功能),也就是说想要横向返回几列数据,就要先选择几列单元格,如下图所示

7.jpg

2、然后在上面的公式位置输入公式

=FILTER(A2:F8,(A2:A8=A12)*(B2:B8=B12)*((D2:D8>90)+(D2:D8=90)),"")

输入完公式之后按「Ctrl+Shift+Enter」三键组合,获取所有的查询结果,如下图所示

8.gif

公式解释:

①公式第二参数(A2:A8=A12)*(B2:B8=B12)*((D2:D8>90)+(D2:D8=90))代表4个条件,

前两个条件查找姓名和班级是逻辑“且”的关系所以用星号*连接;

然后根据数学大于90或者等于90,这个是逻辑值是“或”的关系所以用加号+链接(D2:D8>90)+(D2:D8=90),获取数学大于90或者等于90数据后再用括号和星号再跟前面的两个条件连接用星号*((D2:D8>90)+(D2:D8=90))连接。

②需要注意,WPS Office暂不支持动态数组(据说今年会支持动态数组),故必须使用「Ctrl+Shift+Enter」键快捷设置为数组形式,其他方式均仅返回为单个数值。

③上面主要是为了展示逻辑条件“或”的用法,其实上面的公式完全可以写成下面的格式

公式=FILTER(A2:F8,(A2:A8=A12)*(B2:B8=B12)*(D2:D8>=90),"")


底部广告