|
说起VLOOKUP大家都不陌生，但你有没有试过用VLOOKUP一次处理超过几十万条记录呢？年底了负责做成本分析的同事求助，用VLOOKUP处理大量数据的时候需要等待很长时间甚至Excel会直接卡死，有没有更高效的方法呢？ 下面向大家介绍使用字典对象应对VLOOKUP处理大量数据时力不从心的问题。 1、假设有两个表格：表2存放订单号及部门、类型、金额、日期、负责人、完成状态等相关信息，共100万条记录；表1存放待查找的订单号及金额、完成状态信息，共50万条记录。 2、按Alt+F11快捷键，进入VBA编辑窗口，插入一个模块，写入以下代码： Sub DLOOKUP() Dim dic As Object Dim arr, brr Dim acol As Long, bcol As Long Dim itm Dim crr() As Long Set dic = CreateObject('Scripting.Dictionary') arr = Sheet1.Range('A1').CurrentRegion brr = Sheet2.Range('A1').CurrentRegion acol = Sheet1.Range('A1').CurrentRegion.Columns.Count bcol = Sheet2.Range('A1').CurrentRegion.Columns.Count ReDim crr(2 To acol) '重定义为与表1的列一致 For i = 2 To acol For j = 2 To bcol If arr(1, i) = brr(1, j) Then crr(i) = j Next Next For i = 2 To UBound(brr) dic(brr(i, 1)) = i Next For i = 2 To UBound(arr) itm = dic(arr(i, 1)) If itm <> '' Then For j = 2 To acol arr(i, j) = brr(itm, crr(j)) Next End If Next Sheet1.Range('A1').Resize(UBound(arr), acol) = arr Set dic = Nothing End Sub 3、运行宏DLOOKUP()，1-2分钟后，结果就在表1中显示出来了。 以后只需要在表2中存放数据源（关键字放第1列），在表1中存放待查找的关键字（关键字放第1列）和需要引用的字段名，然后运行下DLOOKUP()就可以了。 字典概念和基本用法： 字典（Dictionary）是脚本语言（Scripting）中提供的一个对象，通过建立字典可以实现高效的数据查询。 用法1：Dictionary（Key）=Item 在字典（Dictionary）中为关键字（Key）和条目（Item）建立对应关系。 用法2：Item=Dictionary（Key） 在字典（Dictionary）中查找关键字（Key）对应的条目（Item）。 上文的DLOOKUP()首先建立了一个字典对象dic，用arr和brr分别保存表1和表2的数据，找出表1所要引用的字段名在表2中对应的列号；然后在字典中为表2的订单号和行号建立对应关系，在字典中查找表1的订单号得到对应的行号；最后根据行号和列号将表2中对应的数据写到表1中。 字典对象还有Key、Item等属性和Add、Remove、Exists等方法，除了实现查找和引用，还可以实现去重、替换、排序等各种功能，而且效率很高。 作者：水长川 |
|
|
