VBA Index関数 の使い方。行と列の指定でセル値の抽出

vbaindexeyecatch001

エクセルVBAでワークシート関数のIndex関数 の使い方を説明します。

行と列の指定で簡単にセル値の抽出をすることが出来ます。

単純にデータを検索するためだけの使い方ではなく、他の関数との複合ワザで利用するタイプです

こんにちは、じゅんぱ店長(@junpa33)です。

今回も、エクセルVBAで利用できるワークシート関数についてがテーマです。

この記事では、データリストから必要な値や数値を取り出してくる「Index関数」について使い方を説明します。

「Index関数」はデータの検索、抽出時に利用されるワークシート関数になります。

この検索と抽出については他にもよく似た関数があります。それぞれに固有の特徴がありますが、特に、

関数が返してくる値に注目してください。

  • Match

検索対象の値が、指定した範囲内でどの位置にあるかを相対的に位置数値(行または列番号)として返します。

vbamatcheyecatch001 VBAで使うMatch関数 活用度アップでテッパン関数に!
  • Index

テーブルまたはセル範囲にある値、またはその値のセル参照を返します。

  • Vlookup

選択範囲の1列目で指定した値を検索し、その選択範囲内で取出指定した別の列の同じ行にある値を返します。

関数VLookupをエクセルVBAで使う。利用法と注意点
  • Find・FindNext

ワークシートで特定の情報をRangeオブジェクトを対象として検索しセルを表すRangeオブジェクトとして返します。

vbafindeyecatch001 Findメソッドでの検索の方法とコード組み立てのコツ

Index関数 行と列の指定で簡単にセル値を抽出

vbaindexsetup004

Index関数の構文は

記述方法

WorksheetFunction.Index( 検索範囲 , 検索値(行番号)  [, 検索値(列番号) ]  )

です。

検索範囲

検索範囲は、「複数行、複数列」でも「1行」でも「1列」でも、指定できます。

検索値

検索値は、2次元の場合は行番号と列番号を指定します。

1次元の場合は、行番号または列番号のどちらかを指定します。

行と列の指定値の区別はなく、→列指定の場合でも(検索範囲, , 5)の様には記述しません。

検索範囲で「1行指定した場合は、左から何番目」、「1列指定した場合は上から何番目」という様に自動セットされます。

(検索行, 5)・・・・・左から5つ目

(検索列, 5)・・・・・上から5つ目

今回の例題の紹介

22行8列の雑誌リストを例題に使います。

テスト1はリスト全体を検索対象にします。

テスト2は、コード項目列(4列目)と11行目の範囲指定をします。

vbaindexsetu001a

コードの組み立ての順序としてはザックリと

  1. 検索範囲の大きさをチェック
  2. 検索範囲をオブジェクト変数にセット
  3. 検索値の入力のセット
  4. エラーコード(開始)
  5. Index関数の記述
  6. 検出結果の処理コード
  7. エラーコード(終了)

という様な手順になります

例題テスト1のコード

データ表全体(22行8列)を検索対象として行います。

検索値の入力時に、検索対象範囲外の数値を入力した場合は、Index関数がエラーを返してきます。

その場合のコード実行がストップしないように、エラー回避コードを記述しておきます。

VBA
Option Explicit

Sub Index関数Test1()
    Dim sKa As Variant, sKb As Variant
    Dim MVal As Variant
    Dim DataRange As Range
'検索範囲をセットする
        Set DataRange = Range("B6").CurrentRegion
        Worksheets("Sheet1").Select
'検索値の入力
        sKa = Range("L1")
        sKb = Range("L2")
'エラーストップを無視するコード
        On Error Resume Next
'コード項目列で検索する
        MVal = WorksheetFunction.Index(DataRange, sKa, sKb)
        Range("L3") = MVal
'エラー無視を解除する
        On Error GoTo 0
End Sub

実行結果

行指定を「12」、列指定を「6」にセットしたときの戻り値が「メンズノンノ」になっています。

行番号と列番号はデータ表の相対位置となっています。

vbaindexsetu002

例題テスト2のコード

行範囲の検索、列範囲の検索それぞれを一度に行います。

範囲指定のコードが長くなりますので、オブジェクト変数としてセットしています。

VBA
Sub Index関数Test2()
    Dim Arow As Long, Acol As Long
    Dim sKc As Variant, sKd As Variant
    Dim MValR As Variant, MValC As Variant
    Dim DataRange As Range
    Dim DataRRange As Range
    Dim dataCRange As Range
'検索範囲をセットする
        Set DataRange = Range("B6").CurrentRegion
        Worksheets("Sheet1").Select
'データ表の行数を調べます
        Arow = DataRange.Rows.Count
'データ表の列数を調べます
        Acol = DataRange.Columns.Count
'行範囲をセットする
        Set DataRRange = Range(Cells(6, 5), Cells(Arow + 5, 5))
'列範囲をセットする
        Set dataCRange = Range(Cells(16, 2), Cells(16, Acol + 1))
'検索値の入力
        sKc = Range("L30")
        sKd = Range("L31")
'エラーストップを無視するコード
        On Error Resume Next
'コード項目列で検索する
        MValR = WorksheetFunction.Index(DataRRange, sKc)
        Range("M30") = MValR
'11行目で検索する
        MValC = WorksheetFunction.Index(dataCRange, sKd)
        Range("M31") = MValC
'エラー無視を解除する
        On Error GoTo 0
End Sub

実行結果

コード行の指定値は「12」として戻り値は「18627」

11行目の指定値は「6」として戻り値は「SENSE」となります。

vbaindexsetu003

Index関数 の使い方。セル値の抽出のまとめ

vbaindexsetup005

行と列番号を検索して値を抽出するIndex関数について説明いたしました。

ただ単純な検索だけなら、行と列番号が分かったならば、Index関数を使わずとも、

Cells( 行番号, 列番号 ).Value で分かるととも思います。

ですのでこの関数は、他のいろんな関数との組み合わせで活かされていく関数だと思います。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

今回の記事はここまでです。   最後までご覧いただき有難うございました。

エクセルVBA最速理解で必要な知識を集めよう!

エクセルVBA業務ツールで日常の業務改善を行いましょう。

VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。