excelperfect

引言：本文學(xué)習(xí)整理自dailydoseofexcel.com，很有意思的一件事。

如何在一列列表中創(chuàng)建包含篩選項目的數(shù)組？SUBTOTAL函數(shù)允許使用有限數(shù)量的工作表函數(shù)對此類數(shù)組進行操作，但它不會展現(xiàn)進行公式操作的這個數(shù)組。然而，OFFSET函數(shù)的第二個參數(shù)是數(shù)組時，例如：

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

會返回一個單元格區(qū)域數(shù)組。如果數(shù)組大小合適，如本例所示，OFFSET函數(shù)會為原始單元格區(qū)域（rng）中的每個單元格返回一個單獨的單元格區(qū)域。因此，如果使用SUBTOTAL函數(shù)操作該數(shù)組，則每個單元格區(qū)域都會單獨計算。這樣，公式：

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

對于每個可見的單元格計算為1，如果單元格不可見則計算為0。使用3作為SUBTOTAL函數(shù)的第一個參數(shù)計算可見區(qū)域內(nèi)的項目數(shù)。由于每個區(qū)域內(nèi)只有一項，因此答案只能是0或1，如下圖1所示。

圖1

這樣，此公式可以用作數(shù)組，指示列表中已過篩選和未篩選的行。如果要返回一列列表中的項目數(shù)組，使用：

=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函數(shù)一起使用的OFFSET函數(shù)返回一個數(shù)組，該數(shù)組可用作數(shù)組公式的一個元素。不帶OFFSET函數(shù)的SUBTOTAL函數(shù)僅返回標(biāo)量值，而不是數(shù)組。

歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識。