Excel VBAでDictionaryの使い方をお探しですね。
広告
Excel VBAで重複削除・高速検索するならDictionary!実務で使える基本と注意点
Excel VBAで大量のデータを扱っていると、「重複のない一覧がほしい」「特定の値があるか素早く調べたい」「商品ごとの件数を集計したい」といった場面がよくありますよね。
こういった処理を、セルをひとつずつ見ていくループや、WorksheetFunction、Findだけで書いてしまうと、データが増えたときにどうしても遅くなりがちです。
そこで活躍するのが、**Dictionary（連想配列）**です。
Dictionaryを使えば、キーの重複を許さない性質を利用して重複削除が簡単にできますし、キーから値を直接取り出せるので高速検索にもぴったりです。
この記事では、Excel VBAでDictionaryを使った重複削除・高速検索の基本から、実務でつまずきやすいポイントまで、わかりやすく解説していきます。
1. Excel VBAのDictionaryって何？
Dictionaryは、VBAで「キー」と「値」をセットで管理できる便利なオブジェクトです。
普通の配列だと、0番目、1番目、2番目のようにインデックス番号で値を取り出しますよね。
でもDictionaryなら、「商品A」「顧客コード001」「メールアドレス」といった好きなキーを使って値を取り出せるんです。
たとえば、キーに商品名、値に販売数を入れておけば、商品名を指定するだけで販売数がすぐわかります。
この仕組みは「連想配列」とも呼ばれていて、表計算でいえば「検索用の見出し」と「対応する値」を一緒に持っているイメージですね。
Dictionaryが重複削除に向いているのは、**同じキーを二重に登録できない**からです。
すでに存在するキーをAddメソッドで追加しようとするとエラーになります。
でも逆にいえば、「まだ登録されていないものだけ追加する」と書けば、自然に重複のない一覧ができあがるわけです。
また、Existsメソッドを使えば、指定したキーが登録済みかどうかをTrueかFalseで簡単に判定できます。
この判定が簡単で速いので、DictionaryはVBAで大量データを扱うときの定番テクニックになっています。
Dictionaryを使う準備には、大きく2つの方法があります。
**1つ目は参照設定を使う方法**です。
VBEの「ツール」から「参照設定」を開いて、「Microsoft Scripting Runtime」にチェックを入れます。
この方法だと、`Dim dic As New Dictionary` のように宣言できて、入力候補も表示されるので初心者の方にはわかりやすいですよ。
**2つ目は、参照設定を使わずに** `CreateObject(“Scripting.Dictionary”)` で作成する方法です。
配布用のマクロや、いろんな環境で動かしたいブックでは後者が便利ですが、学習段階では入力ミスを防げる参照設定ありの書き方もおすすめです。
“`vb
‘参照設定あり
Dim dic As New Dictionary
‘参照設定なし
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)
“`
Dictionaryでよく使う機能は、**Add、Exists、Item、Keys、Items、Count**です。
– **Add**：キーと値を追加
– **Exists**：キーの存在確認
– **Item**：キーに対応する値を取得または更新
– **Keys**：登録されているすべてのキーを配列で返す
– **Items**：すべての値を配列で返す
– **Count**：登録件数を返す（セルへ出力するときの行数指定に便利）
まずは「キーは重複できない」「Existsで存在確認できる」「Keysで一覧を取り出せる」という3点を押さえておけば、重複削除と高速検索の基本が理解しやすくなります。
2. Dictionaryで重複削除する基本コード
Dictionaryで重複削除を行う考え方は、とてもシンプルです。
対象範囲のセルをひとつずつ見ていって、セルの値がDictionaryに登録されていなければ追加します。
すでに登録されている値だったら何もしません。
Dictionaryのキーは重複できないので、この処理が終わった時点でDictionaryのKeysには重複のないリストだけが残ります。
Excel 365ならUNIQUE関数でも似たことができますが、VBA内部で後続処理に使う場合や、集計・検索と組み合わせる場合はDictionaryのほうが柔軟に対応できます。
以下は、A列の商品名から重複のない商品リストを作って、C列へ出力するサンプルです。
ポイントは、キーにセルをそのまま渡すのではなく、必ず `cell.Value` を使うことです。
VBAではRangeオブジェクト自体をDictionaryのキーに登録することもできるため、`cell` と書いてしまうと、セルの中身ではなくセルそのものがキーになってしまう可能性があります。
同じ文字が入っていても別セルなら別オブジェクトとして扱われて、重複削除がうまくいかない原因になるので注意してください。
“`vb
Sub Dictionaryで重複削除する()
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)
Dim ws As Worksheet
Set ws = Worksheets(“データ”)
Dim rng As Range
Set rng = ws.Range(“A2:A100”)
Dim cell As Range
Dim key As String
For Each cell In rng.Cells
key = CStr(cell.Value)
If key <> “” Then
If dic.Exists(key) = False Then
dic.Add key, “”
End If
End If
Next cell
Worksheets(“結果”).Range(“C2”).Resize(dic.Count, 1).Value = _
WorksheetFunction.Transpose(dic.Keys)
End Sub
“`
このコードでは、Dictionaryの値には空文字 `””` を入れています。
重複のない一覧を作るだけなら、値そのものは使わないからです。
ただ、実務では値に元の行番号、件数、金額、顧客情報などを入れることで、単なる重複削除以上の処理に発展させることができます。
たとえば、キーを商品名、値を初回出現行にすれば「同じ商品が最初に出てきた行」を記録できますし、値を件数にすれば「商品ごとの出現回数」を集計できます。
セルへ出力するときは、`dic.Keys` が1次元配列を返す点に注意が必要です。
そのままセル範囲へ代入すると横方向に展開されることがあるので、縦方向に出したいときは `WorksheetFunction.Transpose` を使うのが一般的です。
また、出力先の範囲は `Resize(dic.Count, 1)` のようにDictionaryの登録件数に合わせて広げます。
こうすれば、重複削除後の件数が何件でも、必要な行数だけに結果を書き出せます。
3. Dictionaryで高速検索・件数集計する方法
Dictionaryの強みは、重複削除だけじゃありません。
むしろ実務では、「この値はもう存在するか」「この商品はいくつあるか」「このコードに対応する名称は何か」といった高速検索や集計で大きな効果を発揮します。
普通、シート上で何度もVLOOKUPやFindを繰り返すと、データ量が増えるほど処理時間が長くなりますよね。
でもDictionaryなら、キーをもとに対応する値へ直接アクセスできるので、大量データでも検索処理を短く書きやすく、速度面でも有利なんです。
たとえば、A列に商品名が並んでいて、商品ごとの件数を数えたい場合は、キーに商品名、値に件数を入れます。
初めて出てきた商品なら値を1として追加し、すでに存在する商品なら値を1増やします。
これはExcel関数でいうCOUNTIFに近い処理ですが、VBAの中で完結できるので、別のマクロ処理へつなげやすいのが利点です。
集計結果を配列やDictionaryのまま保持できるので、後で抽出、並べ替え、別シート出力などに活用できます。
“`vb
Sub Dictionaryで件数集計する()
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)
Dim rng As Range
Set rng = Worksheets(“データ”).Range(“A2:A100”)
Dim cell As Range
Dim key As String
For Each cell In rng.Cells
key = CStr(cell.Value)
If key <> “” Then
If dic.Exists(key) Then
dic(key) = dic(key) + 1
Else
dic.Add key, 1
End If
End If
Next cell
Dim outWs As Worksheet
Set outWs = Worksheets(“結果”)
outWs.Range(“A2”).Resize(dic.Count, 1).Value = _
WorksheetFunction.Transpose(dic.Keys)
outWs.Range(“B2”).Resize(dic.Count, 1).Value = _
WorksheetFunction.Transpose(dic.Items)
End Sub
“`
このコードでは、`dic(key)` という書き方でItemプロパティを省略しています。
`dic.Item(key)` とほぼ同じ意味で、登録済みのキーに対応する値を取得・更新できます。
ただし、Dictionaryには「存在しないキーをItemで参照すると、そのキーが作られる場合がある」という挙動があります。
なので、意図しないキーの追加を避けたい場面では、Existsで存在確認してから処理するほうが安全です。
短いコードを書けるのは魅力的ですが、業務用のマクロでは読みやすさと安全性を優先する判断も大切ですね。
高速検索の用途では、マスターデータをDictionaryへ読み込んでおいて、明細データのコードをキーにして名称や単価を取り出す使い方がよくあります。
たとえば、商品コードをキー、商品名を値としてDictionaryに登録しておけば、明細シートのコードから商品名を瞬時に取得できます。
VLOOKUPをセルに大量入力する方法と比べると、ワークシート上の再計算やセルアクセスの回数を減らせるので、処理速度の改善が期待できます。
特に数万行以上のデータを処理する場合は、Dictionaryに加えて配列へ一括読み込み・一括書き込みを組み合わせると、さらに高速化しやすくなります。
4. 実務で使うときの注意点と高速化のコツ
Dictionaryを実務で安定して使うには、いくつか押さえておきたい注意点があります。
まず重要なのが、**キーの型と表記ゆれ**です。
たとえば、数値の「1」と文字列の「1」、前後に空白がある「商品A 」と空白のない「商品A」は、意図しない別キーとして扱われることがあります。
重複削除や検索の精度を上げるには、キーにする前に `Trim` で前後の空白を取り除いたり、`CStr` で文字列に統一したりする処理が効果的です。
大文字小文字を区別したくない場合は、**CompareMode**の設定も検討しましょう。
“`vb
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)
‘大文字小文字を区別しない
dic.CompareMode = vbTextCompare
“`
CompareModeは、Dictionaryにキーを追加する前に設定する必要があります。
すでにキーを追加した後で変更しようとするとエラーになるので、Dictionaryを作成した直後に設定するのが安全です。
英字の商品コードやメールアドレスを扱う場合、大文字小文字を同じものとして扱うべきか、別のものとして扱うべきかは業務ルールによって違います。
この判断を曖昧にすると、重複削除の結果や検索結果が実際の業務意図とずれてしまう可能性があるので注意してください。
次に注意したいのが、**KeysやItemsの扱い**です。
`dic.Keys` や `dic.Items` は、Dictionaryの中にある配列をそのまま参照しているのではなく、呼び出すたびに配列を作って返すイメージです。
そのため、ループの中で何度も `dic.Keys(i)` のように書くと、毎回配列生成が発生して処理が遅くなることがあります。
必要な場合は一度変数に受け取ってから使うか、For EachでKeysを回す書き方にすると無駄が少なくなります。
“`vb
Dim arrKeys As Variant
arrKeys = dic.Keys
Dim i As Long
For i = LBound(arrKeys) To UBound(arrKeys)
Debug.Print arrKeys(i), dic(arrKeys(i))
Next i
“`
また、Dictionaryだけで何でも高速になるわけではありません。
VBAで遅くなりやすい大きな原因は、**セルをひとつずつ読み書きする処理**です。
Dictionaryによる検索や集計が速くても、シートへのアクセスを何万回も繰り返すと全体として遅くなってしまいます。
大量データを扱う場合は、対象範囲をVariant配列に一括で読み込んで、配列をループしながらDictionaryへ登録し、最後に結果をまとめてセルへ出力する設計が効果的です。
Dictionaryは高速化の中心になりますが、配列と組み合わせることで本来の力を発揮しやすくなります。
“`vb
Sub 配列とDictionaryで高速に重複削除する()
Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)
Dim data As Variant
data = Worksheets(“データ”).Range(“A2:A10000”).Value
Dim i As Long
Dim key As String
For i = 1 To UBound(data, 1)
key = Trim(CStr(data(i, 1)))
If key <> “” Then
If dic.Exists(key) = False Then
dic.Add key, “”
End If
End If
Next i
Worksheets(“結果”).Range(“A2”).Resize(dic.Count, 1).Value = _
WorksheetFunction.Transpose(dic.Keys)
End Sub
“`
実務でDictionaryを使うときは、**処理の目的を明確にする**とコードが読みやすくなります。
重複削除なら「キーだけを使う」、件数集計なら「キーに分類名、値に件数を入れる」、マスター検索なら「キーにコード、値に名称や単価を入れる」というように、役割を決めてから書くと迷いにくくなります。
さらに、同じ処理を何度も使う場合は、重複削除用のFunctionや集計用のSubとして切り出しておくと、別のマクロでも再利用できて便利です。
Dictionaryは最初こそ少し難しく感じるかもしれませんが、Exists、Add、Item、Keysの流れを覚えるだけでも、VBAでの重複削除・高速検索・集計処理を大きく改善できます。
ぜひ実務で活用してみてくださいね！
広告
