EXCEL宏:用字典代替Vlookup

[不指定 2020/06/18 06:18 | by 吕进 | Via csdn ]
Vlookup,在vba中一般用Application.Vlookup来实现,但总归要通过循环完成,如有不匹配的还报错,感觉效率不高。这里直接上几个用字典替代vlookup的方法。
方法一(经测试,3000行数据匹配,只需0.0156秒):
Sub VLOOKUP_01()
    Dim t As Date
    t = Timer
    Application.ScreenUpdating = False
    Sheets("DD").Range("AE2:AF10000").Clear
    Set ddcl = Sheets("数据源")
    Set dd = Sheets("目标表")
    
    Dim data, temp, arr, brr
    Dim d, v
    Dim i&, k&
    Set d = CreateObject("scripting.dictionary")
    Set v = CreateObject("scripting.dictionary")
    data = ddcl.[a2].CurrentRegion '被索引的数据表,也可以用具体的区域
    'data = ddcl.Range("A1:D65536")
    For i = 2 To UBound(data)
        d(data(i, 1) & "") = data(i, 3) '被取值所在列,如果只匹配一列,就不需v字典了
        v(data(i, 1) & "") = data(i, 4) '被取值所在列
    Next

    ddm = dd.Range("A65536").End(xlUp).Row
    temp = dd.Range("k1:k" & ddm) '索引参照列,注意必须是第一行开始
    ReDim arr(2 To UBound(temp), 1 To 1)
    ReDim brr(2 To UBound(temp), 1 To 1)
    For k = 2 To UBound(temp)
        arr(k, 1) = d(temp(k, 1))
        brr(k, 1) = v(temp(k, 1))
    Next
    dd.[AE2].Resize(UBound(arr) - 1, 1) = arr
    dd.[AF2].Resize(UBound(brr) - 1, 1) = brr
    Set d = Nothing    
    MsgBox "运行" & Format((Timer - t), "0.0000") & "秒"
End Sub
注意:目标表中的索引参照范围,必须从表的第一行开始,或者用dd.[K1].CurrentRegion
Tags: , , , , ,

VB:条件数组

[不指定 2019/06/06 11:29 | by 吕进 | Via 本站原创 ]
直接上代码,方便日后查阅:
Sub byw()
Dim arr, arr1
Dim i%, j%, s%
arr = Sheet6.Range("A1:K" & [A65536].End(3).Row) '第一行为标题行'
ReDim arr1(1 To UBound(arr), 1 To 11)
s = 1
For i = 2 To UBound(arr)  '数据区域从第二行开始'
    If arr(i, 10) = "是" Then
       For j = 1 To 11
         'arr1(s, j) = arr(i, j) '如果不改变数组的排列
         arr1(s, 1) = s '我给加的一个序号
         arr1(s, 2) = arr(i, 5)
         arr1(s, 3) = arr(i, 4)
         arr1(s, 4) = arr(i, 3)
         arr1(s, 5) = arr(i, 1)
         arr1(s, 6) = arr(i, 6)        
       Next
       s = s + 1
    End If
Next
Sheet1.Range("A4:AU100").ClearContents
Sheet1.[A4].Resize(s, 6) = arr1
End Sub

以上代码会经常使用,可收藏!
Tags: ,
分页: 1/1 第一页 1 最后页 [ 显示模式: 摘要 | 列表 ]