ExcelのINDIRECT関数｜別シート参照と#REF!の5原因

スポンサーリンク

「支店ごとにシートが分かれた表を、数式1つで切り替えて集計したい」。ExcelのINDIRECT関数にたどり着く方の多くは、ここからスタートしますよね。

ところが実際に組んでみると、こんな壁にぶつかります。

  • 別シートを参照した瞬間に #REF! が出る
  • 式は合っているように見えるのに、どこが悪いのかわからない
  • おまけに、ファイルまで重くなってきた

つまずきの正体は、たいてい「文字列の組み立て方」と「揮発性（再計算が多い性質）」の2つ。この2点さえ押さえれば、怖い関数ではありません。

この記事では、INDIRECT関数の使い方を別シート参照の書き方から整理しました。#REF! が消えない5つの原因と確認手順もセットで解説。「重い」ときにINDEXへ置き換える判断基準も、早見表にまとめました。

  1. ExcelのINDIRECT関数とは？文字列をセル参照に変える関数
    1. 基本構文と引数（参照文字列／参照形式）
    2. =A1 と何が違う？最小例で確認する
  2. INDIRECTで別シートを参照する書き方｜シート名をセルで切り替える
    1. 基本形｜シート名を入れたセルと「!セル番地」をつなぐ
    2. シート名にスペースや記号があるときはシングルクォートで囲む
    3. 動かないときの1行デバッグ術｜INDIRECTを外して文字列だけ表示する
    4. SUM・VLOOKUPと組み合わせてシート別に集計する
    5. 選択に連動するプルダウンを作る4ステップ
  3. INDIRECTが#REF!になる5つの原因と直し方
    1. 症状別・原因の切り分け早見表
    2. 原因1・2｜クォート漏れと不可視スペース
    3. 原因3｜参照先のブックが閉じている（回避策3択）
    4. 原因4｜名前の定義が未登録・表記ゆれ
    5. 原因5｜シート名を後からリネームした
  4. 「重い」「使わない方がいい」の理由とINDIRECTをやめる判断基準
    1. 揮発性関数とは？INDIRECTが再計算を増やす仕組み
    2. INDIRECTを使わない代替早見表（用途×代替手段×揮発性）
    3. INDIRECTでしか書けない唯一のケース
    4. 重くなったファイルを軽くする初手
  5. よくある質問｜INDIRECT関数
    1. Q. シート名を変えたら壊れました。追従させる方法はありますか？
    2. Q. Web版Excelでも使えますか？
    3. Q. Googleスプレッドシートでも同じ書き方ですか？
    4. Q. INDIRECTは何個までなら重くなりませんか？
    5. Q. OFFSETとINDIRECTはどちらを使うべきですか？
    6. Q. エラーを事前に防ぐ方法はありますか？
  6. まとめ｜場面別のINDIRECT早見表

ExcelのINDIRECT関数とは？文字列をセル参照に変える関数

INDIRECT関数とは、文字列で書かれたセル番地やシート名を、実際のセル参照に変える関数です。読み方は「インダイレクト」。英語の indirect（間接的な）が語源です。

セルを直接指さず、文字列を経由して間接的に参照することが名前の由来。この「間接性」が便利さの源であり、エラーと重さの原因でもあります。

対応バージョン: INDIRECT関数そのものは Excel 2003 以降のすべてのバージョンで使えます。ただし外部ブック（別ファイル）への参照は Web版Excel（ブラウザ版）では新規作成できません。記事後半では FILTER などのスピル関数にも触れます。こちらは Microsoft 365 / Excel 2021 以降が対象です。

基本構文と引数（参照文字列／参照形式）

構文はシンプルで、覚える引数は実質1つだけ。

=INDIRECT(参照文字列, [参照形式])
引数必須/省略可説明
参照文字列必須セル参照を表す文字列。セル番地・シート付き番地・範囲・名前の定義を指定できる
参照形式省略可TRUE（省略時の既定）＝A1形式で解釈。FALSE＝R1C1形式で解釈

