Excel の INDIRECT 関数でシートを参照するときに #REF! にならない方法

2020年1月27日

例えば、顧客別のシートとそれらを集計する全体集計のシートを作るような場合、INDIRECT 関数を使うと便利な場合があります。しかし、シート名に特殊な文字が含まれていると、#REF! のエラーが出てしまいます。例えばこんな感じ。

このエラーを回避するための方法として、よく紹介されているのは、シート名をシングルクォーテーションで囲む方法です。

画像を見てもわかりにくいですが、最初の部分は「ダブルクォーテーション・シングルクォーテーション・ダブルクォーテーション」、セルの指定の後は「ダブルクォーテーション・シングルクォーテーション・エクスクラメーション」になっています。
文字の連結をしているので難しく見えますが、INDIRECT('山田屋(1)’!a1) のようにしているわけです。

この方法を使うことで、#REF! エラーを回避できるのは、シート名に括弧「（」「）」、ハイフン「ー」、チルダ「～」、アンパサンド「＆」などの記号が含まれている場合です。シングルクォーテーションが含まれている場合は、やはりエラーになってしまいます。

シングルクォーテーションを含むシート名の場合、シート名をシングルクォーテーションで囲む上記の対策を行うと、INDIRECT('Yamada’s Shop’!a1) になりますが、このままだとシート名をシングルクォーテーションで正しく囲めていないからです。

シングルクォーテーションで囲まれている中にシングルクォーテーションを入力したい場合、Excel ではシングルクォーテーションを2つ重ねます。つまり、INDIRECT('Yamada"s Shop’!a1) のようにすれば OK です。

これを関数を使って行うには、SUBSTITUTE 関数を使って、1つのシングルクォーテーションを2つのシングルクォーテーションに置換します。