VBA数组去重(字典去重多种方法+数组去重2种方法)

admin VBA

2024-03-01 0 983

1 用字典的方法去重

方法1，用字典去重
dict1(I) = ""
方法2，用字典去重 + 统计次数
dict2(I) = dict2(I) + 1
方法3，用字典报重复，但没去重复
If Not dict3.exists(I) Then

Sub test_dict1()
Dim dict1 As Object
Set dict1 = CreateObject(\”scripting.dictionary\”)
Dim dict2 As Object
Set dict2 = CreateObject(\”scripting.dictionary\”)
Dim dict3 As Object
Set dict3 = CreateObject(\”scripting.dictionary\”)
arr1 = Range(\”b1:b10\”)
\’去重
For Each I In arr1
dict1(I) = \”\”
Next
For Each J In dict1.keys()
Debug.Print J
Next
Debug.Print
\’去重 + 统计次数
For Each I In arr1
dict2(I) = dict2(I) + 1
Next
For Each J In dict2.keys()
Debug.Print J
Next
Debug.Print
For Each K In dict2.items()
Debug.Print K
Next
Debug.Print
For Each I In arr1
If Not dict3.exists(I) Then
dict3.Add I, \”\”
Else
Debug.Print \”存在重复值\”
Exit Sub
End If
Next
End Sub

2 用数组的方法去重

2.1 用数组循环的方法，去数组重复也可以

双循环
关键点1：双循环的目的是，循环拿1个数组，和另外一个循环的所有数做对比
关键点2：在外层赋值
关键点3：赋值的计数变量得独立，因为不知道有几个非重复数

Sub test_arr1()
Dim arr2
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 1, 2, 1)
ReDim arr2(UBound(arr1)) \’在外面需要1次redim到位
For i = LBound(arr1) To UBound(arr1)
For j = LBound(arr2) To UBound(arr2)
If arr1(i) = arr2(j) Then
GoTo line1
End If
Next
arr2(k) = arr1(i)
k = k + 1
line1: Next
For Each i In arr2
Debug.Print i
Next
End Sub

2.2 用循环数组方法，判断每次内部循环，每次是否可以走一个完整循环

判断每次内部循环，每次是否可以走一个完整循环
为了配合后面得index选择性的停在ubound+1上，否则都停在ubound+1上没法区分
array的index指针停在ubound+1就证明内部循环完整走完没有exit for,证明无重复

Sub test_arr2()
Dim arr2
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 1)
ReDim arr2(LBound(arr1) To UBound(arr1)) \’这样也没问题，一般有重复的列肯定更长
\’ReDim arr2(LBound(arr1) To 99999) 这样也可以，就是故意搞1个极大数
K = 0
For I = LBound(arr1) To UBound(arr1)
For J = LBound(arr2) To K
If arr1(I) = arr2(J) Then
Exit For \’为了配合后面得index选择性的停在ubound+1上，否则都停在ubound+1上没法区分
End If
Next
If J = K + 1 Then \’array的index指针停在ubound+1就证明内部循环完整走完没有exit for,证明无重复
arr2(K) = arr1(I)
K = K + 1
End If
Next
Debug.Print
For Each m In arr2
Debug.Print m;
Next
Debug.Print
End Sub

2.3用数组的方法查重复次数

2.3.1 用数组的方法查某个目标值的重复次数

Sub test001()
\’查某个值得重复次数
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)
target1 = 1
Debug.Print \”arr1的最小index=\” & LBound(arr1)
Debug.Print \”arr1的最大index=\” & UBound(arr1)
For I = LBound(arr1) To UBound(arr1)
If arr1(I) = target1 Then
Debug.Print target1 & \”第\” & m & \”个\” & \”index=\” & I
End If
Next
End Sub

2.3.2 用数组+字典的方法查每个元素重复次数

查所有元素的次数

Sub test002()
\’如果用循环方法查每个重复的值的重复次数
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)
Dim dict1 As Object
Set dict1 = CreateObject(\”scripting.dictionary\”)
Debug.Print \”arr1的最小index=\” & LBound(arr1)
Debug.Print \”arr1的最大index=\” & UBound(arr1)
arr2 = arr1
For I = LBound(arr1) To UBound(arr1)
m = 1
For J = LBound(arr2) To UBound(arr2)
If arr1(I) = arr2(J) Then
dict1(arr1(I)) = m
m = m + 1
End If
Next
Next
For Each I In dict1.keys()
Debug.Print I & \”,\” & dict1(I)
Next
End Sub

只查部分元素的重复次数

Sub test002()
\’如果用循环方法查每个重复的值的重复次数
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)
Dim dict1 As Object
Set dict1 = CreateObject(\”scripting.dictionary\”)
Debug.Print \”arr1的最小index=\” & LBound(arr1)
Debug.Print \”arr1的最大index=\” & UBound(arr1)
arr2 = arr1
For I = LBound(arr1) To UBound(arr1)
m = 1
For J = LBound(arr2) To UBound(arr2)
If arr1(I) = arr2(J) Then
If m >= 2 Then
dict1(arr1(I)) = m
End If
m = m + 1
End If
Next
Next
For Each I In dict1.keys()
Debug.Print I & \”,\” & dict1(I)
Next
End Sub

2.3.3 用纯数组的方法查呢？

不够好
因为还是得先去重，否则就会如下很愚蠢的显示结果

Sub test002()
\’如果用循环方法查每个重复的值的重复次数
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)
Debug.Print \”arr1的最小index=\” & LBound(arr1)
Debug.Print \”arr1的最大index=\” & UBound(arr1)
arr2 = arr1 \’每个元素必然至少重复1次
For I = LBound(arr1) To UBound(arr1)
m = 0
For J = LBound(arr2) To UBound(arr2)
If arr1(I) = arr2(J) Then
m = m + 1
End If
Next
Debug.Print arr1(I) & \”重复了\” & m & \”次\”
Next
End Sub