公式统计不同数据的个数

hopehui

如果用透视表就很容易实现，现在要用公式实现，麻烦大神看看，谢谢。

edwin11891

统计城市，将A改为B：

1. =ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

复制代码

ww87725244

=SUM(N(MATCH($A$2:$A$9,$A$2:$A$9,)=ROW($A$2:$A$9)-1))
=SUM(N(MATCH($B$2:$B$9,$B$2:$B$9,)=ROW($B$2:$B$9)-1))

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr, 2)
   If Not d.exists(arr(1, i)) Then
      Set d(arr(1, i)) = CreateObject("scripting.dictionary")
   End If
   For j = 2 To UBound(arr)
      If Not d(arr(1, i)).exists(arr(j, i)) Then
         d(arr(1, i))(arr(j, i)) = ""
      End If
   Next
Next
lastr = Cells(Rows.Count, "h").End(xlUp).Row
brr = Range("h5:i" & lastr)
For i = 1 To UBound(brr)
   If d.exists(brr(i, 1)) Then
      brr(i, 2) = d(brr(i, 1)).Count
   Else
      brr(i, 2) = "没有匹配数据"
   End If
Next
[h5].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub

沈默00

=COUNTA(UNIQUE(A2:A9))

我的城市你来过

省份5，城市8

sandays

=COUNTA(UNIQUE(A2:A9))

mjm04

参与一下

=HSTACK({"省份";"城市"},VSTACK(ROWS(UNIQUE(A2:A100))-1,ROWS(UNIQUE(B2:B100))-1))