Excel VBA 行と列の条件が一致するセルを取得する

今回は行と列の条件が一致した、交差するセルのデータを取得するコードを紹介します。

VBAだけでなく、INDEX関数とMATCH関数、VLOOKUP関数を使った、Excel関数で取得する方法も併せて紹介します。

VBAで取得する場合も単純にループで取得する方法から、配列を使用する方法、Dictionaryを使用する方法についてご説明します。

ご自身の環境にあった方法で試してみてください。

また、今回使用するループや配列、Dictionaryの使い方については下記記事をご覧ください。

1.列行の条件が一致する値を取得する関数
- INDEX関数とMATCH関数の組み合わせ
- VLOOKUP関数とMATCH関数の組み合わせ
2. 列行の条件が一致する値を取得するVBA
3.サンプルファイルのダウンロード
4.まとめ

1.列行の条件が一致する値を取得する関数

今回は2つの関数の組み合わせ方法を紹介します。

INDEX関数とMATCH関数の組み合わせ

INDEX関数とMATCH関数を組み合わせる事で、条件に一致した行と列を取得出来ます。

それぞれの関数の使い方は以下となります。

INDEX関数

INDEX関数は「指定された行と列が交差する位置にある、セルの参照を返します」

詳細については「INDEX関数の使い方」をご覧下さい。

=INDEX(範囲, 行番号, 列番号)

MATCH関数

MATCH関数は「範囲のセルで指定した項目を検索し、範囲内の項目の相対的な位置を返します」

詳細については「MATCH関数の使い方」をご覧下さい。

=MATCH(検索値,検索範囲,検索方法)

INDEX関数とMATCH関数を組み合わせる

MATCH関数で一致した行と列を取得し、INDEX関数で取得した行と列の交差するセルの値を取得します。

＝INDEX(検索範囲,MATCH(行の検査値,行の検査範囲,0),MATCH(列の検査値,列の検査範囲,0))

の様に入力します。

サンプルデータと式です。

B10とC10の商品名とプランを条件に料金を取得します。

=INDEX(B3:D6,MATCH(E2,A3:A6,0),MATCH(F2,B2:D2,0))

となります。

VLOOKUP関数とMATCH関数の組み合わせ

次はVLOOKUP関数とMATCH関数を組み合わせた方法です。

VLOOKUP関数

VLOOKUP関数は、「表や範囲から行ごとに数値や文字列などを検索します」

詳細については「VLOOKUP関数の使い方」をご覧下さい。

=VLOOKUP(検索値、範囲、列番号、検索方法)

VLOOKUP関数とMATCH関数を組み合わせる

MATCH関数で列番号を取得して、VLOOKUP関数の列番号を指定して検索します。

サンプルデータは一緒ですが、数式がVLOOKUPとMATCH関数となっています。

B10とC10の商品名とプランを条件に料金を取得します。

=VLOOKUP(B10,B2:F6,MATCH(C10,B2:F2,0),0)

となります。

2. 列行の条件が一致する値を取得するVBA

上記で紹介した関数をVBAで実装してみます。

VBAで実装する場合、書き方は今回紹介する方法に限られる訳ではありませんので、あくまで実装例の1つとしてご参考にしてください。

For~NextとIfで取得する

まず、単純にFor~NextのループとIfの分岐で行列を取得して、交差するセルを取得するサンプルをご紹介します。

サンプルデータは同じデータを使用しています。

’標準モジュールに記載した内容
Sub Sample1()

Dim i           As Long
Dim n           As Long
Dim TargetRow   As Long
Dim TargetCol   As Long

'まず商品行を取得する
For i = 3 To 6 '3行目から6行目までループする
  
    If Cells(i, 2) = Cells(10, 2) Then 'ループで取得したB列の値とB10の値が一致した場合
    
        TargetRow = i '一致した行番号を取得する
        Exit For
    
    End If
    
Next i

'次にプラン列を取得する
For i = 3 To 6 'C列からF列までループする
  
    If Cells(2, i) = Cells(10, 3) Then 'ループで取得した2行目の値とC10の値が一致した場合
    
        TargetCol = i '一致した列番号を取得する
        Exit For
    
    End If
    
