【问题】

在进行数据筛选时,如果使筛选项显示在指定单元格,效果如下图所示:

打开网易新闻 查看精彩图片

每次筛选的选项显示在B2单元格,如何用公式实现这种效果?

【解决方法】

在 B2 单元格输入公式“ =LOOKUP(1,0/SUBTOTAL(3,OFFSET(B4,ROW(1:13),)),B5:B17) ”,按 Enter 键,执行运算,即可实现筛选选项出现在B2单元格 ,如下图所示。

打开网易新闻 查看精彩图片

【公式解析】

OFFSET(B4,ROW(1:13),):利用OFFSET偏移函数取选项所在行。此公式是以B4为基点,向下偏移13行,即包括了所有选项。

SUBTOTAL(3,OFFSET(B4,ROW(1:13),)):用SUBTOTAL函数实现每个单元格选项出现次数统计,本部分公式的返回一个出现次数的数组,结果如下图:

打开网易新闻 查看精彩图片

0/SUBTOTAL(3,OFFSET(B4,ROW(1:13),)):用0分别去除上一步公式返回的结果,即0与1组成的数组,凡是0/0返回错误值#DIV/0!,0/1返回0,如下图:

打开网易新闻 查看精彩图片

LOOKUP(1,0/SUBTOTAL(3,OFFSET(B4,ROW(1:13),)),B5:B17):用LOOKUP查找函数,在上一步由错误值#DIV/0!与0构成的数组中查找1,#DIV/0!相当于无限大,所以返回第1个0对应的B5:B17区域对应的选项。

【相关荐读】