エクセルVBA｜連想配列(Dictionary)で高速な検索と集計をするマクロの作り方

エクセルVBAの連想配列とは

エクセルVBAの連想配列とはどういったものか

連想配列(Dictionaryオブジェクト)は、キーとアイテムのペアが代入できるオブジェクト

連想配列は、VBAで使用できる配列の一種で、要素を取り出すためにインデックスを使うのではなく、キーと呼ばれる値を使って要素にアクセスが可能な配列です。具体的には、キーを指定することで、対応する要素を取得したり、要素を追加、削除できます。

通常の配列では、要素は数値のインデックスでアクセスされます。しかし、連想配列では、キーと呼ばれる文字列や数値を使用して要素をアクセスします。このため、特定の要素を簡単に取り出せます。

連想配列は、要素の順序を保持しません。つまり、要素を追加した順序や並び順に格納されるわけではありません。しかし、キーを指定して値を取得ができるため、要素の順序に依存しないマクロに有効です。

また、連想配列は、動的なデータ構造であるため、宣言時に要素数を指定する必要がありません。そのため、要素数が動的に変化する場合に便利に使用できます。

連想配列は、データの検索や集計に適しており、VBA開発において非常に便利な配列の一つです。

VBAには、似たようなオブジェクトとして「コレクション(Collection)」と呼ばれるものがあります。
コレクション(Collection)は、数値や文字列以外にもワークシートや図形などのオブジェクトをまとめられるオブジェクトであり、WorkbooksコレクションやWorksheetsコレクションなど有名ですが、これらはエクセルに最初から用意されたコレクション(Collection)となります。
VBAでは、これら既存のコレクション(Collection)以外に独自にコレクション(Collection)を作成して利用することもできます。

コレクションの詳しい作りかたやメリット・利用方法などは以下の記事で解説しています。
以下のリンクよりご覧ください。

エクセルVBAの連想配列はどのようなマクロで使用できるか

連想配列(Dictionary)をつかったマスターデータの管理

会社などで扱うマスターデータ（顧客情報、商品情報など）は、データの種類が多く、頻繁に更新されることがあります。このようなデータを管理する場合、連想配列を使用することで、効率的にデータを管理できます。

連想配列(Dictionary)をつかったデータの検索や集計

大量のデータを処理する場合、検索や集計に時間がかかることがあります。しかし、連想配列を使用することで、指定したキーを利用でき、効率的にデータにアクセスしたり、取り出すことができます。また、連想配列は、複雑な条件に合致するデータを取り出すことも簡単にできます。

連想配列(Dictionary)をつかったフォームのコントロール管理

VBAを使用してユーザーフォームを作成する場合、フォームに配置されたコントロール（テキストボックス、ボタンなど）を管理する必要があります。このような場合、連想配列を使用することで、コントロールの名前をキーとしてアクセスすることで管理がしやすくなります。

ユーザーフォームとは、マクロの使用者に機能を利用しやすくするためのインターフェース(画面)です。VBAではユーザーフォームを作って高機能なマクロを誰でも利用しやすいものにできます。

ユーザーフォームの作りかたについては、こちらの記事で紹介しています。

エクセルVBAの連想配列の基本的な使いかた

連想配列(Dictionaryオブジェクト)のメソッドやプロパティ

連想配列(Dictionaryオブジェクト)のメソッド

メソッドの種類	機能の説明
Add	連想配列に新しい要素(キーとアイテム)を追加する
Exists	連想配列内に指定したキーが存在するかを判定する
Items	連想配列内のすべてのアイテムの配列を返す
Keys	連想配列内のすべてのキーの配列を返す
Remove	連想配列の要素(キーとアイテム)を削除する
RemoveAll	連想配列のすべての要素(キーとアイテム)を削除する

連想配列(Dictionaryオブジェクト)のプロパティ

プロパティの種類	機能の説明
CompareMode	連想配列内で文字列キーを比較するために比較モードを設定するか返します。
Count	連想配列内のキーとアイテムのペアの数を返します。
Item	連想配列内の指定したキーの項目を設定または返します。
Key	連想配列内のキーを設定します。

