INDIRECT関数で別シート参照時の#REF!エラーへの対処法

ここでは、「INDIRECT関数の別シート参照で#REF!エラーになる時の対処法」を解説します。

これで、IINDIRECT関数でシート名が参照できないエラーを解消することができます。

始めに、INDIRECT関数の別シート参照で#REF!エラーになる状況を、例を含めて確認します。

１－１．シート名を適切に指定できていない

INDIRECT関数の別シート参照で#REF!エラーになるのは、「シート名を適切に指定できていない」からです。

普通に考えると、シート名が「Sheet1」なら「Sheet1」と入力されたセルを指定すればいいだけと思いますが、これだと#REF!エラーになってしまうことがあります。

１－２．エラーになった例を確認

次に、エラーになってしまった例を確認します。

例で使うのは、「シートごとに分けた四半期ごとの売上表（シートは全部で4つ）」と「年間シートの売上表（シート1つ）」です。

四半期ごとの売上金額を年間の売上表へ、INDIRECT関数をかませて手間なくリンクさせたいとします。

上は、「四半期ごとの売上表」の1～3月分のシートです。

その他の四半期ごとのシートも対象月が違うだけで、表の作りやセルの位置は同じとします。

そして、上が「年間売上表」です。

ちなみに「年間売上表」のセルB3からE3までの値は、「四半期売上表」のシート名と一致させています。

これは、年間売上表のセルの位置によってINDIRECT関数で参照するシートを変えるためです。

 年間売上表のセルB4への数式は、

=INDIRECT(B3&"!B4")

と入れました。

数式の意味は「『1～3月（セルB3の値）』シートのセルB4を参照しなさい」で、参照先のセル（B3）の値に置き換えると数式は、

=1～3月!B4

となり、問題なさそうです。

しかし…

結果は、#REF!エラーとなってしまいました。

原因はどこにあるのでしょうか？

INDIRECT関数の別シート参照で#REF!エラーになる直接の原因は、「シート名にある記号『～』」です。

シート名に「～」や「－」、「（」や「）」といった記号が使われていると、先ほどの数式ではダメのです。

では、もっとシンプルな例を見てみましょう。

例えば、上のようにSheet1のセルA1に「テスト」という値が入っていたとします。

そして、Sheet2に「Sheet1のセルA1」の値を数式で表示させたい場合、「Sheet2のセルA1を選択」⇒「『=』入力後、シートSheet1をクリック」⇒「セルA1をクリック」という操作をすると、式は

=Sheet1!A1

となります。

この場合は、例えばセルA5に入れた「Sheet1」という値を使って

=INDIRECT(A5&"!A1")

という数式を書いてもエラーにはなりません。

ちなみに、置き換えると数式は、

=Sheet1!A1

となり、先ほどの数式と一致します。

次に、「～」というシートのセルA1に「テスト2」という値が入っていたとします。

そして、Sheet3に「～シートのセルA1」の値を参照する数式を、INDIRECT関数を使わないで作ると、

='~'!A1

となり、「『'』がシート名『～』の前後につく」のが分かります。

この場合は、セルA5に入れた「～」という値を使って

=INDIRECT(A5&"!A1")

という数式を書いても「#REF!エラー」になります。

置き換えると数式は、

=～!A1

となり、「'」が不足しているのが分かります。

つまり、INDIRECT関数でシート名を指定する時、「シート名に『～』や『－』、『（』や『）』の記号が使われている場合には『'』が必要」なのです。

最後に、#REF!エラーの回避方法と、エラーにならない分かりやすい数式の作り方についてです。

３－１．#REF!エラーの回避方法

まず、#REF!エラーの回避方法は、「シート名の前後に『’』をつける」ことです。

ただし、「『'』は『"』で囲む必要もあります」。

例えば先ほどの年間売上表の場合、正しい数式は、

=INDIRECT("'"&B3&"'!B4")

です。

クォーテーションがたくさんあって分かりづらいですが、INDIRECT(の後は「"」→「'」→「"」。&B3&の後は「"」→「'」→「!B4」→「"」です。

まず前提として、この数式は「各シートのセルB4を参照」させるためのもので、シート名だけを可変にしています。

そのためINDIRECT関数の数式は、「『可変のシート名』と『固定のセルB4』を「&」でつなげた形」になります。

そしてそもそもINDIRECT関数では、可変の部分はそのまま、固定の部分（例だと「'」や「'!B4」）は「"」で囲む必要があります。

この点を踏まえて一つ一つ解説すると、「=INDIRECT（」の次は「”」→「'」→「”」です。つまり、「'」を固定しています。

そして、シート名が入る部分はセルB3を参照する可変なので、「＆B3＆」となります。

その後「”」→「'!B4」→「”」で「'!B4」を固定します。

以上のことから数式は、

=INDIRECT("'"&B3&"'!B4")

となるのです。

３－２．エラーにならない分かりやすい数式の作り方

ただ、実際に数式を作るとなると、こんがらがってしまうものです。

そこで、INDIRECT関数でシート名を可変にする場合のおすすめは、「INDIRECT関数を使わない数式の形を見ながら作る」です。

例えば先ほどの数式は、INDIRECT関数を使わない場合、上のように

='1～3月'!B4

となります。

なので、INDIRECT関数の場合でも使わない数式を可変と固定に分けて、「シート名の前後に『'』が文字列として入るように固定部分を『"』でくくる」とよいのです。

このように、INDIRECT関数で#REF!エラーが出た場合には「INDIRECT関数を使わない数式」を作ってみて、その形を見ながら作り直すとよいでしょう。

以上、参考になれば幸いです。

関数の問題解決記事一覧へはこちら