Next i

'D10に出力する
Cells(10, 4) = Cells(TargetRow, TargetCol).Value

End Sub

このままでは、都度マクロを実行する必要がありますので、Sheetモジュールにチェンジイベントを記載してB10かC10が変更されると、実行されるように対応させます。

’シートモジュール(ここではSheet1)に記載
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("B10:C10")) Is Nothing Then'Chandeイベントの範囲を指定

        Exit Sub'B10~C10以外は離脱

    Else

        Call Sample1'指定された範囲が変更された場合はSampleを呼び出す

    End If
    
End Sub

Sampleコードでは単純に行をループして、B10と一致した行を取得して変数へ格納します。

次に列をループして、C列に一致した列と取得して変数へ格納します。

行列番号を格納した変数を使用して、D10に結果を出力します。

配列に格納して For~NextとIfで取得する

シートモジュールの「Private Sub Worksheet_Change」は同じなので、Sample1のみ書き換えます。

基本的なロジックは同じです。

今回の注意事項は配列の要素数はLBoundやUBoundで最小値、最大値を取得出来ますが、次元数(列数)は取得できないので、別の方法で取得しています。

要素数と次元数を固定値で記載すると、もっと簡素なコードで書けますが、あえて要素数と次元数が動的に変更されても対応出来るように書いてます。

Option Explicit

'標準モジュールに記載した内容
Sub Sample2()

Dim i           As Long
Dim n           As Long
Dim TargetRow   As Long
Dim TargetCol   As Long
Dim myArray     As Variant

'変数に格納する
myArray = Range(Cells(2, 2), Cells(6, 6))

'まず商品行を取得する
For i = LBound(myArray) To UBound(myArray) '配列の要素数分ループする
    
    '注意(配列に格納すると行番号が変わります)
    If myArray(i, 1) = Cells(10, 2) Then 'ループで取得した値とB10の値が一致した場合
    
        TargetRow = i '一致したインデックスを取得する
        Exit For
    
    End If
    
Next i

'配列の次元数を取得する
Dim myInt       As Long

i = 0
On Error Resume Next 'エラーを無視する
Do While Err.Number = 0
    i = i + 1
    myInt = UBound(myArray, i)
Loop
On Error GoTo 0

'次にプラン列を取得する
For i = 1 To myInt '1次元から最終次元までループする
  
    If myArray(1, i) = Cells(10, 3) Then 'ループで取得した値とC10の値が一致した場合
    
        TargetCol = i '一致した次元を取得する
        Exit For
    
    End If
    
Next i

'D10に出力する
Cells(10, 4) = myArray(TargetRow, TargetCol)

End Sub

Dictionary(連想配列)を使って取得する

次はDictionary(連想配列)に格納して、条件に一致するデータを取得します。

Dictionaryに行の項目と列の項目を連結して、Kyeとして格納して、交差する料金をItemとして格納しています。

DictionaryはKyeを検索値として、Itemを取得する事が出来ますので、B10とC10を連結してKyeとして、Itemを取得してD10に出力しています。

データ範囲を静的(固定値)にしているため、簡素なコードになっています。

上の配列の様に動的に書くことも可能です。

'標準モジュールに記載した内容
Sub Sample3()

Dim i           As Long
Dim n           As Long
Dim TargetRow   As Long
Dim TargetCol   As Long
Dim myDic       As Object
Dim myVal       As String

'Dictionary
Set myDic = CreateObject("Scripting.Dictionary")

'Dictionaryにデータを格納する
'行と列を交差するように連結して格納する

For i = 3 To 6 '行ループ

    For n = 3 To 6 '列ループ
    
        myVal = Cells(i, 2) & Cells(2, n) '行列の項目を連結
        
        If Not myDic.Exists(myVal) Then 'Dictionaryに未登録の場合格納
        
            myDic.Add myVal, Cells(i, n).Value
            
        End If
    
    Next n
    
Next i

'D10に出力する
Cells(10, 4) = myDic(Cells(10, 2) & Cells(10, 3)) 'B10とC10を連結してKyeとして、DicのItemを取得

End Sub