excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列

首页 > 职场 > 作者:YD1662023-12-23 10:19:01

FILTER函数是Excel 2021版本中的新函数之一,作用是筛选符合条件的单元格。

常用写法为:

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

借助Excel 2021的数组溢出功能,这个函数让数据查询变得非常便捷。今天咱们就一起来分享一下这个函数的一些典型应用。

1、一对多查询

如下图所示,希望根据F2单元格中指定的部门,提取出左侧列表中“生产部”的所有人员姓名。

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

=FILTER(A2:A16,B2:B16=F2)

excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列(1)

公式中的A2:A16部分,是需要返回内容的单元格区域,B2:B16=F2部分是指定的条件。当指定条件返回逻辑值TRUE时,FILTER函数就返回A2:A16单元格区域中,与之对应的整行记录。

2、多对多查询

如下图所示,希望提取出部门为“生产部”,并且学历为“本科”的所有记录。

I2单元格输入以下公式,按回车。

=FILTER(A2:A16,(B2:B16=F2)*(C2:C16=G2))

excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列(2)

本例中要返回内容的单元格区域是A2:A16,指定的条件为(B2:B16=F2)*(C2:C16=G2)。

两个条件之间使用乘号,表示同时符合。B2:B16=F2和C2:C16=G2分别返回一组由TRUE和FALSE构成的逻辑值的内存数组。

当两组逻辑值的对应位置都是TRUE时,乘法结果返回1,否则返回0。

条件返回不等于0的数值时,相当于逻辑值TRUE,FILTER函数就返回A2:A16单元格区域中与之对应的整行记录。

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

如下图所示,希望查询学历中包含关键字“科”的所有姓名。不论是本科、专科还是民科,都符合要求。

H2单元格输入以下公式,按回车。

=FILTER(A2:A16,ISNUMBER(FIND(F2,C2:C16)))

excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列(3)

本例中指定的条件为ISNUMBER(FIND(F2,C2:C16))。

先使用FIND函数,返回F2单元格中的内容在C2:C16区域中每个单元格所处的位置。如果某个单元格里包含F2中的内容,FIND函数返回表示位置的数字,否则返回错误值。最终得到一组由数字和错误值构成的内存数组。

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

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

4、提取另一列没有出现的人员

如下图所示,希望从A列的人员列表中,提取出没有在C列出现的姓名。

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

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

excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列(4)

本例中指定的条件为COUNTIF(C2:C5,A2:A11)=0

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

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

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

5、提取指定条件的不重复名单

如下图所示,某公司组织体育比赛,同一员工有多个比赛项目。

希望从左侧的列表中,提取出销售部的参赛人员名单。

F2单元格输入以下公式,按回车。

=UNIQUE(FILTER(A2:A11,C2:C11=E2))

excel怎么按条件整行提取数据,excel怎么提取特定数据的行和列(5)

本例中先使用FILTER函数提取出符合条件C2:C11=E2的所有记录,再使用UNIQUE函数提取出不重复记录。

关于FILTER函数,你还有哪些有趣的应用,可以在留言区分享给大家。

好了,今天咱们的分享就是这些,祝各位一天好心情~~

图文制作:祝洪忠



栏目热文

文档排行

本站推荐

Copyright © 2018 - 2021 www.yd166.com., All Rights Reserved.