分类:查找和引用函数
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函数的其他用法。
前面的基本用法是在区域中筛选行,但是,我们同样可以使用FILTER函数来筛选列:
公式:
=FILTER(B2:D7,B2:D2="产品")
在B2:D7区域中,筛选标题行为“产品”的列。
实际上,FILTER函数根据你给出的条件的形式来判断是筛选行还是筛选列:
如果你给出的条件是在列上的,比如:C3:C7>0,那么就是筛选行
如果你给出的条件是在行上的,比如:B2:D2="产品",结果就是筛选列
这是理解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))
如果需要使用多个条件,可以在多个条件中使用*或者+,其中:
* 表示两个条件必须同时成立
+ 表示两个条件成立一个即可
例如,
=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+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服