Excel VBA

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

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

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

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

・そもそもINDIRECT関数がどのような機能であるかわからない
・INDIRECT関数の使い方を知りたいが説明がよくわからない
・VBAを使いINDIRECT関数を一括設定したいが方法がわからない

ですよね。

今回はそんなお悩みを解決する

・Excel INDIRECT関数の使い方の基本から応用
・VBAによるINDIRECT関数の一括設定方法

についてまとめます！

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

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

INDIRECT関数とはA1などセルの番地を指定し、該当するセルの値を取得する処理のことをいいます。
似たような関数にINDIRECT関数がありますが、INDIRECT関数は行と列の番号を指定します。

こんな表があり、

値をとりたいセルをセルの番地で指定して、値を取得していきます。

別シートのセルの値も取得できます。

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

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

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

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

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

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

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

引数の文字列に指定する値をF1以降に設定します。

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

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

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

関数名 INDIRECT
機能 参照検索・行列によって指定されるセルに入力されている検索・行列を介して、間接的にセルを指定します。
書式 =INDIRECT(参照文字列, ［参照形式］)
引数 参照文字列：参照したいセルの位置を番地で指定します。
参照形式：
「TRUE」A1形式のセル参照となります。省略の場合デフォルト値となります。
「FALSE」R1C1形式のセル参照となります。
エラー値 #REF

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

A1形式で指定する

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

まずは、セル参照先の番地をA1形式で指定してみます。

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

数式を以下のように入力し、

=INDIRECT(F1)

J列までドラッグしていきます。

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

R1C1形式で指定する

次は参照先セルの番地をR1C1形式で指定してみましょう。

F1以降の番地をR1C1形式へ変更します。

数式を以下のようにし、J1までドラッグします。

=INDIRECT(F1,FALSE)

こちらも、参照範囲の1列目の値がすべてとれましたね。

INDIRECT関数で指定したセルの値を取得する

A1形式で指定する

INDIRECT関数で指定した番地のセルの値を取得してみましょう。
番地をA1形式で指定してみます。

F1の数式を以下のように入力し、

=INDIRECT(F1)

H列までドラッグしていきます。

はい、指定した番地のセルの値が取得できましたね。

R1C1形式で指定する

次は参照先セルの番地をR1C1形式で指定してみましょう。

F1～H1の値をR1C1形式に変更します。

こちらも、参照範囲の1列目の値がすべてとれましたね。

INDIRECT関数で指定した別シートのセルの値を取得する

INDIRECT関数で指定した番地にシート名を加え、別シートのセルの値を取得してみましょう。

セル参照先を「別シート」にあるA列にし、値を取得していきます。

数式はA1形式と同様です。F1からJ1まで「別シート!**1」と入力します。

はい、別シートのセルの値が取得できましたね。

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

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

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

文字列がセル番地の形式が異なる場合にエラーとなります。

例えば番地をA1形式としているけど、空だったりスペース文字やR1C1形式だったりA1形式以外の文字列を指定している場合が該当します。

また別シートや別ブックを参照している場合、シート名やブック名が存在しない場合もエラーとなります。

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

セル番地の形式にあった文字列を指定するようにしましょう。

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

=IFERROR(INDIRECT(I1),"該当なし")

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

INDIRECT関数で0となった場合の原因と回避方法

INDIRECT関数で0となった場合の原因と回避方法について説明をします。

INDIRECT関数の結果が0となる原因

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

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

INDIRECT関数の結果が0となる回避方法

参照範囲内の番地を指定するようにしましょう。

エラー表記されないため、以下のようにIF関数を使い0判定をします。

=IF(INDIRECT(I1)=0,"該当なし",INDIRECT(I1))

応用：INDIRECT関数とMATCH関数でVLOOKUP関数の2つ目の結果を出す

関数の説明

INDIRECT関数とMATCH関数でVLOOKUP関数の2つ目の結果を出すこともできます。

サンプルデータは以下の通り、A列に都道府県、B列に名前が入力されています。

検索キーをE2の「千葉県」に設定し、1番目は通常のVLOOKUP関数で出力します。

次に2番目の検索条件にマッチした結果を出していきます。

MATCH関数で得た一つ目の検索位置をINDIRECT関数でアドレス指定するやり方で、一つ目以降の検索範囲をVLOOKUP関数へ指定していきます。

=VLOOKUP(E2,INDIRECT(“A”&MATCH($E$2,A$1:A$13,0)+1 &”:B13″),2,FALSE)

2番目の検索結果が表示されていますね。

 

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

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

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

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

タカヒロ
タカヒロ
ここからこの記事のメインとなります。

Formulaで数式を挿入する

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

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

A1形式で指定する

まずは参照先セルの番地指である、INDIRECTの第一引数をA1形式で指定してみましょう。

サンプルの表は関数版と同じものを利用します。

VBAは以下の通りです。

Sub INDIRECT関数を一括挿入する1()
    For i = 1 To 5
        Cells(2, 5 + i).Formula = "=INDIRECT(" & Cells(1, i + 5).Address & ")"
    Next

End Sub

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

For i = 1 To 5

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

Cells(2, 5 + i).Formula = “=INDIRECT(” & Cells(1, i + 5).Address & “)”

CellsオブジェクトのAddressプロパティでセルの位置情報をA1形式で取得しています。

VBAを実行しましょう。

はい、できましたね。

なお、A1形式はデフォルト設定ですので省略OKですが、以下のように第二引数にTRUEを指定しても同じ結果が得られます。

Sub INDIRECT関数を一括挿入する2_1()
    For i = 1 To 5
        Cells(2, 5 + i).Formula = "=INDIRECT(" & Cells(1, i + 5).Address & ",TRUE)"
    Next

End Sub

R1C1形式で指定する

次は参照先セルの番地をR1C1形式で指定してみましょう。

サンプルの表は関数版と同じものを利用します。

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

VBAを実行しましょう。

結果はA1形式と同じですね。

また、FormulaプロパティではなくR1C1形式対応のFormulaR1C1プロパティでも同様の結果を得ることができます。

Sub INDIRECT関数を一括挿入する2_2()
    For i = 1 To 5
        Cells(2, 5 + i).FormulaR1C1 = "=INDIRECT(""" & Cells(1, i + 5).Value & """,FALSE)"
    Next
End Sub

INDIRECT関数の結果を挿入する

INDIRECT関数の数式ではなく、INDIRECT関数の結果を直接挿入するようにしてみましょう。

ワークシート関数をVBAで扱うにはWorksheetFunctionを使いますが、INDIRECT関数は対応していないのでRangeオブジェクトで代用する形となります。

こちらはエラーとなります。

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

こちらを使用します。
RangeオブジェクトとCellsオブジェクトを使い、指定セルの値を出力します。

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

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

VBAを実行する

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

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

②「完了しました。」が表示されたら、処理結果を確認しましょう。

さいごに

いかがでしょうか。

今回は、

・Excel INDIRECT関数の使い方の基本から応用
・VBAによるINDIRECT関数の一括設定方法

についてまとめました。

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

RECOMMENDこちらの記事も人気です。