excel - VBA中的Application.SumProduct/Countifs

我正在尝试在vba中使用Application SumProduct和Countifs而不是.Formula方法。以下是我每月运行的报告的示例：

我需要能够通过多个条件来组织此数据。下面是电子表格的图片，我试图按照给定的标准组织这些数据：

我创建了宏，以便此电子表格的用户在一个选项卡上输入条件，然后根据其输入自动格式化该选项卡。数据需要组织如下：

Adjuster Home Office名称>数据集（报告中的C列）>索赔类型（报告中的E列）>状态（报告中的G列）

经过几次尝试后，我知道sumproduct公式中的countifs将实现此目的。以下是到目前为止我编写的代码的片段：

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行，则我需要能够针对状态“ AK”，“ CA”，“ HI”，“ TX”评估第一张图片中报告的G列。

我无法弄清楚如何使用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

最佳答案

首先，您可以在此处使用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