Excel VBA 查找匹配数据量太大，运行太慢怎么办？

数据有几十万行，不论调用match还是调用vlookup，跑起来都很慢，卡在那里转十几分钟才好了1万多行，用sql、pq甚至是Excel上直接使用函数匹配都没有这么慢。
求高人指教效率高一些的方法，不要跑这么慢。感谢！

以下是具体情景和我尝试过的几个思路。
重点说明：
1. 原数据有几十列和几十万行，这里只是举例做了一个小数据说明遇到的问题。
2. 请勿纠结代码本身有没有错误。代码本身没有错误，我用很小的数据量试过了没有问题。这里只是手摘出来相关的代码，所以可能有些地方看起来不对。
3. 逐步测试过，全是在匹配那一步开始卡的，前面都很快。

目标：通过sht1中的“Code”与sht2中的“Match_code”匹配，在sht1中的第2列输入对应的sht2中的“Target”的值。

img

img

尝试过的思路1：所有三列数据存入数组，在数组中匹配，匹配结果存入新数组后，将新数组数据导入目标区域。

dim code_num aslong, code_col as integer, match_num as long, match_code_col as integer,target_col as integer
code_num =sht1.usedrange.rows.count
code_col = sht1.rows(1).find(“Code”).column

workbooks.openwb2_path
match_code_num =sht2.usedrange.rows.count
match_code_col =sht2.rows(1).find(“Match_code”).column
target_col = sht2.rows(1).find(“Target”).column

dim arr_code asvariant, arr_match as variant, arr_target as variant, arr_data() as string
arr_match =sht2.Cells(1,match_code_col).resize(match_num,1).value
arr_target = sht2.Cells(1,target_col).resize(match_num,1).value

wb2.close

arr_code = sht1.Cells(2,code_col).resize(code_num - 1,1).value
dim match_i as long, data_i as long
redim arr_data(1 to code_num - 1)
for data_i = 1 to code_num – 1
       if iserror(application.match(arr_code(data_i, 1), arr_match_code, 0)) = true then
              arr_data(data_i)= “Default”
       else
             match_i= application.match(arr_code(data_i, 1), arr_match_code, 0)
              arr_data(data_i)= arr_target(match_i, 1)
       end if
next data_i

dim data_col as integer, xrow as long
data_col = sht1.usedrange.columns.count + 1

sht1.cells(1, data_col).value = “Data”
for xrow = 2 to code_num
       sht1.cells(xrow,data_col).value = arr_data(xrow-1)
next xrow

上面这一小块代码也尝试过整块区域直接赋值：

with sht1
       .cells(1,data_col).value = “Data”
       .cells(2,data_col).resize(match_num– 1, 1).value = arr_data
end with

尝试过的思路2：将sht2中有用的两列复制到sht1中后，调用match函数匹配。

workbooks.open wb2_path

dim col_num as ingerger, match_code_col as integer, target_colas integer
col_num = sht1.usedrange.columns.count
match_code_col =sht2.rows(1).find(“Match_code”).column
target_col = sht2.rows(1).find(“Target”).column

with sht2
       .columns(match_code_col).copysht1.columns(col_num + 3)
       .columns(target_col).copysht1.columns(col_num + 4)
end with

wb2.close

dim code_num as long, code_col as integer,data_i as long, match_i as long
code_num = sht1.usedrange.rows.count
code_col = sht1.rows(1).find(“Code”).column
sht1.cells(1, col_num + 1).value = “Data”
for data_i = 2 to code_num
       if iserror(application.match(sht1.cells(data_i, code_col), sht1.columns(col_num +3), 0)) = true then
              sht1.cells(data_i,code_col + 1).value = “Default”
       else
              match_i= application.match(sht1.cells(data_i, code_col), sht1.columns(col_num + 3), 0)
              sht1.cells(data_i,code_col + 1).value = sht1.cells(match_i, col_num + 4).value
       end if
next data_i

with sht1
       .columns(col_num+ 4).delete
       .columns(col_num+ 3).delete
end with

尝试过的思路3：将sht2中有用的两列复制到sht1中后，调用iferror函数和vlookup函数匹配。

for data_i = 2 to code_num
       sht1.cells(data_i,code_col + 1).value = application.worksheetfunction.iferror(application.worksheetfunction.vlookup(sht1.cells(data_i,code_col), sht1.cells(1, col_num + 3).currentregion, 2, false),"Default")
next data_i

帮你整理了一下思路，借鉴了一下gpt的回答，你看下是否可以解决你的问题：
对于处理大量数据时的效率问题，以下是几个可能提高速度的方法：

  1. 使用数组：Excel VBA中使用数组来处理数据通常比使用单元格操作更高效。可以将需要匹配的数据读取到数组中，然后在数组中进行匹配操作，最后将结果写回到工作表。这样可以减少对单元格的读写操作，加快处理速度。

  2. 使用字典：字典数据结构在VBA中的查找速度非常快。可以将sht2中的数据读取到字典中，以"Match_code"作为键，"Target"作为值。然后遍历sht1中的每个"Code"，通过字典快速查找匹配的"Target"。这种方式可以避免多次遍历和匹配的开销，从而提高效率。

  3. 批量处理：将需要匹配的数据分成小块进行批量处理，而不是逐行或逐个进行匹配。可以设置一个适当的批量大小，例如每1000行进行一次匹配，然后将匹配结果写回到对应的范围中。这样可以减少循环的次数，提高效率。

  4. 使用内存表：可以考虑使用ADODB对象来创建内存表，并将sht1和sht2的数据导入到内存表中。然后通过SQL语句进行连接和匹配操作，最后将结果写回到工作表。ADODB在处理大量数据时通常比Excel VBA的原生操作更高效。

  5. 使用其他语言：如果Excel VBA无法满足高效处理大量数据的需求，可以考虑使用其他编程语言来处理数据。例如，可以使用Python的pandas库、SQL数据库等来进行数据匹配和处理，这些工具通常在处理大量数据时拥有更高的性能。