☰

Excel 数组函数——如何计算多个工作表中的非空白单元格？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function array
Excel 数组函数——如何计算多个工作表中的非空白单元格？

我有一个 Excel 工作簿，其中包含多个工作表，其中一些工作表的单元格中包含数值A1：

  'DATA_1'        'DATA_2'        'DATA_3'  
    A               A               A       
1    1.6        1   -0.8        1

A1我想通过单个公式确定整个工作簿中包含数据的单元格数量，因此上述示例的结果应该是2因为最后一张工作表中的单元格是空白的。

由于工作表的数量可能有所不同，我使用用户定义函数（UDF），它返回包含所有工作表名称的数组：

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim idx As Long, retArray() As String
    Application.Volatile True

    With ThisWorkbook.Sheets
        ReDim retArray(.Count)
        For idx = 1& To .Count
            retArray(idx) = .Item(idx).Name
        Next idx
        SHEET_NAMES() = retArray
    End With
End Function

此 UDF 运行正常，公式{=SHEET_NAMES()}返回预期数组（{"DATA_1","DATA_2","DATA_3"}）。（请注意，UDF 需要被视为不可编辑。）

我的想法现在是将 UDF 与函数一起使用ADDRESS()并动态INDIRECT()引用A1所有可用工作表的单元格，用于ISNUMBER()检查是否存在有效数字，将布尔值FALSE/转换TRUE为0/ 1by N()，最后用于SUM()总结所有的0/ 1，如下所示：

{=SUM(N(ISNUMBER(INDIRECT(ADDRESS(ROW(A1);COLUMN(A1);;;SHEET_NAMES())))))}

然而，结果始终是0，即使没有工作表包含空单元格A1。

我发现该ADDRESS()函数返回正确的字符串数组，表示对所有单元格的引用A1，即{"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"}。
INDIRECT()返回，{#VALUE!,#VALUE!,#VALUE!}因为它显然不支持数组，但是容器函数ISNUMBER()确实支持数组，因此这似乎正确地对数组元素进行了迭代，并且结果为{TRUE,TRUE,FALSE}。
该N()函数以预期的方式进行转换，因此结果为{1,1,0}。
但最终结果SUM()始终为0，无论有多少张工作表，也无论其中是否部分或全部在单元格中包含有效数字A1。（附注：如果我将公式作为非数组函数输入，则结果仅取决于第一个工作表。）

我尝试使用NOT(ISBLANK())和NOT(ISERROR())代替ISNUMBER()，并尝试将其更改SUM(N(ISNUMBER()))为SUM(COUNT())，但没有成功（所有结果都是0，除了NOT(ISBLANK())给出3）。
（此外，我甚至尝试分别用 和 替换和ROW(A1)，因为根据一些网页，这应该可以解决 的数组相关问题，但同样没有任何变化。）COLUMN(A1)ROWS($A$1:A1)COLUMNS($A$1:A1)INDIRECT()

那么你能告诉我我在这里做错了什么吗，以及如何在不失去灵活性的情况下克服失败？

（如果可能的话，我更喜欢一种不使用COUNTIF()或SUMIF()任何其他需要将特定条件以字符串形式给出的函数的解决方案，以保持可移植性。）

答案1

您可以尝试使用此代码：

=SUMPRODUCT(SUBTOTAL(2;INDIRECT(ADDRESS(ROW();COLUMN();;;SHEET_NAMES))))

答案2

您不需要所有工作表名称，只需要第一个和最后一个：

=COUNT(Sheet1:Sheet3!A1)

相关内容