Excelの数式が突然『#REF!』エラーに。参照がずれた原因と直し方

結論から先に

「#REF!」は、参照していた行・列・シートが消えたか、外部リンク先のファイルが移動・削除されたサインです。

Ctrl+Zを10〜20回押して直前の状態に戻せるなら、戻して原因を見極めるのが最速です。戻せない場合は、Ctrl+Hで「#REF!」を検索して影響範囲を把握し、参照を作り直します。

今後の予防には、テーブル化(Ctrl+T)、名前付き範囲、絶対参照($付き)を使うと、行・列の追加・削除に強くなります。配布前に「リンクの編集」で外部リンクをチェックする習慣も役立ちます。

#REF!が出る代表的な原因

#REF!は「参照していたものがなくなった」というエラーです。次のいずれかが原因のことがほとんどです。

• 行・列を削除した:数式が削除された行/列を参照していた
• シートを削除した:他のシートを参照していた数式
• 外部ブックの場所が変わった/削除された
• コピー&ペーストで参照範囲が無効になった(例:A1:A10をコピーしてB列に貼り付け、元の範囲が消えた)
• マクロ・VBAでセルを削除した

「昨日まで動いていた」というケースは、ほぼ削除か移動が原因です。

まずやる:Ctrl+Zで戻せるか確認

Excelには通常、最大100回までの取り消し(Undo)履歴があります(バージョンによる)。

• ファイルを開いた直後なら、Ctrl+Zでは元に戻せない(履歴は閉じるとリセット)
• 作業中に出たエラーなら、Ctrl+Zで戻す
• 戻しすぎたら、Ctrl+Yで戻す

「保存して閉じた後で気づいた」場合は、Undoでは戻せません。バックアップやOneDrive・SharePointの「バージョン履歴」を確認してください。

影響範囲を見える化する

修正前に、#REF!がどこに広がっているかを把握します。

1. Ctrl+Hで「置換」ダイアログを開く
2. 「検索する文字列」に「#REF!」と入力
3. 「オプション」→「検索対象」で「数式」を選択
4. 「すべて検索」をクリック
5. 検索結果一覧で、影響を受けたセルを確認

これで「どのシート・どのセル」にエラーが出ているかが一覧化されます。

復旧の手順

参照を作り直す場合の手順です。

1. エラーが出ているセルを1つ選択
2. 数式バーを見て、どこを参照しようとしているか確認
3. 正しい参照範囲(または値)を再指定
4. 同じパターンが他のセルにもある場合、最初の1つを直してコピー
5. 検索置換で「#REF!」を含む数式の塊を順に処理