第2引数は、ほとんどの場面で省略してかまいません。R1C1形式（行も列もアルファベットではなく数値で表す参照方式）を使う機会はまれ。基本は第1引数だけで十分です。

=A1 と何が違う？最小例で確認する

普通の数式は =B2 のように参照先を直接書きます。対してINDIRECTは、「B2」という文字を受け取ってからB2を参照する仕組み。

セルA1に「B2」と入力し、別のセルに次の数式を入れてみてください。

=INDIRECT(A1)

これでセルB2の値が返ります。A1を「C5」に書き換えれば、数式はそのままで参照先がC5に変わる。この「後から参照先を動かせる」点こそがINDIRECTの存在意義です。

逆に言うと、切り替える必要のない参照にINDIRECTを使う理由はありません。

INDIRECTで別シートを参照する書き方｜シート名をセルで切り替える

INDIRECTが本領を発揮するのは、支店別・部署別・月別にシートが分かれた表の集計です。同時に、ここがエラーの最大の発生源。クォートのルールを正確に押さえましょう。

基本形｜シート名を入れたセルと「!セル番地」をつなぐ

セルA1にシート名が入っている前提で見てみましょう。

=INDIRECT(A1&"!B2")

A1が「1月」なら、A1&"!B2" は「1月!B2」という文字列になります。INDIRECTがこれを参照に変換し、1月シートのB2の値を返します。

A1を「2月」に変えるだけで、参照先も2月シートに切り替わる。数式は1文字も書き換えません。月次レポートで重宝するパターンですよ。

シート名にスペースや記号があるときはシングルクォートで囲む

ここが最大の落とし穴。シート名にスペースやハイフンなどの記号が含まれていると、さきほどの書き方では #REF! になります。

シート名が「売上 データ」のように空白を含むなら、正しくはこう書きます。

=INDIRECT("'"&A1&"'!B2")

分解すれば3ブロックだけ。

  • "'" … 開きのシングルクォート1文字
  • A1 … シート名（例:「売上 データ」）
  • "'!B2" … 閉じのシングルクォートと、セル番地

つながると '売上 データ'!B2 になり、Excelの正式なシート参照の形と一致します。手入力ならExcelが自動で付けてくれるクォートを、INDIRECTでは自分で組み込むわけです。

迷ったら常にクォート付きで書くのが安全。スペースのないシート名に付けても、問題なく動きますよ。

動かないときの1行デバッグ術｜INDIRECTを外して文字列だけ表示する

#REF! が出たら、真っ先にやってほしい確認があります。空いているセルに、INDIRECTのカッコの中身だけを入力してみてください。

="'"&A1&"'!B2"

すると '売上 データ'!B2 のように、受け取っている文字列が画面に出ます。ここが正しい形なら、INDIRECT側は必ず正しく参照してくれるはず。

先頭のクォートが抜けていたら、数式の "'" が欠けています。エラーの原因は、たいていこの文字列の組み立て方。1行足すだけで自己解決できますよ。

SUM・VLOOKUPと組み合わせてシート別に集計する

INDIRECTは範囲も返せるので、SUM関数の引数にもそのまま入ります。

=SUM(INDIRECT(A1&"!B2:B10"))

A1で選んだシートのB2:B10だけを合計する数式です。支店を切り替えるたびに数式を直す作業から解放されますね。

VLOOKUP関数（表を縦に検索して該当行の値を取り出す関数）の検索範囲も、同じ要領で切り替えられます。

=VLOOKUP(A2,INDIRECT(B1&"!A:C"),2,0)

B1にシート名（部署名）を入れておきます。B1を変えるだけで、同じ数式のまま検索先が切り替わる。スペース入りのシート名なら、クォート付きの形に直してくださいね。

選択に連動するプルダウンを作る4ステップ

「大分類で『果物』を選ぶと、小分類に『リンゴ・ミカン・バナナ』だけが並ぶ」。この連動プルダウンも、INDIRECTの代表的な使い道です。

  1. カテゴリごとに、選択肢を縦方向に並べておきます。
  2. 各範囲を選択し「数式」タブの「名前の定義」へ。カテゴリ名と完全一致する名前を付けます。
  3. 大分類のセル（例: B2）に「データの入力規則」でリストを設定します。ソースは「果物,野菜」とカンマ区切り。
  4. 小分類のセル（例: C2）の入力規則のソースに =INDIRECT(B2) と入力します。

B2で「果物」が選ばれると、INDIRECTはその文字列を名前の定義として解釈します。名前の定義とは、セル範囲に付けた呼び名のこと。登録された範囲が、そのまま小分類の選択肢になる仕組みです。

つまずきやすいのは名前の付け方。名前の定義にはスペースが使えないため、カテゴリ名に空白が入ると登録できません。

=INDIRECT(SUBSTITUTE(B2," ","_"))

SUBSTITUTE関数（文字列の一部を別の文字に置き換える関数）を挟む形です。空白を _ に変換してから、名前を呼び出します。

名前の登録手順はExcelの名前の定義、入力規則の操作はExcelのプルダウン（ドロップダウンリスト）完全ガイドへ。手が止まったら、あわせて読んでみてください。

INDIRECTが#REF!になる5つの原因と直し方

INDIRECTのエラーは、ほぼ #REF! の一択です。#REF! は「参照先が見つからない」という意味のエラー値。ほかのエラー値との違いはExcelのエラー値一覧と対処法にまとめています。

やっかいなのは、同じ表示なのに原因が5通りある点。まずは早見表で、自分の症状がどれに当たるかを絞り込みましょう。

症状別・原因の切り分け早見表

症状・心当たり原因1分でできる確認対処
シート名にスペース・記号がある1. クォート漏れ="'"&A1&"'!B2" で文字列を目視"'"&A1&"'! の形に直す
他システムからコピーした値を参照2. 不可視スペース=LEN(A1) で文字数を数えるTRIM／SUBSTITUTEで空白を削除
別ファイル（別ブック）を参照3. ブックが閉じているそのブックを開くと直るか試す回避策3択（後述）
連動プルダウン・名前を参照4. 名前が未登録・表記ゆれ「名前の管理」で一覧と突合名前を登録／表記を揃える
最近シート名を変更した5. リネームに追従しない数式内の文字列と実際の名前を比較文字列を書き換える

上から順に見ていけば、たいてい数分で原因は特定できます。ここからは原因別に直し方を確認しましょう。

原因1・2｜クォート漏れと不可視スペース

原因1は、シングルクォートの付け忘れ。シート名に空白・ハイフン・記号が含まれると、クォートなしでは参照が成立しません。

=INDIRECT(A1&"!B2")        ← シート名が「売上 データ」だと #REF!
=INDIRECT("'"&A1&"'!B2")   ← これが正解

前述の1行デバッグ術で文字列を表示すれば、クォートの有無はひと目で確認できます。

原因2は、参照文字列に紛れ込んだ目に見えない空白です。他システムから貼り付けた値でよく起こります。「東京」のつもりが「東京 」（末尾に空白）なら、シート名と一致せずエラーに。

切り分けは =LEN(A1) で文字数を数えるだけ。「東京」なら2のはず。3以上が返るなら、余計な文字が混ざっています。

=INDIRECT(TRIM(A1)&"!B2")

TRIM関数（文字列の前後にある余分な半角スペースを削除する関数）で挟めば、たいていは直ります。

ただしTRIMが消せるのは半角スペースだけ。全角スペースや、Webからのコピーに多いCHAR(160)には効きません。その場合はSUBSTITUTEで半角スペースに変換してから渡しましょう。

=INDIRECT(TRIM(SUBSTITUTE(A1,"　"," "))&"!B2")

"　" の中身には、全角スペースを直接入力してくださいね。

原因3｜参照先のブックが閉じている（回避策3択）

別のExcelファイルをINDIRECTで参照する場合、そのファイルを開いていないと #REF! になります。不具合ではなく仕様です。

比べてみましょう。普通のリンク数式（=[Book1.xlsx]Sheet1!A1 の形）なら、相手を閉じていても値が残ります。最後に取得した値をキャッシュとして保持するからです。

一方のINDIRECTは、毎回文字列を評価して参照を組み立て直す仕組み。だからキャッシュが効きません。回避策は次の3つです。

回避策手軽さ自動更新Web版Excel
参照先ブックを開いたまま使う設定は不要。2ファイルを同時に開く負担はある開いている間はリアルタイム外部ブック参照の新規作成ができない
通常のリンク数式に切り替える数式を書き換えるだけ閉じていても直近のキャッシュ値を表示。最新化は「データ」タブ→「リンクの編集」→「値の更新」新規作成は不可（既存の参照は表示可）
Power Queryで統合する初期構築の手間はやや大きい更新ボタンや自動更新の設定で更新できる2026年1月から利用可能に。クエリ編集まで使えるかはプランによる

Power Query（Excelに標準搭載されたデータ取得・整形の機能）は、複数ブックの統合に強い選択肢。ただしWeb版での対応範囲はプラン次第なので、導入前に自分の環境で確かめてください。

そもそもシート名を切り替えたいだけなら、1ファイル内にシートを集約するのが結局いちばんラクですよ。

原因4｜名前の定義が未登録・表記ゆれ

連動プルダウンで多いのがこのパターン。=INDIRECT(B2) でB2が「果物」でも、名前「果物」が登録されていなければ #REF! です。

確認は「数式」タブ→「名前の管理」から。ありがちなのは、全角と半角の違いや末尾スペースといった表記ゆれ。

  • 名前が未登録 → 範囲を選び直して登録する
  • 表記ゆれ → 選択肢側の文字列と名前を完全一致させる
  • カテゴリ名に空白がある → SUBSTITUTEで _ に置換する（前述）

事前に防ぐならISREF関数（参照として有効かを判定する関数）が使えます。=ISREF(INDIRECT(B2)) は、有効ならTRUE・無効ならFALSEを返す数式。IFERROR関数と組み合わせておくと安心ですよ。

原因5｜シート名を後からリネームした

意外と見落とされがちなのが、これ。シート名を変更しても、INDIRECTの中の文字列は追従しません。

普通の参照 =Sheet1!A5 なら、シート名を「4月」に変えた瞬間にExcelが追従します。数式は =4月!A5 へ自動で書き換わるので、こちらは何もしなくて大丈夫。

ところがINDIRECTの引数は、Excelから見ればただの文字列。書き換えの対象にならず、リネーム後は #REF! になります。

この挙動は、公式ドキュメントに明記されているわけではありません。ただし多くの実務現場で共通して確認されている動きです。シート名を触った直後のエラーなら、真っ先に疑ってください。

対処は2択。シート名をセルで管理しているなら、そのセルの値を新しい名前に書き換えます。数式内に "Sheet1!A5" と直書きしているなら、文字列を修正しましょう。

切り替えの必要がないなら、=Sheet1!A5 という普通の参照に戻すのがいちばん確実。この事故のリスクごと消えますよ。

「重い」「使わない方がいい」の理由とINDIRECTをやめる判断基準

INDIRECTには「重い」という評判がついて回ります。ここでは、その仕組みと使い続けてよい場面の線引きを見ていきましょう。

揮発性関数とは？INDIRECTが再計算を増やす仕組み

INDIRECTは揮発性関数（シートのどこかが変わるたびに再計算される関数）です。ここが「重い」と言われる理由のすべて。

普通の関数は、自分が参照しているセルが変わったときだけ計算し直します。対して揮発性関数は、無関係なセルを1文字打っただけでも再計算が走ります。ファイルを開いた瞬間も同じ。

同じ仲間には、TODAY・NOW・RAND・RANDBETWEEN、そしてOFFSET関数があります。

