【VBA】Dictionary（辞書）の使い道が一発で分かる記事｜ループ内SQLをやめて高速化する実務テク

ループ内SQLをやめて高速化する実務テク

うさちゃん

うさです🐰🌿
今回は、VBAでよくある「処理が遅い…」問題を、Dictionary（辞書）で解決する話です✨️

この記事のポイントはここ👇

ループの中でSQLを毎回発行していると、DBとの往復が行数分発生して、処理がかなり遅くなりやすいということ。

うさちゃん

1. ループの前で SQLを1回だけ実行
2. SQLの取得結果を Dictionaryに全部入れる（キー→値の形にする）
3. ループ中は Dictionary検索だけで値を取る

この形にすると、処理速度が体感で別物になる。

この記事では、いきなり難しいことはしないで、次の順で説明していくよ。

• まず「Dictionaryって何？」（キーで一発検索できる仕組み）
• 次に「よくある使い道3つ」（コード変換／重複集計／ループ内SQLの高速化）
• 最後に「本命：SQL結果をDictionaryに入れて、ループを高速化する実務サンプル」

読み終わるころには、「Dictionaryって怖い」じゃなくて、“遅い処理のボトルネックを外す道具”として使えるようになるはず。

ねこちゃん

ねこは全然わかんないので、読んでも「Dictionaryって怖い」ってなってます。

Dictionaryって何？（一言で）

VBAの Dictionary（辞書） は、
「キー（検索に使うID） → 値（取り出したいデータ）」を保存して、キーで一発検索できる仕組みです。

配列だと「何番目？」を探す。
Dictionaryだと「商品コード“12345”の情報ちょうだい」で終わる。

広告

Razer(レイザー) Cobra ゲーミングマウス 58g 軽量 コンパクト つかみ持ち/つまみ持ちにフィット 有線 第3世代 オプティカルマウススイッチ 没入感を高めるアンダーグロー Chroma ライティング 8500 DPI オプティカルセンサー Speedflex ケーブル コブラ 【日本正規代理店保証品】

58gの軽量&コンパクトなデザインでつかみ持ち/つまみ持ち時にフィットする有線ゲーミングマウス「Razer Cobra」の登場です。耐久性に優れたスイッチとアンダーグローグラデーションのChromaライティングを備え、小型ながらもパフォーマンスに優れたマウスで高精度と没入感を実現します。
【58gの軽量デザイン】ほとんどのマウスの持ち方に対応した軽量デザインのRazer Cobraは、高速かつ高精度のコントロールを可能にするだけでなく、長時間のゲームでも非常に快適に使用できます。
【第3世代 Razer オプティカルマウススイッチ】デバウンス遅延のない0.2msの高速動作、チャタリングを起こさない9,000万回のクリック寿命など、他を圧倒する信頼性とスピードを持っています。
【アンダーグローグラデーションのChromaライティング】1,680万色のカラーオプションと無限のライティング効果を備え、数百のChroma対応ゲームでマウスをダイナミックに反応させることで、優れた没入感が体験できます。
【高精度のセンサー調整】Razer 8500 DPI オプティカルセンサーを搭載。50 DPI単位で高精度の調整を行うことができ、プレイスタイルに合わせた完璧な設定が可能です。

Amazonで見る

最新の価格・在庫状況はAmazonでご確認ください。

Dictionaryの強い使い道（実務でよくある3つ）

1) コード変換（商品コード→商品名など）

マスタを辞書化して、明細の変換を高速化。

2) 重複集計（同じキーをまとめる）

商品コード別の数量集計、得意先別の件数集計など。

3) ループ内SQLをやめて高速化（本命）

明細1行ごとにSQL発行してると、DB往復が発生して激遅になります。
そこで、ループの前でSQLを1回だけ実行し、結果をDictionaryに全部入れておく。
あとはループ中はDictionary検索だけにすれば、桁違いに速くなります。

まずは超基本：Dictionaryの最小セット

実務でまず使うのはこの4つだけでOK。

• dic(key) = value：追加/更新（実務はこれが一番安全）
• dic.Exists(key)：存在チェック
• dic(key)：値の取得
• dic.Count：件数

使い道の例①：商品コード別に数量を集計（重複をまとめる）

（A列：商品コード / B列：数量）

Option Explicit

Sub SUB_SAMPLE_SUM_BY_CODE()

    Dim l_objDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String
    Dim l_LngQty As Long

    Set l_objDic = CreateObject("Scripting.Dictionary")

    With ActiveSheet
        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow
            l_StrCode = CStr(.Cells(l_LngRow, "A").Value)
            l_LngQty = CLng(.Cells(l_LngRow, "B").Value)

            If l_objDic.Exists(l_StrCode) Then
                l_objDic(l_StrCode) = CLng(l_objDic(l_StrCode)) + l_LngQty
            Else
                l_objDic(l_StrCode) = l_LngQty
            End If
        Next
    End With

    Dim l_VarKey As Variant
    For Each l_VarKey In l_objDic.Keys
        Debug.Print l_VarKey & " = " & l_objDic(l_VarKey)
    Next

End Sub

使い道の例②：マスタ変換（コード→名称）を高速化

商品マスタを辞書にして、明細ループで引くだけ。

Option Explicit

Private Function FNC_GET_SHOUHIN_DIC(ByVal p_ObjMstSheet As Worksheet) As Object

    Dim l_objDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String
    Dim l_StrName As String

    Set l_objDic = CreateObject("Scripting.Dictionary")

    With p_ObjMstSheet
        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow
            l_StrCode = CStr(.Cells(l_LngRow, "A").Value) ' 商品コード
            l_StrName = CStr(.Cells(l_LngRow, "B").Value) ' 商品名
            l_objDic(l_StrCode) = l_StrName              ' 追加/更新
        Next
    End With

    Set FNC_GET_SHOUHIN_DIC = l_objDic

End Function

広告

アキュビュー オアシス 【BC】8.8【PWR】-3.50 6枚入

コンタクトレンズは高度管理医療機器です。必ず事前に眼科医にご相談の上、検査・処方を受けてお求めください。 ご使用前に必ず添付文書をよく読み、取り扱い方法を守り、正しく使用してください。
販売名：アキュビュー オアシス / 承認番号：21800BZY10252000 / 登録商標 原産国：アイルランド共和国 / アメリカ合衆国
DIA（直径）:14.0mm 、BC（ベースカーブ）:8.8 / 8.4mm、PWR:-0.50 ～ -6.00（0.25ステップ）、-6.50 ～ -12.00（0.50ステップ）、+0.50 ～ +5.00（0.25ステップ）
2週間頻回交換コンタクトレンズ / 1箱6枚入り / 両目6週間分/酸素透過率147DK/L / 含水率38％
コンタクトレンズの乾きから自由になることを目指したアキュビュー オアシス。それを可能にしたのは、次世代素材「シリコーンハイドロゲル」*と、独自の技術「ハイドラクリアプラス・テクノロジー」*の融合。変わらないみずみずしさと目の健康への思いが実を結んだ、コンタクトレンズの新しいカタチ。 ※ レンズ素材名：セノフィルコンA ※ Johnson&Johnson社独自のテクノロジー名。

Amazonで見る

最新の価格・在庫状況はAmazonでご確認ください。

使い道の例③（本命）：ループ内SQLをやめてDictionaryで高速化する

ここが一番「効く」使い方。

NG例：ループの中でSQLを毎回発行（遅い）

明細が1000行なら、SQLも1000回。
DB往復が一番遅いので、これがボトルネックになりがち。

' ※概念例（この構造が遅い）
For l_LngRow = 2 To l_LngLastRow

    l_StrCode = CStr(.Cells(l_LngRow, "A").Value)

    ' ここで毎回SQL → 遅い（往復が積み上がる）
    l_StrSql = "SELECT 商品名 FROM 商品マスタ WHERE 商品コード = '" & l_StrCode & "'"
    ' Recordset.Open l_StrSql, ...
    ' .Cells(l_LngRow, "B").Value = rs!商品名

Next

※文字列連結でSQLを作る方法は、条件値の扱いによってはセキュリティ面でも注意が必要です。ここではDictionaryによる高速化の説明に絞っています。

OK例：ループの前にSQL結果をDictionaryへ読み込む（速い）

やり方はシンプル：

• ループ前に SQLを1回だけ発行
• 取得結果を dic(商品コード)=商品名 の形で全部入れる
• ループ中は dic(code) を引くだけ（DBアクセスしない）

① SQL結果をDictionary化する関数（ADO想定）

※ADO参照設定なしでも動きやすいように、CreateObjectで書いています。実務では環境に合わせて接続方法を調整してください。

Option Explicit