例えば「=SUM(A1:#REF!)」のようになっていれば、SUMの範囲を正しいセルに置き換えます。

外部ブックリンクが原因のとき

外部ブックを参照していて#REF!になった場合の確認方法です。

1. 「データ」タブ → 「リンクの編集」をクリック
2. ソース欄でリンク先のブック名を確認
3. 「リンク先の確認」でファイルの場所をチェック
4. ファイルが移動していたら「リンクの変更」で正しい場所を指定
5. 不要なリンクは「リンクの解除」(値貼り付けに変換)

配布前のブックでは、外部リンクを残さないようにすると、相手側で#REF!が出にくくなります。

マクロ・VBAでエラーが出た場合

VBAで列・シートを削除する処理を書いていると、思わぬところに#REF!が出ることがあります。

• マクロ実行前にブックのバックアップを取る
• 削除する範囲が、他のシートから参照されていないか事前に確認
• ActiveSheet.UsedRange の扱いに注意
• エラーが出たら、保存せずに閉じて開き直す

「マクロを実行したら一斉に#REF!」というパターンは、原因がほぼ確実にVBAコードです。

テーブル化で参照ずれを予防

データ範囲をテーブルにすると、行を追加しても自動的に参照範囲が広がり、削除しても#REF!が出にくくなります。

• 範囲を選択 → Ctrl+T → 「テーブルとして書式設定」
• テーブル名を「売上テーブル」のように分かりやすくする
• 数式は「=SUM(売上テーブル[金額])」のように書く
• 行・列の挿入削除で参照が壊れにくい

複数人で共有する見積書、台帳系のシートではテーブル化が特に有効です。

名前付き範囲も有効

頻繁に参照する範囲には「名前」をつけると、後から範囲が変わっても数式を直さずに済みます。

• 範囲を選択 → 名前ボックス(数式バー左)に「単価表」と入力 → Enter
• 数式で「=VLOOKUP(A1, 単価表, 2, FALSE)」のように書く
• 「数式」タブ → 「名前の管理」で一覧確認・編集

範囲の追加・削除があっても、名前付き範囲の定義を更新すれば、数式は直さずに済みます。

復旧できないときのバックアップ確認

どうしても戻せない場合の最終手段です。

• OneDrive・SharePoint:右クリック → バージョン履歴
• Microsoft 365:ファイル → 情報 → バージョン履歴
• ローカル:エクスプローラーで右クリック → 「以前のバージョン」(設定による)
• Git管理:過去のコミットから取り出す

バージョン履歴が残っていれば、エラーが出る前の状態に戻すのが一番早い復旧方法です。

配布前のチェックリスト

人に渡すExcelファイルでエラーを出さないために、配布前に確認したい項目です。

• Ctrl+F → 「#」で始まるエラーを検索
• 「数式」タブ → 「エラーチェック」を実行
• 外部リンクを「リンクの編集」で確認
• 非表示シートに残骸がないか確認
• 「校閲」タブ → 「シートの保護」で意図しない編集を防ぐ

「自分のPCでは動くのに、相手のPCで開くと#REF!」というのは、外部リンクが原因の代表例です。

再発を防ぐ習慣

長く使うExcelファイルでは、次の習慣が再発防止に効きます。

• 重要なファイルはOneDrive/SharePointで自動バージョン管理
• 共有前に「値貼り付け」で外部リンクを断つ
• 元データシートと計算シートを分ける
• 範囲はテーブル化または名前付き範囲で扱う
• 月初・四半期ごとに「リンクの編集」を点検

数式が複雑になるほど、参照ミスのリスクは増えます。早めに小さい改善を積むのが現実的です。

よくある質問

Q. #REF!と#N/A、#VALUE!の違いは何ですか？

#REF!は「参照していたセル・シート・ブックがなくなった」エラー、#N/Aは「VLOOKUPやMATCHで値が見つからない」エラー、#VALUE!は「数式の引数の型が想定と違う(数値を期待する場所に文字列など)」エラーです。原因が違うので、対処方法も異なります。#REF!は参照の作り直し、#N/Aは検索値・範囲の確認、#VALUE!はデータ型の確認が基本です。

Q. Ctrl+Zで戻したくないけど、修正したい場合は？

Ctrl+Hで「置換」ダイアログを開き、「検索する文字列」に「#REF!」と入力すると、エラーを含むセルを一覧で確認できます。1つずつ正しい参照に書き直すか、近くの正常な数式をコピーして書き換えると効率的です。検索オプションで「数式」を対象にすると、数式内の#REF!も探せます。

Q. 外部ブックを参照している場合、#REF!を防ぐには？

外部リンクは、参照先のブックが移動・削除されると簡単に#REF!になります。対策として、可能なら同じブック内にデータを集める、外部リンクが必要な場合は「データ」タブの「リンクの編集」で状態を定期確認する、参照先ブックのファイル名や場所を変更しない、などがあります。配布前にリンクを値貼り付けに置き換える運用も有効です。

Q. テーブル化と名前付き範囲とは何ですか？

テーブル化(Ctrl+T)はセル範囲を「テーブル」として扱い、行を追加しても自動的に範囲が広がる仕組みです。名前付き範囲は「売上_2026年」のように範囲に名前をつけて、数式で参照する機能です。どちらも、セルアドレス(A1:B100など)の直接参照より、行列の追加・削除に強くなります。

参考資料

• Microsoft「Excelのエラー値の解説」— エラーの種類と意味
• Microsoft「テーブルの概要」— テーブル化の手順
• Microsoft「名前付き範囲を使った数式」— 範囲名の活用法