函数公式求重复值的个数

27234aa

ppla666 发表于 2024-7-28 20:18
SUMPRODUCT((COUNTIF(F$2:F$25,F$2:F$25)>1)*1)/2

谢谢ppla666老师！

zhangcheng6688

学习一下字典，打扰了
Sub aaa()
lastr = Cells(Rows.Count, "f").End(xlUp).Row
arr = Range("f2:j" & lastr)
ReDim brr(1 To 1, 1 To UBound(arr, 2))
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr, 2)
   For j = 1 To UBound(arr)
      If arr(j, i) <> "" Then
         d(arr(j, i)) = d(arr(j, i)) + 1
      End If
   Next
   For Each vv In d.items
      If vv > 1 Then
         brr(1, i) = brr(1, i) + 1
      End If
   Next
   d.RemoveAll
Next
[f27].Resize(1, UBound(brr, 2)) = brr
End Sub