在Exce中使用带有动态数组公式的切片器

excelperfect

标签：切片器，动态数组，LAMBDA函数

本文的示例数据如下图1所示。这是一个名为“表1”的表，由Excel自动命名。

图1

准备数据

Excel中，有一些特别的函数仅计算可见行，例如SUBTOTAL函数和AGGREGATE函数。本文示例使用SUBTOTAL函数。

如下图2和图3所示，使用SUBTOTAL函数统计可见行数，

图2

图3

在单元格B9中的公式为：

=SUBTOTAL(103,表1)

公式中，参数103告诉SUBTOTAL在统计时忽略隐藏行。

现在，在上面列表旁添加一个名为“标志”的列，并为每一行使用SUBTOTAL函数，对于每个可见行返回1，如下图4和图5。

图4

图5

在单元格C3中的公式为：

=SUBTOTAL(103,[@示例列表])

创建切片

选择表中的任意单元格。单击功能区“插入”选项卡“筛选器”组中的“切片器”。在“插入切片器”对话框中选择所需要的列，如下图6所示，单击“确定”。

图6

结果如下图7所示。

图7

此时，单击切片器，将筛选列表数据。

将切片器连接到公式

使用FILTER函数来仅返回表中的可见行，即“标志”列为1的行，如下图8所示。

图8

单元格B13中的公式为：

=FILTER(表1[示例列表],表1[标志]=1)

如果不想在原表中添加额外列（如本例中的“标志”列），则可以使用LAMBDA函数，如下图9所示。

图9

单元格H13中的公式为：

=DROP(REDUCE('',表1[示例列表],LAMBDA(a,v,IF(SUBTOTAL(103,v),VSTACK(a,v),a))),1)

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。