大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、大量データから「高速」で値を検索したいという場合は、「Dictionary」を使うとできます。Dictionaryを使えば、検索する際のループ回数を減らすことができるので、高速化できます。うまくDictionaryを使って、VBAの処理を高速化していきましょう。

はじめに

この記事では、大量データから高速で値を検索する方法について、ご紹介します。

Dictionaryを使えば、ループする回数を減らすことができますので、かなり高速で値を検索することができます。

大量データを扱いたい場合や、もう少しVBAの処理を高速化させたい、といった場合に使えます。

うまくDictionaryを使って、VBAの処理を高速化していきましょう。

では、Dictionaryを使って、大量データから高速で値を検索する方法について、解説していきます。

この記事を読むメリット

  • Dictionaryを使って、大量データから「高速」で値を検索できるようになります。

本記事の内容を動画でまとめています

目次から見たい項目へ移動すると便利ですよ。

目次

Dictionaryを使って値を検索

Dictionaryを使って値を検索するについて、簡単に解説します。

Dictionaryを使う方法

Dictionaryは、「キー」と「アイテム」をセットで使います。

「キー」と「アイテム」がセット

「キー」と「アイテム」がセットになります

Dictionaryを使うには、最初にDictionaryに、値を登録します。

Dictionaryに登録

最初にDictionaryに登録します

あとは、キーで検索して、アイテムを取り出すことができます。

キーで検索して、アイテムを取得

キーで検索して、アイテムを取り出すことができます

こんな感じで、キーで検索して、アイテムを取り出すことができます。

このアイテムの検索がかなり高速なので、VBAの処理を高速化するこができます。

Dictionaryの詳細な使い方を確認したい場合は、こちらでまとめていますので、参考になるかと思います。

Dictionaryで値を検索

Dictionaryで値を検索するVBAコードになります。

  • Dictionaryを作成
  • Dictionaryに値を登録
  • Dictionaryで検索

という手順です。

Sub TEST1()
  
  Dim A, B
  '辞書を作成
  Set A = CreateObject("Scripting.Dictionary")
  B = Range("A2:B7") '配列に格納
  
  '辞書に登録
  For i = 1 To UBound(B)
    A.Add B(i, 1), B(i, 2)
  Next
  
  Range("E2") = A(Range("D2").Value) '価格を取得
  
End Sub

Dictionaryで値を検索してみます。

Dictionaryで値を検索

商品の「B」で検索して、価格を取得してみます。

Dictionaryで値を検索してみます

Dictionaryで検索して、価格を取得できます。

Dictionaryで検索して、価格を取得できました

というような感じで、Dictionaryを使って値を検索できます。

大量データから高速で値を検索

では、大量データを使って値を検索してみます。

次の2つの方法で、値を検索する時間を比較してみます。

  • ForとIfと配列
  • Dictionary

では、やってみます。

ForとIfと配列を使って値を検索してみる

まずは、ForとIfと配列を使って値を検索してみます。

  • 検索する値をループ
  • データベースをループ
  • 一致する場合に値を取得
  • 配列をセルに入力

という流れになります。

Sub TEST2()
  
  t = Timer
  
  Dim A, B
  A = Range("A2:B17577") 'データベースを配列に格納
  B = Range("D2:E2001") '検索する値を配列に格納
  
  For i = 1 To UBound(B)
    For j = 1 To UBound(A)
      '値が一致した場合
      If B(i, 1) = A(j, 1) Then
        B(i, 2) = A(j, 2) '価格を取得
        Exit For
      End If
    Next
  Next
  
  '配列をセルに入力
  Range("D2").Resize(UBound(B, 1), UBound(B, 2)) = B
  
  Debug.Print Timer - t & " 秒"
  
End Sub

配列を使っているので、そこそこ高速になります。

大量データで値を検索

大量データを用意しました。

データベースが「17,577行」で、検索する値は「2,000行分」となります。

ForとIfと配列を使って、大量データから値を検索してみます

実行すると、大量データから値を検索できます。

大量データから値を検索できました

大量データから値を検索できました。

では、かかった時間をみてみます。

かかった時間

ForとIfと配列の場合は、ちょっと遅いという感じです

「約2秒」ですね。

配列を使って、ある程度は高速化しているんですけど、ちょっと遅いという感じです。

Dictionaryを使って高速で値を検索

次は、Dictionaryを使って高速で値を検索してみます。

  • Dictionaryを作成
  • Dictionaryにデータベースを登録
  • 検索値でDictionaryを検索して値を取得
  • 配列をセルに入力

という感じです。

VBAコードは、次のようになります。

Sub TEST3()
  
  t = Timer
  
  Dim A, B, C
  '辞書を作成
  Set A = CreateObject("Scripting.Dictionary")
  B = Range("A2:B17577") 'データベースを配列に格納
  C = Range("D2:E1001") '検索する値を配列に格納
  
  '辞書に登録
  For i = 1 To UBound(B)
    A.Add B(i, 1), B(i, 2)
  Next
  
  For i = 1 To UBound(C)
    C(i, 2) = A(C(i, 1)) '価格を取得
  Next
  
  '配列をセルに入力
  Range("D2").Resize(UBound(C, 1), UBound(C, 2)) = C
  
  Debug.Print Timer - t & " 秒"
  
End Sub

では、大量データで検索してみます。

大量データで値を検索

先ほどと同じで、データベースが「17,577行」で、検索値が「2,000行分」になります。

Dictionaryを使って、高速で値を検索してみます

実行すると、Dictionaryを使って、値を検索できます。

Dictionaryを使って、値を検索できました

Dictionaryを使って、値を検索できました。

では、かかった時間をみてみます。

かかった時間

結果は、かなり高速です

「約0.02秒」となりました。

結果は、かなり高速です。

こんな感じで、大量データから値を検索したいという場合は、Dictionaryを使うとかなり高速で、値を検索することができます。

おわりに

この記事では、大量データから高速で値を検索する方法について、ご紹介しました。

Dictionaryを使えば、ループする回数を減らすことができますので、かなり高速で値を検索することができます。

大量データを扱いたい場合や、もう少しVBAの処理を高速化させたい、といった場合に使えます。

うまくDictionaryを使って、VBAの処理を高速化していきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す