☰

如何计算多个工作表中值位于给定范围之间的单元格数量？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function array
如何计算多个工作表中值位于给定范围之间的单元格数量？

我有一个 Excel 工作簿，其中有许多工作表，其中包含如下数据：

    'DATA_1'                        'DATA_2'                        'DATA_3'        
    A       B                       A       B                       A       B       
1   value1     1.6              1   value1     0.8              1   value1     2.0  
2   value2     2.5              2   value2     2.2              2   value2     0.5  
3   value3     3.4              3   value3     3.1              3   value3     3.2

在所有其他工作表的前面还有一个名为 的工作表SUMMARY，其如下所示：

    'SUMMARY'                       
    A       B       C       D       
5           min     max     count   
6   value1     1.0     1.8          
7   value2     1.5     2.5          
8   value3     2.0     3.0

未显示的1行是空的且隐藏。4

您可能已经猜到了，我现在正尝试正确填充列count( )。它应包含整个工作表中所有值D的数量，这些值在从到限制的相应范围内（等于任一限制的值均视为在范围内）。因此，我对给定的样本数据的期望是：value?DATA_?minmax

    D       
5   count   
6        1  
7        2  
8        0

到目前为止，我有一个用户定义的函数来检索工作表名称数组，它工作正常：

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

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

此外，我可以使用 成功访问value1所有工作表中的（代表性）数据{=N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))}。

我能够使用 检查value1值是否符合其min限制{=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6)}。当此数组函数跨越 4 个单元格时，结果如下：

 0       1       0       1

我能够使用 检查value1值是否符合其max限制{=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6)}。当此数组函数跨越 4 个单元格时，结果如下：

 1       1       1       0

最后，我还可以使用 测试单元格的数值{=N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES()))))}。当此数组函数跨越 4 个单元格时，结果如下：

 0       1       1       1

如前所述，这0来自于SUMMARY单元格为空的工作表。B1

我现在的想法是使用函数SUMPRODUCTfinally 等来获取单个计数：{=SUMPRODUCT(N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6);N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6);N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))))}。
但是，尽管我期望1 = (0 * 1 * 0) + (1 * 1 * 1) + (0 * 1 * 1) + (1 * 0 * 1)，但这总是导致0奇怪的结果。所以你能告诉我我在这里做错了什么吗？

（如果可能的话，我更喜欢一种不使用COUNTIF、、、和其他需要将条件作为字符串给出的类似函数的解决方案。）COUNTIFSSUMIFSUMIFS

答案1

将您的 UDF 更改为如下所示：

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

    ReDim retArray(1 To ThisWorkbook.Sheets.Count - 1)
    Do While index <= ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(index).Name <> "SUMMARY" Then
            retArray(i) = ThisWorkbook.Worksheets(index).Name
            i = i + 1
        End If
        index = index + 1
    Loop
    SHEET_NAMES = retArray
End Function

唯一真正重大的变化是我让它忽略了 SUMMARY 表。

然后使用这个数组公式，改编自这里（使用 ctrl+shift+enter 确认）：

=SUM(IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))>=B6,IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))<=C6,1,0),0))

请注意，要使此方法有效，SUMMARY 表上的数据必须从第 6 行开始（如您的示例）。如果不行，您必须调整位，ROW()-5使其在第一行等于 1。它必须以这种方式工作，因为当您自动填充公式时，行数会增加。

编辑：针对您的评论，请使用此公式。它可以与您的原始 UDF 一起使用，并用于ADDRESS()创建引用。

=SUM(IFERROR(IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))>=B6,IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))<=C6,1,0),0),0))

相关内容