なにぬねのーつ

No.17 VBAで連想配列 〜 Scripting.Dictionary
2007/04/16 EXCEL, VBA, 連想配列 scriptingdict.zip
VBAな方には連想配列というのはなじみのない言葉かもしれません。awkにはじまりPerlやJava、ノーツ(LotusScript)でもリストとして実装されているものです。通常の配列の場合は数字をインデックスとして値を格納しますが、連想配列の場合は文字列をインデックスとすることができます。箱を配列とすると値は中身、それぞれの箱に名札(ラベル)を付けることができるので値を取り出すときも感覚的にわかりやすいですよね。それ以外にもメリットがあります。通常の配列の場合あらかじめ領域を確保しておく必要がありますが、連想配列の場合は不要で配列が溢れることを気にする必要がないのです。

Perlでの記述例
$Result{'山田太郎'} = 95;
$Result{'鈴木次郎'} = 78;
$Result{'田中三郎'} = 83;
print $Result{'山田太郎'};

Dictionaryオブジェクト

さてVBAですが、VBA自体には連想配列の機能がありませんがその代わりDictionaryオブジェクトというものを利用できます。ヘルプを見ると以下のように書かれています。VBAのヘルプはわかりにくいですね。とにかく連想配列であることだけは間違いないようです。
Dictionary オブジェクトは、PERL の関連配列と同等です。任意の型のデータにできる項目は、配列に格納されます。項目は、重複しないキーで関連付けられます。キーは各項目を取得するのに使用され、通常、整数型か文字列型ですが、配列にはできません。

売上集計(クロス集計)を作成する

以下の入力データを使って売上集計(クロス集計)を作ってみましょう。これ自体はごくごく簡単なものでEXCELのピボットでものの数分で作成できるものですが、連想配列のサンプルとしては適当なのでこれを使用してみます。
(入力)売上明細シート



(出力)売上集計シート


以下がVBAで連想配列を使用したコーディングになります。たったこれだけ?っていうほどコーディングが短くてすみますね!
とにかくデータをすべて連想配列に読み込んでから集計、出力ということですからメモリは食いますが、通常の方法(Lookup系やマッチング等)より数倍速いと思います。詳細はサンプルを実際に動かしてみてください。
なお、架空の売上データ(1000行)はExcelでお仕事!さんに掲載されているVBAを元に作成しました。
(2008/8/10) 読者の方からのご指摘で.Cells()の後の.Valueは省略せず明示したほうがよいとのことで修正しました。少しぐぐってみると右辺は省略してもよいが、左辺や引数などの場合は省略しないほうが良いみたいです。Y.Mさん、ありがとうございました。現時点でサンプルは修正していませんのでご注意願います。

リファレンス

一応、Dictionaryオブジェクトのリファレンスを掲載しておきます。
Set obj = CreateObject("Scripting.Dictionary")
obj.Add "山田", 95
obj.Add "田中", 78
obj.Add "鈴木", 83
上記のコードを例に説明します。なおこの中で"項目"と表現しているのはキーに割り当てる値のことです。
プロパティ Count 連想配列に格納された項目の数を返します。
obj.Count
→結果は3
Item(key) キー(key)に関連付けられた項目を取り出します。
obj.Item("鈴木")
→結果は83
以下のように簡略化して記述することもできます。
obj("鈴木")
Item(key) = newitem キー(key)に新しい項目(newitem)を関連付けます。
obj.Item("鈴木") = 85
以下のように簡略化して記述することもできます。
obj("鈴木") = 85
Key(key) = newkey キー(key)を新しいキー(newkey)に置き換えます。
obj.Key("鈴木") = "近藤"
→"鈴木"に対する項目は無くなり、"近藤"がそれに取って代わります。
CompareMode vbBinaryCompare(0) … 大文字小文字を区別します。
vbTextCompare(1) … 大文字小文字を区別しません。
obj.CompareMode = vbBinaryCompare
メソッド Add(key, item) 未設定のキー(key)に項目(item)を関連付けます。(キーが存在する場合はエラー)
obj.Add("伊藤", 90)
Exists(key) 指定されたキー(key)が存在するかどうか論理値(True/False)を返します。
If obj.Exists("山田") Then
Items 連想配列の項目を(0から始まる)配列にして返します。
v = obj.Items
→これは95、78、83の配列になります
Keys 連想配列のキーを(0から始まる)配列にして返します。
v = obj.Keys
→これは"山田"、"田中"、"鈴木"の配列になります。
Remove(key) キー(key)と項目の対を削除します。(指定されたキーが存在しない場合はエラー)
obj.Remove("鈴木")
RemoveAll すべてのキーと項目を削除します。
obj.RemoveAll

関連情報:
No.9 EXCEL 〜 配列数式でクロス集計

使用条件・免責事項



[戻る]