Excel 批量查找录入

• 简介

VLOOKUP（垂直查找）是Excel中常用的函数之一，用于在表格中查找某个值，并返回该值所在行中的另一个值。VLOOKUP在批量处理数据时非常高效，比如需要根据一个字段（如“地点”）查找并返回其他相关数据（如“经度”和“纬度”）的场景。

比如，已知表1中的信息，现在需要得到表2中的经纬度时（特别在数据量大，比如又不同时间的不同地点的数据需要对其分别填充经纬度），此功能是快捷又准确的方法。

表1

表2

• 小体量数据-工具栏操作

小体量数据可以直接使用工具栏快速操作，具体步骤如下:

1.找到查找录入工具

 

2.选择数据区域

查找表区域为已有数据区域，如上面的表1，而录入表区域则是我们需要填充数据的表格如上面表2。这两个区域可以在同一个sheet表中，也可以位于不同sheet表中。完成后点击下一步。

 

3.选择对应字段。

其中查找字段为已知字段，录入字段为需要录入信息的字段。自己可以按照实际需求增减字段信息。设置完成后点击开始录入即可得到结果。

 

• 大体量数据-VBA编辑器

当数据量较小的时候，此方法是非常方便的，担当数据量大时未收到限制，因为该功能的最大单元格数量限制为10000。为解决此限制可以采取以下方法：

方法 1: 使用 INDEX 和 MATCH 代替 VLOOKUP

INDEX 和 MATCH 的组合比 VLOOKUP 更灵活，并且没有 VLOOKUP 中列数的限制。可以将其替代 VLOOKUP 进行数据查找。

方法 2: 使用字典对象优化查找

如果你有一个非常大的数据集，可以使用 VBA 中的 Scripting.Dictionary 来快速查找数据。字典对象可以通过键值对存储数据，并且查找速度非常快。

方法 3: 分批查找

如果你不得不使用 VLOOKUP，可以尝试分批查找来避免一次性加载过多数据。你可以将数据分成几个较小的部分，分别执行查找操作。这样可以减轻 Excel 处理大量数据时的压力。

方法 4: 使用数组进行查找

如果数据量特别大，使用数组处理数据会比直接在工作表中操作更高效。你可以将数据加载到数组中，再进行查找和操作。

其中方法4可以解决大部分问题，此处给出示例代码，可根据自己的需求修改工作表名称，匹配和录入字段名称，字段行数、所在列等信息：

在开发工具-VB编辑器中输入并运行一以下代码：

Sub BatchLookupAndInsert()

    Dim wsData As Worksheet

    Dim wsLookup As Worksheet

    Dim dict As Object

    Dim i As Long, stationName As String

    Dim longitude As Variant, latitude As Variant

    Dim resultRow As Long

    

    ' 初始化工作表

    Set wsData = ThisWorkbook.Sheets("Sheet") '录入数据所在工作表名称

    Set wsLookup = ThisWorkbook.Sheets("Sheet2") ' 查找数据所在工作表

    ' 创建字典对象

    Set dict = CreateObject("Scripting.Dictionary")

    ' 载入查找数据 (Sheet2: stationname, Longitude, Latitude)

    For i = 1 To n ' n为查找数据的行数

        stationName = Trim(wsLookup.Cells(i, k).Value) ' k查找字段所在的列，如以上的地点字段

        Longitude = wsLookup.Cells(i, l).Value ' l 录入字段 经度 所在列

        Latitude = wsLookup.Cells(i, m).Value ' m 录入字段 维度 所在列  

        ' 检查 Longitude 和 Latitude 是否为有效的数值

        If IsNumeric(longitude) And IsNumeric(latitude) Then

            ' 将 stationname 作为键，Longitude 和 Latitude 作为值存储到字典

            If Not dict.Exists(stationName) Then

                dict.Add stationName, Array(longitude, latitude)

                Debug.Print "已加载字典: " & stationName & " -> Longitude: " & longitude & ", Latitude: " & latitude

            End If

        Else

            ' 如果数据无效，则跳过该行

            Debug.Print "无效数据: " & stationName & " -> Longitude: " & longitude & ", Latitude: " & latitude

        End If

    Next i

    ' 遍历数据1 (查找字段为 stationname，录入字段为 Longitude, Latitude)

    resultRow = 1 ' 从第一行开始处理数据1中的行

    For i = 1 To p ' p为录入数据的总行数

        stationName = Trim(wsData.Cells(i, k).Value) ' 获取 匹配字段，如地点（在第k列）

        ' 如果字典中存在该 stationname，则进行录入

        If dict.Exists(stationName) Then

            wsData.Cells(i, l).Value = dict(stationName)(0) ' 录入 Longitude（第l列）

            wsData.Cells(i, m).Value = dict(stationName)(1) ' 录入 Latitude（第m列）

            Debug.Print "已录入: " & stationName & " -> Longitude: " & dict(stationName)(0) & ", Latitude: " & dict(stationName)(1)

        End If

        ' 每处理1000行数据输出进度

        If i Mod 1000 = 0 Then

            Debug.Print "已处理 " & i & " 行"

        End If

    Next i

    MsgBox "查找并录入完成！"

End Sub