【Excel関数】indirect:文字列を指定してセルを参照する

説明

indirectは文字列を指定してセルを参照することができる関数です。

この説明だと分かりにくいので、例を挙げて説明します。

ケース(例)

たとえば、A1のセルの値を参照したいとします。A1のセルの値を参照する場合の数式は次のように記述します。

=A1

なお、indirectを使ってもA1のセルを参照することができます。indirectを使ってA1の値を参照するには次のように記述します。

=indirect(“A1”)

ここで注目すべきは、indirectはダブルクオーテーションで囲んで指定するところです。

ちなみに、次のようにダブルクォーテーションを使わずに指定するとエラーとなります。

=indirect(A1)

(※エラーの値:#REF!)

各計算式と結果(戻り値)

ここまでの説明では分かりにくいので、まとめてみました。

例として、セルA1に「ABCDE」と入っている場合の数式の結果(戻り値)は次の通りです。

数式(計算式)
結果(戻り値)
備考
ケース1
=A1
ABCDE
ダブルクォーテーション無し
ケース2
=A1
A1
ダブルクォーテーションあり
ケース3
=indirect(A1)
エラー(エラーの値:#REF!)
ダブルクォーテーション無し
ケース4
=indirect(A1)
ABCDE
ダブルクォーテーションあり
【ケース1】

【ケース2】

【ケース3】

【ケース4】

以上の検証結果を見て分かるとおり、indirectを使ってセルの値を参照する場合は、パラメータにセルを「”(ダブルクォーテーション)」で囲んで使わないとエラーになるので、「”(ダブルクォーテーション)」を忘れずに使う必要があります。

indirectはどういう時に使うの?(応用)

indirectの使い方を説明しましたが、どういう特に使う関数なのか分かりにくいですよね。では、indirectはどういう時に使うのかというと、次の2点です。

  1. 何らかの計算式をindirectのパラメータに指定してセルを参照するとき
  2. Excelのセルをindirectのパラメータに指定してセルを参照するとき

①何らかの計算式をindirectのパラメータに指定してセルを参照するとき

1つ目の使い方として、何らかの計算式をindirectのパラメータに指定してセルを参照するときに使います。

【使用例①】

たとえば、Excelに10行分のデータ(※ヘッダ行無し)が入っており、セルB10には「埼玉県」とデータが入っているとします。

以上のデータでは、B列の最後尾のセルは「埼玉県」になりますが、この「埼玉県」をindirectを使って取得したい場合、次の通りに記述します。

=INDIRECT(“B”&MAX(IFERROR(MATCH(MAX(B:B)+1,B:B, 1),0),IFERROR(MATCH(“”,B:B,-1),0)))

上記の記述では、B列と最終行(※つまり10)を結合した値をIndirectのパラメータに指定しています。(※最終行は、match関数とmax関数を使用して取得)

説明
値または数式
B列
“B”
最終行
MAX(IFERROR(MATCH(MAX(B:B)+1,B:B, 1),0),IFERROR(MATCH(“”,B:B,-1),0))
では、実際に上記のパラメータを使った結果は次の通りです。

B列の最終行であるセルB10の値「埼玉県」が、セルC1にも出力されています。

以上のように、何らかの計算式で取得した値を使ってセルを参照するにはindirectを使います。

ちなみに、参考としてサンプルファイルを用意しましたのでお試しください。

⇒サンプルExelファイル

ちなみに、indirectを使わずに、例えば次の使い方をしても「埼玉県」は取得できません。結果はB10と出力されます。

=”B”&MAX(IFERROR(MATCH(MAX(B:B)+1,B:B, 1),0),IFERROR(MATCH(“”,B:B,-1),0))

上記のように計算式を使ってもセルを参照することは出来ないので、indirectを使う必要があります。

ちなみに、参考としてサンプルファイルを用意しましたのでお試しください。

⇒サンプルExelファイル

【使用例②】

たとえば、Excelに10行分のデータ(※ヘッダ行無し)が入っており、各県別に値が設定されているとします。そして、県名はB列、値はC列に設定されています。

上記データの中から、「福岡県」の値がいくつなのかを取得したい場合は次の通りに記述します。

出力結果はセルD1に「76」と出力されています。福岡県の値はD4の値76なので、正しく取得されているのが分かります。

ちなみに、参考としてサンプルファイルを用意しましたのでお試しください。

⇒サンプルExelファイル

②Excelのセルをindirectのパラメータに指定してセルを参照するとき

2つ目の使い方として、Excelのセルをindirectのパラメータに指定してセルを参照するときに使います。

たとえば、セルE14に「B」、セルE15に「12」と値が入っているとします。

indirectなら、セルE14とセルE15をパラメータに指定することでB12の値を参照することができます。記述の仕方は次のとおりです。

=INDIRECT(E14&E15)

セルC12と同じ値が出力されています。

もしセルに入った文字列を使ってセルの値を参照したい場面で使うと良いです。

ちなみに、参考としてサンプルファイルを用意しましたのでお試しください。

⇒サンプルExelファイル