大体でIT

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

大体でIT

Excel VBAで、配列をループして検索する方法について、ご紹介します。配列をループするのは、「For」と「For Each」があります。配列を検索するには、完全一致で検索する「If」と、検索値を含む場合で検索する「InStr」があります。

はじめに

この記事では、配列をループして検索する方法について、ご紹介します。

配列をループする方法は、「For」と「For Each」があります。

基本的には、「For」を使って配列をループします。

検索する方法としては、「If」と「InStr」を使った方法があって、完全一致と、検索値を含む場合になります。

では、配列をループして検索する方法について、解説していきます。

この記事で紹介すること

  • 配列を、完全一致で検索する方法
  • 配列を、検索値を含む場合で検索する方法

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

目次

VBAで配列をForでループして検索

VBAで、配列をForでループして検索する方法について、ご紹介します。

配列の値を検索する方法として、完全一致で検索する「If」と、検索値を含む場合で検索する「InStr」があります。

両方の使い方について、解説していきます。

完全一致の場合(If)

配列の中から、完全に一致する場合で検索する方法について、「If」を使ってやってみます。

VBAコードはこちらです。

Sub TEST1()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    '配列をループ
    For i = 1 To UBound(a, 1)
        '「If」で検索
        If a(i, 2) = "伊藤" Then
            Debug.Print "伊藤は、No " & a(i, 1) & " です"
        End If
    Next
    
End Sub

完全一致の場合なので、「If」を使って、一致した値の「No」の値を取得しています。

次の表を用意しました。

表に値を入力

表を用意

この表を配列に格納して、「伊藤」を検索したあと、「No」を取得してみます。

では、実行します。

配列から「伊藤」のNoを検索

配列を使って、完全一致した検索値からNoを取得した結果

結果は、「伊藤は、No 5 です」となりました。

「伊藤」は、No「5」ですので、配列を正しく検索できています。

検索値を含む場合(InStr)

次に、配列の中で、検索値を含むという条件で、検索してみます。

使うVBA関数は、「InStr」です。

配列から「伊藤」を含むNoを検索

セルに入力された値を、配列に格納して、名前の列で「伊藤」を含む値を検索して、Noを取得してみます。

Sub TEST2()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    '配列をループ
    For i = 1 To UBound(a, 1)
        '「InStr」で検索
        If InStr(a(i, 2), "伊藤") > 0 Then
            Debug.Print "伊藤は、No " & a(i, 1) & " に含まれています"
        End If
    Next
    
End Sub

次の表を使います。

表を用意

配列の中で、検索値の一部を含む値を検索しますので、「InStr」を使います。

では、実行してみます。

配列の中で、検索値の一部を含む値を検索した結果

結果は、「伊藤は、No 5 に含まれています」となりました。

No「5」に「伊藤」が含まれていますので、正しく配列を検索できています。

配列から、「一郎」を含むNoを検索

ついでに、表の中から、「一郎」が含まれる「No」を検索してみます。

VBAコードは、こちらになります。

Sub TEST3()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    '配列をループ
    For i = 1 To UBound(a, 1)
        '「InStr」で検索
        If InStr(a(i, 2), "一郎") > 0 Then
            Debug.Print "一郎は、No " & a(i, 1) & " に含まれています"
        End If
    Next
    
End Sub

先ほどと同じ表を使います。

表を用意

では、実行してみます。

表を配列に格納して、「一郎」を含む値を検索した結果

「一郎」を含む値を検索して、「No」を取得することができました。

こんな感じで、完全一致もしくは、検索値を含むという条件で、配列を検索することができます。

VBAでFor Eachを使ってループして検索

配列は、「For Each」を使ってループすることもできます。

配列をFor Eachでループ 

セルに入力した値を、配列に格納して、「For Each」で配列をループしてみます。

Sub TEST4()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    '配列をループ
    For Each b In a
        '結果を出力
        Debug.Print b
    Next
    
