Excel INDEX関数の使い方の基本と応用！VBAの一括設定も！

ExcelでINDEX関数を見かけ、何の用途で使っているか、INDEX関数がどのように機能しているのかわからないときはないでしょうか。

例えば前任者から引き継いだ資料の関数にINDEX関数が使用されどのような処理をしているか把握したいときなどです。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する
Excel INDEX関数の使い方を基本から応用、そしてVBAによる一括設定の方法について
まとめます！

ExcelでINDEX関数を使用するイメージ

ExcelでINDEX関数を使用するイメージについて説明をします。

INDEX関数とは〇行目と△列目がクロスするセルの値を取得する処理のことをいいます。

こんな表があり、

値をとりたいセルを行と列の番号で指定して、

値を取得していきます。

タカヒロ

クレーンゲームを操作するイメージに近いでしょうか。

INDEX関数単体で使う場合は、列や行の一部の範囲を抽出したり、連続取得したい場合などですね。

またMATCH関数と組み合わせてVLOOKUP関数と同じ結果を取得することもできます。

検索結果が表示されました。

さらにVBAでINDEX関数を一括設定することもできます。

説明だけですと理解しにくいので、

実際に使用しながら理解していきましょう！

INDEX関数を使用する表を用意する

INDEX関数を使用する表を用意しましょう。

内容はなんでもよいですが、表の値はすべてことなる値にしてください。

サンプルは以下のようにしました。

INDEX関数の書式/機能/引数について

INDEX関数の詳細について説明をします。
INDEX関数の書式と機能については以下の通りです。

INDEX関数で1列目の値をすべて取得する

INDEX関数で1列目の値をすべて取得してみましょう。

以下のように入力します。

数式を以下のように入力し、J列まで同じ要領で入力していきます。

はい、参照範囲の1列目の値がすべてとれましたね。

INDEX関数で1行目の値をすべて取得する

INDEX関数で1行目の値をすべて取得してみましょう。

以下のように入力します。

数式を以下のように入力し、H列まで同じ要領で入力していきます。

はい、参照範囲の1行目の値がすべてとれましたね。

INDEX関数で指定した行、列番号の値を取得する

INDEX関数で指定した行、列番号のセルの値を取得してみましょう。

以下のようにF1~H2セルの行と列指定の値を入力します。

指定した行、列番号のセルの値が取得できましたね。

INDEX関数で「#REF」エラーとなった場合の原因と回避方法

INDEX関数で「#REF」エラーとなった場合の原因と回避方法について説明をします。

INDEX関数「#REF」エラーの原因

行や列で指定した番号が参照範囲を超えてしまっているためとなります。

例えば3列しかないのに4列目を指定している場合が該当します。

INDEX関数「#REF」エラーの回避方法

参照範囲内の行、列番号を指定するようにしましょう。

エラーであっても「#REF」を表示させないようにするには以下のようにIFERROR関数を使います。

エラーではなく「該当なし」になりましね。

サンプルでは該当なしとしていますが、表示させたくない場合は引数を””としてください。

応用：INDEX関数とMATCH関数でVLOOKUP関数と同じ結果を出す

INDEX関数とMATCH関数でVLOOKUP関数と同じ結果を出すこともできます。

H列にMATCH関数の出力結果である検索位置、I列に検索値があるセルの番号を出力する値を入力しますので、空欄のセルを用意します。

1行目の項目名は「検索位置」、「セル番号」とします。

検索結果を出力する

表の用意ができましたら、一つ目の検索結果を出力していきます。
G2のセルへ以下を入力します。

E2の「千葉県」をキーにA列を検索し、一つ目の位置番号を出力します。

結果は1行目から3つ目を意味する3となりました。

続いてI2セルへ以下を入力します。

最後にG2セルへ以下を入力します。

はい、検索結果が表示されましたね。

タカヒロ

さらに複数該当の結果も抽出できます。詳細は以下をご覧ください。

INDEXとMATCH関数で複数該当の値をすべて取得する方法！VBAで同じ処理も可！

VBAでINDEX関数を一括挿入する

これまで関数を手入力する必要がありましたが、次はVBAを使い、関数を挿入する処理や計算結果を挿入する処理を一括で行っていきましょう。

タカヒロ

ここからこの記事のメインとなります。

Formulaで数式を挿入する

Formulaを使い、数式のままで挿入をしてみましょう。

Formulaはその名の通り数式を入れるプロパティとなります。
値として受け渡す情報は文字情報でしかないですが、Formulaを使うことにより数式としてセルへ代入をしてくれます。

VBAは以下の通りです。

Sub index関数を一括挿入する1()
    For i = 1 To 5
        Cells(3, 5 + i).Formula = "=INDEX(A1:C5," & Cells(1, i + 5).Address & "," & Cells(2, 6).Address & ")"
    Next
End Sub

For文で5回処理を繰り返します。

F3からFセルまで数式を代入します。

CellsオブジェクトのAddressプロパティでセルの位置情報（アドレス）を取得しています。

はい、できましたね。

CellsオブジェクトのAddressプロパティではなく、参照先セルの値を直接INDEX関数に指定することでも同じ結果を得ることができます。

Sub index関数を一括挿入する2()
    For i = 1 To 5
        Cells(3, 5 + i).Formula = "=INDEX(A1:C5," & Cells(1, i + 5).Value & "," & Cells(2, 6).Value & ")"
    Next
End Sub

数式は変わっていますが、結果は同じですね。

数式の結果を個々に挿入する

数式ではなく、数式の算出結果を挿入してみましょう。

Sub index関数の結果を一括挿入する1()
    For i = 1 To 5
        Cells(3, 5 + i).Value = WorksheetFunction.Index(Range("A1:C5"), Cells(1, i + 5).Value, Cells(2, 6).Value)
    Next
End Sub

Excelワークシート関数をVBAで使うにはWorksheetFunctionをワークシート関数の頭につけます。引数の部分はRangeオブジェクトとCellsオブジェクトを使います。

はい、数式ではなく数式の算出した結果が入力されましたね。

INDEX関数ではなくCellsオブジェクトを使い同じ結果を得る

次はINDEX関数ではなくCellsオブジェクトを使い同じ結果を出してみましょう。

Sub index関数の結果を一括挿入する2()
    For i = 1 To 5
        Cells(3, 5 + i).Value = Cells(Cells(1, i + 5).Value, Cells(2, 6).Value).Value
    Next
End Sub

 
Cellsオブジェクトを使い、引数を出力先セル上部にあるセルの値から取得します。

はい、同じ結果になりましたね。

また、引数に値を直接入力しても同じ結果を得ることができます。

Sub index関数の結果を一括挿入する3()
    For i = 1 To 5
        Cells(3, 5 + i).Value = Cells(i, 1).Value
    Next
End Sub

処理結果です。

タカヒロ

Cellsオブジェクトを使う方法が一番シンプルですね。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、
Excel INDEX関数の使い方を基本から応用、そしてVBAによる一括設定の方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。