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

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

INDIRECT 関数で #REF! のエラーが出るパターン その1

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

INDIRECT 関数で #REF! のエラーが出るパターン その2

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

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

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

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

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

正しい INDIRECT 関数でのシート名の参照の仕方

これでシート名が何であっても INDIRECT 関数で参照することができます。

備考

他のプログラミング言語をかじっていると、シート名に半角の括弧などが含まれているとダメっぽいのは何となく想像がつくので、括弧を全角にしてみたりするのですが、全角にしてもエラーは回避できません。どうしてこんな仕様になってしまったのか、謎ではありますが。。

この記事を書いた人

グッドネイバー

“ Webに悩むお客さまの「よき隣人」でありたい ” をモットーに、Web システム開発(主に Laravel)、Web マーケティング支援の仕事をしています。詳しい業務内容はこちら。お仕事のご依頼・ご相談はこちらからお気軽にどうぞ。

Excel