大体でIT

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

大体でIT

Excel VBAで、Dictionaryを使って複数条件で検索するには、検索値を区切り文字で結合して、登録することでできます。検索する際には、検索値を区切り文字で結合して、検索するという感じです。Dictionaryをうまく使って、VBAの処理を高速化していきましょう。

はじめに

この記事では、Dictionaryを使って、複数条件で検索する方法について、ご紹介します。

検索値を区切り文字で結合してキーに登録することで、複数条件で検索できるようになります。

検索する際は同じように、検索値を区切り文字で結合して、Dictionaryを検索する、という感じです。

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

では、Dictionaryを複数条件で検索する方法について、解説していきます。

この記事を読むメリット

  • Dictionaryを使って、複数条件で検索する方法がわかります。

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

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

目次

Dictionaryを使って複数条件で検索する方法

まずは、Dictionaryを複数条件で検索する方法についてみてみます。

Dictionaryに登録できるキーは1つのみ

Dictionaryに登録できるキーは、1つのみになります。

登録できるキーは1つのみ

Dictionaryに登録できるキーは1つのみです

なので、ちょっと工夫をして、複数条件で検索できるようにしていきます。

複数の検索値を結合してキーに登録する

複数条件で検索できるようにするためには、複数の検索値を結合してキーに登録します。

検索値を結合して登録する

検索値を区切り文字で結合して、Dictionaryに登録します

検索する際は、検索値を区切り文字で結合して、検索するという感じになります。

検索値を結合して検索する

検索値を区切り文字で結合して、Dictionaryを検索します

これで、キーは1つなんですけど、複数条件でDictionaryを検索することができます。

Dictionaryを使って複数条件で検索してみる

簡単な例で、Dictionaryを使って、複数条件で検索してみます。

VBAコード

やりたい内容は、Dictionaryを使って、複数条件で検索をしたいということになります。

複数条件で検索したい

Dictionaryを使って、複数条件で検索してみます

では、VBAコードをみてみます。

Dictionaryを使って複数条件で検索

Dictionaryを使って、複数条件で検索するVBAコードです。

Sub TEST1()
  
  Dim A, B
  '辞書を作成
  Set A = CreateObject("Scripting.Dictionary")
  B = Range("A2:C10") 'データベースを取得
  
  'データベースを辞書に登録
  For i = 1 To UBound(B)
    A.Add B(i, 1) & "/" & B(i, 2), B(i, 3)
  Next
  
  '辞書を検索して、値を取得
  Range("G2") = A(Range("E2").Value & "/" & Range("F2").Value)
  
End Sub

まずは、区切り文字で結合して、Dictionaryに登録します。

区切り文字で結合して、Dictionaryに登録します

商品と支店を区切り文字で結合して、Dictionaryを検索します。

商品と支店を区切り文字で結合して、Dictionaryを検索します

これで、複数条件で検索できます。

複数条件で検索できた

複数条件で検索できました

複数条件で検索できました。

こんな感じで、複数条件で検索したい場合は、検索値を結合して登録します。

Dictionaryがどれぐらい高速かを確認

Dictionaryがどれぐらい高速かを確認してみます。

文字列の結合をしている分ちょっと遅くなってしまうんじゃないか、という懸念もありますので、次の2つの方法で、実行する時間を比較してみます。

  • ForとIfと配列を使う
  • Dictionaryを使う

では、やってみます。

ForとIfと配列を使って計測

ForとIfと配列を使って時間を計測してみます。

次のデータベースが「17,577行」まで入力されていて、検索値は「2,000行分」を検索します。

大量データから複数条件に一致した値を取得したい

大量データから複数条件に一致した値を取得してみます

では、ForとIfと配列を使う場合でやってみます。

ForとIfと配列を使う場合

ForとIfと配列を使って、大量データから複数条件で検索するVBAコードです。

Sub TEST2()
  
  t = Timer
  
  Dim A, B
  A = Range("A2:C17577") 'データベースを取得
  B = Range("E2:G2001") '検索値を取得
  
  For i = 1 To UBound(B) '検索値のループ
    For j = 1 To UBound(A) 'データベースのループ
      '複数条件の一致
      If B(i, 1) = A(j, 1) And B(i, 2) = A(j, 2) Then
        B(i, 3) = A(j, 3) '価格を取得
        Exit For
      End If
    Next
  Next
  
  '検索した結果をセルに入力
  Range("E2").Resize(UBound(B, 1), UBound(B, 2)) = B
  
  Debug.Print Timer - t & " 秒"
  
End Sub

実行すると、大量データから複数条件に一致する値を取得できます。

大量データから複数条件に一致する値を取得できました

大量データから複数条件に一致する値を取得できました。

かかった時間をみてみます。

かかった時間

実行にかかる時間はちょっと遅いです

実行にかかる時間はちょっと遅いという感じです。

Dictionaryを使って計測

次は、Dictionaryを使って計測してみます。

使うデータは先ほどと同じで、データベースが「17,577行」で、検索値は「2,000行分」を検索します。

Dictionaryで大量データから複数条件一致を取得

Dictionaryを使って、複数条件に一致した値を取得してみます

では、Dictionaryを使って、複数条件に一致した値を取得してみます。

Dictionaryで高速化

Dictionaryを使って、複数条件で一致した値を取得するVBAコードです。

Sub TEST3()
  
  t = Timer
  
  Dim A, B, C
  '辞書を作成
  Set A = CreateObject("Scripting.Dictionary")
  B = Range("A2:C17577") 'データベースを取得
  C = Range("E2:G2001") '検索値を取得
  
  'データベースを辞書に登録
  For i = 1 To UBound(B)
    A.Add B(i, 1) & "/" & B(i, 2), B(i, 3)
  Next
  
  '辞書を検索
  For i = 1 To UBound(C)
    C(i, 3) = A(C(i, 1) & "/" & C(i, 2))
  Next
  
  '検索した結果を、セルに入力
  Range("E2").Resize(UBound(C, 1), UBound(C, 2)) = C
  
  Debug.Print Timer - t & " 秒"
  
End Sub

実行すると、複数条件に一致した値を取得できます。

Dictionaryを使って、複数条件に一致した値を取得できました

Dictionaryを使って、複数条件に一致した値を取得できました。

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

かかった時間

Dictionaryを使うと、100倍ぐらい速くなりました

結果は、「約0.05秒」です。

比較対象と比較して、Dictionaryを使うと、100倍ぐらい速くなりました。

こんな感じで、Dictionaryを使えば、文字列の結合があっても、かなり高速で値を検索することができます。

おわりに

この記事では、Dictionaryを複数条件で検索する方法について、ご紹介しました。

検索値を区切り文字で結合してキーに登録することで、複数条件で検索できるようになります。

検索する際は同じように、検索値を区切り文字で結合して、Dictionaryを検索する、という感じです。

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

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す