您可以使用数组公式
第一个月
输入公式后，您需要按Ctl + Shift + Enter，而不是Enter

在VBA中，可按如下所示使用上述公式

Option Explicit

Sub Main()
    Dim Param As String
    Param = "b"
    
    MsgBox "The count for " & Param & " is " & UniqueValues(Param)
End Sub

Function UniqueValues(Param As String) As String
    Dim EvaluateString As String
    Dim ws As Worksheet
    Dim wsName As String
    
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    wsName = "'" & ws.Name & "'!"
    
    'SUM(IF(Sheet1!A1:A5="a",1/COUNTIFS(Sheet1!A1:A5,"a",Sheet1!B1:B5,Sheet1!B1:B5)),0)
    EvaluateString = "SUM(IF(" & wsName & "$A$1:$A$5=" & _
                     Chr(34) & Param & Chr(34) & _
                     ",1/COUNTIFS(" & wsName & "$A$1:$A$5," & _
                     Chr(34) & Param & Chr(34) & _
                     "," & wsName & "$B$1:$B$5," & _
                     wsName & "$B$1:$B$5)),0)"
                     
    UniqueValues = Evaluate(EvaluateString)
End Function

行动中

当数据位于“Sheet1”中的A列和B列（从第1行开始）时，可以使用此宏（结果显示在D列和E列）：

Sub macro1()
Dim a As Integer, p As Integer, x As Integer, y As Integer
a = 0: p = 0: x = 1: y = 1
With Sheets("Sheet1")
.Columns("d:e").ClearContents
Do Until x > .Cells(.Rows.Count, 1).End(xlUp).Row
a = 1
Do While .Cells(x, 1) = .Cells(y, 1)
If .Cells(x, 2) <> .Cells(y, 2) Then a = a + 1
x = x + 1
Loop
p = p + 1
.Cells(p, 4) = .Cells(y, 1)
.Cells(p, 5) = a
y = x
Loop
End With
End Sub