excel - 基于其他 2 列的计数值

Question

如何在 vba 或 excel 公式中计算值，如下例所示，计数列应具有 column1 和 column2 组合的值。

标签： excelvbaexcel-formulacount

Answer 1

比较计数两列

• 如果代码在包含工作表的工作簿中，那么您可以替换Range(FirstCell)为 egThisWorkbook.Worksheets("Sheet1").Range(FirstCell)以确保它在正确的工作表上运行。

编码

Option Explicit

Sub compareCountTwoColumns()
    
    Const FirstCell As String = "A2"
    
    Dim rng As Range
    With Range(FirstCell)
        Set rng = .Resize(.Worksheet.Rows.Count - .Row + 1, 2)
        Set rng = rng.Find( _
            What:="*", _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious)
        If rng Is Nothing Then
            MsgBox "No data found.", vbCritical, "No Data"
            Exit Sub
        End If
        Set rng = .Resize(rng.Row - .Row + 1, 2)
    End With
    
    Dim Data As Variant: Data = rng.Value
    
    Dim Previous As Variant: ReDim Previous(1 To 2)
    Previous(1) = Data(1, 1): Previous(2) = Data(1, 2)
    Dim Current As Variant: ReDim Current(1 To 2)
    Dim Result As Variant: ReDim Result(1 To UBound(Data, 1), 1 To 1)
    
    Dim cCount As Long
    Dim i As Long
    
    For i = 1 To UBound(Data, 1)
        Current(1) = Data(i, 1): Current(2) = Data(i, 2)
        If Previous(1) <> Current(1) Or Previous(2) <> Current(2) Then
            Result(i - 1, 1) = cCount
            cCount = 1
        Else
            cCount = cCount + 1
        End If
        Previous(1) = Current(1): Previous(2) = Current(2)
    Next i
    Result(i - 1, 1) = cCount
    
    rng.Resize(, 1).Offset(, 2).Value = Result

End Sub