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

vbaindexeyecatch001

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

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

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

 

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

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

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

 

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

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

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

 

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

 

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列)を検索対象として行います。

例題テスト1

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

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

コード
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のコード

 

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

例題テスト2

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

コード
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関数 の使い方。セル値の抽出のまとめ

 

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

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

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

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

 

エクセルVBAをはじめから学ぶために

 

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

エクセルVBAをはじめから学ぶときのガイダンス記事を書いています。

よろしければ参考にしてください。

 

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。
エクセルVBAの独習で良質な参考書を7冊選ぶ。良書との出会いは大切です

 

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできると思います。
エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

このサイトでよく使うVBAのコードのトピックを纏めています。

「VBA最速理解」の記事一覧を開く

 

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

 

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