【簡単エクセル/Excel VBA マクロ】重複データを検出する方法｜Dictionaryオブジェクト｜ワンポイントテクニック #006

2024年12月28日 2025年2月16日

運営者・ポテ

アフィリエイト広告を利用しています。

いつもありがとうございます。

ノンプログラマー向け「Excelマクロ・VBAワンポイントテクニック解説シリーズ」へようこそ！

本稿では「重複データを検出する方法」を解説します！

Information

ノンプログラマー
プログラミングを専門にしていない人たちのことです。
VBAとマクロの違い
VBAは、Visusal Basic for Application の略で、プログラミング言語のことです。マクロは、VBAを使って作成される「機能」のことです。

Excelで大量のデータを扱っていると、名前や商品名などが重複して記録されていることがあります。たとえば名簿管理や売上管理で、同じ人が二重登録されていたり、同じ商品の売上が重複して入力されてしまうケースです。こうした重複は、集計や分析の結果を歪めてしまう原因になります。

そこで今回は、VBAを使って重複データを検出する手法を紹介いたします。複数の方法がありますが、それぞれの方法のメリット、デメリット、適したデータの規模を解説します。なかでもおすすめは Dictionaryオブジェクトを使った方法です！小規模データから大規模データまで幅広く対応でき、コードも簡潔で高速に動作します。

結論

ほとんどの場合、Dictionary を使っておけば、問題ない。

今回紹介するテクニックを使い、スマートに重複検出処理を実装すれば、あなたのマクロの合理性・可読性が一段と上がり、でプロフェッショナルな仕上がりになります！

VBAを活用して、自分やコミュニティに合ったアプリを作成し、仕事量は半分に、成果は2倍にしていきましょう！初心者の方にも分かりやすく丁寧に解説していきますので、ぜひ最後までご覧ください！

Contents

VBAで重複データを検出する6つの方法
Dictionary オブジェクトを使い重複データを検出する方法
- シナリオ
- コードの実装
おわりに
関連記事
VBAプログラミングスキルアップのための参考情報
- VBAプログラミングのスキルアップ
- プログラミングの一般教養

VBAで重複データを検出する6つの方法

VBAで重複データを検出する方法は複数ありますが、主な方法は6つです。それぞれの特徴を下表にまとめました。

特別な事情がなければ、Dictionaryを使った方法で実装しておけば間違いありません。小規模データから大規模データまで幅広く対応でき、コードも簡潔で高速に動作します。

方法	概要	メリット	デメリット	適したデータ規模
WorksheetFunction.CountIfを使う	`WorksheetFunction.CountIf` を使用して、範囲内での値の出現回数をカウントし、2以上のデータを検出します。	実装が簡単	小規模データ向けセルへの依存がある	小規模（～数百行以下）
繰り返し処理を使う	データを1つずつ順番に確認し、それぞれの値が他の値と同じかどうかを比較します。	実装が比較的簡単特別なライブラリは不要基本的な仕組みで汎用性が高い	データ量が多い場合に処理速度が遅いセル操作は直接アクセスが多く非効率	小規模（～数百行以下）
Collectionを使う	`Collection` にデータを格納し、エラー処理で重複を検出します。	実装が簡単サイズ管理が不要	辞書型ほど効率的ではない	中規模（～数千行以下）
Dictionaryを使う	`Dictionary` を用いて一意性を判定しながら重複を検出します。	高速でキーの存在確認をすることができるコードが簡潔幅広いデータ量に対応可能	外部ライブラリを有効にする必要がある（Scripting Runtime）	小規模～大規模（～数万行）
SQLクエリを使う	ADOを使い仮想テーブルを作成し、SQL文で重複データを検索します。	SQLの柔軟性を活用できる大量データでも効率的	セットアップが複雑小規模データにはオーバーヘッドが大きい	大規模（数万行以上）

Dictionary オブジェクトを使い重複データを検出する方法

シナリオ

次のようなフォルダ・ファイル構成です。Dictionaryで重複を検出する.xlsmにコード記述します。このファイルが、重複を含むデータ.xlsxを見に行き、重複しているデータを検出します。

