【Excel】外部ブックを閉じると#REF!になる時|参照の修復と値の固定

参照元を閉じた時に#REF!になるなら、まず使っている関数と参照先を確認します。外部参照のすべてが、閉じたブックを参照できないわけではありません。今後も更新が必要か、確定した数値だけ残したいかで対処を分けます。

リンクの解除は数式を現在の値へ変える操作です。元の式を失うため、必ずコピーを保存し、数値が正しいと確認してから実行します。

1. 関数と参照先を確認する

INDIRECTで別ブックを参照する場合、参照元が開いていないと#REF!になります。参照元を開いた状態と閉じた状態を比べてください。式の中に#REF!が直接入っている場合は、削除されたシートやセル参照も調べます。

「揮発性関数だから閉じたブックは必ず参照できない」と一括りにはできません。ファイルの移動、名前変更、アクセス権、更新の許可なども確認します。

2. 更新を続けたい場合はリンクを修復する

「データ」の「ブックのリンク」または「リンクの編集」で、参照元を確認します。ファイルが移動したなら、正しい参照元へ変更します。同名の別年度ファイルを選ばないよう、内容と更新日を確認してください。

INDIRECTの動的な指定が不要なら、直接参照などへ見直せます。キャッシュに値が見えていても、最新の値が取得できているとは限らないため、参照元の代表セルと比較します。

3. 確定値だけが必要な範囲を固定する

一部のセルだけを固定するなら、参照元を開いて正しい結果を確認し、その範囲をコピーして「形式を選択して貼り付け」→「値」を使います。元の数式があるファイルは別名で保存して残します。

固定後は参照元を変えても値が追従しません。エラーや古い値が表示されている状態で値貼り付けをすると、その状態を残すことになります。基準日と確定した範囲を記録しておくと、更新版との取り違えを防げます。

4. ブック単位のリンク解除は対象を絞る

1. 変更前のコピーを保存します。
2. 参照元を開き、必要な値と集計結果を照合します。
3. 「ブックのリンク」などで、解除したい参照元を選びます。
4. 影響する数式を確認してから「リンクの解除」を実行します。
5. 保存後に開き直し、数値と残るリンクを確認します。

この解除は通常の取り消しでは戻せません。定義名、グラフ、クエリなどにも参照がある場合があり、一覧の解除だけであらゆる外部依存がなくなるとは限りません。

5. Power Queryを使う場合も更新元を管理する

定期的に表を取り込む用途なら、「データの取得」からExcelブックを選ぶPower Queryも選択肢です。読み込んだ結果を保持し、必要な時に更新できます。

ただしファイルの場所、アクセス権、列名などが変われば更新に失敗する場合があります。前回の値が残っているだけの状態と、更新に成功した状態を区別してください。取り込んだ日時や件数を照合します。

6. 保存版と更新版を分ける

提出用に固定したファイルと、次回も計算する元ファイルを分けます。固定版から元の計算式を再構築しようとせず、保存した原本を使って更新します。

制約はINDIRECTの仕様、解除と修復はMicrosoftのブックのリンク管理を参照できます。

📊

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

🏆

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