エクセルVBAの連想配列の宣言方法

エクセルVBAで連想配列(Dictionaryオブジェクト)をつかうための宣言ですが、事前の参照設定をしている場合と、そうでない場合で宣言方法が異なります。

まずは事前の設定をしていない状態からの宣言方法を紹介します。

VBEで連想配列(Dictionaryオブジェクト)の参照設定をしていない場合

連想配列である辞書(Dictionary)オブジェクトを宣言するコードは以下のとおり

Sub 連想配列マクロ()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")

End Sub

他の変数や、配列と同じようにDimをつかって変数・配列名をオブジェクト型で宣言します。2行目では、宣言したオブジェクトを辞書型にするコードを書きます。なお、サンプルのコードでは、『 mydic 』という名前の辞書にしています。

それでは、辞書(Dictionary)が作られているかをローカルウィンドウで確認してみましょう。

画像の右側部分に”型”が表示されていますが、そちらを確認すると、『 mydic 』という名前のDictionaryオブジェクトがつくられたことが確認できました。

現時点ではDictionaryオブジェクトの箱を用意したにすぎないため、要素(キー・アイテム)は何も代入されていません。

なお、本記事の次項で参照設定の方法を紹介しますが、それ以外の部分においては、原則として参照設定をおこなわない方法でコードを書いています。

あらかじめご了承ください。

VBEで連想配列(Dictionaryオブジェクト)の参照設定をする場合

こちらの方法で連想配列(Dictionaryオブジェクト)をつかう場合は、事前に以下の画像の設定をしておきます。

上記の画像の設定が完了したら、連想配列(Dictionaryオブジェクト)の宣言コードを書いていきます。

Sub 連想配列マクロ()

    Dim mydic As Dictionary
    Set mydic = New Dictionary

End Sub

参照設定を行わなかった場合とちがって、配列の宣言コードのデータ型にDictionaryを指定することで連想配列(Dictionaryオブジェクト)が作られます。

エクセルVBAの連想配列に要素を追加する方法

連想配列(Dictionaryオブジェクト)に要素を追加する場合、Addメソッドをつかいます。

連想配列(Dictionaryオブジェクト).Add key, item

Sub 連想配列マクロ_追加()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add "中国", "北京"
    mydic.Add "インド", "ニューデリー"
    mydic.Add "韓国", "ソウル"
    
End Sub

上記のコードは、連想配列(Dictionaryオブジェクト)に国名をキー(Key)として、首都の名前を値(Item)として3つのペアを要素として代入しています。

連想配列(Dictionaryオブジェクト)の特徴として、キー(Key)が重複するものを追加しようとするとエラーが発生しますのでおぼえておきましょう。また、ここでは詳しく触れませんが、このキー(Key)の重複ができない仕様をつかって、重複しないリストをつくる場合に利用するテクニックもあります。

エクセルVBAの連想配列から要素を取得する方法

連想配列(Dictionaryオブジェクト)の要素を取得する場合、キー(key)を指定して配列要素の値(Item)を取得します。

Sub 連想配列マクロ_追加()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add "中国", "北京"
    mydic.Add "インド", "ニューデリー"
    mydic.Add "韓国", "ソウル"

    '連想配列(Dictionaryオブジェクト)名とキー(key)を指定して値を取得する
    MsgBox mydic("インド") 'or MsgBox mydic.item("インド")でも可

End Sub

2つの着色したコード部分のいずれも実行が可能で同じ結果です。
連想配列(Dictionaryオブジェクト)内のキー(Key)を指定して、ペアになっている値(Item)の”ニューデリー”がメッセージボックスに表示されます。

こんどは逆に連想配列(Dictionaryオブジェクト)から、キー(Key)を取り出してみます。
キー(Key)を取り出すには、値(Item)を取り出すときの書きかたとは違い、キー(Key)を指定するのではなく、連想配列(Dictionaryオブジェクト)のインデックスを指定して取り出すことができます。

