トップ > VBA・VBS > Excel VBA > 重複を削除して名寄せリストを作る

重複を削除して名寄せリストを作る

Excel VBA(元の名簿には触らない安全な名寄せ)

複数経路で集めた名簿には、同じ人が何度も入っています。並べ替えて目で探す名寄せは、件数が多いほど漏れますし、元の名簿を直接いじるのは怖いものです。このレシピでは、指定した列(メールアドレスなど)が重複する行を除いたリストを、新しいシートに書き出すマクロを作ります。元データには一切触らないので、失敗してもやり直せます。「最初の1件を正」として残すのがルールです。

✅ これができます:ボタン1つで、重複のない名寄せリストを別シートに作れるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)は重複の入った名簿。実行すると(右)、最初の1件だけを残した名寄せリストが別シートにできます。元の名簿はそのままです

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① 名寄せもとになるデータのシート名
Const DATA_SHEET As String = "名簿"
' ② 名寄せ結果を書き出すシート名
Const OUTPUT_SHEET As String = "名寄せ結果"
' ③ 重複を判定する列の番号(A列=1、B列=2…。例はメールアドレスの列)
Const KEY_COL As Long = 3
' ④ データの見出し行の数
Const HEADER_ROWS As Long = 1
' ===== ここから下は変えなくてOK =====
 
Sub 名寄せリストを作る()
    Dim wsData As Worksheet, wsOut As Worksheet
    Dim dic As Object
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, outRow As Long, removed As Long
    Dim key As String
 
    If DATA_SHEET = OUTPUT_SHEET Then
        MsgBox "①データと②結果のシート名が同じになっています。変えてください。", vbExclamation, "名寄せ"
        Exit Sub
    End If
 
    Set dic = CreateObject("Scripting.Dictionary")
    Set wsData = ThisWorkbook.Worksheets(DATA_SHEET)
    Set wsOut = シートを用意する(OUTPUT_SHEET)
    lastRow = wsData.Cells(wsData.Rows.Count, KEY_COL).End(xlUp).Row
    lastCol = wsData.Cells(HEADER_ROWS, wsData.Columns.Count).End(xlToLeft).Column
 
    ' 結果シートをまっさらにし、見出しを写す
    wsOut.Cells.Clear
    wsData.Range(wsData.Cells(1, 1), wsData.Cells(HEADER_ROWS, lastCol)).Copy wsOut.Cells(1, 1)
    outRow = HEADER_ROWS + 1
    removed = 0
 
    For r = HEADER_ROWS + 1 To lastRow
        ' 前後の空白を取り、大文字・小文字の違いは同じものとして判定する
        key = LCase(Trim(CStr(wsData.Cells(r, KEY_COL).Value)))
        If key <> "" And dic.Exists(key) Then
            removed = removed + 1
        Else
            wsOut.Cells(outRow, 1).Resize(1, lastCol).Value = wsData.Cells(r, 1).Resize(1, lastCol).Value
            outRow = outRow + 1
            If key <> "" Then dic.Add key, True
        End If
    Next r
 
    wsOut.Activate
    MsgBox "名寄せが終わりました。(" & (outRow - HEADER_ROWS - 1) & " 件/重複 " & removed & " 件を除きました)", vbInformation, "名寄せ"
End Sub
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの4か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1DATA_SHEET名寄せもとになるデータのシート名自分のブックの名簿シートの名前に書き換えます。このシートは変更されません
2OUTPUT_SHEET名寄せ結果を書き出すシート名無ければ自動で作られます。実行のたびに中身は作り直されます
3KEY_COL重複を判定する列の番号メールアドレスが C列なら 3。会員番号や電話番号など、「同じ人なら同じ値になる列」を指定するのがコツです
4HEADER_ROWSデータの見出し行の数見出しが1行なら 1

組み込み手順

  1. 名簿の中で「重複判定に使える列」(メールアドレス・会員番号など)を決め、列番号を数えます
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①〜④を書き換えます
  4. ファイルを .xlsm で保存し、マクロを実行します(ボタン登録が便利です)
  5. 「名寄せ結果」シートに重複なしのリストができ、除外した件数が表示されれば成功です

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

元の名簿シートは一切変更されません。結果シートは実行のたびに作り直されるので、名簿が更新されたら再実行するだけで最新化できます。判定キーが空欄の行は、重複かどうか判断できないため、そのまま残す仕様です。

カスタマイズ例

大文字・小文字を区別して判定したい

コード内の LCase( の部分を外すと、文字どおり完全一致で判定します。会員番号のように大小の区別が意味を持つ列では、そちらが正しい場合もあります。

「氏名+電話番号」の組み合わせで判定したい

キーを2列の連結にするカスタマイズができます。key の行で、もう1列の値を & でつなげる形です。単独列では別人と同値がぶつかる場合に有効です。

重複した行も控えとして残したい

除いた行を別のシート(重複控え)に書き出す形にすると、後から「何を除いたか」を検証できます。件数が多い現場では、控えつきが安心です。

つまずきどころ

関連レシピ

次はこれ:複数シートのデータを1枚に縦に統合する
名寄せの前に複数シートを統合する工程と組み合わせると、集めた名簿の処理が一連の流れになります

よくある質問

Q. Excel標準の「重複の削除」と何が違う?

A. 標準機能は元の表そのものを書き換えます。このレシピは結果を別シートに出すので、元の名簿が無事で、件数の報告も出ます。定型作業として毎月回すなら、こちらの形が事故に強いです。

Q. 残るのは最初と最後のどっちの行?

A. データの上から見て最初の1件が残ります。「最新の申込を残したい」場合は、元データを日付の新しい順に並べ替えてから実行してください。

Q. 関数の UNIQUE でも同じことができる?

A. できます(当サイトの関数レシピ UNIQUE 参照)。ただしキーの列だけの重複判定や、件数の報告まで含めると、このレシピの形が向いています。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る