请教大神：VBA不要辅助列怎样统计？谢！

白云无尽9987

请教大神：VBA不要辅助列怎样统计？谢！.zip (30.72 KB, 下载次数: 8)

2025-4-12 11:52 上传

点击文件名下载附件

良良

=COUNTIFS(A$2:A2,A2,B$2:B2,B2)

leepoison

=MAP(A2:A9,B2:B9,LAMBDA(x,y,COUNTIFS(A$2:x,x,B$2:y,y)))

沈默00

=COUNTIFS(A$2:$A2,$A2,B$2:$B2,$B2)

niko88819

VBA ？

1. =niko(A2:A9,B2:B9)

复制代码

1. Function niko(rng1 As Range, rng2 As Range) As Variant
   
2.     Dim arr1 As Variant, arr2 As Variant
   
3.     Dim res() As Variant
   
4.     Dim i As Long, j As Long
   
5.     Dim count As Long
   
6.     If rng1.Rows.count <> rng2.Rows.count Then
   
7.         niko = CVErr(xlErrRef)
   
8.         Exit Function
   
9.     End If
   
10.     arr1 = rng1.Value
    
11.     arr2 = rng2.Value
    
12.     ReDim res(1 To UBound(arr1, 1), 1 To 1)
    
13.     For i = 1 To UBound(arr1, 1)
    
14.         count = 0
    
15.         For j = 1 To i
    
16.             If arr1(j, 1) = arr1(i, 1) And arr2(j, 1) = arr2(i, 1) Then
    
17.                 count = count + 1
    
18.             End If
    
19.         Next j
    
20.         res(i, 1) = count
    
21.     Next i
    
22.     niko = res
    
23. End Function

复制代码

飞天篮球猪

仅供参考，时间复杂度O(n)

zhangcheng6688

借楼学习一下字典
Sub test()
arr = [a2].CurrentRegion
Set dic = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   strkey = arr(i, 1) & arr(i, 2)
   dic(strkey) = dic(strkey) + 1
   arr(i, 4) = dic(strkey)
Next
[a2].CurrentRegion = arr
Set dic = Nothing
End Sub

ykcbf1100

用VBA的话也简单，不需要辅助列，用字典就可以。

ykcbf1100

1. Sub ykcbf()  '//2025.4.15
   
2.     Set d = CreateObject("Scripting.Dictionary")
   
3.     r = Cells(Rows.Count, 1).End(3).Row
   
4.     arr = [a1].Resize(r, 4)
   
5.     For i = 2 To UBound(arr)
   
6.         s = arr(i, 1) & arr(i, 2)
   
7.         d(s) = d(s) + 1
   
8.         arr(i, 4) = d(s)
   
9.     Next
   
10.     [a1].Resize(r, 4) = arr
    
11. End Sub
    

复制代码