☰

比较两个 Excel 表中的姓名和出生日期并返回第三列中的值

tag-icon • tag-icon microsoft-excel vba
比较两个 Excel 表中的姓名和出生日期并返回第三列中的值

尊敬的程序员，我希望您能帮助我修改下面的 VBA 代码，使其正常工作。我有一个包含两个工作表的 Excel 工作簿。工作表“数据”包含姓名（列 A）、出生日期（列 B）和数字（列 C），最多可容纳 30 万行。工作表“搜索”包含姓名（列 A）和出生日期（列 B）。我想将工作表“搜索”与工作表“数据”进行比较，如果出生日期完全相同，并且姓名至少有一部分相同，则将“数字”列的值返回到“搜索”选项卡的 C 列。例如，其中一个工作表只能在姓名中添加大写字母或插入或娘家姓，而另一个工作表中可能缺少这些信息。我尝试修改我在网上找到的代码，但我还是 VBA 新手，它已经在标题处给我一个错误。似乎无法找出我做错了什么（将其放在模块中）。希望能得到一些有用的提示。

Option Explicit

    Sub Search()

        Dim i As Long, j As Long
        Dim LastrowS1 As Long, LastrowS2 As Long
        Dim NameS1 As String, DOBS1 As String, NameS2 As String, DOBS2 As String
        Dim NumberS1 As Number

        LastrowS1 = Data.Cells(Data.Rows.Count, "A").End(xlUp).Row
        LastrowS2 = Search.Cells(Search.Rows.Count, "A").End(xlUp).Row

        For i = 2 To LastrowS1
            With ThisWorkbook.Worksheets("Data")
                NameS1 = .Range("A" & i).Value
                DOBS1 = .Range("B" & i).Value
                NumberS1 = .Range("C" & i).Value
            End With
                For j = 2 To LastrowS2
                    With ThisWorkbook.Worksheets("Search")
                        NameS2 = .Range("A" & j).Value
                        DOBS2 = .Range("B" & j).Value
                    End With

                    If NameS1 = NameS2 And DOBS1 = DOBS2 Then
                        Search.Range("C" & j).Value = NumberS1
                        Exit For
                    End If

                Next j

            Next i

    End Sub

在此处输入图片描述

答案1

替代答案（也因为用公式做这件事很有趣）：

我实际上会尝试避免使用 VBA。我会按 DOB 对“数据”进行排序，然后在 D2 中向下添加一个辅助列“匹配的 DOB”，如下所示=IF(B3=B2,D3,ROW())。这将跟踪相同的 DOB 数量。

现在，对 DOB 执行 MATCH 将返回第一个匹配的 DOB 的索引，而 D 行中的相应值表示最后一个匹配的 DOB 的索引。然后，匹配的 DOB 的完整名称范围将是

=INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0)))               ...can be optimised a bit with LET in office 365 to only calculate MATCH(...) once

现在输入以下内容作为数组公式（CTRL+SHIFT+ENTER）

=MATCH(TRUE, ISNUMBER(SEARCH(*range above*,A2)), 0)           ...array formula CTRL+SHIFT+ENTER

它将返回索引到以上范围数据中的“简称”出现在搜索表 A2 的某个位置。

C 列中匹配姓名和出生日期的最后一个数字将是

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + *match index above* - 1)

或一体化（记住 CTRL+SHIFT+ENTER）：

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + MATCH(TRUE, ISNUMBER(SEARCH(INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0))),A2)), 0) - 1)

公式可能包含一些错误，但概念应该可行

更灵活的搭配 Excel 提供了一些高级模糊比较功能 - 您可以通过不同的方式获得这些功能，具体取决于您的版本号。如果您使用的是 Excel 2016 或更早版本，请谷歌搜索“Excel 模糊查找插件”。我还建议您在 YouTube 上观看一些随机培训视频。它肯定会匹配 JONG DE 和 DE JONG。如果您使用的是 Office 365，Power Query 中已内置模糊比较功能。再次查看 YouTube（例如https://www.youtube.com/watch?v=3s5KcTNy4rs）。后一个选项非常强大，我想您只需单击几下即可生成所需的数据，而无需任何公式。

无法排序？

简单的答案：将工作簿保存在 Google Drive 上，然后打开/转换为 Google Sheets 并在那里进行排序，或者如果这是一次性的事情，请朋友在功能更强大的 PC 上执行此操作。您还应该选择文件 > 另存为 > 浏览 > 另存为类型 >“Excel 二进制工作簿 (*.xlsb)”。这可能会/应该减少内存需求，至少对于自动保存和保存/加载时间而言。

但我的公式！

最后一个选项，它解决了排序和模糊搜索的问题。您可以通过从姓名字段中提取两个或多个最长的单词并将其与出生日期分别连接起来（例如“01-01-1964 JONG”）来构建自己的半模糊比较。首先，您可以创建最多 4 个辅助列，并使用空格、破折号和逗号作为分隔符从姓名列中提取四个关键字。无需排序，您现在可以根据每个连接的值进行唯一的匹配。匹配的数量将为您提供与 C 列数字配对的置信度。

如果您可以排序（仍然可取），则只需在两个表中较小的一个上进行关键字拆分。无需与 DOB 进行连接。您可以按上述方法找到 DOB 列表，然后使用每个关键字搜索整个名称字符串。

相关内容