【VBAリファレンス】第10回重複したデータを関数で探す 4/4：高度な抽出と実務的最適化の極意

ExcelVBA

2026.09.06

概要

Excel VBAを用いた業務自動化において、データの「重複チェック」は避けて通れない重要な課題です。本連載の最終回となる第4回では、これまで扱った基本的なCOUNTIF関数による判定を超え、VBAの「連想配列（Dictionaryオブジェクト）」を活用した高速・高精度な重複抽出テクニックを解説します。数万行を超える大規模データに対して、関数をセルに埋め込む手法は計算負荷を増大させますが、VBAでメモリ上に展開して処理することで、劇的なパフォーマンス向上を実現できます。本記事では、単なる判定にとどまらず、重複データの「リストアップ」と「自動ハイライト」までを網羅し、実務で即戦力となるプロフェッショナルの技術を伝授します。

詳細解説

Excel関数のみで重複を管理する場合、VBAのイベントや条件付き書式と組み合わせるのが一般的です。しかし、データ量が増えると「再計算」が繰り返され、シートの動きが重くなるという致命的な欠点があります。これを解決するのが、VBAの「Scripting.Dictionary」オブジェクトです。

Dictionaryオブジェクトは、キー（Key）と値（Item）のペアを保持する構造体です。キーは一意である必要があるため、既に存在するキーを追加しようとすると上書き、または無視される性質を利用します。これを用いて、データを一度メモリに読み込み、重複を判定するアルゴリズムを構築します。

具体的な処理フローは以下の通りです。
1. 対象範囲を配列として一気にメモリへ取り込む。
2. Dictionaryオブジェクトを生成し、ループで各セルを巡回。
3. キーが存在するかどうか（Existsメソッド）を判定し、存在すれば重複リストへ追加。
4. 最終的に重複データのみを抽出、または色付けを行う。

この手法の最大の利点は「計算量」です。COUNTIF関数をセルに埋め込むとO(n^2)の計算量になりがちですが、Dictionaryを用いた手法であればO(n)の線形時間で処理が完了します。VBAを単なる「マクロの記録」の延長としてではなく、アルゴリズムの最適化ツールとして捉えることで、業務効率は一段上の次元へと引き上げられます。

サンプルコード

以下のコードは、A列にあるデータの中から重複している項目を検索し、B列に「重複」と出力する実務的なサンプルです。


Sub ExtractDuplicates_Dictionary()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Variant
    Dim dict As Object
    Dim i As Long
    Dim val As Variant
    
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 範囲を配列として取得（高速化のため）
    dataRange = ws.Range("A1:A" & lastRow).Value
    
    ' Dictionaryの生成
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 画面更新を停止して高速化
    Application.ScreenUpdating = False
    
    ' 重複判定の実行
    For i = 1 To UBound(dataRange, 1)
        val = dataRange(i, 1)
        If Not IsEmpty(val) Then
            If dict.Exists(val) Then
                ' 重複している場合、B列にフラグを立てる
                ws.Cells(i, 2).Value = "重複"
                ws.Cells(i, 1).Interior.Color = RGB(255, 200, 200)
            Else
                ' 初回出現時は登録
                dict.Add val, i
            End If
        End If
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "重複チェックが完了しました。", vbInformation
End Sub

実務アドバイス

実務でこのコードを使用する際、考慮すべきポイントが3点あります。

1. 大文字・小文字の区別：
Dictionaryのキーはデフォルトで大文字・小文字を区別しません。もし区別が必要な場合は、VBAのStrComp関数を組み合わせる必要があります。実務上の顧客名や商品コードでは、全角・半角の揺れも問題になります。事前にStrConv関数を用いてデータを正規化（すべて半角にする、全角にするなど）してから判定にかけるのが、バグを減らす鉄則です。

2. メモリの解放：
大規模なデータを扱う場合、Dictionaryオブジェクトや大きな配列は使用後に「Set dict = Nothing」で明示的に解放し、メモリをクリーンに保つ習慣をつけましょう。

3. ユーザー体験の向上：
処理中に「処理中…」といったステータスバーへのメッセージ表示を行うことや、完了後のメッセージボックスで「何件の重複が見つかったか」をカウントして表示することで、エンドユーザーにとって信頼性の高いツールとなります。単に動けば良いというコードではなく、運用を想定した「気遣い」のあるコードが、ベテランの条件です。

まとめ

第4回にわたり、重複データを関数とVBAで探す手法を解説してきました。COUNTIF関数による基本的な判定から、Dictionaryオブジェクトを用いた高度な抽出まで、手法は多岐にわたります。重要なのは、「どの手法が今のデータ量と要件に対して最適か」を判断するエンジニアリングの視点です。

Excelは単なる表計算ソフトではなく、VBAと組み合わせることで強力なデータ処理プラットフォームへと変貌します。今回紹介したDictionaryを活用する手法は、今後あなたがどのような複雑なデータセットを扱うことになっても、必ず力強い武器となるはずです。本連載を通じて、VBAの柔軟性と計算速度の重要性を理解し、ぜひ自身の業務を自動化・効率化する一歩を踏み出してください。技術は使えば使うほど研ぎ澄まされます。次のステップとして、今度は抽出した重複データを別シートに自動転記し、レポートを作成するような一連のワークフロー構築に挑戦してみてはいかがでしょうか。