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

1 用字典的方法去重

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

Sub test_dict1()Dim dict1 As ObjectSet dict1 = CreateObject("scripting.dictionary")Dim dict2 As ObjectSet dict2 = CreateObject("scripting.dictionary")Dim dict3 As ObjectSet dict3 = CreateObject("scripting.dictionary")arr1 = Range("b1:b10")'去重For Each I In arr1 dict1(I) = ""NextFor Each J In dict1.keys() Debug.Print JNextDebug.Print'去重 + 统计次数For Each I In arr1 dict2(I) = dict2(I) + 1NextFor Each J In dict2.keys() Debug.Print JNextDebug.PrintFor Each K In dict2.items() Debug.Print KNextDebug.PrintFor Each I In arr1 If Not dict3.exists(I) Then dict3.Add I, "" Else Debug.Print "存在重复值" Exit Sub End IfNextEnd Sub

2 用数组的方法去重

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

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

Sub test_arr1()Dim arr2arr1 = 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 + 1line1: NextFor Each i In arr2 Debug.Print iNextEnd Sub

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

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

Sub test_arr2()Dim arr2arr1 = 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 = 0For 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 IfNextDebug.PrintFor Each m In arr2 Debug.Print m;NextDebug.PrintEnd Sub

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

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

Sub test001()'查某个值得重复次数arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)target1 = 1Debug.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 IfNextEnd Sub

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

查所有元素的次数

Sub test002()'如果用循环方法查每个重复的值的重复次数arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)Dim dict1 As ObjectSet dict1 = CreateObject("scripting.dictionary")Debug.Print "arr1的最小index=" LBound(arr1)Debug.Print "arr1的最大index=" UBound(arr1)arr2 = arr1For 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 NextNextFor Each I In dict1.keys() Debug.Print I "," dict1(I)NextEnd Sub

只查部分元素的重复次数

Sub test002()'如果用循环方法查每个重复的值的重复次数arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 6, 7, 8, 9)Dim dict1 As ObjectSet dict1 = CreateObject("scripting.dictionary")Debug.Print "arr1的最小index=" LBound(arr1)Debug.Print "arr1的最大index=" UBound(arr1)arr2 = arr1For 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 NextNextFor Each I In dict1.keys() Debug.Print I "," dict1(I)NextEnd 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 "次"NextEnd Sub

其他方法

没看懂

https://cloud.tencent.com/developer/article/1468729

用collection方法的

https://www.cnblogs.com/sylar-liang/p/5563610.html

https://mbd.baidu.com/newspage/data/landingsuper?context={%22nid%22%3A%22news_9430753016572783149%22} n_type=1 p_from=3

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