FILTER函数详解 |
您所在的位置:网站首页 › filter函数用法 › FILTER函数详解 |
分类:查找和引用函数 FILTER函数也是动态数组函数,因此只有Office 365中的Excel或者Excel网页版及Excel 2021之后的版本才可以使用。 我们前面简单介绍过这个函数,也介绍过一些案例。但是这个函数不像看上去那么好理解。我们今天详细解析一下。 基本语法FILTER函数的作用就是从一组数据(数组或者区域)中筛选出符合条件的数据。 语法如下: FILTER(数组,条件,[未找到时的返回值])。 其中, 数组 => 指待筛选的数据,可以是常量数组,也可以是区域,也可以是其他函数返回的数组。 条件 => 指用于筛选的条件,实际上就是一些逻辑表达式,比如:A1:A10>0。 未找到时的返回值 => 指如果函数没有在数组中发现满足条件的数据时,可以返回该值。这个参数是可选的。 FILTER函数从一般使用上来说,还是比较简单的,也非常容易理解: 公式: =FILTER(B3:D7,D3:D7>15)筛选B3:D7区域中满足D3:D7大于15的那些数据。 这是大部分人使用FILTER函数最多的一种场景,确实可以解决很多问题。但是不能充分发挥FILTER函数的作用。我们必须深入理解FILTER函数,掌握FILTER函数的其他用法。 用法2:筛选列前面的基本用法是在区域中筛选行,但是,我们同样可以使用FILTER函数来筛选列: 公式: =FILTER(B2:D7,B2:D2="产品")在B2:D7区域中,筛选标题行为“产品”的列。 实际上,FILTER函数根据你给出的条件的形式来判断是筛选行还是筛选列: 如果你给出的条件是在列上的,比如:C3:C7>0,那么就是筛选行 如果你给出的条件是在行上的,比如:B2:D2="产品",结果就是筛选列 用法3:如何筛选常量数组和动态数组这是理解FILTER函数,从而充分发挥FILTER函数作用的关键。 我们在前面的例子中,都是使用区域,条件也是区域中的列(或行)。 但是,如果要在下面的数组中,进行筛选,应该怎么做呢? {1,2,3;4,5,6;7,8,9}这是一个3行3列的数组,从实质上相当于: 如果要对这个数组进行筛选: =FILTER({1,2,3;4,5,6;7,8,9},? 第二个参数应该怎么写呢? 又比如,我们这里不是一个常量数组,而是一个动态数组: =FILTER(UNIQUER(A1:D100),? 这里的条件参数又应该怎么写呢? 要想找到解决方法,我们必须理解FILTER的条件参数。 FILTER函数的条件参数是一个逻辑值数组 其实,FILTER函数的条件参数并不一定要跟数据区域有关系,只不过是一个逻辑值数组。 比如: =FILTER(B3:D7,{TRUE;TRUE;FALSE;FALSE;TRUE})这里的条件: {TRUE;TRUE;FALSE;FALSE;TRUE}就是一个5行的数组,其中第1行,第2行以及第5行是TRUE,所以就需要在前面的区域中返回第1,2,5行: 而在下面的公式中: =FILTER(B3:D7,D3:D7>15)条件参数: D3:D7>15不过就是这样的逻辑值数组: {FALSE;FALSE;TRUE;TRUE;TRUE}因此, 完整的FILTER公式: =FILTER(B3:D7,D3:D7>15)其实就是: =FILTER(B3:D7,{FALSE;FALSE;TRUE;TRUE;TRUE})返回区域的后三行: 所以,在常量数组中, {1,2,3;4,5,6;7,8,9}如果想筛选其中的某些行,完全可以这么写: =FILTER({1,2,3;4,5,6;7,8,9},{TRUE;FALSE;TRUE})筛选数组的第1,3行: 当然,如果不能直接写筛选条件数组,可以使用INDEX函数来取出其中的某一列作为条件: =FILTER({1,2,3;4,5,6;7,8,9},INDEX({1,2,3;4,5,6;7,8,9},0,2)>4)其中, INDEX({1,2,3;4,5,6;7,8,9},0,2)就是从数组中取出第二列: {2;5;8}条件就变成了: {2;5;8}>4这就是一个数组: {FALSE;TRUE;TRUE}有时候,这个数组太长,直接写有点啰嗦,这时就可以用到LET函数了: =LET(arr,{1,2,3;4,5,6;7,8,9},col,INDEX(arr,0,2),FILTER(arr,col>4))如果是动态数组,也可以这么写: =LET(arr,UNIQUE(A1:D100),col,INDEX(arr,0,2),FILTER(arr,col>4))多条件时为什么不能使用AND函数和OR函数如果需要使用多个条件,可以在多个条件中使用*或者+,其中: * 表示两个条件必须同时成立 + 表示两个条件成立一个即可 例如, =FILTER(B3:D9,(D3:D9>15)*(C3:C9="雪碧"))这个公式中筛选结果必须同时满足:数量>15和产品为“雪碧”, 而公式: =FILTER(B3:D9,(D3:D9>15)+(C3:C9="雪碧"))则返回所有数量超过15的或者产品是雪碧的数据: 这就是逻辑值的运算,为什么不使用AND函数和OR函数呢?毕竟,这两个函数就是用来连接多个条件的。 这是因为,AND/OR函数不能返回数组,我们将两个逻辑值数组用AND做运算, =AND(C3:C9="雪碧",D3:D9>15)结果只能是一个逻辑值,而不是一个逻辑值数组。所以,AND/OR函数不能用于FILTER函数中的条件。 更多FILTER函数案例,请参见: 【Excel技巧】Filter函数的一个很有意思的应用:逐渐减少的下拉列表 Excel中特别有用的函数之Filter Excel+Power Query+Power Pivot+Power BI Power Excel 知识库 按照以下方式进入知识库学习Excel函数 底部菜单:知识库->Excel函数自定义函数 底部菜单:知识库->自定义函数 Excel如何做 底部菜单:知识库->Excel如何做面授培训 底部菜单:培训学习->面授培训 也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |