【Excel/VBA】ディクショナリって配列より素敵だと思う瞬間

突然ですが。ディクショナリが好きです。

キーを入れるだけで対応する値を取り出してくれるから。

配列だとキー値を探すのに最初からひとつずつチェックしないといけないのに比べてなんか簡単な気がするので。

ループせずとも一発であるかないかが判明するのが魅力。

ちなみにこの記事内ではディクショナリを使うために参照設定した場合の書き方をしています。

参照設定はVBAエディターで「ツール」→「参照設定」から、

Microsoft Scripting Runtime　にチェックを入れ、「OK」で設定します。

参照設定したほうが、ディクショナリを使うときにメソッドやプロパティの入力候補が出てくるのでお勧めです。

例えば、生徒IDと点数の一覧表があったとします。

これを配列、ディクショナリにいれて、そこから指定した生徒IDの点数を取り出す、というのをやってみようと思います。配列VSディクショナリです。

まずは配列を使った方法。

これがコード全文

Sub test配列()
　Dim arr As Variant
　With ActiveSheet.Range(“A1”).CurrentRegion
　　arr = .Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count).Value
　End With

　Dim i As Long
　For i = LBound(arr) To UBound(arr)
　　If arr(i, 1) = “B03” Then
　　　Debug.Print arr(i, 1) & “の点数は” & arr(i, 2)
　　　Exit Sub
　　End If
　Next i
End Sub

解説です。

配列に値を入れます。

Dim arr As Variant
With ActiveSheet.Range(“A1”).CurrentRegion
　arr = .Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count).Value
End With

セルA1から一続きのセル範囲について、一つ下にずらし、縦の長さを一つ小さくし、横の長さはそのまま、のセル範囲の値を配列に代入する。という書き方をしています。

分けて解説すると

セルA1の一続きのセル範囲について　With ActiveSheet.Range(“A1”).CurrentRegion　

一つ下にずらす　　　　　　　　　　.Offset(1, 0)

サイズ変更（セル範囲の縦の長さ-１、横の長さ(そのまま)）

　　　　　　　　　　　　　　　　　.Resize(.Rows.Count – 1, .Columns.Count)

　.Rows.Count でCurrentRegionの範囲の行数を取得しています。.Columns.Countは範囲の列数をカウントします。Rows、Columnsの前の . (ピリオド）を忘れずに！

本当に狙ったところを指定できているのだろうか。。。と不安な時は、デバックで一行ずつ実行するときに試しにselectしてみると指定範囲を確認できます
With ActiveSheet.Range(“A1”).CurrentRegion
　.Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count).Select

End With

話がそれました。

さて、生徒IDがB03の人の点数をイミディエイトウィンドウに出してみたいと思います。配列を最初からループして「B03」かどうかを配列の値をひとつずつ判定していき、ヒットしたらイミディエイトウインドウに出力し、ループを抜けます。

Dim i As Long
For i = LBound(arr) To UBound(arr)　　　　　’配列の最初から最後まで
　If arr(i, 1) = “B03” Then　　　　　　　　　’もし配列の値がB03だったら
　　Debug.Print arr(i, 1) & “の点数は” & arr(i, 2)　　　　’出力
　　Exit Sub　　　　　　　　　　　　　　　　　　　　’ループを抜ける
　End If
Next i

ディクショナリを使ったコード全文

Sub testDic()
　Dim dic As Dictionary
　Set dic = New Dictionary

　Dim i As Long
　With ActiveSheet
　　For i = 2 To .Range(“A1”).End(xlDown).Row
　　　dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
　　Next i
　End With

　Debug.Print “B03の点数は” & dic(“B03”)

End Sub

解説です。

ディクショナリを宣言し、Newで利用できるようにしています。

Dim dic As Dictionary
Set dic = New Dictionary

これは参照設定しているときの書き方です。

参照設定していない場合は以下の書き方にすればよいです。

Dim dic As Object
Set dic = CreateObject(“Scripting.Dictionary”)

2行目から一続きの最終行（.Range(“A1”).End(xlDown).Row　で取得）までループして、ディクショナリにキーと値を入れています。おい、ループしてんじゃねえか、と自分で突っ込みたくなりました。

ディクショナリにキーと値を追加するのは

