項目毎に理解度UPの解説付きでコードを紹介します
引き続き連想配列(Dictionary)の解説です。
配列との比較や使い時、メリット、デメリットについては前回記事を確認してください。
今回はコードの紹介とコードの解説をしていきます
前回記事
連想配列とは、メリットデメリット、使い時について紹介しています。
連想配列からキーを使ってアイテムを取り出す方法はこちら。
アイテムを取得する方法VBA×連想配列｜キーを使いアイテムを抽出｜２次元配列で同様の作業を構築し比較する
関連書籍
連想配列はこちらの書籍で勉強することができます。私はWebスクレイピングの為にこの書籍を購入しました。
ポチップ
こちらの書籍は電子書籍がおすすめです。
事例
コードを紹介し解説するために事例を1つ用意しました。
事例の詳細
前回記事と同じ事例を使います。検索値10万件、DB10万件×7列の情報を使います。
続いてやりたい事を説明します。簡単に言うとVLOOKUP関数と同じことをやろうとしています。
- シート名「検索値と出力範囲」のB列とシート名「検索範囲」のA列を照合します。
- 照合後シート名「検索範囲」のB列にあるフリガナをシート名「検索値と出力範囲」のC列に貼り付け
今回はこの作業を行うにあたり配列と連想配列を使うことにします。
作業環境を画像にしました
画像で作業の流れを紹介しておきます。ざっとで良いので見ておいてください。
配列は作業をイメージするのが難しくなりますが画像を見ておけば頭の中である程度イメージがふくらみます。
結果このあと紹介する事例の仕様やコード、解説が読みやすくなると思います。
ポイントは「最初と最後しかワークシートに触れない」ということです。
値を取得してしまえばアウトプットが用意できるまでは全て配列を使って作業をすることになります。
シート名「検索値と出力範囲」
「連想配列を使ってみる」という画像の中で紹介した要素に合わせて説明します。
- 配列A ・・・ 以下画像のB列
- 配列C ・・・ 以下画像のC列
シート名「検索範囲」
「連想配列を使ってみる」という画像の中で紹介した要素に合わせて説明します。
- 配列B ・・・ 以下画像のAからG列
- 連想配列 ・・・ 配列Bを再格納（重複確認を行いユニーク値のキー＆アイテムを格納）
コード
こちらがコードになります。最初の画像にある「実行ボタン」に紐付いています。
Sub Dic単数列()
'例のごとく出来るだけ変数は日本語を使っています。
'気になる方はコピペ後英語に置き換えてください。
'**********************************************************************
'1、変数の定義
Dim 最終行 As Long 'rng検索値の最終行を取得する為の変数
Dim 検索範囲の最終行 As Long '検索範囲の最終行を取得する為の変数
Dim 検索範囲の最終列 As Long '検索範囲の最終列を取得する為の変数
Dim ary検索値() As Variant '検索したい値を配列として取得する為の変数
Dim ary検索範囲() As Variant '検索される値を配列として取得する為の変数
Dim ary() As Variant 'Dictionaryから値を配列で一旦預かる為の変数
Dim rng出力範囲 As Range '出力される範囲を決めるための変数
Dim myDic As Object 'オブジェクトを格納する為の変数
Set myDic = CreateObject("Scripting.Dictionary") '参照設定をしなくても使える様に宣言
Dim i As Long '各所にあるループ用の変数
'**********************************************************************
'2、セルに居るデータの範囲の取得
'変数の取得とデーが無い時のエラー対応
最終行 = Sheets("検索値と出力範囲").Cells(Rows.count, 2).End(xlUp).Row
検索範囲の最終列 = Sheets("検索範囲").Cells(7, Columns.count).End(xlToLeft).Column
検索範囲の最終行 = Sheets("検索範囲").Cells(Rows.count, 1).End(xlUp).Row
If 最終行 <= 5 Or 検索範囲の最終行 <= 6 Then
MsgBox "検索値と検索範囲にデータを入力してから実行ボタンを押して下さい", vbExclamation, "データを見直しましょう"
Exit Sub
End If
'3、データ範囲の値を配列に格納
'検索値を配列にセット
With Sheets("検索値と出力範囲")
ary検索値 = .Range(.Cells(6, 2), .Cells(最終行, 2))
End With
'検索範囲を配列にセット
With Sheets("検索範囲")
ary検索範囲 = .Range(.Cells(7, 1), .Cells(検索範囲の最終行, 検索範囲の最終列))
End With
'4、キーの重複確認、Dictionaryに値を格納
For i = 1 To UBound(ary検索範囲)
'Dictionsryに検索範囲の値が居ない時は・・・？
If Not myDic.Exists(ary検索範囲(i, 1)) Then
'DictionsryのmyDicに情報追加 ・・・ add キー,アイテム
myDic.Add ary検索範囲(i, 1), ary検索範囲(i, 2)
End If
Next
'Redimで2次元配列を用意
ReDim ary(1 To UBound(ary検索値), 1 To 1)
'5、検索値とDictionaryの照合
'6、アイテムを配列に格納
'myDicの中にある値からary検索値の値と合致した行に居る要素のアイテムをaryに入れる
For i = 1 To UBound(ary検索値)
ary(i, 1) = myDic.Item(ary検索値(i, 1))
Next
'7、配列からセルへ貼付け
With Sheets("検索値と出力範囲")
Set rng出力範囲 = .Range(.Cells(6, 3), .Cells(最終行, 3))
End With
'出力範囲に配列aryを代入 直接 セル範囲 = ary でも良いと思う
rng出力範囲 = ary
MsgBox "並べ替え完了", vbInformation, "確認"
End Sub解説
以下のような順番で作事をしています。
- 1変数の定義
- 2セルに居るデータ範囲を取得
- 3データ範囲の値を配列に格納
- 4キーの重複確認、Dictionaryに値を格納
- 5検索値とDictionaryの照合
- 6アイテムを配列に格納
- 7配列からセルに貼りつけ
1_変数の定義
各種変数の定義です。
1つポイントがあります。連想配列は外部ライブラリになりますので参照設定が必要です。
私はコード内で設定するタイプですのでオブジェクト変数を用意してその中にDictionaryをセットします。
参考：参照設定を使う場合
各種変数の定義ゾーンにある14、15行目のコードを以下コードに書き換えてください。
- Dim myDic As Scripting.Dictionary
- Set myDic = New Scripting.Dictionary
続いて参照設定の紹介です。
VBEを開き上部のツールタブから参照設定を選択。
以下画像のようなダイアログボックスがアクティブになります。
Microsoft Scripting Runtime にレ点を付けましょう。
その後ダイアログボックス右上の「OKボタン」を押下します。
これで参照設定の完了です。
メリットは自動メンバー表示が出るようになるという事ですね。
最初のうちはすごく助かりますが慣れればどちらでも良い機能です。
私は状況によって参照設定を使う／使わないを切り替えています。
参照設定を使う時はそもそも参照設定とは・・・という説明が必要になります。
VBAに慣れている方との仕事や自分だけで使うコードの時は参照設定を使う事が多いです。
2_セルに居るデータの範囲を取得
配列に取り込む前にデータの範囲を確認し配列に格納する値を確定させるための処理です。
3_データ範囲の値を配列に格納
2番で確認した値を各種配列に格納します。
4_キーの重複確認、Dictionaryに値を格納
Dictionaryオブジェクトの中にExistsというメソッドが居ます。
このメソッドで連想配列内にキーの重複が無いか確認します。
重複していない値であればキー&アイテムのセットで配列に加えるという判別作業を行います。
実際に連想配列に値を格納する際はaddメソッドを使います。
これでキーとアイテムだけのスリムなリストが作成出来ます。
なぜ配列に取り込んだデータを連想配列として持ち直すのか
事例を使って説明してみます。
タイムラインの3番で配列に取り込んだ情報を見直しましょう。
DBの情報を取り込んだ配列は複数の列の情報を持っています。
実際に作業する時は使用する情報は少ないです。
行×列で照合した結果交点に居る値を取ってくることになる為です。
対象としている行、列以外はデータを使わない事になります。
例えば上の図の様に氏名と都道府県を使って値を探すという時は極端に言うと関わりの無い列は不要です。
この画像からすると一番左の氏名の列と都道府県の列だけあれば仕事は進められます。
連想配列は大きなデータからキーとアイテムだけ切り出します。
検索に耐えられる中で一番スリムな形でデータを用意してくれます。
加えてキーの重複も削除出来るので「検索される側のリスト」として最適な状態で値を用意する事が出来ます。
あくまでイメージですが連想配列に格納する値は以下の様になります。
キーとアイテムをイメージしてもらう為に用意しました。
ここでは氏名をキーにして都道府県をアイテムにしています。
重複確認を行ったうえで上記のような構造で連想配列に値を格納することになります。
これで「検索される側のデータ」として準備が整いました。
5番のコードに入る前にアイテムを格納する配列を用意します。
「ReDim」というコードで配列を定義しています。
変数の定義の後でコード内で配列を再定義する際に使うコードです。
これは動的配列という要素を勉強すると出てくるコードになります。
動的配列については今回はサラっと流します。
もう少し深堀りする機会を作りますのでその時に説明していきたいと思います。
5_検索値とDictionaryの照合
ここですね。連想配列のストロングポイントです。
検索値が連想配列内に居るのか確認する際に非常に手数を少なく検索する事ができます。
配列だとLBound、UBoundを使って配列の要素をループして値を探すのですが連想配列は違います。
探し方が配列とは比較になりません。
検索値が連想配列内に居るのか？居るなら何番目に居るのかを1発（1手）で出してきます
配列の後方でも前方でも関係ないです。1撃で仕留めてきます。
検索作業は1手で完了です。
LBound、UBoundでループ・・・とは比較にならないぐらい手数が減る事になります。
値を抽出してくるだけの事を考えれば検索値が10万件なら10万手で終わりという事です。
6_アイテムを配列に格納
5番の処理で抽出されたアイテムを貯めこむ配列を用意しておきその配列に値を渡します。
この配列に値を貯めてから最後にセルに貼り付けます。
5番と6番は一連のコードになっていますが説明を分けたかったので別の項目として書きました。
7_配列からセルへ貼付け
6番で値が格納された配列をセルに貼り付けます。これで作業終了となります。
用意した配列と同じ大きさの貼付け範囲を指示しないとエラーになりますのでご注意ください。
まとめ
ちょっと分かりにくいのですが実質弱点が無いので使わない手はないです。
是非覚えていただきご自身の仕事で使ってみてください。
連想配列はこんなこともできます。アイテムを集合として持てるのでより実務に近いことができるようになりますよ。
これで連想配列の記事は終了となります。
あとはコレクション、動的配列、2次元動的配列あたりを勉強してください。
実際は2次元動的配列を使う機会が一番多いです。使える様になればどんなデータが来ても対応できます。
連想配列まで勉強出来た方は自力で勉強出来ると思いますので是非チャレンジしてみてください。
参考：連想配列の前後に勉強すると効率が良い要素は以下記事で紹介しています。
勉強する順番を紹介マクロは何から勉強するのか｜学習をサポートするためのロードマップを作成