Sub 連想配列マクロ()

    Dim mydic As Dictionary
    Set mydic = New Dictionary

   '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add "中国", "北京"
    mydic.Add "インド", "ニューデリー"
    mydic.Add "韓国", "ソウル"

    '連想配列(Dictionaryオブジェクト)名とインデックスを指定してキーを取得する
    MsgBox mydic.keys()(1)

End Sub

連想配列(Dictionaryオブジェクト)はインデックスが0からはじまるため、1番に登録されているキー(Key)である”インド”がメッセージボックスに表示されます。

エクセルVBAの連想配列の要素を削除する方法

連想配列(Dictionaryオブジェクト)のRemoveメソッドで要素を個別に削除する

連想配列(Dictionaryオブジェクト)の要素を個別に削除するときは、Removeメソッドをつかいます。

連想配列(Dictionaryオブジェクト).Remove key

Sub 連想配列マクロ_削除()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add ("中国"), ("北京")
    mydic.Add ("インド"), ("ニューデリー")
    mydic.Add ("韓国"), ("ソウル")
    
    'Removeメソッドのkeyを指定して削除する
    mydic.Remove "インド"
        
End Sub

連想配列(Dictionaryオブジェクト)のRemoveメソッドで削除したいキー(Key)を指定することで要素の削除ができます。

または、keysメソッドをつかって、キー(Key)を一度別の配列に代入することでインデックスの指定で連想配列(Dictionaryオブジェクト)の要素を個別に削除する方法もあります。

Sub 連想配列マクロ_削除2()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add "中国", "北京"
    mydic.Add "インド", "ニューデリー"
    mydic.Add "韓国", "ソウル"
    
    'keysメソッドをつかって配列を格納するための変数を宣言する
    Dim keys_list As Variant
    
    'keysメソッドをつかって配列にキーの一覧を代入する
    keys_list = mydic.keys
        
    'Removeメソッドの配列のインデックスを指定して削除する
    mydic.Remove keys_list(1)
        
End Sub

まずは、キーのリストを代入するための変数(配列)をバリアント型で宣言をします。
サンプルコードではkeys_listという名前にしています。そのあとで、連想配列(Dictionaryオブジェクト)のKeysメソッドをつかって、キーの一覧をkeys_listに代入します。

これによって、連想配列(Dictionaryオブジェクト)のキーリストが一次元配列keys_listに代入されましたので、さいごにRemoveメソッドで配列のインデックスを指定することで、配列keys_listのインデックスが1番であるキー(“インド”)が指定されます。

連想配列(Dictionaryオブジェクト)のRemoveAllメソッドで全ての要素を削除する

連想配列(Dictionaryオブジェクト)の要素を全部削除するときは、RemoveAllメソッドをつかいます。

連想配列(Dictionaryオブジェクト).RemoveAll

Sub 連想配列マクロ_全削除()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add ("中国"), ("北京")
    mydic.Add ("インド"), ("ニューデリー")
    mydic.Add ("韓国"), ("ソウル")
    
    'Removeメソッドのkeyを指定して削除する
    mydic.RemoveAll
        
End Sub

連想配列(Dictionaryオブジェクト)の要素を全て削除するときは、RemoveAllメソッドをつかいます。これにより連想配列(Dictionaryオブジェクト)の要素は全て削除され、初期化されます。

エクセルVBAの連想配列(Dictionaryオブジェクト)をつかったマクロ応用例

ここでは、連想配列(Dictionaryオブジェクト)をつかった応用例を2つ紹介します。紹介する応用例は「データ検索」と「データ集計」です。

エクセルVBAの連想配列をつかったデータの検索

連想配列(Dictionaryオブジェクト)に、既に存在するキーであるかを判定するマクロのサンプルです。Existsメソッドをつかうことで、連想配列(Dictionaryオブジェクト)内を検索し、検索値であるキーが既に存在するかの判定ができます。

連想配列(Dictionaryオブジェクト).Exists(key) as Boolean

