快捷導(dǎo)航

VBA數(shù)組去重(字典去重多種方法+數(shù)組去重2種方法)

更新時(shí)間：2023年08月01日 15:26:15 作者：奔跑的犀牛先生

本文主要介紹了VBA數(shù)組去重(字典去重多種方法+數(shù)組去重2種方法),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

1 用字典的方法去重

方法1，用字典去重
dict1(I) = ""
方法2，用字典去重 + 統(tǒng)計(jì)次數(shù)
dict2(I) = dict2(I) + 1
方法3，用字典報(bào)重復(fù)，但沒(méi)去重復(fù)
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
'去重 + 統(tǒng)計(jì)次數(shù)
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 "存在重復(fù)值"
       Exit Sub
    End If
Next
End Sub

2 用數(shù)組的方法去重

2.1 用數(shù)組循環(huán)的方法，去數(shù)組重復(fù)也可以

雙循環(huán)
關(guān)鍵點(diǎn)1：雙循環(huán)的目的是，循環(huán)拿1個(gè)數(shù)組，和另外一個(gè)循環(huán)的所有數(shù)做對(duì)比
關(guān)鍵點(diǎn)2：在外層賦值
關(guān)鍵點(diǎn)3：賦值的計(jì)數(shù)變量得獨(dú)立，因?yàn)椴恢烙袔讉€(gè)非重復(fù)數(shù)

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 用循環(huán)數(shù)組方法，判斷每次內(nèi)部循環(huán)，每次是否可以走一個(gè)完整循環(huán)

判斷每次內(nèi)部循環(huán)，每次是否可以走一個(gè)完整循環(huán)
為了配合后面得index選擇性的停在ubound+1上，否則都停在ubound+1上沒(méi)法區(qū)分
array的index指針停在ubound+1就證明內(nèi)部循環(huán)完整走完沒(méi)有exit for,證明無(wú)重復(fù)

Sub test_arr2()
Dim arr2
arr1 = Array(1, 2, 3, 4, 5, 1, 2, 3, 1)
ReDim arr2(LBound(arr1) To UBound(arr1))  '這樣也沒(méi)問(wèn)題，一般有重復(fù)的列肯定更長(zhǎng)
'ReDim arr2(LBound(arr1) To 99999)  這樣也可以，就是故意搞1個(gè)極大數(shù)
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上沒(méi)法區(qū)分
        End If
    Next
    If J = K + 1 Then       'array的index指針停在ubound+1就證明內(nèi)部循環(huán)完整走完沒(méi)有exit for,證明無(wú)重復(fù)
       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用數(shù)組的方法查重復(fù)次數(shù)

2.3.1 用數(shù)組的方法查某個(gè)目標(biāo)值的重復(fù)次數(shù)

Sub test001()
'查某個(gè)值得重復(fù)次數(shù)
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 & "個(gè)" & "index=" & I
    End If
Next
End Sub

2.3.2 用數(shù)組+字典的方法查每個(gè)元素重復(fù)次數(shù)

查所有元素的次數(shù)

Sub test002()
'如果用循環(huán)方法查每個(gè)重復(fù)的值的重復(fù)次數(shù)
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

只查部分元素的重復(fù)次數(shù)

Sub test002()
'如果用循環(huán)方法查每個(gè)重復(fù)的值的重復(fù)次數(shù)
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 用純數(shù)組的方法查呢？

不夠好
因?yàn)檫€是得先去重，否則就會(huì)如下很愚蠢的顯示結(jié)果

Sub test002()
'如果用循環(huán)方法查每個(gè)重復(fù)的值的重復(fù)次數(shù)
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   '每個(gè)元素必然至少重復(fù)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) & "重復(fù)了" & m & "次"
Next
End Sub