your_folder/
　　├── Dictionaryで重複を検出する.xlsm
　　└── 重複を含むデータ.xlsx

なお、重複を含むデータ.xlsx には次のようなデータが記録されています。

コードの実装

Dictionary オブジェクトを使い重複データを検出するコードと、その実行結果を示します。

コードは次の通りです。

Option Explicit


Sub DetectDuplicates()


    ' 変数宣言
    Dim src_fp              As String         ' データファイルのパス
    Dim this_wb             As Workbook       ' 現在のワークブック
    Dim data_wb             As Workbook       ' データが格納されているワークブック
    Dim data_ws1            As Worksheet      ' データが格納されているワークシート

    ' 配列関連
    Dim data_array()        As Variant        ' データを格納する配列
    Dim dup_data_array()    As Variant        ' 重複データを格納する配列
    Dim array_row_idx       As Long           ' 配列内の現在の行インデックス
    Dim dup_array_row_idx   As Long           ' 重複データ配列の現在の行インデックス

    ' 辞書関連
    Dim dict                As Dictionary     ' 重複検出に使用する辞書オブジェクト
    Dim key                 As Variant        ' Dictionaryのキーとして使用

    ' シート操作関連
    Dim write_row_idx       As Long           ' 結果を書き込む行インデックス

    
    ' データファイルのパスを取得
    src_fp = ThisWorkbook.Path & "\重複を含むデータ.xlsx"
    
    ' ワークブックとワークシートを取得
    Set this_wb = ThisWorkbook
    Set data_wb = Workbooks.Open(src_fp)
    Set data_ws1 = data_wb.Worksheets(1)

    ' 重複検出用のDictionaryオブジェクトを作成
    Set dict = New Dictionary

    ' A列のデータを配列形式で取得
    data_array = data_ws1.Range("A1:A20").Value

    ' データの重複を検出してDictionaryに格納
    For array_row_idx = 1 To UBound(data_array, 1)
    
        ' 辞書にすでにフルーツ名が存在する場合、その行番号をリストに追加
        If dict.exists(data_array(array_row_idx, 1)) Then
            dict(data_array(array_row_idx, 1)) = _
                dict(data_array(array_row_idx, 1)) & "," & array_row_idx
        
        ' フルーツ名が辞書に存在しない場合、新しいデータとして追加
        Else
            dict.Add data_array(array_row_idx, 1), CStr(array_row_idx)
        End If
    Next array_row_idx

    ' 重複データを格納する配列のサイズを調整
    ' 1列目にフルーツ名、2列目に行番号
    ReDim dup_data_array(1 To dict.Count, 1 To 2)

    ' 重複データとその行番号を配列に格納
    dup_array_row_idx = 1
    For Each key In dict.Keys
        If InStr(dict(key), ",") > 0 Then   ' カンマが含まれる（重複の）場合
            dup_data_array(dup_array_row_idx, 1) = key ' フルーツ名
            dup_data_array(dup_array_row_idx, 2) = dict(key) ' 行番号
            dup_array_row_idx = dup_array_row_idx + 1
        End If
    Next key

    ' 重複データをC列とD列に出力
    If dup_array_row_idx > 1 Then ' 重複データが存在する場合
        data_ws1.Range("C1:D" & _
            dup_array_row_idx - 1).Value = dup_data_array
    End If
    

End Sub

コードの実行結果は次の通りです。C列には重複しているデータの名前が、D列にはそのデータが存在する行番号が表示されます。

解説をしていきます。

Option Explicit

ここでは、Option Explicit を有効にしています。この設定を使うと、変数を使用する前に必ず宣言が必要になります。これにより、変数のタイプミスや未定義変数によるエラーを防ぎ、コードの安全性と信頼性を高めることができます。

    ' 変数宣言
    Dim src_fp              As String         ' データファイルのパス
    Dim this_wb             As Workbook       ' 現在のワークブック
    Dim data_wb             As Workbook       ' データが格納されているワークブック
    Dim data_ws1            As Worksheet      ' データが格納されているワークシート

    ' 配列関連
    Dim data_array()        As Variant        ' データを格納する配列
    Dim dup_data_array()    As Variant        ' 重複データを格納する配列
    Dim array_row_idx       As Long           ' 配列内の現在の行インデックス
    Dim dup_array_row_idx   As Long           ' 重複データ配列の現在の行インデックス

    ' 辞書関連
    Dim dict                As Dictionary     ' 重複検出に使用する辞書オブジェクト
    Dim key                 As Variant        ' Dictionaryのキーとして使用

    ' シート操作関連
    Dim write_row_idx       As Long           ' 結果を書き込む行インデックス

