連想配列(Dictionary)をうまく使うと処理速度が劇的に上がる
連想配列(Dictionary)について書いていきます。
本記事は連想配列がどんなものかを解説しています。コードと解説は次の記事で用意します。
- ワークシート等に用意された情報を取り込む
- 取り込んだ情報をメモリ上に格納
- 適切なタイミングに適切な場所に情報を出力する
配列を用いることで取得した情報を取り込み記憶するので情報を探しやすくなります。
よって作業性が大幅に上がります。
ただし数十万行×○○列という様に扱うデータ量が多くなってくると話は別で配列だけでは対応出来なくなってきます。
配列で対応出来ない大容量のデータでもなんとか簡単に扱えるようにしたいものです。こんな時は連想配列を使います。
連想配列は通常の配列よりさらに高速に処理を行う事が出来ます。
連想配列とは何かを簡単に説明してから使い時、メリットやデメリットについて書いていきます。
これをふまえて次の記事でコードを紹介することにします。
前回記事を紹介
二次元配列を使って処理の高速化を図っています。コードも掲載しています。
この本で勉強しました
連想配列は参考になる書籍やインターネットの情報が少ないです。私は連想配列をこの本で勉強しました。
連想配列の他にもWebスクレイピングなど「Excelでこんなことできるの？」という発見がある本です。
関連記事
ExcelでPythonが使えるようになります。配列や連想配列はPythonでも使う事になります。
ここで勉強しておくのが得策です。
ExcelでPythonが使える2023年発表「Python in Excel」とは｜いつから×できる事×今後の展望
連想配列とは
当たり前ですが連想配列は配列です。もう少し詳しく話すとある特徴をもった配列です。ポイントは3つです。
- 配列の一種
キーとアイテムの関係で
情報をストック
- 素早く情報を取り出せる
Dictionaryと
呼ばれています
Enjoy Excel
1_配列の一種
配列の一種です。情報を格納したり取り出す事が出来る箱が用意されると思ってください。
配列が理解できていない方はこちらの記事をご覧ください。
なぜ配列を使うのか？【一次元配列の考え方】マクロでアルファベットのAからZZを用意
2_キーとアイテムの関係で情報をストック
キーとアイテムを1対1の関係でストックします。裏を返すとキーに対して1つのアイテムしか持てません。
加えてキーはユニーク値です。重複データは排除されます。アイテムの重複はOKです。
キーが重複していない配列を用意できるのが連想配列最大のメリットです。
このリストは検索される側のデータとして非常に使いやすいです。
検索される側のデータを事例をもとに解説
例えば上の画像のリストから「Bさんのアイテムを探す」という作業を作ってみましょう。
キー列にはBさんは1人しか居ません。Bさんはすぐ特定出来ます。
Bさんが特定できているのでアイテムは隣の列にある赤い車だと分かります。
コードにするには以下のような考え方をします。
検索値（今回はBさん）とキー列の情報が一致したらキーが持っているアイテムを取得する
このようなコードを書くと一定のテーブルから任意のデータを取り出す事が出来ます。
キーはユニーク値なので「特定される／値が無い」のどちらかです。
値があればアイテムを取り出す事が出来て値が無ければ結果が返ってこないという事になります。
ここまで説明すると気付いた方も居るでしょう。
VLOOKUP関数では第2引数で検索範囲を指定します。第3引数で返して欲しい値の列数を指定します。
連想配列はアイテムは１つです。列を指定しなくても持っているアイテムが返ってくるという仕組みです。
検索される側のテーブルに最適という話をする為にVLOOKUP関数を使用しました。
実際に使う時はVLOOKUP関数に直接埋め込むという使い方はしないです。
どんな使い方をするかは次回記事で解説しています。
3_素早く情報を取り出す
情報まで到達するプロセスが少ないので結果早く情報を取り出す事が出来ます。
アルゴリズムの話になるので詳細は省略し出来るだけ簡単に説明してみます。
上の画像でEさんを認識する際配列だと配列内をループ等で捜索しEさんを見付ける必要があります。
代わって連想配列は非常に少ない手数で情報に到達します。
理由はキーを使って情報を探す為です。
連想配列内のキーは重複してないのでキーを探す事で楽にアイテムに到達できます。
- リストの中でキーは何処にいますか？
- キーが持っているアイテムを下さい
この様に少ない手順で欲しい情報に到達出来ます。よって連想配列の要素が多くても検索時間にさほど影響を与えません。
とにかく処理が速いです。この事からも「検索される側のデータとして最適なリスト」となります。
検索される側のデータ量によっては連想配列を使うと配列よりも作業スピードを上げる事が出来ます。
よって多くのデータを扱う事が出来る様になります。
連想配列の使い時
結論から申し上げますと具体的に要件として挙げるのは難しいので事例で説明します。
「○○のような時に使う」というかたちで紹介しています。
参考：私が連想配列を使う時に考える2つのポイント
本当は使用する際の要件を数値などで明確に示した方が良いのですが正直難しいです。
しかし「○○だったら連想配列を使うと良いですよ」と言えるケースはあります。
- 検索される側のデータが大量
- 配列でコードを組んでみて「遅いな」と感じた
こんな時は連想配列でコードを組み直してみましょう。
慣れると「このボリュームの仕事は連想配列でやらないと無理だな」という見立てが出来る様になります。
事例：配列と連想配列を比較
サンプルとして用意したデータを使って配列と連想配列を比較してみました。
結論としては配列と連想配列では作業スピードに大きな差がある事が分かります。
まずは配列です。あるデータ量の仕事に対して配列でコードを組むと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新谷 奈津子（キー）さんは兵庫県出身(アイテム)です。といった具合でキーとアイテムをセットで考える様にします。
キーとアイテムを1つの情報として配列に格納しておき後で指定した要素だけ取り出せるようにします。
例えば「キーだけ」、「アイテムだけ」、「キーとアイテムの両方」という具合です。
画像ではアイテムが栃木県の方が何名か居ますよね。
よってキーは重複なし、アイテムは重複OKというかたちで情報をストックします。
キーが重複しないリストが作れるという事がメリットだと書きました。
「何でそれがメリットなの？」というのはこれで分かっていただけたかと思います。
2_処理速度の向上
２つめは使い時と被りますが作業スピードの向上です。検索方法がシンプルになり手数が減るので仕事が減ります。
やる事が減るので結果処理速度が上がるというイメージです。
デメリット
デメリットも2つです。
1_キーとアイテムは1対1の関係
1つめのデメリットはキーに対してアイテムは1つしか持てないという事です。
複数のアイテムを持てないので複数の値を取り出したい時は工夫が必要です。
検索値に対応したアイテムを取得後連想配列を開放し再度別のアイテムを取得するというコードを書きます。
一見面倒な感じがしますが扱うデータ量によっては連想配列の方が格段に作業速度が速いです。
2_扱いが難しい
2つめのデメリットは単純です。ちょっと難しいという事です。
連想配列だけで使う事は少なく配列と組み合わせて使う事が多いです。
配列をうまく使いこなせないと連想配列との連携も難しいです。よって配列のスキルはマストです。
また連想配列と一緒に配列を使う際は配列に格納する値も多くなることが想定されます。
セルへの値貼付けの際2次元配列の要素が一定量を超えた段階でTranseposeを使うと値がバグるという決まって起こるエラーも回避しないといけないのでそれなりのコードの作り込みが必要です。
まとめ
というわけで少し闇深い機能ですが慣れれば何とかなります。
裏を返せば使い方に慣れてしまえばメリットしかなくデメリットはないです。
次回はコードの紹介をした後にブロック毎に細かく分けて解説します。
記事は保護しています。閲覧希望の方は問い合わせフォームより連絡をお願いします。
お知らせ2023/12/01 以下リンク先記事の保護を解除しました。
参考：関連記事
本シリーズはこちらの記事とも連携しています。