Private Function FNC_GET_SHOUHIN_FROM_DB_DIC(ByVal p_ObjCn As Object) As Object
On Error GoTo ERROR_HANDLER

    Dim l_objDic As Object
    Dim l_ObjRs As Object
    Dim l_StrSql As String
    Dim l_StrCode As String
    Dim l_StrName As String

    Set l_objDic = CreateObject("Scripting.Dictionary")

    ' 例：必要なマスタを一括取得（条件があるなら WHERE で絞る）
    l_StrSql = ""
    l_StrSql = l_StrSql & "SELECT 商品コード, 商品名 "
    l_StrSql = l_StrSql & "FROM 商品マスタ "

    Set l_ObjRs = CreateObject("ADODB.Recordset")
    l_ObjRs.Open l_StrSql, p_ObjCn

    Do While l_ObjRs.EOF = False
        l_StrCode = Format$(CLng(l_ObjRs.Fields(0).Value), "00000")
        If IsNull(l_ObjRs.Fields(1).Value) Then
            l_StrName = ""
        Else
            l_StrName = CStr(l_ObjRs.Fields(1).Value)
        End If

        l_objDic(l_StrCode) = l_StrName ' 追加/更新

        l_ObjRs.MoveNext
    Loop

    l_ObjRs.Close
    Set l_ObjRs = Nothing

    Set FNC_GET_SHOUHIN_FROM_DB_DIC = l_objDic
    Exit Function

ERROR_HANDLER:
    If Not l_ObjRs Is Nothing Then
        If l_ObjRs.State <> 0 Then l_ObjRs.Close
        Set l_ObjRs = Nothing
    End If

    Set FNC_GET_SHOUHIN_FROM_DB_DIC = Nothing
End Function

② 明細ループはDictionary検索だけ

Option Explicit

Sub SUB_SAMPLE_FAST_LOOKUP()

    On Error GoTo ERROR_HANDLER

    Dim l_ObjCn As Object
    Dim l_ObjDic As Object
    Dim l_LngLastRow As Long
    Dim l_LngRow As Long
    Dim l_StrCode As String

    ' DB接続は環境に合わせて設定する
    Set l_ObjCn = CreateObject("ADODB.Connection")

    l_ObjCn.ConnectionString = "接続文字列を設定"
    l_ObjCn.Open

    ' ★ ここがポイント：ループ前に辞書を作る
    Set l_ObjDic = FNC_GET_SHOUHIN_FROM_DB_DIC(l_ObjCn)

    If l_ObjDic Is Nothing Then
        MsgBox "商品マスタの取得に失敗しました。", vbExclamation
        GoTo EXIT_PROC
    End If

    With ThisWorkbook.Worksheets("明細")

        l_LngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For l_LngRow = 2 To l_LngLastRow

            l_StrCode = Format$(CLng(.Cells(l_LngRow, "A").Value), "00000")

            If l_ObjDic.Exists(l_StrCode) Then
                .Cells(l_LngRow, "B").Value = CStr(l_ObjDic(l_StrCode))
            Else
                .Cells(l_LngRow, "B").Value = "（未登録）"
            End If

        Next

    End With

EXIT_PROC:

    If Not l_ObjCn Is Nothing Then
        If l_ObjCn.State <> 0 Then
            l_ObjCn.Close
        End If
    End If

    Set l_ObjDic = Nothing
    Set l_ObjCn = Nothing

    Exit Sub

ERROR_HANDLER:

    MsgBox "エラーが発生しました。" & vbCrLf & _
           "エラー番号：" & Err.Number & vbCrLf & _
           "内容：" & Err.Description, _
           vbExclamation

    Resume EXIT_PROC

End Sub

なぜ速くなるの？（理由はDBの往復）

• ループ内SQL：行数分、DB往復
• Dictionary方式：DB往復は1回（もしくは少数回）＋ ループはメモリ参照だけ
• メモリ参照（Dictionary検索）は、DB往復より圧倒的に軽いので、行数が増えるほど差が出ます。

※ただし、マスタが巨大で明細件数が少ない場合は、明細側の商品コードを重複排除したうえで、IN句、JOIN、作業テーブルなどを使用し、必要なデータだけを1回または少数回のSQLで取得すると省メモリです。原則として、明細1行ごとのSQL発行は避けます。Dictionary化する範囲は、「明細件数」と「マスタ件数」のバランスを見て判断してください。

落とし穴（実務で事故りやすいポイント）

1) キーは型ブレさせない（基本は文字列）

Excel側とDB側で、00123と123のように形式が異なると、同じコードでもDictionaryのキーが一致しません。
商品コードが5桁固定の場合は、Dictionaryへ登録する側と検索する側の両方を、同じ5桁形式に統一します。

l_StrCode = Format$(CLng(value), “00000”)

2) 取得件数が多すぎる場合はSQLで絞る

