

FILTER函数和 SORT函数联合应用：动态筛选与排序模型（2）

Excel数据分析之道

2023-06-15

上一篇文章，我介绍的是指定一个条件来筛选和排序，实际数据分析中，我们还可以进行多条件筛选并排序，对数据进行灵活的筛选和排名分析。

下图是一个各个门店、各个产品的销售统计表，现在要设计一个动态筛选表，要求如下：

指定门店性质，可以选择自营、加盟或全部；
指定产品，可以选择某个产品或者全部产品；
指定最低销售数；
对筛选出的数据，从大到小排序。

与前一个例子一样，要对指定商品筛选，则需要使用OFFSET函数先引用该商品的数据区域，这里要使用MATCH定位出该列：

OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)

筛选条件有2个，分别如下。

门店性质筛选条件，这里要注意门店性质可以选择自营或加盟，也可以是全部门店：

IF($M$2="全部",1,$C$3:$C$23=$M$2)

产品筛选条件，对OFFSET函数引用的数据区域进行判断：

OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)>$M$4

这样，筛选公式如下：

FILTER($B$3:$I$23,

IF($M$2="全部",1,$C$3:$C$23=$M$2)

*(OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)>$M$4)

)

对FILTER结果进行排序的公式如下，排序数据表就是FILTER结果：

SORT(

FILTER($B$3:$I$23,IF($M$2="全部",1,$C$3:$C$23=$M$2)

*(OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)>$M$4)),

MATCH($M$3,$B$2:$I$2,0),-1)

而从FILTER结果数据表中提取门店名称和指定产品的数据，使用INDEX函数即可。

单元格L7的公式如下：

=IFERROR(INDEX(SORT(FILTER($B$3:$I$23,IF($M$2="全部",1,$C$3:$C$23=$M$2)

*(OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)>$M$4)),

MATCH($M$3,$B$2:$I$2,0),-1),ROW(A1),1),"")

单元格M7的公式如下：

=IFERROR(INDEX(SORT(FILTER($B$3:$I$23,IF($M$2="全部",1,$C$3:$C$23=$M$2)

*(OFFSET($C$3,,MATCH($M$3,$D$2:$I$2,0),COUNTA($B$3:$B$23),1)>$M$4)),

MATCH($M$3,$B$2:$I$2,0),-1),ROW(A1),MATCH($M$3,$B$2:$I$2,0)),"")

公式看起来很长很复杂，你只需要关注以下几点就明白了：

（1）FILTER函数的结果是一个有多行多列的表；

（2）要从FILTER结果里提取数据，需要使用INDEX函数和MATCH函数。

**********************************

关注公众号，回复 FILTER和SORT ，免费获取本文案例素材。

结束语

关于FILTER函数SORT函数（SORTBY函数）的系列文章，到此结束了，祝朋友们能通过这些文章，能学到更多职场数据处理和分析的知识和技能。

看完文章，别忘了点赞，也是对我的鼓励。

=======================

近期文章一览表

【声明】内容源于网络

Excel数据分析之道

全面介绍Excel在企业数据分析中的各种应用，实际案例模板分享，经验心得，帮助个人和企业提升Excel应用能力。

内容 1106

粉丝 0

Excel数据分析之道全面介绍Excel在企业数据分析中的各种应用，实际案例模板分享，经验心得，帮助个人和企业提升Excel应用能力。

总阅读346

粉丝0

内容1.1k

FILTER函数 和 SORT函数联合应用：动态筛选与排序模型（2）

FILTER函数和 SORT函数联合应用：动态筛选与排序模型（2）