やっかいなのは、再計算が連鎖する点です。INDIRECTの結果を使うVLOOKUPやSUMIFも、つられて毎回計算し直されます。

数個なら体感は変わりません。しかし数百・数千セルに広がると、入力のたびに固まるファイルができあがります。

INDIRECTを使わない代替早見表（用途×代替手段×揮発性）

「INDIRECTは使わない方がいい」という意見をよく見かけますよね。正確に言えば、代替できる用途では使わない方がいい、です。用途別に整理しました。

用途INDIRECTでの書き方代替手段代替後の揮発性
行・列を数値で指定する=INDIRECT(ADDRESS(3,2))=INDEX(A:Z,3,2)非揮発性になる
列は固定・行だけ可変=INDIRECT("B"&A1)=INDEX(B:B,A1)非揮発性になる
増減する表の範囲を追う=INDIRECT("A2:A"&COUNTA(A:A))テーブルの構造化参照非揮発性になる
同一シート内の絞り込み作業列＋INDIRECTFILTER関数（365・2021以降）非揮発性になる
別ブックのデータを取り込む=INDIRECT("'[Book1.xlsx]Sheet1'!A1")通常のリンク数式／Power Query非揮発性になる
シート名を文字列で切り替える=INDIRECT(A1&"!B2")代替なし揮発性のまま
名前の定義を動的に呼ぶ=INDIRECT(B2)代替なし揮発性のまま

用語を2つ補足します。構造化参照とは、表をテーブル化して テーブル1[金額] のように列名で書く方式のこと。COUNTAは空白でないセルの個数を数える関数です。

置き換えの王道は上の2行。ADDRESS(3,2) が作るのは「$B$3」なので、INDIRECTの参照先はB3です。INDEX(A:Z,3,2) も範囲A:Zの3行目2列目、つまりB3を指します。

2行目も同じ理屈。A1が5なら INDIRECT("B"&A1) はB5、INDEX(B:B,A1) もB5を返します。戻り値は同じまま、揮発性だけが消えるわけです。

ADDRESS関数（行番号と列番号からセル番地の文字列を作る関数）との組み合わせを見かけたら、INDEX関数に置き換えられないか疑ってみてください。判断軸は1本だけ。

  • 参照先を行番号・列番号の数値で決められる → INDEXに置き換えて軽量化する
  • 参照先をシート名や名前（文字列）で決めたい → INDIRECTのまま使う

INDIRECTでしか書けない唯一のケース

代替なしの2行には共通点があります。どちらも「シート名や名前という文字列を、その場で参照に解決している」こと。

INDEXの範囲引数は、実物のセル範囲を受け取る仕様です。「”1月”という文字からシートを探して」という指示は解釈できません。FILTERやXLOOKUPも同じで、扱えるのは指定した範囲の中の絞り込みだけ。

つまりExcel 365の時代でも、役割分担は変わっていません。

  • 同一シート内の条件絞り込み → FILTERなどのスピル関数（1つの数式で複数セルに結果が自動展開される関数）
  • シートをまたぐ動的な切り替え → INDIRECT

競合しているようで、実は守備範囲が違うんですね。無理に置き換えず、使う個数を抑える方向で付き合いましょう。

重くなったファイルを軽くする初手

すでに重いファイルでも、全部を置き換える必要はありません。効果が出るのは、同じ数式が何百行も並んでいる箇所だけです。

  1. Ctrl + F を押して「INDIRECT」を検索します。検索場所は「数式」を選んでください。
  2. 数百セル以上に展開されている列を特定します。
  3. その列だけ、上の早見表に沿ってINDEXやテーブル参照に置き換えましょう。

改善しないなら、原因はINDIRECT以外にあるかもしれません。条件付き書式の重複や不要な画像、巨大すぎる範囲指定も定番の犯人。Excelが重い・固まる・保存できないときの対処で切り分け手順を確認してみてくださいね。

よくある質問｜INDIRECT関数

Q. シート名を変えたら壊れました。追従させる方法はありますか？

