【Excel】INDIRECTで別ブックを閉じると#REF!になる原因と代替方法

INDIRECTで別ブックのセルを参照している場合、参照元ブックを閉じると#REF!になるのは関数の制約です。元のデータが消えたとは限りません。開いた状態で使うのか、閉じたファイルからデータを取り込む構成に変えるのかを選びます。

1. 参照先を開くと戻るか確認する

数式が作っているファイル名とシート名を確認し、正しい参照元を開いて再計算します。Microsoftの仕様では、INDIRECTで外部ブックを参照するには、そのブックが開いている必要があります。

方法	更新の考え方
INDIRECT	外部参照では元ブックを開いて計算
直接の外部参照	固定した参照先とのリンクを管理
Power Queryで取り込み	読み込み先に保存したデータを更新

2. 開いても#REF!なら文字列を点検する

ファイル名、シート名、セル番地、かっこや引用符を確認します。外部ブックを開く条件を満たしていても、作成した参照文字列が間違っていればエラーは残ります。

同じ名前の別ファイルを開いていないかも確認してください。Web版Excelでは外部ブックを指すINDIRECT参照がサポートされない点にも注意します。

3. 少数の固定セルなら直接参照を検討する

元ブックを開き、集計先で=を入力して元のセルを選ぶと、外部参照の式を作れます。参照元を閉じた状態でも扱える直接参照の方法ですが、ファイル移動、アクセス権、リンク更新の許可は引き続き管理が必要です。

保存済みの値が見えていても、最新値に更新できたとは限りません。元の金額を変えたコピーで更新を試し、参照先と更新日時を確認します。これはハイパーリンクとは別の機能です。

4. 明細表を使うならPower Queryで取り込む

Windows版の「データ」→「データの取得」→「ファイルから」→「Excelブックから」で元ファイルを選び、必要なシートやテーブルを取り込みます。読み込んだ表を自ブック内の数式で参照すれば、計算時に元ブックを開いておく必要を減らせます。

取り込みは更新した時点のデータです。元ファイルの未保存の変更が自動で反映されるわけではありません。ファイルへのアクセスとクエリ更新は必要であり、移動や列名変更による更新エラーも確認します。

5. 動的な切り替えはデータの持ち方から見直す

月ごとにファイル名を組み立てているなら、月の列を持つ一覧にまとめ、条件で選ぶ方法もあります。取り込んだマスター表からINDEXなどで取得する構成と、閉じた外部ファイルをINDIRECTで参照する構成は別です。

XLOOKUPやINDEXという関数名に置き換えるだけで、元の文字列参照をそのまま実現できるとは限りません。利用するExcelの版や外部参照の方式に合わせて設計します。

6. 配布先で更新できるかを確認する

修正したコピーを参照元が閉じた状態で開き、表示と更新の両方を試します。共有先のパスや権限が違う場合も想定してください。確定資料として値だけ配るなら、数式版を別に保存し、データの時点を残します。

参考: Microsoft公式: INDIRECT、ブックのリンク管理

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】