☰

2つのExcelシートの名前と生年月日を比較し、3番目の列に値を返します。

tag-icon • tag-icon microsoft-excel vba
2つのExcelシートの名前と生年月日を比較し、3番目の列に値を返します。

尊敬するプログラマーの皆さん、以下の VBA コードを修正して正しく動作するように手伝っていただければ幸いです。2 つのワークシートで構成された Excel ブックがあります。ワークシート「データ」には、名前 (列 A)、生年月日 (列 B)、および数値 (列 C) が含まれており、最大 30 万行まで可能です。ワークシート「検索」には、名前 (列 A) と生年月日 (列 B) が含まれています。シート「検索」とシート「データ」を比較し、生年月日が完全に一致し、少なくとも名前の一部が一致する場合は、列「数値」の値を「検索」タブの列 C に返します。たとえば、シートの 1 つでは大文字のみ、または名前に挿入または旧姓が追加されているのに対し、もう 1 つのシートではこの情報が欠落している可能性があります。オンラインで見つけたコードを修正しようとしましたが、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 を避けるようにしています。 「データ」を生年月日で並べ替え、次に D2 にヘルパー列「一致する生年月日」を下方向に追加します=IF(B3=B2,D3,ROW())。 これにより、同じ生年月日の数を追跡できます。

これで、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

インデックスを返します上記の範囲データ内の「短縮名」が SEARCH シートの 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 のあいまい検索アドイン」を Google で検索してください。また、YouTube でいくつかのトレーニング ビデオをランダムに視聴することをお勧めします。JONG DE と DE JONG は間違いなく一致します。Office 365 を使用している場合は、あいまい比較が Power Query に組み込まれています。もう一度 YouTube をご覧ください (例:https://www.youtube.com/watch?v=3s5KcTNy4rs）。後者のオプションは非常に強力で、数式を使わずに数回クリックするだけで必要なデータを生成できると思います。

並べ替えができませんか?

答えは簡単です。ワークブックを Google ドライブに保存し、Google スプレッドシートで開く/変換して並べ替えるか、1 回限りの場合は、より高性能な PC で友人に依頼してください。また、[ファイル] > [名前を付けて保存] > [参照] > [ファイルの種類] > [Excel バイナリ ワークブック (*.xlsb)] も選択する必要があります。これにより、少なくとも自動保存と保存/読み込み時間に関しては、メモリ要件が軽減される可能性があります。

しかし、私の公式は！

最後のオプションは、ソートとあいまい検索の両方を解決します。名前フィールドから 2 つ以上の最長の単語を抽出し、それを個別に DOB と連結することで、独自のセミあいまい比較を作成できます (例: "01-01-1964 JONG")。まず、最大 4 つのヘルパー列を作成し、スペース、ダッシュ、およびコンマを区切り文字として使用して、名前列から 4 つのキーワードを抽出します。ソートせずに、連結された各値に基づいて一意の MATCH を実行できるようになりました。一致の数から、列 C の番号と組み合わせる信頼度レベルが得られます。

並べ替えが可能な場合 (それでも望ましい)、2 つのテーブルのうち小さい方のテーブルでキーワードの分割を行うだけで済みます。また、生年月日との連結を行う必要はありません。上記のように生年月日のリストを見つけてから、各キーワードで名前の文字列全体を検索します。

関連情報