全件マスタが巨大なら、必要な範囲だけ取得します。WHEREで対象データを絞り、SELECTする列も必要なものだけにします。必要に応じてJOINも使用します。

3) 「存在しない」ケースを必ず処理

未登録コードは現場で普通に出るので、Exists は実務では必須。

まとめ：Dictionaryは「検索のためのメモリDB」

• 配列より読みやすい
• コード変換・集計に強い
• ループ内SQLを減らせる場面では、体感で別世界に速くなることがあります

広告

Apple AirPods 4 アクティブノイズ キャンセリング搭載、ワイヤレスイヤホン、Bluetooth 5.3、ライブ翻訳、適応型オーディオ、外部音取り込みモード、パーソナライズされた空間オーディオ、USB-C充電ケース、ワイヤレス充電、H2チップ、防塵性能と耐汗耐水性能、「探す」対応、Qi充電

快適性のために再設計 ̶ 再設計された AirPods 4 は、一日中つけても抜群に快適。安 定感も向上しました。シルエットが改良されて軸部分が短くなり、すばやく押すだけで 音楽や通話をコントロールできる機能も搭載。
アクティブノイズキャンセリング ̶ アクティブノイズキャンセリング搭載 AirPods 4 は、あなたの耳に届く前に外部からの雑音を低減。いま聴いているものに夢中になれま す*。
周囲の様子を聞ける ̶ AirPods 4 はパワフルな H2 チップを内蔵。適応型オーディオ がアクティブノイズキャンセリングと外部音取り込みモードをシームレスに融合するの で、周囲の音が正確かつ快適に聞こえます。周りの様子もわかるので、あらゆる環境で 最高のリスニング体験を楽しめます*。近くにいる人と話している時は、会話感知機能 が再生中のオーディオの音量を自動的に下げます*。
さらに優れた音質と通話品質 ̶ 「声を分離」機能は、雑音の大きい場所での通話品質 を向上させます。先進的なコンピュテーショナルオーディオを活用して、周囲の雑音を 低減。あなたの声を分離して、クリアな音声にしながら通話の相手に届けます*。
魔法のような体験 ̶「Hey Siri」と話しかけるだけで、曲の再生、電話の発信、スケ ジュールのチェックが思いのまま*。これからは、「はい」ならうなずく、「いいえ」 なら首を横に振るだけで Siri に応答できます*。ペアリングしたい時は、AirPods 4 をあ なたのデバイスの近くに置き、画面上で「接続」をタップするだけ*。2 組の AirPods で音楽や映画を共有するのも簡単です*。肌検出センサーがオーディオを再生するタイ ミングを識別するので、あなたが AirPods をつけている時だけ再生し、外すと一時停 止します。「探す」アプリで AirPods と充電ケースを探すこともできます*。
パーソナライズされた空間オーディオ ̶ パーソナライズされた空間オーディオとダイ ナミックヘッドトラッキングが、あなたの周りに音を配置。音楽、テレビ番組、映画、 ゲームなどに映画館のようなリスニング体験をもたらします*。
再設計されたケース ̶ 充電ケースは、ワイヤレス充電対応モデルでは業界最小のサイ ズ。充電には Apple Watch の充電器、USB-C 充電ケーブル、Qi 規格の充電器が使えま す*。
長時間持続するバッテリー - アクティブノイズキャンセリングを有効にすると、1 回 の充電で最大 4 時間、充電ケースを使えば最大 20 時間再生できます。アクティブノイ ズキャンセリングを使わない場合は、合計再生時間が最大 30 時間になります*。
防塵性能と耐汗耐水性能 - AirPods 4 と充電ケースは、IP54 等級の防塵性能と耐汗耐 水性能を持っています。激しいワークアウトや雨で濡れても安心です*。
*免責事項 ̶ これらは製品の主な機能の概要です。詳しくは以下をご覧ください。

Amazonで見る

最新の価格・在庫状況はAmazonでご確認ください。

免責事項

本記事のサンプルプログラムは、学習・参考用として掲載しているもので、動作や結果を保証するものではありません。 利用する場合は、ご自身の環境に合わせて確認しながらお使いください。万が一トラブルや損害が発生した場合でも、当サイトでは責任を負いかねます。

できるExcelパーフェクトブック 困った! &便利ワザ大全 Copilot対応 Office 2024/2021/2019 & Microsoft 365版

インプレス

Amazon

楽天

Yahoo!ショッピング

できるExcel マクロ&VBA Copilot対応

インプレス

Amazon

楽天

Yahoo!ショッピング