arrays - VBA，使用多个条件从每个工作表中获取计数

Question

我的工作簿中有 10 张纸，第 10 张纸包含前 9 张纸的名称。我试图使用 VBA 创建一个公式来获取每张纸的计数。

例如，我在 sheet1 中有数据从范围(a1:b500)开始，计数的标准是对于A列中的每个非空白单元格，B列中应该有一个空白单元格。

是否可以创建一个UDF，它将范围(a1:a500)作为一个数组，将范围(b1:b500)作为第二个数组并根据上述标准给出计数？函数中的参数应该只是工作表名称 - 例如，函数应该将参数作为 =GetCount(sheet1) , 其中 GetCount是函数名，而 sheet1 是参数。

我找到了使用循环的解决方案，但我想避免在 vba 中使用循环，countif和 sum(array)在 Excel 中使用数组功能进行试用

Function GetCount(Str As String)
    Application.Volatile (True)
    Dim wb As Workbook
    Dim sh As Worksheet
    Dim rng1 As Range
    Dim r As Integer
    Dim strCount As Integer
        Set wb = Workbooks("Integrated Working Tracker")
        Set sh = wb.Sheets(Str)
        Set rng1 = sh.Range("a1:a500")
        For Each c In rng1.Cells
         If c.Value <> "" And c.Offset(0, 1).Value = "" Then
         strCount = strCount + 1
         End If
        Next
    GetCount = strCount
    Set sh = Nothing
    Set wb = Nothing
    Set rng1 = Nothing
End Function

任何帮助深表感谢。先感谢您

Answer 1

您可以使用 sumproduct公式。

在单元格本身中，公式的工作方式如下:

=SUMPRODUCT((A1:A500<>"")*(B1:B500=""))

如果在计算中组合单个条件表达式，则将其转换为 1 或 0，然后将逐行结果相加。

在 VBA 中，有 Worksheetfunction.SumProduct ，但是(据我记得，现在没有再次测试)这不支持这种“特定”功能(=将 bool 表达式转换为 1 或 0)。所以你必须使用 Evaluate .

Function GetCount(shName As String)
    Dim wb As Workbook
    Dim sh As Worksheet

    Set wb = Workbooks("Integrated Working Tracker")
    Set sh = wb.Worksheets(shName)
    GetCount = sh.Evaluate("SUMPRODUCT((A1:A500<>"""")*(B1:B500=""""))")
End Function

编辑:自 Str也是一个集成函数，我不会用它作为变量名。