Sub 連想配列マクロ_データの検索()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
    '国名をkey(キー)に、首都をitem(値)にして連想配列に追加する
    mydic.Add ("中国"), ("北京")
    mydic.Add ("インド"), ("ニューデリー")
    mydic.Add ("韓国"), ("ソウル")
    
    If mydic.Exists("インド") Then
        Debug.Print mydic("インド")
    Else
        Debug.Print "インドは存在しません！"
    End If
 
End Sub

If文とExistsメソッドの組み合わせで、連想配列(Dictionaryオブジェクト)内に”インド”というキーが存在するかを判定しています。Existsメソッドの戻り値は、True または Falseでかえってくるので、キーが存在した場合は、Trueを、逆に存在しない場合は、Falseの判定結果がかえります。

なお、こちらのサンプルコードでは、”インド”のキーが存在しているため、Existsメソッドの判定結果では、Trueがかえってきます。そのため、Debug.Print mydic(“インド”)の処理が実行され、値(Item)の“ニューデリー”がイミディエイトウィンドウに表示されます。

連想配列のデータ検索をつかってセル範囲の値を代入するマクロ

前項では、Existsメソッドを利用して、連想配列(Dictionaryオブジェクト)に既に存在するキー(Key)かを判定し、存在している場合は、指定したキー(Key)とペアになっている値(Item)を表示する。といったシンプルなマクロでした。

Existsメソッドをつかって、もう少し実践的なマクロを紹介したいと思います。

ここで紹介するのは、セルの範囲に入力されたキー(Key)と値(Item)を連想配列(Dictionaryオブジェクト)に代入するマクロです。

具体的には、以下のデータを連想配列(Dictionaryオブジェクト)に代入していく処理ですが、見ておわかりのとおり、5行目と22行目は重複して”インド”と”ニューデリー”が記入されていますので、22行目のデータは連想配列(Dictionaryオブジェクト)に代入されない結果が望ましいです。

Existsメソッドで判定した結果、重複のないキーであった場合、セル範囲のA列を連想配列(Dictionaryオブジェクト)にキー(Key)として、セル範囲のB列を値(Item)を代入していくマクロです。

Sub 連想配列マクロ_データの検索とセル範囲の値を登録()

    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")
    
'Withステートメントでシートまでの記述を省略できるようにする
    With ThisWorkbook.Worksheets("国と首都名一覧")
    
'カウンタ変数であるiを宣言する
        Dim i As Long

'検索するキーを代入する変数tKeyを宣言する
        Dim tKey As String
    
'ForNext文で連想配列にキー(Key)と値(Item)を代入する
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
    
'代入するキー(Key)を変数tKeyに代入する
            tKey = .Cells(i, 1).value
    
'Existsメソッドをつかって既に存在するキーであるかを判定する
'存在している場合は何もせず次のループ処理に、存在しない場合は連想配列にキー(Key)と値(Item)を代入する
            If mydic.Exists(tKey) Then
            Else
'A列をキー(Key)として、B列を値(Item)として代入する
                mydic.Add .Cells(i, 1).value, .Cells(i, 2).value
            End If
        Next i
    End With
    Stop
    
End Sub

さきほどと同じで、If文とExistsメソッドの組み合わせで条件判定していますが、判定結果がTrueであったときは、既に代入されているキーとなるので、何も処理はしません。
一方、Falseであったときは、セルに記入された値を連想配列(Dictionaryオブジェクト)に代入する処理をしていきます。
そして、この処理をワークシート上の最終行まで繰り返すようにしたいので、ForNext文をつかっています。

以下の画像で向かって右側部分のローカルウィンドウで、連想配列(Dictionaryオブジェクト)であるmydicにセル範囲内に入力されていた値が、キー(key)と値(Item)として代入することができました。

エクセルVBAの連想配列をつかったデータの集計

エクセルVBAの連想配列の応用例の2つ目として紹介したいのは、連想配列(Dictionaryオブジェクト)をつかったデータの集計です。具体的には、キー(Key)をもとにして配列内に同じものがどれだけ存在するかを集計するマクロです。