ここでは、コード内で使用する変数が宣言されています。変数は Dim 変数名 As データ型 の構文で宣言します。これにより、各変数は As 以降で指定したデータ型のデータを格納できるようになります。

ここでは、具体的には、データファイルのパスや現在のワークブックを表す変数、配列、辞書オブジェクト（Dictionary）など、重複データ検出に必要な変数を宣言しています。可読性を高めるため、カテゴリごとに整理して記述しています。

なお、各データ型の意味は下表の通りです。

データ型	種類	意味
`Workbook`	オブジェクト型	現在のワークブックやデータが格納されているワークブック（操作対象のファイル）を表すオブジェクト型です。
`Worksheet`	オブジェクト型	データが格納されているワークシートを表すオブジェクト型のデータ型です。
`Dictionary`	オブジェクト型	重複データ検出に使用するキーと値のペアを管理する辞書オブジェクト型のデータ型です。
`String`	文字列型	ファイルパスや名前などのテキスト情報を格納する文字列型のデータ型です。
`Long`	数値型	-2,147,483,648 から 2,147,483,647 までの範囲の整数を格納できる数値型のデータ型です。
`Variant`	汎用型	任意のデータ型を格納できる柔軟なデータ型です。ここでは配列や辞書のキーなど多用途に使用されています。

    ' データファイルのパスを取得
    src_fp = ThisWorkbook.Path & "\重複を含むデータ.xlsx"

ここでは、変数src_fpに、操作対象であるデータファイルのパスを設定しています。ThisWorkbook.Path は、このコードを実行しているExcelファイル（自分自身）の保存場所を意味します。これに、ファイル名をつなげることで、ファイルの完全なパスを作成しています。

    ' ワークブックとワークシートを取得
    Set this_wb = ThisWorkbook
    Set data_wb = Workbooks.Open(src_fp)
    Set data_ws1 = data_wb.Worksheets(1)

ここでは、各ワークブックを開くと同時にオブジェクト変数に割り当てています。また、必要なシートも取得し、対応するオブジェクト変数に割り当てています。これにより、以降の操作は、これらのオブジェクト変数を通じて実行できます。

なお、Set キーワードは、オブジェクト型の変数に値を代入する際に使用します。文字列や数値など、オブジェクト以外の値を代入する場合には Set は不要です。このコードでは、this_wb、data_wb、data_ws1 がオブジェクト型の変数であるため、Set を用いて代入しています。

    ' 重複検出用のDictionaryオブジェクトを作成
    Set dict = New Dictionary

ここでは、重複データを検出するための辞書オブジェクトを作成しています。Dictionary はキーと値のペアを管理できるデータ構造であり、効率的に重複を検出するのに適しています。

    ' A列のデータを配列形式で取得
    data_array = data_ws1.Range("A1:A20").Value

ここでは、ワークシートのA列からデータを取得し、data_array 配列に格納しています。配列に格納することで、データをメモリ上で処理できるため、Excelアプリケーション（セル）を逐次操作する場合と比べてオーバーヘッドが削減され、高速な処理が可能になります。

Information

オーバーヘッド
VBAでセルを1つずつ操作する場合、各処理ごとに「オーバーヘッド」と呼ばれる準備作業が発生します。これは、Excelのセルからデータを取得したり書き込んだりする際に、Excelとの間で行われるやり取りのことを指します。このような準備作業が繰り返されることで、全体の処理速度が低下する原因となります。

    ' データの重複を検出してDictionaryに格納
    For array_row_idx = 1 To UBound(data_array, 1)
    
        ' 辞書にすでにフルーツ名が存在する場合、その行番号をリストに追加
        If dict.exists(data_array(array_row_idx, 1)) Then
            dict(data_array(array_row_idx, 1)) = _
                dict(data_array(array_row_idx, 1)) & "," & array_row_idx
        
        ' フルーツ名が辞書に存在しない場合、新しいデータとして追加
        Else
            dict.Add data_array(array_row_idx, 1), CStr(array_row_idx)
        End If
    Next array_row_idx