End Sub

こちらの表を使って、VBAコードを実行してみます。

1列の配列から値を取得

1列の配列を用意

では、実行してみます。

配列をFor Eachを使ってループした結果

配列の値を「For Each」でループすることができました。

表の値を変えて、やってみます。

セルに入力する値を変えて、列を増やしてみました。

2列の配列から値を取得

2列の配列を用意

では、先ほどと同じVBAコードを、実行してみます。

配列をFor Eachを使ってループした結果

1列目から始まって、次に、2列目にループする感じですね。

こんな感じで配列をループしています。

For Eachのループ順番

2列の配列をループずる順番

という感じです。

完全一致の場合(If)

「For Each」を使って、完全一致の場合で、「If」を使って、配列を検索してみます。

配列の中から、「山本」を検索して、何番目かを取得します。

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

Sub TEST5()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    i = 0
    '配列をループ
    For Each b In a
        i = i + 1 'カウントアップ
        '「If」で検索
        If b = "山本" Then
            '結果を出力
            Debug.Print b & "は、" & i & " 番目です"
        End If
    Next
    
End Sub

次の表を用意しました。

1列の表を用意

1列の表を用意

では、VBAコードを実行して、「山本」の順番を取得してみます。

配列から「山本」の順番を検索

配列をFor EachでIFを使って、完全一致で、検索した結果

結果は、「山本は、7 番目です」となりました。

配列を「For Each」でループした、完全一致で検索する場合は、こんな感じです。

検索値を含む場合(InStr)

次は、「For Each」を使って、配列をループして、検索値を含む場合で検索してみます。

検索値を含む場合で検索なので、「InStr」を使います。

配列から「一郎」を含む順番を、取得するVBAコードです。

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

Sub TEST6()
    
    'セルの値を配列に格納
    a = ActiveSheet.Range("A1").CurrentRegion
    
    i = 0
    '配列をループ
    For Each b In a
        i = i + 1 'カウントアップ
        '「InStr」で検索
        If InStr(b, "一郎") > 0 Then
            '結果を出力
            Debug.Print "一郎は、" & i & " 番目に含まれています"
        End If
    Next
    
End Sub

次の表を用意しました。

1列の表を用意

表を用意

この表を配列に格納して、「For Each」を使って、検索値を含む場合で検索してみます。

配列から「一郎」を含む値の順番を取得

For Eachを使って、検索値を含む場合で、検索した結果

「一郎」を含む順番を、取得することができました。

VBAで一致した値を取得する

応用編として、一致した値を取得する、というのをやってみます。

配列を使って一致した値を取得

まずは、「配列」を使って、一致した値を取得するというのをやってみます。

データベースとして、100,000個のデータを用意しました。

100,000個のデータベース

100,000個のデータベース

このデータベースから、10,000個を検索してみます。

10,000個の検索値

10,000個を検索する

10,000個のデータを、ランダムで作成しました。

配列を使って、10,000個分のデータを検索するVBAコードが、こちらになります。

Sub TEST7()
    
    t = Timer
    
    'データベースの値を、配列に格納
    Dim MData
    MData = Worksheets("データベース").Range("A2:B100001")
    
    '検索する値を、配列に格納
    Dim SData
    SData = Worksheets("検索").Range("A2:A10001")
    
    '検索した結果を格納する配列を定義
    Dim ReData
    ReDim ReData(1 To UBound(SData, 1), 1 To 1)
    
    '検索する値のループ
    For i = 1 To UBound(SData, 1)
        'データベースのループ
        For j = 1 To UBound(MData, 1)
            '検索値とデータベースの値が一致した場合
            If SData(i, 1) = MData(j, 1) Then
                '検索結果の配列に、格納する
                ReData(i, 1) = MData(j, 2)
                Exit For 'ループを抜ける
            End If
        Next
    Next
    
    '検索結果の配列を、セルに入力
    With Worksheets("検索")
        .Range("B2").Resize(UBound(ReData, 1), UBound(ReData, 2)) = ReData
    End With
    
    Debug.Print Timer - t & " 秒"
    
    '103.5156 秒
    