dic.add キー,　値

と書きます。

　Dim i As Long
　With ActiveSheet
　　For i = 2 To .Range(“A1”).End(xlDown).Row
　　　dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
　　Next i
　End With

ちなみにディクショナリはキーに重複を認めませんので、キーに重複がある場合、上の書き方だけではエラーとなります。エラー対応の書き方は後述します。

取り出しは配列に比べて簡単に書けます。

dic(キー）で値を取り出しできます。一つずつのチェックは必要ありません。

　Debug.Print “B03の点数は” & dic(“B03”)

さて、

こんどは同じシートの14行目以降に指定した生徒IDの点数を出力していこうと思います。

配列を使ったコード全文

Sub test配列2()
　Dim arr As Variant
　With ActiveSheet.Range(“A1”).CurrentRegion
　　arr = .Offset(1, 0).Resize(.Rows.Count – 1, .Columns.Count).Value
　End With

　Dim vRow As Long, idx As Long
　With ActiveSheet
　　For vRow = 14 To 17
　　　For idx = LBound(arr) To UBound(arr)
　　　　If arr(idx, 1) = .Cells(vRow, 1).Value Then
　　　　　.Cells(vRow, 2).Value = arr(idx, 2)
　　　　　Exit For
　　　　End If
　　　Next idx
　　Next vRow
　End With
End Sub

配列に入れることろは最初と同じです。

そのあと、14行目以降、キー値をワークシートから取得し、対応する値を配列から探し、ワークシートに点数を出力します。ここが二重ループになり、ダルイな、と思ってしまうんですよね。

ディクショナリを使ったコードです。

Sub testDic2()
　Dim dic As Dictionary
　Set dic = New Dictionary

　Dim i As Long
　With ActiveSheet
　　For i = 2 To .Range(“A1”).End(xlDown).Row
　　　dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
　　Next i
　End With

　Dim vRow As Long
　With ActiveSheet
　　For vRow = 14 To 17
　　　If dic.Exists(.Cells(vRow, 1).Value) Then
　　　　.Cells(vRow, 2).Value = dic(.Cells(vRow, 1).Value)
　　　End If
　　Next vRow
　End With
End Sub

元データをディクショナリに入れるところは最初と同じです。

そのあと、14行目以降、キー値をワークシートから取得し、対応する値をディクショナリから取り出すところがループ一つで済むので、ここが好きです。

エラー対応で

If dic.Exists(.Cells(vRow, 1).Value) Then

と、dicにキー値があった場合、としていますが、確実にキー値がある場合はexistsで存在を調べることをせずに

.Cells(vRow, 2).Value = dic(.Cells(vRow, 1).Value)

だけでもだいじょうぶです。

dic.Exists(キー)　は　ディクショナリにキー値があるかどうかを調べることができる書き方です。あればTrue、なければFalseを返します。

ディクショナリに入れるとき、ディクショナリから出力するときにエラーで止まらないようにするためには、Ifで存在を確認してからディクショナリに入れる、または値を取得するという対応をよくやります。入れるときはキーが重複しているとエラーで止まり、出力するときはキー値がディクショナリに存在していないとエラーで止まってしまうので。

キー値が二回目に登場するときには無視する場合の書き方の一例は以下の通りです。

Dim i As Long
　With ActiveSheet
　　For i = 2 To .Range(“A1”).End(xlDown).Row
　　　If Not dic.Exists(.Cells(i, 1).Value) THen

dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
End If　

Next i
　End With

キー値が二回目に登場するときには同じキー値に値を足したりする書き方の一例は以下の通りです。

　Dim i As Long
　With ActiveSheet
　　For i = 2 To .Range(“A1”).End(xlDown).Row
　　　If Not dic.Exists(.Cells(i, 1).Value) Then
　　　　’初登場の場合はdicに追加

　　　　dic.Add .Cells(i, 1).Value, .Cells(i, 2).Value
　　　Else
　　　　’二回目以降の登場の場合は値を追加

　　　　dic(.Cells(i, 1).Value) = dic(.Cells(i, 1).Value) + .Cells(i, 2).Value
　　　End If
　　Next i
　End With

A04が重複している例で、足しこみコードを実行してみました。

16行目のA04の点数は21+5=26が出力されました。