ここでは、A列のデータを1行ずつ確認し、重複しているかを辞書オブジェクトで管理しています。既に存在するキーには行番号を追加し、新しいデータの場合は辞書に登録しています。

ここがポイントなので、もう少し詳しく解説します。

このコードでは、A列のデータを辞書オブジェクトに格納し、重複データを管理しています。辞書オブジェクトは、キーと値のペアでデータを扱うため、A列の値をキーとして登録し、値として行番号を記録します。

データがすでに登録されているかどうかは、dict.exists メソッドで確認しています。このメソッドは、指定したキーが辞書に存在するかを判定するものです。ここでは、キーが辞書に存在する場合は、そのキーに関連付けられた値を更新しています。つまり、行番号をカンマ区切りで追加しています。一方、キーが存在しない場合は、dict.Add を使って新しいデータとして登録しています。

これにより、同じデータに関連する複数の行番号を効率的に管理できるほか、配列とは異なり、辞書オブジェクトはキーが一意であるため、データの重複を簡単に検出できる仕組みになっています。

    ' 重複データを格納する配列のサイズを調整
    ' 1列目にフルーツ名、2列目に行番号
    ReDim dup_data_array(1 To dict.Count, 1 To 2)

ここでは、重複データを格納する配列 dup_data_array のサイズを設定しています。ReDim を使い、辞書オブジェクト dict のキーの数（重複データの種類）を行数として設定し、1列目にデータ名、2列目に行番号を格納できる2列の構造にしています。

この操作は必ず行う必要があります。これをしないと、配列のサイズが定義されていないため、配列dup_data_array にデータを格納する際にエラーが発生します。

    ' 重複データとその行番号を配列に格納
    dup_array_row_idx = 1
    For Each key In dict.Keys
        If InStr(dict(key), ",") > 0 Then   ' カンマが含まれる（重複の）場合
            dup_data_array(dup_array_row_idx, 1) = key ' フルーツ名
            dup_data_array(dup_array_row_idx, 2) = dict(key) ' 行番号
            dup_array_row_idx = dup_array_row_idx + 1
        End If
    Next key

ここでは、辞書に登録されたデータから重複しているデータのみを抽出し、配列 dup_data_array に格納しています。この配列は後のワークシートへの出力処理に使用されます。

なお、「dup」は「duplicate（重複）」の略です。重複データを格納するための配列であることを示しています。

    ' 重複データをC列とD列に出力
    If dup_array_row_idx > 1 Then ' 重複データが存在する場合
        data_ws1.Range("C1:D" & _
            dup_array_row_idx - 1).Value = dup_data_array
    End If

ここでは、抽出した重複データをワークシートのC列とD列に出力しています。C列にはデータ名、D列には行番号が出力されます。配列の内容を一度にワークシートに書き戻すため、逐次的にセルを操作するよりも処理速度が上がります。

以上で解説は終了です

なお、配列に関してはこちらの記事で詳しく解説しております。もしよろしければ、合わせて活用ください。

【簡単Excelマクロ・VBA】配列を使って繰り返し処理を高速化する方法｜ワンポイントテクニック #003 | みんなの実用学

おわりに

ご覧いただきありがとうございました。

今回の記事では、「重複データを検出する方法」を解説いたしました。

お問い合わせやご要望等ございましたら、「お問い合わせ／ご要望」またはコメントにて、ご連絡いただければ幸いでございます。

皆様の人生がより一層素晴らしいものになるよう、少しでもお役に立てれば幸いでございます。

なお、当サイトでは様々な情報を発信しております。もしよろしければ、トップページもご覧いただけると幸いでございます。

トップページ