INDIRECTの引数は文字列なので、自動で追従させる方法はありません。現実的な対策は2つ。

1つは、シート名をセルに入力しておき、そのセルの値だけを更新する運用にすること。もう1つは、切り替えが不要なら普通の =Sheet1!A5 に戻すことです。通常の参照ならExcelが自動で追従してくれますよ。

Q. Web版Excelでも使えますか？

INDIRECT関数そのものは、Web版Excel（ブラウザ版）でも使えます。同じブック内のシート切り替えなら問題ありません。

ただし外部ブック（別ファイル）への参照はWeb版では新規作成できません。既存の参照は表示できますが、作成や更新設定の変更にはデスクトップ版が必要。別ファイル参照はデスクトップ版で組んでくださいね。

Q. Googleスプレッドシートでも同じ書き方ですか？

はい、スプレッドシートにも同名のINDIRECT関数があり、構文はExcelとほぼ同じ。別シート参照でシングルクォートが必要な点も共通です。

ただし別スプレッドシートを参照する仕組みは異なります。Sheets側の書き方はスプレッドシートのINDIRECT関数の使い方を参考にしてください。

Q. INDIRECTは何個までなら重くなりませんか？

明確な上限はありません。体感で言えば数十個なら気になりませんが、数百・数千セルに展開すると入力のたびに待たされます。

判断は個数より用途で。数値で参照先が決まる箇所をINDEXに置き換えれば、残りが多少あっても実用速度に戻せますよ。

Q. OFFSETとINDIRECTはどちらを使うべきですか？

どちらも揮発性関数なので、速度面の優劣はほぼありません。使い分けは参照先の決め方で判断します。

  • 基準セルから「何行下・何列右」で位置を決めたい → OFFSET
  • シート名や名前を文字列で組み立てたい → INDIRECT

なお、OFFSETの用途の多くはINDEXで置き換えられます。軽さを優先するなら、まずINDEXを検討するのが定石ですよ。

Q. エラーを事前に防ぐ方法はありますか？

次の3つを習慣にすると、#REF! の大半は起きなくなります。

  • シート名は常にクォート付きで書く（="'"&A1&"'!B2" の形）
  • セルから受け取る文字列はTRIMで挟む
  • 名前の定義は「名前の管理」の一覧と突合してから使う

そのうえでISREF関数の判定を挟むか、IFERROR関数で表示を整えておくと万全。出てから直すより、出さない作りの方が結局ラクですよね。

まとめ｜場面別のINDIRECT早見表

INDIRECT関数は、文字列をセル参照に変える関数でした。強みは「参照先を後から動かせる」こと。弱点は「文字列が崩れると #REF! になる」ことと「揮発性で重くなる」ことです。

最後に、場面別の結論を早見表で整理しておきます。

場面結論
別シート参照（スペースなし）=INDIRECT(A1&"!B2") で参照できる
別シート参照（スペース・記号あり）=INDIRECT("'"&A1&"'!B2") とクォートで囲む
#REF! が出たクォート漏れ／不可視スペース／閉じたブック／名前未登録／シート名リネーム の5点を順に確認
式が正しいか確かめたいINDIRECTを外して ="'"&A1&"'!B2" で文字列を目視
別ブックを参照したいブックを開いたまま使う。または通常のリンク数式かPower Queryへ
行・列を数値で決められるINDEXに置き換えて軽量化する
シート名・名前を文字列で切り替えるINDIRECTが唯一の手段
同一シート内を絞り込みたいFILTERなどのスピル関数を優先（365・2021以降）

迷ったときの判断軸はシンプル。「文字列で参照先を切り替えたいか」だけです。イエスならINDIRECT、ノーならINDEXやテーブルの構造化参照を選んでください。

#REF! に出くわしたら、まずは1行デバッグ術で文字列を目視。それだけで大半は自己解決できます。月別シートの切り替えから、気軽に試してみてくださいね。

スポンサーリンク
タイトルとURLをコピーしました