|
ID:午夜洗衣机 学号:A04- Option Explicit
- Sub 求不重复值1()
- Dim d As New Dictionary '前期绑定
- Dim arr(), k
- Dim i As Integer
- arr = Sheets("求不重复值").Range("a1").CurrentRegion.Value
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = i
- Next
- Cells(2, 3).Resize(d.Count) = Application.Transpose(d.Keys)
- End Sub
复制代码- Sub 求不重复值2()
- Dim d As New Dictionary
- Dim arr(), k, i
- arr = Sheets("求不重复值").Range("a1").CurrentRegion.Value
- d.CompareMode = TextCompare '进行文本比较,不区分大小写
- For Each i In arr
- d(i) = i
- Next
- Cells(2, 4).Resize(d.Count) = Application.Transpose(d.Keys)
- End Sub
复制代码- Sub 双向求值()
- Dim d, k, i
- Dim arr
- Dim x As Integer
- Set d = CreateObject("scripting.dictionary") '后期绑定
- arr = Sheets("双向查找").Range("a2:b6").Value
- For x = 1 To UBound(arr)
- d(arr(x, 1)) = arr(x, 2) 'keys为城市,items为简写
- d(arr(x, 2)) = arr(x, 1) 'keys为简写,items为城市 这样就把城市和简写,都写入KEYS了
- Next
- MsgBox d(Cells(3, 4).Value)
- End Sub
复制代码- Sub 多条件查找()
- Dim arr, sr, arr1
- Dim i As Integer
- Dim d As New Dictionary
- Dim d1 As New Dictionary
- arr = Range("A2:d5").Value
- arr1 = Range("a12:d13").Value
- For i = 1 To UBound(arr)
- sr = arr(i, 1) & "-" & arr(i, 2) '连接AB两列的数值当做keys
- d(sr) = arr(i, 3) '数量写入字典1的items
- d1(sr) = arr(i, 4) '单价写入字典2的items
- Next
- For i = 1 To 2
- sr = arr1(i, 1) & "-" & arr1(i, 2)
- arr1(i, 3) = d(sr) '查找arr1中,在字典1中相应的items
- arr1(i, 4) = d1(sr) '查找arr1中,在字典2中相应的items
- Next
- Cells(12, 3).Resize(2, 1) = Application.Index(arr1, 0, 3) '写入单元格
- Cells(12, 4).Resize(2, 1) = Application.Index(arr1, 0, 4)
- End Sub
复制代码- Sub 单条件求和()
- Dim d As New Dictionary
- Dim arr, k
- Dim i As Integer
- arr = Sheets("单条件求和").Range("B2:C5").Value
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) + arr(i, 2) '相同关键字,对应的项相加
- Next
- Cells(2, 5).Resize(d.Count) = Application.Transpose(d.Keys)
- Cells(2, 6).Resize(d.Count) = Application.Transpose(d.Items)
- End Sub
复制代码- Sub 多列求和()
- Dim arr1(1 To 10000, 1 To 4)
- Dim n
- Dim arr, x As Integer, sr As String, k As Integer
- Dim d As New Dictionary
- arr = Range("a2:D6").Value
- For x = 1 To UBound(arr)
- sr = arr(x, 1) & "," & arr(x, 3) '把两个条件连在一起
- If d.Exists(sr) Then
- n = d(sr) '行数
- arr1(n, 2) = arr1(n, 2) + arr(x, 2) '如果条件相同,那么值相加
- arr1(n, 4) = arr1(n, 4) + arr(x, 4)
- Else '如果keys不存在
- k = k + 1
- d(sr) = k
- arr1(k, 1) = arr(x, 1) '棋盘arr1装入入arr的值
- arr1(k, 2) = arr(x, 2)
- arr1(k, 3) = arr(x, 3)
- arr1(k, 4) = arr(x, 4)
- End If
- Next x
- Range("a13").Resize(k, 4) = arr1
- End Sub
复制代码- Sub 多条件求和()
- Dim arr1(1 To 10000, 1 To 3)
- Dim n
- Dim arr, x As Integer, sr As String, k As Integer
- Dim d As New Dictionary
- arr = Range("a2:c6").Value
- For x = 1 To UBound(arr)
- sr = arr(x, 1) & "," & arr(x, 2) '把两个条件连在一起
- If d.Exists(sr) Then
- n = d(sr) '行数
- arr1(n, 3) = arr1(n, 3) + arr(x, 3) '如果条件相同,那么值相加
- Else '如果keys不存在
- k = k + 1
- d(sr) = k
- arr1(k, 1) = arr(x, 1) '棋盘arr1装入入arr的值
- arr1(k, 2) = arr(x, 2)
- arr1(k, 3) = arr(x, 3)
- End If
- Next x
- Range("a11").Resize(k, 3) = arr1
- End Sub
复制代码 |
评分
-
查看全部评分
|