この記事を書いた人

運営者・ポテ

■人生を追求する凡人■日本一安全で、気の向くままに自分の時間を過ごせる、こだわりのキャンプ場を作るのが夢■光学・機械系エンジニア（歴20年、内マネジメント10年、特許数件権利化）/副業フリーランスエンジニア■読書・文学愛好■人生は時間そのもの。ひとりでも多くの人が「より良い人生にするために時間を使って欲しい」と願い、仕事のスキルの向上、余暇の充実、資産形成を研究。■VBAアプリ開発サービス提供中（業務委託 / VBA使用経験20年）■Python愛好（歴5年）■VBAエキスパート「Excel VBA スタンダード」（上級者向け資格）/ Python 3 エンジニア認定基礎（経済産業省「ITスキル標準（ITSS）」に掲載）

本稿と関連の深い記事です。もしよろしければ、合わせてご活用ください。

VBAプログラミングスキルアップのための参考情報

ここでは参考図書を紹介いたしますが、これらに限らず自分に合うものを選ぶことが重要だと考えております。皆様の、より一層のご成功を心よりお祈りしております。

VBAプログラミングのスキルアップ

学習用としてもハンドブックとしても役立つ便利な書籍がこちらです。価格はやや高めですが、その内容は非常に充実しています。相応のスキルを身に付けるためには、こうしたしっかりとした書籍を一冊持っておくと良いでしょう。

リンク

入門書に関しては、どの書籍も大きな違いはありません。あまり迷うことに時間をかけるよりは、手頃なものを一冊選んでみると良いでしょう。VBAの入門書は数多く出版されていますので、興味がある方はぜひチェックしてみてください。

甲乙つけがたい場合、私はインプレス社の「いちばんやさしい」シリーズを選ぶことが多いです。

＼チェックしてみよう／

Amazon で見る

＼チェックしてみよう／

楽天市場で見る

＼チェックしてみよう／

Yahoo!ショッピングで見る

VBAのプログラミング能力を客観的に証明したい場合には「VBAエキスパート試験」があります。この試験はVBAの知識を公式に認定するものです。VBAの総合的な能力獲得を目指す方に適しています。以下の公式テキストが販売されております。

リンク

プログラミングの一般教養

「独学プログラマー」というプログラミングの魅力を解説した書籍があります。これはVBAではなくPythonを題材としていますが、プログラミングの基本的な知識や思考法、仕事の進め方まで幅広く学べます。

リンク

こちらの記事でも紹介しております。もしよろしければご覧ください。

【独学プログラマーにおすすめ】「独学プログラマー」｜影響を受けた愛読書 |

カテゴリー: 02｜Excel VBA マクロ、02｜ワーク

タグ: Excel（エクセル）VBA プログラミングマクロ自動化・効率化

【簡単エクセル/Excel VBA マクロ】重複データを検出する方法｜Dictionaryオブジェクト｜ワンポイントテクニック #006

結論

VBAで重複データを検出する6つの方法

Dictionary オブジェクトを使い重複データを検出する方法

シナリオ

コードの実装

おわりに

関連記事

VBAプログラミングスキルアップのための参考情報

VBAプログラミングのスキルアップ

プログラミングの一般教養

コメントを残すコメントをキャンセル

タカキベーカリーの小さい食パンとスライスチーズがジャストフィット！

【簡単エクセル/Excel VBA マクロ】フォルダ内のファイルをひとつだけ開く方法｜Dir関数/FileSystemObject｜ワンポイントテクニック #005

結論

VBAで重複データを検出する6つの方法

Dictionary オブジェクトを使い重複データを検出する方法

シナリオ

コードの実装

おわりに

関連記事

VBAプログラミングスキルアップのための参考情報

VBAプログラミングのスキルアップ

プログラミングの一般教養

コメントを残す コメントをキャンセル

タカキベーカリーの小さい食パンとスライスチーズがジャストフィット！

【簡単エクセル/Excel VBA マクロ】フォルダ内のファイルをひとつだけ開く方法｜Dir関数/FileSystemObject｜ワンポイントテクニック #005

コメントを残すコメントをキャンセル