vba - VBA 中的应用程序.SumProduct/Countifs

Question

我尝试在 vba 中使用应用程序 SumProduct 和 Countifs，而不是 .Formula 方法。以下是我每月运行的报告的示例:

我需要能够按多个标准组织这些数据。下面是电子表格的图片，我尝试根据给定的条件组织这些数据:

我创建了宏，以便该电子表格的用户在一个选项卡上输入条件，然后该选项卡会根据他们的输入自动设置格式。数据需要按如下方式组织:

理算员内政部名称>数据集(报告中的 C 列)> claim 类型(报告中的 E 列)>州(报告中的 G 列)

经过多次尝试后，我知道 sumproduct 公式中的 countif 可以实现这一点。下面是我迄今为止编写的代码片段:

Dim linerngs As Range
Dim lineitem As Range
Dim lastlinerow As Long
Dim Tab1LastRow As Long
Dim claimstab As String

Dim wsf
Dim SpecialStates As Range
Dim DedicatedUnits As Range
Dim HomeOffice As String
Dim DataSet As String

claimstab = Sheet2.Range("C2") & " Claims"
priorclaimstab = Sheet2.Range("C3") & " Claims"

Tab1LastRow = Sheets(claimstab).Cells(Sheets(claimstab).Rows.Count, "A").End(xlUp).Row


Set wsf = Application.WorksheetFunction

Set SpecialStates = Sheet2.Range("E2:AA2")
Set DedicatedUnits = Sheet2.Range("E5:BB5")

lastlinerow = Sheet2.Range("D" & Rows.Count).End(xlUp).Row

Set linerngs = Sheet2.Range("D12:D" & lastlinerow).SpecialCells(xlCellTypeConstants, 23)

    For Each lineitem In linerngs

HomeOffice = lineitem.Offset(0, -3).Value
DataSet = lineitem.Offset(0, -1).Value

firsttype = lineitem.Offset(0, 1).Address(False, False)
lasttype = lineitem.Offset(0, 6).Address(False, False)

firststate = lineitem.Offset(0, 7).Address(False, False)
laststate = lineitem.Offset(0, 15).Address(False, False)

ClaimTypes = Sheet2.Range(firsttype & ":" & lasttype)
StateJuris = Sheet2.Range(firststate & ":" & laststate)

    If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") = 0 Then

        lineitem.Offset(0, 16) = Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimTypes, Sheets(claimstab).Range("G2:G" & Tab1LastRow), StateJuris))

    End If

Next lineitem

这对所有内容都返回零值。我开始摸索，发现如果我将变量“ClaimTypes”替换为“IN”，并将变量“StateJuris”替换为“TX”或 Countifs 代码中的任何其他州缩写，它会给我一个正确的计算。我知道在 countifs 的电子表格公式版本中，您可以引用水平线的单元格并立即评估它们。例如，如果您查看第二张图片的第 12 行，我需要能够评估第一张图片中报告的 G 列的状态“AK”；“CA”；“HI”；“TX” .

我不知道如何使用 countifs 和 sumproduct 的 VBA 应用程序版本来实现此功能。对此的任何帮助将不胜感激。

编辑:

为了不计算我发布的第二张图片中 E5:W5 范围内的状态，我尝试了如下操作:

        If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then
        c = 0
        For Each ClaimType In ClaimTypes
            For Each DedState In DedicatedUnits
            c = c + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), "<>" & DedState.Value))
            Next DedState
        Next ClaimType

        lineitem.Offset(0, 16) = c
        End If

但是，这不起作用，而且我也不认为我会这么做。我假设我需要做类似的事情:

If InStr(1, lineitem.Value, "Indemnity") > 0 And InStr(1, lineitem.Value, "AOS") > 0 Then

    c = 0
    nc = 0

For Each ClaimType In ClaimTypes

c = c + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value))

Next ClaimType


For Each ClaimType In ClaimTypes

    For Each DedState In DedicatedUnits

            nc = nc + Application.WorksheetFunction.SumProduct(wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.Value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), DedState.Value))

    Next DedState

Next ClaimType

    lineitem.Offset(0, 16) = c - nc

End If

Answer 1

首先，您可以使用 SUM()在这里而不是 SUMPRODUCT() - 您仅向 SUMPRODUCT() 提供一个参数，所以它只是返回总和。

其次，如果您确实想在 VBA 中执行数组公式，请将整个公式包装在 EVALUATE([formula]) 中。 。但这对你不起作用，因为......

第三，即使您在 Excel 工作表中将其作为数组公式输入，如果 claim types 都有多个条件，它也无法正常工作。和states ，因为它只会添加 claim1 出现的次数和state1和claim2和state2等等(但是，如果任一列只有一个，则该公式将有效。)

所以，如果你想这样做，我会做的是嵌套 FOR循环，其中每个 Claim Type-State组合，你再做一个COUNTIFS()使用特定的Claim Type和State并增加运行总计。

更新:例如，类似

c = 0
for each claimType in claimTypes
    for each state in StateJuris
        c = c + wsf.CountIfs(Sheets(claimstab).Range("B2:B" & Tab1LastRow), HomeOffice, Sheets(claimstab).Range("C2:C" & Tab1LastRow), DataSet, Sheets(claimstab).Range("E2:E" & Tab1LastRow), ClaimType.value, Sheets(claimstab).Range("G2:G" & Tab1LastRow), State.value)
    next state
next claimType

lineitem.Offset(0, 16) = c