状況
深夜2時、スプレッドシートのダッシュボードが壊れていて、サマリータブに #REF! エラーしか表示されないと3人からすでにメッセージが届いています。原因を追うと、先週まで正常に動いていた INDIRECT 関数の集まりにたどり着きました。誰かがシート名を SalesData から Sales Data Q2 に変更するまでは。
表示されているエラー：
Error: Function INDIRECT parameter 1 value 'My Sheet!A1' is not a valid cell/range reference.
数式はこのようなものでした：
=INDIRECT("My Sheet!A1")
至って普通の書き方です。でも完全に壊れています。
なぜこうなるのか
Google スプレッドシートでは、スペースや特殊文字（-、(、)、&）を含むシート名、または数字で始まるシート名にはシングルクォートが必要です。数式を直接入力する場合、スプレッドシートが自動的にクォートを付加してくれます。しかし INDIRECT 関数にはそのような補助機能がありません。プレーンテキストの文字列を受け取り、自分で参照を組み立てるため、クォートの自動付加は行われません。
文字列内でクォートを省略すると、スプレッドシートは無効な参照とみなします。これが #REF! の原因です。
通常の数式（スプレッドシートが内部でクォートを処理）：
='My Sheet'!A1 ✓ 動作する
INDIRECT に生の文字列を渡した場合（クォートなし）：
=INDIRECT("My Sheet!A1") ✗ 壊れる — #REF!
デバッグの手順
ステップ 1：シート名が問題かどうかを確認する
簡単なサニティチェックを行います。空白のセルに、スペースや特殊文字を含まないクリーンな名前のシートを参照してみましょう：
=INDIRECT("Sheet1!A1")
これが動作する場合、問題は数式のロジックや対象セルではなく、シート名のフォーマットにあります。
ステップ 2：INDIRECT 文字列の実際の内容を確認する
参照文字列がセルの値から動的に組み立てられている場合、まずそれを単独で確認します。空いているセルに以下を入力してください：
=A1&"!B2"
出力を確認します。My Sheet!B2 と表示されていれば、それが壊れた文字列です。シート名をシングルクォートで囲んだ 'My Sheet'!B2 が必要です。
ステップ 3：クォートが必要なシート名を特定する
以下のパターンはすべて、INDIRECT 文字列内でシングルクォートが必要です：
- スペースを含む：
Sales Data、Q1 Report - 特殊文字を含む：
Revenue-2024、Data (Final)、Team & Results - 数字で始まる：
2024 Budget - アポストロフィ自体を含む（厄介なエッジケース — 後述）
修正方法
オプション 1：INDIRECT にシート名をハードコードする
文字列内でシート名をシングルクォートで囲みます：
=INDIRECT("'My Sheet'!A1")
外側のダブルクォートは文字列の区切り文字です。内側のシングルクォートがシート名を囲みます。スプレッドシートは 'My Sheet'!A1 を正しく解析し、期待したセルの値を返します。
オプション 2：セルからシート名を動的に取得する
セル A1 に My Sheet というテキストが入っているとします。その周りにシングルクォートを連結します：
=INDIRECT("'"&A1&"'!B2")
内訳：
"'"— 開きシングルクォート（1文字の文字列）&A1&— セル A1 からのシート名"'!B2"— 閉じシングルクォートとセル参照
これにより 'My Sheet'!B2 が組み立てられ、INDIRECT は問題なく解析します。
オプション 3：シート名とセル参照を両方動的に取得する
両方の値をセルから取得する場合：
=INDIRECT("'"&A1&"'!"&B1)
ここで A1 にシート名、B1 に C5 や D12 のようなセルアドレスが入っています。
エッジケース：シート名にアポストロフィが含まれる場合
誰かがタブを Manager's Report と名付けたとします。クォートの中にクォートが入るという問題が発生します。INDIRECT 文字列内では、アポストロフィを二重にすることでエスケープします：
=INDIRECT("'Manager''s Report'!A1")
ラッパーの内側で連続する2つのシングルクォート（''）は、閉じクォートではなくリテラルのアポストロフィを意味します。
アポストロフィを含む可能性がある動的なシート名の場合は、SUBSTITUTE を使ってエスケープを自動的に処理します：
=INDIRECT("'"&SUBSTITUTE(A1,"'","''")&"'!B1")
これはすぐに複雑になります。長期的により良い修正方法は、シートの名前を変更してアポストロフィを完全に取り除くことです。
確認
修正を適用した後：
- 目視確認：
#REF!エラーが消え、対象シートから期待した値がセルに表示されます。 - 数式の監査：セルをクリックして数式バーを確認し、INDIRECT 文字列がシート名をシングルクォートで囲んでいることを確かめます。
- 行をまたいでテスト：動的な数式は 20〜50 行にわたることがよくあります。シート名が異なるいくつかの行を抽出して確認し、すべてのケースでクォートのロジックが正しく機能しているかチェックします。
- 名前変更テスト：対象シートを一時的にクリーンな名前（スペースなし）に変更すると、数式は再び壊れるはずです。元の名前に戻すと正常に動作するはずです。これにより修正を切り分け、他の変数を排除できます。
再発防止
このエラーは、動的参照に INDIRECT を使用するダッシュボードで最も深刻な影響を与えます。開発中はシート名がクリーンで数式は問題なく動作しています。そして誰かが「わかりやすい」名前にタブを変更した途端、月曜日の朝に50個の壊れたセルを見つめることになります。
これを防ぐためのいくつかの習慣：
- INDIRECT では常にシート名をクォートで囲む。名前が安全に見えても同様です。
=INDIRECT("'Sheet1'!A1")は=INDIRECT("Sheet1!A1")と同じように動作します。クォートは不要なときでも無害ですが、名前が変わったときには不可欠です。 - ヘルパー列を使用して、クォート付きの完全な参照文字列を組み立て、それを INDIRECT に渡します。1つの独立したセルをデバッグする方が、50個のネストされた連結を監査するよりもはるかに簡単です。
- シートの命名規則を設ける。他の人がブックを編集する場合は、コントロールタブのコメントに記載しましょう。「シート名にスペースを使わない」という一行だけでも、このクラスのバグ全体を防ぐことができます。
- 可能な場合は INDIRECT の代わりに名前付き範囲を検討する。名前付き範囲はシートの名前が変更されてもデータを追跡しますが、INDIRECT 数式は黙って壊れます。