【VBAリファレンス|実務向け】実務で差がつく!INDEX・MATCH関数とVBAによる動的シート参照の自動化

スポンサーリンク

1. 導入:なぜINDEX・MATCH関数をVBAで制御すべきか

実務において、複数のシート(プラン別料金表など)からデータを抽出する際、VLOOKUP関数ではなくINDEX・MATCH関数を使うことは、柔軟なレイアウト変更に対応するための「必須スキル」です。しかし、シート数が増えるたびに手動で数式を修正するのはミスのもとです。今回は、INDEX・MATCH関数の強力な組み合わせを解説した上で、VBAを使って「シート名を動的に切り替えて値を抽出する」という実務的な自動化手法を紹介します。

2. 基礎知識:INDEX関数とMATCH関数の役割

INDEX関数は「指定した範囲内の指定した行・列にある値」を返し、MATCH関数は「指定した値が範囲内の何番目にあるか」を調べます。
VLOOKUPと異なり、検索値の右側にあるデータだけでなく、左側にあるデータも抽出できるため、表の構成変更に強いのが特徴です。

・INDEX(範囲, 行番号, [列番号])
・MATCH(検索値, 範囲, 照合の型)

この2つを組み合わせることで、「行番号」と「列番号」を動的に特定し、目的のセルをピンポイントで射抜くことができます。

3. 実装:VBAでシート参照を自動化する

手動で数式内のシート名を書き換えるのは非効率です。VBAを使えば、セルに入力された「プラン名(シート名)」を元に、自動で正しいシートを参照する数式をセルにセットしたり、直接計算結果を取得したりすることが可能です。

4. サンプルプログラム:シート名を動的に指定して値を抽出する

以下のコードは、指定したシート(例:松プラン)から特定の項目を検索し、値をセルに書き込む実務的なサンプルです。

Sub GetPriceByDynamicSheet()
Dim wsResult As Worksheet
Dim targetSheetName As String
Dim targetValue As String
Dim result As Variant

‘ 結果を出力するシートを指定
Set wsResult = ThisWorkbook.Sheets(“集計表”)

‘ 検索条件(プラン名と検索値)
targetSheetName = wsResult.Range(“B2”).Value ‘ 例:松プラン
targetValue = wsResult.Range(“C2”).Value ‘ 例:宿泊人数

‘ INDEX・MATCHをVBAから実行する場合の基本形
‘ Evaluateメソッドを使うことで、ワークシート関数をVBA内で直接計算できます
On Error Resume Next ‘ エラー回避(シート名間違い等)
result = Evaluate(“INDEX(‘” & targetSheetName & “‘!B:B, MATCH(“”” & targetValue & “””, ‘” & targetSheetName & “‘!A:A, 0))”)

If IsError(result) Then
MsgBox “指定されたデータが見つかりませんでした。”
Else
wsResult.Range(“D2”).Value = result
End If
On Error GoTo 0
End Sub

5. 応用・注意点:現場で陥りやすいバグの回避策

1. シート名の存在チェック: VBAでシート名を参照する際、存在しないシート名を指定すると実行時エラーになります。必ず「If Not Evaluate(“ISREF(‘” & targetSheetName & “‘!A1)”) Then」のように、シートの存在確認を行うロジックを入れましょう。
2. 絶対参照の意識: 数式をセルに代入する場合(Formulaプロパティを使用)、相対参照か絶対参照かによって、コピーした際にズレが生じます。特に「列全体(A:A)」を指定する際は、列の挿入などで数式が壊れないよう注意が必要です。
3. 計算負荷の軽減: 大量のシートに対してループ処理を行う場合、`Application.ScreenUpdating = False` を記述して画面更新を停止させると、処理速度が劇的に向上します。

これらを組み合わせれば、Excelの「関数」の柔軟性と、VBAの「自動化」のスピードを両立した、強固な業務システムを構築できます。ぜひ現場でお試しください。

タイトルとURLをコピーしました