Sub 連想配列マクロ_データの集計()

'サンプル配列データを宣言する
    Dim data As Variant
    data = Array("中国", "インド", "韓国", "中国", "インド", "中国")

'連想配列(Dictionaryオブジェクト)を宣言する
    Dim mydic As Object
    Set mydic = CreateObject("Scripting.Dictionary")

'繰り返し処理用のカウンタ変数を宣言する
Dim i As Integer

'ForNext文をつかって配列であるdataの要素の数だけ繰り返す
For i = 0 To UBound(data)
'Existsメソッドをつかって連想配列(Dictionaryオブジェクト)内を検索する
    If mydic.Exists(data(i)) Then
'Existsメソッドの判定結果がTrueなら、値(Item)に変数i＋1を代入する
        mydic(data(i)) = mydic(data(i)) + 1
    Else
'Existsメソッドの判定結果がFalseなら、値(Item)に変数iを代入する
        mydic.Add data(i), 1
    End If
Next i

'要素代入分の変数を宣言する(Variant型か、Object型)
Dim key As Variant

'ForEacht文をつかって連想配列(Dictionaryオブジェクト)であるmydicの要素の数だけ繰り返す
For Each key In mydic.Keys
'イミディエイトウィンドウにmydicのキー(Key)と値(Item)を出力する
    Debug.Print key & ":" & mydic(key)
Next key

End Sub

まずはこのマクロでつかうためのデータですが、バリアント型の変数dataを宣言しています。そしてdataの要素として、Array関数で3種類の国の名前(重複しているので要素数は全部で6個です)を代入することで配列にします。

次に、連想配列(Dictionaryオブジェクト)としてmydicを宣言します。つまりこの時点で、要素が6つ代入された一次元配列のdataと、空の連想配列(Dictionaryオブジェクト)のmydicがある状態です。

では、ここからmydicにキー(Key)と値(Item)を代入していくわけですが、ここでは配列であるdataの要素インデックスの上限値を返すUBound関数を指定しています。

配列dataの要素インデックスは0～5が割り振られているため、上限値は5です。つまり、変数iが0から5までの6回を繰り返すことになるわけです。

繰り返し回数についてわかったところで、話を処理の内容に移したいと思いますが、まずはExistsメソッドでの判定をおこないます。詳しく説明すると繰り返し処理のなかでは、まず”中国”がmydicに存在するかを判定します。

当然ですが、この時点でmydicには、なにも代入されていないため、Existsメソッドでの判定結果はFalseです。
この判定結果をうけて、If文はElseルートになり、mydicにキー(Key)として”中国”、値(Item)として、数値の1を代入する処理を実行します。

上記の処理をくり返していきますが、3回目の完了時点での連想配列(Dictionaryオブジェクト)であるmydicは、以下の状態となります。

※繰り返し処理を3回実行した時点でのmydicの内容

キー(Key)	値(Item)
中国	1
インド	1
韓国	1

引き続き4回目の繰り返し処理で、はじめてキー(Key)の重複が発生するため、If文の処理でTrueルートの処理を実行します。Trueのルートで実行する処理は、同じキー(Key)である要素の値(Item)の数値に＋1をします。

もっと具体的にいえば、くり返し4回目のExistsメソッドで判定する文字列は”中国”ですので、既にmydicに存在するキーと判定され、前の処理で入力された値(Item)である1に＋1をする処理が実行されます。
結果、中国のキー(Key)とペアとなる値(Item)に代入された1に＋1が実行されて2へと変化します。

繰り返し(ループ)のさいごまで実行した時点の連想配列(Dictionaryオブジェクト)mydicです。

※繰り返し処理をさいご(6回)実行した時点でのmydicの内容

キー(Key)	値(Item)
中国	3
インド	2
韓国	1

上の表でおわかりのとおり、配列であるdataの各要素が、どれだけの数があるかの集計することができました。

サンプルコードのForEach文以降は、この結果をDebug.Printをつかってイミディエイトウィンドウに表示するためのものですので、ここでの説明は割愛します。

