Excel公式技巧：使用OFFSET函数生成的数组

excelperfect

引言：本文学习整理自dailydoseofexcel.com，很有意思的一件事。

如何在一列列表中创建包含筛选项目的数组？SUBTOTAL函数允许使用有限数量的工作表函数对此类数组进行操作，但它不会展现进行公式操作的这个数组。然而，OFFSET函数的第二个参数是数组时，例如：

OFFSET(rng,ROW(rng)-MIN(ROW(rng)),,1)

会返回一个单元格区域数组。如果数组大小合适，如本例所示，OFFSET函数会为原始单元格区域（rng）中的每个单元格返回一个单独的单元格区域。因此，如果使用SUBTOTAL函数操作该数组，则每个单元格区域都会单独计算。这样，公式：

=SUBTOTAL(3,OFFSET(rng,ROW(rng)-MIN(ROW(rng)),,1))

对于每个可见的单元格计算为1，如果单元格不可见则计算为0。使用3作为SUBTOTAL函数的第一个参数计算可见区域内的项目数。由于每个区域内只有一项，因此答案只能是0或1，如下图1所示。

图1

这样，此公式可以用作数组，指示列表中已过筛选和未筛选的行。如果要返回一列列表中的项目数组，使用：

=IF(SUBTOTAL(3,OFFSET(rng,ROW(rng)-MIN(ROW(rng)),,1)),rng,'')

又如下图2和图3所示。在图2中，是未进行筛选操作的；在图3中，是进行了筛选操作的。

图2

图3

在单元格B12中的公式：

=SUM((range1='完美Excel')*(SUBTOTAL(3,OFFSET(range2,ROW(range2)-MIN(ROW(range2)),,1))))

单元格B13中的公式：

=SUM((range1='完美Excel')*(SUBTOTAL(9,OFFSET(range2,ROW(range2)-MIN(ROW(range2)),,1))))

与SUBTOTAL函数一起使用的OFFSET函数返回一个数组，该数组可用作数组公式的一个元素。不带OFFSET函数的SUBTOTAL函数仅返回标量值，而不是数组。

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