【Excel】VBAでDictionaryを使って高速な重複チェック・集計をする方法

Excelでのデータ処理で、重複データのチェックや集計作業に時間がかかっていませんか?大量のデータを扱う場合、標準機能だけでは処理に限界を感じることがあります。

VBAのDictionaryオブジェクトを使えば、これらの処理を劇的に高速化できます。

この記事では、VBAのDictionaryオブジェクトを活用して、Excelでの重複チェックと集計を効率的に行う方法を解説します。

Dictionaryオブジェクトの仕組みとメリット

Dictionaryオブジェクトは、VBAでデータを効率的に管理するための強力なツールです。

これは、連想配列とも呼ばれ、データを「キー(Key)」と「値(Item)」のペアとして格納します。キーは一意である必要があり、このキーを使って値に素早くアクセスできます。

Dictionaryの最大のメリットは、その検索速度にあります。データ量が膨大になっても、キーを指定すればほぼ一定の速度で目的のデータを見つけ出せます。これは、Excelのシート上でデータを検索するよりもはるかに高速です。

特に、重複チェックやユニークな値のリスト作成、各項目の出現回数のカウントといった集計処理において、その真価を発揮します。

Dictionaryを使った重複チェックの実装手順

Dictionaryオブジェクトを使って重複チェックを行う基本的な流れは、以下のようになります。

1. Dictionaryオブジェクトの宣言と生成
   まず、Dictionaryオブジェクトを使用するために、変数に格納し、実際にオブジェクトを生成します。
2. データソースの準備
   重複をチェックしたいデータが含まれるExcelシートの範囲を特定します。
3. データソースのループ処理
   データソースの各セル(または各行)を順番に処理します。
4. Dictionaryへのキー追加と判定
   各データをDictionaryのキーとして追加しようと試みます。
5. 重複の検出
   キーが既にDictionaryに存在する場合、そのデータは重複していると判断できます。
6. 結果の出力
   重複しているデータ、またはユニークなデータのみを別の場所に書き出します。

このプロセスをVBAコードで記述することで、手作業では時間のかかる重複チェックを自動化し、高速に実行できます。

サンプルコード:重複チェック

以下は、A列にあるデータの中から重複をチェックし、重複していないデータのみをB列に出力するVBAコードの例です。

Sub CheckDuplicates()

    Dim ws As Worksheet
    Dim dict As Object ' Dictionaryオブジェクト
    Dim lastRow As Long
    Dim i As Long
    Dim data As Variant

    ' 使用するシートを設定
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 対象シート名に変更してください

    ' Dictionaryオブジェクトを生成
    Set dict = CreateObject("Scripting.Dictionary")

    ' 最終行を取得 (A列を基準)
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

    ' 結果出力用のB列をクリア
    ws.Range("B:B").ClearContents

    ' A列のデータをループ処理
    For i = 1 To lastRow
        data = ws.Cells(i, "A").Value

        ' データが空白でなければ処理
        If data <> "" Then
            ' Dictionaryにキーが存在するかチェック
            If dict.Exists(data) Then
                ' 既に存在する場合(重複) - 何もせず次のデータへ
                ' 必要であれば重複フラグなどを立てる処理を追加
            Else
                ' 存在しない場合(ユニーク) - Dictionaryに追加し、B列に出力
                dict.Add data, 1 ' 値はカウントなどに使うことも可能
                ws.Cells(i, "B").Value = data ' ユニークなデータをB列に出力
            End If
        End If
    Next i

    ' オブジェクトの解放
    Set dict = Nothing
    Set ws = Nothing

    MsgBox "重複チェックが完了しました。", vbInformation

End Sub

このコードでは、`CreateObject(“Scripting.Dictionary”)`でDictionaryオブジェクトを生成しています。`dict.Exists(data)`でキー(ここではA列のデータ)が既にDictionary内に存在するかどうかを確認しています。存在しない場合にのみ、`dict.Add data, 1`でDictionaryに追加し、同時にB列にそのデータを書き出しています。

Dictionaryを使った集計の実装手順

Dictionaryオブジェクトは、単なる重複チェックだけでなく、各項目の出現回数を数えるといった集計処理にも非常に有効です。

1. Dictionaryオブジェクトの宣言と生成
   重複チェックと同様に、Dictionaryオブジェクトを準備します。
2. データソースの準備
   集計対象のデータ範囲を特定します。
3. データソースのループ処理
   データソースの各セル(または各行)を順番に処理します。
4. Dictionaryへのキー追加と値の更新
   各データをDictionaryのキーとして追加します。もしキーが既に存在する場合は、対応する値(カウントなど)を更新します。
5. 集計結果の取得
   ループ処理後、Dictionaryに格納されているキーと値のペアから、集計結果を取り出します。
6. 結果の出力
   集計結果をExcelシート上の指定した場所に出力します。

この手順により、例えば「各商品が何回購入されたか」といった集計を高速に行うことができます。

サンプルコード:集計(出現回数カウント)

以下は、A列にあるデータ(例:商品名)の出現回数をカウントし、結果をC列(商品名)とD列(回数)に出力するVBAコードの例です。