End Sub

手順としては、

  • ①データベースの値を配列に格納
  • ②検索する値を配列に格納
  • ③検索結果の配列を作成
  • ④検索値に一致するデータベースの値を検索
  • ⑤検索結果をセルに入力

という流れです。

では、実行してみます。

検索した結果

データベースから検索値を検索した結果

10,000個分の検索値を、検索することができました。

ちなみに、かかった時間は、次のようになりました。

かかった時間

「103.51 秒」

結構、時間がかかります。

場合によりますけども、一致した値を取得する場合は、「VLOOKUP関数」を使うと速くなります。

次で紹介します。

VLOOKUP関数を使って一致した値を取得

次に、「VLOOKUP関数」を使って、一致した値を取得する、というのをやってみます。

先ほどと同じように、100,000個のデータベースを使います。

100,000個のデータベース

100,000個のデータベースを用意

このデータベースから、10,000個分の値を検索します。

10,000個の検索値

10,000個分の値を検索する

条件は、先ほどと同じです。

「VLOOKUP関数」を使って、値を検索するVBAコードです。

Sub TEST8()
    
    t = Timer
    
    With Worksheets("検索")
        'VLOOKUP関数を、セルに入力
        .Range("B2:B10001") = "=VLOOKUP(RC[-1],データベース!R2C1:R100001C2,2,FALSE)"
        '結果を、値に変換する
        .Range("B2:B10001").Value = Worksheets("検索").Range("B2:B10001").Value
    End With
    
    Debug.Print Timer - t & " 秒"
    
    '0.953125 秒
    
End Sub

VBAコードが、かなりシンプルになりました。

手順は、

  • ①VLOOKUP関数で、一致する値を検索
  • ②数式を値に変換

という流れです。

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

検索した結果

VLOOKUP関数で一致する配列を検索した結果

「VLOOKUP関数」で、一致する値を検索できました。

では、実行するのにかかった時間です。

かかった時間

「0.95 秒」

かなり速くなりました。

正直、おどろきです。

単純に一致する値を検索したい場合は、「VLOOKUP関数」を使った方がいいです。

R1C1形式で数式を作成する方法

ちなみに、VLOOKUP関数は、R1C1形式になっていて、直接入力しようとするとめんどくさいです。

なので、手順としては、次にようにやると楽です。

  • ①一旦セルにVLOOKUP関数の数式を入力
  • ②入力した数式をR1C1形式で取得
  • ③VBAコードに貼り付け

という流れです。

手順①:一旦セルにVLOOKUP関数の数式を入力

VLOOKUP関数の数式を一旦セルに入力する

手順②:入力した数式をR1C1形式で取得

この数式を、「セル.FormulaR1C1」を使って、「R1C1形式」で取得します。

Sub TEST9()
    
    'セルに入力した数式をR1C1形式で取得
    a = ActiveSheet.Range("B2").FormulaR1C1
    
End Sub

このVBAコードを途中まで実行すると、セルに入力された数式を取得できます。

セルに入力された数式を取得

こんな感じで、R1C1形式のVLOOKUP関数を取得することができます。

'セルに入力した数式
"=VLOOKUP(RC[-1],データベース!R2C1:R100001C2,2,FALSE)"

手順③:VBAコードに貼り付け

あとは、この文字列をコピーして、VBAコードに、貼り付ければ完成です。

VBAを高速化したい場合は、場合に応じてエクセル関数を使ったり、配列を使ったりしましょう。

おわりに

この記事では、配列をループして検索する方法について、ご紹介しました。

配列をループする方法は、「For」と「For Each」があります。

検索する方法としては、「If」と「InStr」を使った方法があって、完全一致と、検索値を含む場合になります。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す