連想配列(Dictionary)をうまく使うと処理速度が劇的にあがる
連想配列(Dictionary)について書いていきます。
本記事は連想配列がどんなものかを解説しています。
コードと解説は次の記事で用意します。
まずは配列のおさらいです。配列を簡単に説明すると以下のような事が出来ます。
一次元配列、二次元配列でも基本的にできる事は同じです。違うのは情報の持ち方です。
- ワークシート等に用意された情報を取り込む
- 取り込んだ情報をメモリ上に格納
- 適切なタイミングに適切な場所に情報を出力する
取得した情報を取り込み記憶するので情報を探しやすくなります。
よって作業性は大幅に上がります。
結果配列を使うと作業速度は大幅に上がる事になります。
ただし数十万行×○○列という様に扱うデータ量が多くなってくると話は別です。
配列だけでは対応出来なくなってきます。
配列で対応出来ない大容量のデータでもなんとか簡単に扱えるようにしたいものです。
こんな時は連想配列を使うと高速に処理を行う事が出来ます。
連想配列とは何かを簡単に説明してから使い時、メリットやデメリットについて書いていきます。
これをふまえて次の記事でコードを紹介することにします。
前回記事を紹介
二次元配列を使って処理の高速化を図っています。コードも掲載しています。
この本で勉強しました
連想配列は参考になる書籍やインターネットの情報が少ないです。
私は連想配列をこの本で勉強しました。
連想配列の他にもWebスクレイピングなど「Excelでこんなことできるの？」という発見がある本です。
連想配列とは
当たり前ですが連想配列は配列です。
もう少し詳しく話すとある特徴をもった配列です。ポイントは3つです。
- 配列の一種
キーとアイテムの関係で
情報をストック
- 素早く情報を取り出せる
Enjoy Excel
配列の一種
配列の一種です。情報を格納したり取り出す事が出来る箱が用意されると思ってください。
キーとアイテムの関係で情報をストック
キーとアイテムを1対1の関係でストックします。
裏を返すとキーに対して1つのアイテムしか持てません。
加えてキーはユニーク値です。重複データは排除されます。アイテムの重複はOKです。
キーが重複していない配列を用意できるのが連想配列最大のメリットです。
このリストは検索される側のデータとして非常に使いやすいです。
検索される側のデータを事例をもとに解説
例えば上の画像のリストから「Bさんのアイテムを探す」という作業を作ってみましょう。
キー列にはBさんは1人しか居ません。Bさんはすぐ特定出来ます。
Bさんが特定できているのでアイテムは隣の列にある赤い車をだと分かります。
コードにするには以下のような考え方をします。
検索値（今回はBさん）とキー列の情報が一致したらキーが持っているアイテムを取得する
このようなコードを書くと一定のテーブルから任意のデータを取り出す事が出来ます。
キーはユニーク値なので「特定される／値が無い」のどちらかです。
値があればアイテムを取り出す事が出来て値が無ければ結果が返ってこないという事になります。
ここまで説明すると気付いた方も居るでしょう。
VLOOKUP関数では第2引数で検索範囲を指定します。
第3引数で返して欲しい値の列数を指定します。
連想配列はアイテムは１つです。列を指定しなくても持っているアイテムが返ってくるという仕組みです。
検索される側のテーブルに最適という話をする為にVLOOKUP関数を使用しました。
実際に使う時はVLOOKUP関数に直接埋め込むという使い方はしないです。
どんな使い方をするかは次回記事で解説しています。
素早く情報を取り出す
情報まで到達するプロセスが少ないので結果早く情報を取り出す事が出来ます。
アルゴリズムの話になるので詳細は省略し出来るだけ簡単に説明してみます。
上の画像でEさんを認識する際配列だと配列内をループ等で捜索しEさんを見付ける必要があります。
代わって連想配列は非常に少ない手数で情報に到達します。
理由はキーを使って情報を探す為です。
連想配列内のキーは重複してないのでキーを探す事で楽にアイテムに到達できます。
- リストの中でキーは何処にいますか？
- キーが持っているアイテムを下さい
この様に少ない手順で欲しい情報に到達出来ます。
よって連想配列の要素が多くても検索値のキーが多くても検索時間にさほど影響を与えません。
とにかく処理が速いです。この事からも「検索される側のデータとして最適なリスト」となります。
検索される側のデータ量によっては連想配列を使うと配列よりも作業スピードを上げる事が出来ます。
よって多くのデータを扱う事が出来る様になります。
連想配列の使い時
結論から申し上げますと具体的に要件として挙げるのは難しいので事例で説明します。
「○○のような時に使う」というかたちで紹介しています。
事例紹介
本当は使用する際の要件を数値などで明確に示した方が良いのですが正直難しいです。
しかし○○だったら連想配列を使うと良いですよと言えるケースはあります。
- 「検索される側のデータが大量」
- 配列でコードを組んでみて「遅いな」と感じた
こんな時は連想配列でコードを組み直してみましょう。
慣れると「このボリュームの仕事は連想配列でやらないと無理だな」という見立てが出来る様になります。
配列と連想配列を比較
あるデータを使って配列と連想配列を比較してみました。
作業スピードにかなり差がある事が分かります。
あるデータ量の仕事に対して配列でコードを組むと8秒でした。
このあと対象のデータ量を10倍にして連想配列でコードを組んだところ2秒で作業完了しました。
「エクセルマクロは遅い」と言われたり聞いたりするのですが決してそんなことはないです。
以下事例を読んで確認してみてください。
データシート
ボタンが用意されている最初の画像は検索値が1万件並んでいます。
2枚目の画像はDB用のデータです。同じく1万件×7列のデータです。
最初の画像のC列にDBのB列の値を持ってくるという作業をやってみます。
2枚目の画像のDBのB列をコピーして最初の画像のC列にペーストしたら終わるのですがそれはやめます。
検索した結果値を取りましたという理屈でコードを用意してみました。
氏名と番号が一緒になっているので少し違和感ありますが同姓同名の方が居たのでこうなってます。
本来は1つのセルに複数の情報を書くのであれば「番号_氏名」のように書くべきです。
今回このまま使わせていただきます。
コード
最初の画像にある「実行」ボタンに紐付いたコードです。まずは配列のみでコードを組みました。
Sub 配列LBoundUBoundで計測()
’今回も変数はあえて日本語を使っています
’気になる方はコピペした後に置換で書き換えて下さい
Dim 検索値 As Variant
Dim DB As Variant
Dim 検索結果 As Variant
Dim 最終行 As Long
Dim 最終列 As Long
Dim 検索 As Long
Dim 照合 As Long
'最初の画像はSheet1、2枚目の画像はSheet2です
'データの最終行や最終列は変数に取ることも考えましたが
'これは比較様に準備したコードでこれ以上使わない為絶対値で用意しました
検索値 = Range(Cells(6, 2), Cells(10006, 2))
DB = Sheet2.Range(Sheet2.Cells(7, 1), Sheet2.Cells(10007, 7))
検索結果 = Range(Cells(6, 3), Cells(10006, 3))
For 検索 = LBound(検索値) To UBound(検索値)
For 照合 = LBound(DB) To UBound(DB)
'単純にループで検索値とDBの値が同じかどうか判定しているだけ
If 検索値(検索, 1) = DB(照合, 1) Then
'コードとしてはDBの7列目まで持ってこれるが今回は最初の列のみ
検索結果(検索, 1) = DB(照合, 2)
' 検索結果(検索, 2) = DB(照合, 3)
' 検索結果(検索, 3) = DB(照合, 4)
' 検索結果(検索, 4) = DB(照合, 5)
' 検索結果(検索, 5) = DB(照合, 6)
' 検索結果(検索, 6) = DB(照合, 7)
Exit For
End If
Next
Next
'最後にシートへ貼付け
Range(Cells(6, 3), Cells(10006, 3)) = 検索結果
End Sub
配列
まずは配列で作業をしてみます。このコードを実行すると・・・私のPCだと8秒程度かかります。
DBとして配列に取り込んだ情報は1万行分のデータです。検索値も1万件。1万件×1万件の照合です。
配列でも8秒かかってしまいます。
単純なループですのでDBの上方に居るデータとの照合は速いです。
しかしDBの下方に居るデータとの照合はそこまで読みに行く時間も含まれます。
DBが10件ぐらいならループが少ないので検索結果が十万件を超えても1秒ぐらいで結果が出ます。
しかしDBが多い=ループが多くなる為配列でも処理速度でメリットが出しにくいです。
連想配列
ではこれを解決させてみましょう。
手段としては冒頭でも紹介させていただきました連想配列を使います。
誤解が無い様に書いておきますが連想配列単体では作業スピードを上げる事は出来ないです。
配列と絡めて使う事で作業スピードが格段に上がります。
せっかくなので先程の事例からデータ量も増やします。
10万件の検索値と10万行のDBで検索をします。
先程の配列だけの仕事に対して検索値もDBも10倍です。
コード実行後の作業時間は私のPCで2秒です。
「え？」ってなりませんか？これぐらい強力な機能です。
連想配列を使う事が出来れば大概のExcelデータを片付ける事が出来ます
連想配列のメリットとデメリット
コードの考え方、書き方は次回記事で紹介します。
まずは使い時やメリット、デメリットをみていきましょう。
メリット
主に2つです。
検索される側のリストに適している
１つめのメリットです。といいますか冒頭でも紹介している様に連想配列のそもそもの機能です。
キーの重複がないリストを作成する事が出来ます。
連想配列は配列というぐらいですので文字通り配列を用意することになります。
その際キーとアイテムという形で値をペアで持つことになります。
配列内でキーの重複は許されないので配列が出来ると同時にユニーク値がキーとなった配列が用意されます。
リストを使って説明
冒頭でも画像で紹介していますが今度はイメージしやすいようにエクセルのリストを使って説明します。
連想配列はこのようにキーとアイテムをセットにしてメモリ上に情報を持つことが出来ます。
アイテムは重複していますね。これは問題無いです。
1新谷 奈津子（キー）さんは兵庫県出身(アイテム)です。といった具合です。
これで1つの情報として配列に格納していきます。
画像ではアイテムが栃木県の方が何名か居ますよね。
よってキーは重複なし、アイテムは重複OKというかたちで情報をストックします。
キーが重複しないリストが作れるという事がメリットだと書きました。
「何でそれがメリットなの？」というのはこれで分かっていただけたかと思います。
処理速度の向上
２つめは使い時と被りますが作業スピードの向上です。
検索方法がシンプルになり手数が減るので仕事が減ります。
やる事が減るので結果結果処理速度が上がるというイメージです。
デメリット
デメリットも2つです。
キーとアイテムは1対1の関係
1つめのデメリットはキーに対してアイテムは1つしか持てないという事です。
複数のアイテムを持てないので複数の値を取り出したい時は工夫が必要です。
検索値に対応したアイテムを取得後連想配列を開放し再度別のアイテムを取得するというコードを書きます。
一見面倒な感じがしますね。
しかしこの状況を差し引いても扱うデータ量によっては連想配列の方が格段に作業速度が速いです。
扱いが難しい
2つめのデメリットは単純です。ちょっと難しいという事です。
連想配列だけで使う事は少なく配列と組み合わせて使う事が多いです。
配列をうまく使いこなせないと連想配列との連携も難しいです。
よって配列のスキルはマストです。
連想配列と一緒に配列を使う際は配列に格納する値も多くなることが想定されます。
セルへの値貼付けの際2次元配列の要素が一定量を超えた段階でTranseposeを使うと値がバグるという決まって起こるエラーも回避しないといけないのでそれなりのコードの作り込みが必要です。
まとめ
というわけで少し闇深い機能ですが慣れれば何とかなります。
裏を返せば使い方に慣れてしまえばメリットしかなくデメリットはないという事になります。
次回はコードの紹介をした後にブロック毎に細かく分けて解説してみようと思います。
