☰

比較兩個 Excel 工作表中的姓名和出生日期並傳回第三列中的值

tag-icon • tag-icon microsoft-excel vba
比較兩個 Excel 工作表中的姓名和出生日期並傳回第三列中的值

尊敬的程式設計師，我希望您能幫我修改下面的VBA程式碼，使其正常運作。我有一個 Excel 工作簿，其中包含兩個工作表。工作表「資料」包含姓名（A 欄）、出生日期（B 列）和數字（C 欄），最多可達約 300k 行。工作表「搜尋」包含名稱（A 列）和DOB（B 列） 我想將工作表「搜尋」與工作表「資料」進行比較，並將值從「編號」列傳回「搜尋」標籤的C 列（如果DOB）是完全相同的，且名稱的至少一部分是相同的。例如，其中一張紙可以僅包含大寫字母或添加到名稱中的插入或婚前姓名，而在另一張紙中可能會丟失此資訊。我試圖更改我在網路上找到的程式碼，但我仍然是 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 中向下新增一個輔助列“MATCHING DOBs”，如下所示=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）。後一個選項非常強大，我想您只需單擊幾下即可生成所需的數據，而無需任何公式。

無法排序？

簡單的答案：將工作簿保存在谷歌驅動器上，然後打開/轉換為谷歌表格並在那裡排序，或者如果這是一次性的事情，請朋友在更強大的電腦上完成。您還應該“檔案”>“另存為”>“瀏覽”>“另存為類型”>“Excel 二進位工作簿(*.xlsb)”。時間而言。

但是我的公式！

最後一個選項，可以同時解決排序和模糊搜尋問題。您可以透過從名稱欄位中提取兩個或多個最長的單字並將其與出生日期單獨連接（例如「01-01-1964 JONG」）來建立自己的半模糊比較。首先，您建立最多 4 個輔助列，並使用空格、破折號和逗號作為分隔符號從名稱列中提取四個關鍵字。如果不進行排序，現在可以根據每個串聯值執行唯一的 MATCH。匹配的數量將為您提供與 C 列數字配對的置信度。

如果您可以排序（仍然更好），您只需對兩個表中較小的一個進行關鍵字拆分。並且無需與 DOB 進行串聯。您找到上面的出生日期列表，然後使用每個關鍵字搜尋整個姓名字串。

相關內容