実行結果は以下のとおりです。

イミディエイトウィンドウに配列dataの集計結果が出力されました。このように連想配列(Dictionaryオブジェクト)をつかって、配列の要素を集計するといった使い方もできます。

エクセルVBAで連想配列をつかうメリットと注意点

エクセルVBAの連想配列をつかうメリット

本記事では、これまでに連想配列の宣言から基本のつかいかたと応用例も紹介してきました。本項では連想配列(Dictionaryオブジェクト)をつかうことのメリットをいくつか紹介していきます。

キー(Key)を使用した高速な要素の検索

通常の配列では、要素を検索するために線形探索を行う必要があります。しかし、連想配列(Dictionaryオブジェクト)ではキー(Key)を使用して要素を直接検索するため、検索速度が大幅に向上します。

柔軟なデータ構造の作成

連想配列(Dictionaryオブジェクト)は、通常の配列とは異なり、インデックス番号を指定する必要がありません。そのため、要素の順序を変更したり、追加・削除したりする場合に、通常の配列よりも柔軟に対応できます。

読みやすいコードの実現

連想配列(Dictionaryオブジェクト)は、要素に意味のあるキー(Key)を付けます。そのため、キー(Key)とペアとなっている要素を取得するコードが読みやすくなります。また、変数名としてもキー(Key)を使用できるため、コードの可読性が向上します。

メモリの効率的な使用

連想配列(Dictionaryオブジェクト)は、要素を格納するために必要なメモリ量を最小限に抑えられます。例えば、ある要素の値が空の場合、その要素を格納するために必要なメモリは、通常の配列よりも少なくなります。

エクセルVBAの連想配列をつかうときの注意点

連想配列(Dictionaryオブジェクト)をつかう上での注意点については以下のようなものがあります。

キー(Key)の重複に注意する

連想配列(Dictionaryオブジェクト)には、同じキー(Key)を持つ要素を複数格納はできません。そのため、キー(Key)の重複については注意が必要です。同じキー(Key)を持つ要素が登録される場合には、上書きされるため、データの不整合が発生する可能性があります。

キー(Key)のデータ型に注意する

連想配列(Dictionaryオブジェクト)では、キー(Key)を使用して要素を検索するため、キー(Key)の型には注意が必要です。キー(Key)のデータ型が一致しない場合には、要素を検索できません。
例えば、文字列(String)型のキーで検索する場合に、整数(IntegerやLong)型のキーが登録されていると、検索結果は空になってしまいます。

要素の削除に注意する

連想配列(Dictionaryオブジェクト)から要素を削除する場合には、Removeメソッドを使用します。しかし、Removeメソッドを使用すると、要素が削除された後にキー(Key)が残ってしまうことがあります。そのため、キー(Key)が不要になった場合には、別途キー(Key)の削除処理を行う必要があります。

メモリ使用量に注意する

連想配列(Dictionaryオブジェクト)は、要素を格納するために必要なメモリ量を最小限に抑えられますが、要素の数が増えると、必要なメモリ量が大きくなるため、メモリ使用量に注意が必要です。大量の要素をあつかう場合には、メモリ使用量が増大することがあり、プログラムの動作が遅くなる場合があります。

「エクセルVBAで連想配列の特徴と書きかた」まとめ

ここまで、連想配列(Dictionaryオブジェクト)について紹介してきました。本項ではそれらをまとめていきたいと思います。

連想配列(Dictionaryオブジェクト)の強みと特徴

連想配列(Dictionaryオブジェクト)をつかうときの注意点

連想配列(Dictionaryオブジェクト)はデータの検索や集計などに幅広く活用されています。特に、複雑なデータ構造を扱う場合に、効率的な処理を実現できます。

エクセルVBAを用いたプログラミングにおいても、連想配列(Dictionaryオブジェクト)は非常に便利なツールであるため、マクロを開発する上で身につけて積極的につかいたいテクニックの一つだと言えます。

今回はここまで。