－ちょっと使えるネタを紹介－
Excel VBAで、配列をループして検索する方法について、ご紹介します。配列をループするのは、「For」と「For Each」があります。配列を検索するには、完全一致で検索する「If」と、検索値を含む場合で検索する「InStr」があります。
この記事では、配列をループして検索する方法について、ご紹介します。
配列をループする方法は、「For」と「For Each」があります。
基本的には、「For」を使って配列をループします。
検索する方法としては、「If」と「InStr」を使った方法があって、完全一致と、検索値を含む場合になります。
では、配列をループして検索する方法について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
VBAで、配列をForでループして検索する方法について、ご紹介します。
配列の値を検索する方法として、完全一致で検索する「If」と、検索値を含む場合で検索する「InStr」があります。
両方の使い方について、解説していきます。
配列の中から、完全に一致する場合で検索する方法について、「If」を使ってやってみます。
VBAコードはこちらです。
Sub TEST1()
Dim A
'セルの値を配列に格納
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 5 です」となりました。
「伊藤」は、No「5」ですので、配列を正しく検索できています。
次に、配列の中で、検索値を含むという条件で、検索してみます。
使うVBA関数は、「InStr」です。
セルに入力された値を、配列に格納して、名前の列で「伊藤」を含む値を検索して、Noを取得してみます。
Sub TEST2()
Dim A
'セルの値を配列に格納
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」を検索してみます。
VBAコードは、こちらになります。
Sub TEST3()
Dim A
'セルの値を配列に格納
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」を取得することができました。
こんな感じで、完全一致もしくは、検索値を含むという条件で、配列を検索することができます。
配列は、「For Each」を使ってループすることもできます。
セルに入力した値を、配列に格納して、「For Each」で配列をループしてみます。
Sub TEST4()
Dim A, B
'セルの値を配列に格納
A = ActiveSheet.Range("A1").CurrentRegion
'配列をループ
For Each B In A
'結果を出力
Debug.Print B
Next
End Sub
こちらの表を使って、VBAコードを実行してみます。
では、実行してみます。
配列の値を「For Each」でループすることができました。
表の値を変えて、やってみます。
セルに入力する値を変えて、列を増やしてみました。
では、先ほどと同じVBAコードを、実行してみます。
1列目から始まって、次に、2列目にループする感じですね。
こんな感じで配列をループしています。
という感じです。
「For Each」を使って、完全一致の場合で、「If」を使って、配列を検索してみます。
配列の中から、「山本」を検索して、何番目かを取得します。
VBAコードは、次のようになります。
Sub TEST5()
Dim A, B
'セルの値を配列に格納
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
次の表を用意しました。
では、VBAコードを実行して、「山本」の順番を取得してみます。
結果は、「山本は、7 番目です」となりました。
配列を「For Each」でループした、完全一致で検索する場合は、こんな感じです。
次は、「For Each」を使って、配列をループして、検索値を含む場合で検索してみます。
検索値を含む場合で検索なので、「InStr」を使います。
配列から「一郎」を含む順番を、取得するVBAコードです。
VBAコードは、次のようになります。
Sub TEST6()
Dim A, B
'セルの値を配列に格納
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
次の表を用意しました。
この表を配列に格納して、「For Each」を使って、検索値を含む場合で検索してみます。
「一郎」を含む順番を、取得することができました。
応用編として、一致した値を取得する、というのをやってみます。
まずは、「配列」を使って、一致した値を取得するというのをやってみます。
データベースとして、100,000個のデータを用意しました。
このデータベースから、10,000個を検索してみます。
10,000個のデータを、ランダムで作成しました。
配列を使って、10,000個分のデータを検索するVBAコードが、こちらになります。
Sub TEST7()
t = Timer
'データベースの値を、配列に格納
Dim A
A = Worksheets("データベース").Range("A2:B100001")
'検索する値を、配列に格納
Dim B
B = Worksheets("検索").Range("A2:A10001")
'検索した結果を格納する配列を定義
Dim C
ReDim C(1 To UBound(B, 1), 1 To 1)
'検索する値のループ
For i = 1 To UBound(B, 1)
'データベースのループ
For j = 1 To UBound(A, 1)
'検索値とデータベースの値が一致した場合
If B(i, 1) = A(j, 1) Then
'検索結果の配列に、格納する
C(i, 1) = A(j, 2)
Exit For 'ループを抜ける
End If
Next
Next
'検索結果の配列を、セルに入力
With Worksheets("検索")
.Range("B2").Resize(UBound(C, 1), UBound(C, 2)) = C
End With
Debug.Print Timer - t & " 秒"
End Sub
手順としては、
という流れです。
では、実行してみます。
10,000個分の検索値を、検索することができました。
ちなみに、かかった時間は、次のようになりました。
「103.51 秒」
結構、時間がかかります。
場合によりますけども、一致した値を取得する場合は、「VLOOKUP関数」を使うと速くなります。
次で紹介します。
次に、「VLOOKUP関数」を使って、一致した値を取得する、というのをやってみます。
先ほどと同じように、100,000個のデータベースを使います。
このデータベースから、10,000個分の値を検索します。
条件は、先ほどと同じです。
「VLOOKUP関数」を使って、値を検索するVBAコードです。
Sub TEST8()
t = Timer
With Worksheets("検索")
'VlookUp関数を、セルに入力
.Range("B2:B10001") = "=VLOOKUP(A2,データベース!$A$2:$B$100001,2,FALSE)"
'結果を、値に変換する
.Range("B2:B10001").Value = .Range("B2:B10001").Value
End With
Debug.Print Timer - t & " 秒"
End Sub
VBAコードが、かなりシンプルになりました。
手順は、
という流れです。
では、VBAコードを実行してみます。
「VLOOKUP関数」で、一致する値を検索できました。
では、実行するのにかかった時間です。
「0.95 秒」
かなり速くなりました。
正直、おどろきです。
単純に一致する値を検索したい場合は、「VLOOKUP関数」を使った方がいいです。
一旦、セルにVLOOKUP関数の数式を入力します。
あとは、この文字列をコピーして、VBAコードに、貼り付ければ完成です。
VBAを高速化したい場合は、場合に応じてエクセル関数を使ったり、配列を使ったりしましょう。
この記事では、配列をループして検索する方法について、ご紹介しました。
配列をループする方法は、「For」と「For Each」があります。
検索する方法としては、「If」と「InStr」を使った方法があって、完全一致と、検索値を含む場合になります。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する