Excel中VBA操作相关数组和字典VBA操作Excel中数组分类汇总：
1Sub VBA数组分类汇总()
2Dim arr1()
3arr = [a2:c13]
4For i = 1 To UBound(arr)
5    ReDim Preserve arr1(1 To 2, 1 To n + 1)
6    For j = 1 To UBound(arr1, 2)
7        If arr1(1, j) = arr(i, 1) Then
8            arr1(2, j) = arr1(2, j) + arr(i, 3)
9            GoTo 100
10        End If
11    Next
12        n = n + 1
13        arr1(1, n) = arr(i, 1)
14        arr1(2, n) = arr(i, 3)
15100:
16Next
17[e2].Resize(n, 2) = Application.Transpose(arr1)
18End Sub
VBA操作Exce中Fileter函数与数组：
1Sub 筛选()
2[d2:f999].Clear
3i = Cells(Rows.Count, 1).End(xlUp).Row
4Range("c2:c" & i).FormulaArray = "=a2:a" & i & " & ""-"" & b2:b" & i
5arr = Range("c2:c1" & i)
6Range("c2:c1" & i).Clear
7  a = Filter(Application.Transpose(arr), [g1], True)
8For Each b In a
9    n = n + 1
10      c = Split(b, "-")
11    Cells(n + 1, "d") = c(0)
12    Cells(n + 1, "e") = c(1)
13Next
14End Sub
VBA操作Exce中字典实例(字典与数组经典结合)：
1Sub test()
2Set d = CreateObject("scripting.dictionary")
3arr = Sheet1.Range("a1:b" & Sheet1.Cells(Rows.Count, "a").End(xlUp).Row)
4For Each Rng In arr
5    arr1 = VBA.Split(Rng, "|")
6    For Each rngs In arr1
7        d(rngs) = ""
8    Next
9    i = VBA.Join(d.keys, "|")
10    n = n + 1
11    Sheet2.Cells(n, "a") = i
12      d.RemoveAll
13Next
14End Sub
VBA操作Exce中字典应⽤（多列合并计算）：
1Sub 多列合并计算()
2Dim arr1()
3Set d = CreateObject("scripting.dictionary")
4arr = Range("a2:d" & Cells(Rows.Count, 2).End(xlUp).Row) 5For i = 1 To UBound(arr)
6    If ists(arr(i, 1)) Then
7        n = n + 1
8        d(arr(i, 1)) = n
9        ReDim Preserve arr1(1 To 4, 1 To n)
10        arr1(1, n) = arr(i, 1)
11        arr1(2, n) = arr(i, 2)
12        arr1(3, n) = arr(i, 3)
13        arr1(4, n) = arr(i, 4)
14    Else
15        m = d(arr(i, 1))
16        arr1(2, m) = arr1(2, m) + arr(i, 2)
17        arr1(3, m) = arr1(3, m) + arr(i, 3)
excel数组函数的实例18        arr1(4, m) = arr1(4, m) + arr(i, 4)
19    End If
20Next
21[f2].Resize(n, 4) = Application.Transpose(arr1)
22End Sub
VBA操作Exce中字典实例(分类计算)：
1Sub 分类求和()
2Dim arr1
3Set d = CreateObject("scripting.dictionary")
4arr = Range("b2:c" & Cells(Rows.Count, 2).End(xlUp).Row) 5For i = 1 To UBound(arr)
6    d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2)
7Next
8[e8].Resize(d.Count) = Application.Transpose(d.keys)
9[f8].Resize(d.Count) = Application.Transpose(d.items)
10End Sub
1Sub 分类计数()
2Dim arr1
3Set d = CreateObject("scripting.dictionary")
4arr = Range("b2:b" & Cells(Rows.Count, 2).End(xlUp).Row) 5For Each Rng In arr
6    i = d(Rng)
7    d(Rng) = d(Rng) + 1
8    i = d(Rng)
9Next
10[e1].Resize(d.Count) = Application.Transpose(d.keys)
11[f1].Resize(d.Count) = Application.Transpose(d.items)
12End Sub

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。