Sub CountOccurrences()

    Dim ws As Worksheet
    Dim dict As Object ' Dictionaryオブジェクト
    Dim lastRow As Long
    Dim i As Long
    Dim data As Variant
    Dim itemCount As Long

    ' 使用するシートを設定
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 対象シート名に変更してください

    ' Dictionaryオブジェクトを生成
    Set dict = CreateObject("Scripting.Dictionary")

    ' 最終行を取得 (A列を基準)
    lastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row

    ' 結果出力用のC列とD列をクリア
    ws.Range("C:D").ClearContents

    ' A列のデータをループ処理
    For i = 1 To lastRow
        data = ws.Cells(i, "A").Value

        ' データが空白でなければ処理
        If data <> "" Then
            ' Dictionaryにキーが存在するかチェック
            If dict.Exists(data) Then
                ' 既に存在する場合 - カウントを1増やす
                itemCount = dict(data) ' 現在のカウントを取得
                dict(data) = itemCount + 1 ' カウントを更新
            Else
                ' 存在しない場合 - 新規追加し、カウントを1とする
                dict.Add data, 1 ' 初回はカウント1
            End If
        End If
    Next i

    ' 集計結果をシートに出力
    Dim key As Variant
    Dim rowNum As Long
    rowNum = 1 ' C列の開始行

    ' Dictionaryのキーをループして結果を出力
    For Each key In dict.Keys
        ws.Cells(rowNum, "C").Value = key ' キー(データ)を出力
        ws.Cells(rowNum, "D").Value = dict(key) ' 値(カウント)を出力
        rowNum = rowNum + 1
    Next key

    ' オブジェクトの解放
    Set dict = Nothing
    Set ws = Nothing

    MsgBox "集計が完了しました。", vbInformation

End Sub

このコードでは、`dict.Exists(data)`でキーの存在を確認し、存在する場合は`dict(data) = itemCount + 1`で値を更新しています。存在しない場合は`dict.Add data, 1`で新規追加し、初期値を1としています。ループ終了後、`dict.Keys`で全てのキーを取得し、それぞれのキーに対応する値(カウント)をC列とD列に出力しています。

Dictionaryオブジェクトの高度な使い方と注意点

Dictionaryオブジェクトは非常に便利ですが、いくつかの注意点や、さらに活用できる機能があります。

大文字・小文字の区別

デフォルトでは、Dictionaryオブジェクトはキーの大文字・小文字を区別します。つまり、「Apple」と「apple」は別々のキーとして扱われます。

もし大文字・小文字を区別せずに扱いたい場合は、Dictionaryオブジェクト生成時に`CompareMode`プロパティを設定する必要があります。

' 大文字・小文字を区別しない場合
Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare ' vbTextCompare を設定すると区別しない

`vbTextCompare`を設定することで、「Apple」と「apple」は同じキーとして扱われるようになります。

キーの重複エラーへの対処

`dict.Add`メソッドで、既に存在するキーを再度追加しようとするとエラーが発生します。

これを避けるために、通常は`If Not dict.Exists(data) Then`のように、追加する前にキーの存在を確認します。サンプルコードでもこの方法を採用しています。

もし、エラーを発生させずに処理を続けたい場合は、`On Error Resume Next`文を使用する方法もありますが、コードの可読性が低下するため、限定的な使用に留めるべきです。

値のデータ型

Dictionaryオブジェクトの値(Item)は、任意のデータ型を格納できます。数値、文字列、配列、さらには別のDictionaryオブジェクトやCollectionオブジェクトなども格納可能です。

例えば、集計で単なるカウントだけでなく、合計値や平均値なども同時に管理したい場合に、値として配列やカスタムクラスなどを格納することで、より複雑なデータ構造を扱えます。

パフォーマンスに関する注意点

Dictionaryオブジェクトは非常に高速ですが、それでも処理対象のデータが数百万件を超えるような極端なケースでは、メモリ使用量や処理時間に影響が出る可能性があります。

また、DictionaryオブジェクトはVBAの標準機能ではなく、COMコンポーネント(Microsoft Scripting Runtime)を利用しています。そのため、実行環境によっては、参照設定を追加する必要がある場合もあります(`CreateObject`を使用すれば通常は不要です)。

代替手段との比較

Excelでの重複チェックや集計には、Dictionary以外にもいくつかの方法があります。

例えば、Excelの標準機能である「重複の削除」や「COUNTIF関数」、「ピボットテーブル」などです。これらはGUI操作で完結するため、VBAに慣れていないユーザーにとっては手軽です。

しかし、これらの標準機能は、処理対象のデータ量が増えるとパフォーマンスが著しく低下することがあります。また、複雑な条件での重複チェックや、動的な集計処理を自動化したい場合には、VBAとDictionaryの組み合わせが圧倒的に有利になります。

Power Queryも強力なデータ整形・集計ツールですが、VBAはExcelファイル内での処理を完結させたい場合や、他のExcel操作と連携させたい場合に特に適しています。

まとめ

この記事では、VBAのDictionaryオブジェクトを使った高速な重複チェックと集計の方法を解説しました。

Dictionaryオブジェクトを活用することで、大量のデータに対しても、これまで以上に迅速かつ効率的に処理を実行できるようになります。

今回紹介したサンプルコードを参考に、ご自身の業務で発生する重複チェックや集計作業の自動化にぜひ挑戦してみてください。

さらに、Dictionaryの`CompareMode`プロパティや、値に配列などを格納する応用も検討することで、より高度なデータ処理が可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】