Excel #REF!エラーの原因と修正方法6選｜削除・VLOOKUP・シート参照まで

Office365/XLSX/

Excelで作業中に突然セルへ #REF! と表示されて困った経験はないでしょうか。このエラーは「リファレンスエラー」と読み、英語の「Reference（参照）」を省略したものです。数式が参照しているセル・行・列・シートが何らかの理由で存在しなくなったときに発生します。原因のパターンさえ覚えれば必ず解決できるエラーですので、本記事では原因別に具体的な修正方法をわかりやすく解説します。

#REF!エラーとは何か

#REF!エラーは、Excelの数式が参照しているセルやセル範囲が削除されたり、別の場所に移動されたりして、Excelが元の参照先を見つけられなくなった場合に表示されるエラーです。

数式は「存在するはずの場所」を参照しようとしますが、そこには何もなくなってしまっているため、「どこを参照すればいいかわからない」という状態になっています。その状態を示すのが #REF! というエラーメッセージです。

重要なポイントとして、#REF!エラーは数式全体がエラーになるのではなく、削除されたセルのアドレス部分だけが「#REF!」という文字列に置き換えられます。たとえば以下のようなイメージです。

// 元の数式
=SUM(B2, C2, D2)

// C列を削除した後の数式（C2の部分が#REF!に置き換わる）
=SUM(B2, #REF!, C2)

このように数式バーを確認すると、どのセル参照が無効になっているかをひと目で特定できます。

#REF!エラーが発生する主な原因と修正方法

原因1：参照しているセル・行・列を削除した

#REF!エラーの原因として最も多いのが、数式で使用していたセルを削除してしまうケースです。たとえば、B列・C列・D列の合計を個別に指定した数式を入力している場合、C列を削除するとエラーが発生します。

// 元の数式（各セルをカンマで個別指定）
=SUM(B2, C2, D2)

// C列を削除するとエラーになる
=SUM(B2, #REF!, C2)  // ← #REF!エラー発生

修正方法1：Ctrl + Z で元に戻す

直前の操作でセルを削除してしまった場合は、すぐに Ctrl + Z（元に戻す）を押すことでエラーを解消できます。ただし、複数の操作を行った後や、一度ファイルを閉じてしまった場合は元に戻せないことがあります。

修正方法2：範囲参照の数式に書き換える

個別指定（カンマ区切り）ではなく、コロン（:）を使った範囲参照に書き換えることで、列を削除しても数式が自動的に調整されてエラーになりにくくなります。

// カンマ区切りの個別指定（列削除でエラーになりやすい）
=SUM(B2, C2, D2)

// コロンを使った範囲参照（列削除でも自動調整される）
=SUM(B2:D2)

範囲参照に書き換えることで、範囲内の列を削除しても数式が自動的に縮まり、#REF!エラーが発生しにくくなります。

原因2：参照しているシートを削除した

別のワークシートのセルを参照している数式を使用している際に、その参照元シートを削除してしまうと#REF!エラーが発生します。これはファイルを整理しているときによく起こるミスです。

// 別シートを参照している数式
=Sheet2!A1

// Sheet2を削除すると以下のようになる
=#REF!A1  // ← #REF!エラー発生

なお、参照しているシートの名前を変更した場合は数式が自動的に調整されますが、シート自体を削除してしまうと自動調整は行われません。また、シートの削除は Ctrl + Z で元に戻すことができませんので注意が必要です。

修正方法：置換機能でエラーを修正する

シートを削除してしまい元に戻せない場合は、Ctrl + H の検索と置換機能を使って #REF! を含む数式を修正します。

  • Ctrl + H で「検索と置換」ダイアログを開く
  • 「検索する文字列」に #REF! と入力する
  • 「置換後の文字列」に正しいシート名と参照先を入力する
  • 「すべて置換」をクリックする

原因3：カット＆ペーストで参照先のデータを上書きした

通常のコピー＆ペーストと異なり、カット（Ctrl + X）してから別のセルに貼り付けた場合、元のセルのデータが移動するため参照先がなくなり#REF!エラーが発生することがあります。

// C1セルにある数式
=A1 + B1

// A1やB1に別のセルからカット＆ペーストで上書きすると
// 元のA1やB1への参照が無効になり #REF!エラーが発生する

修正方法：カット＆ペースト直後であれば Ctrl + Z で元に戻します。元に戻せない場合は、数式バーで #REF! となっている箇所を正しいセル参照に書き直します。

原因4：VLOOKUPの列番号が参照範囲の列数を超えている

VLOOKUP関数の第3引数（列番号）が、第2引数で指定した参照範囲の列数より大きい場合にも#REF!エラーが発生します。

// 参照範囲が A2:D5（4列分）なのに、5列目を指定している
=VLOOKUP(A8, A2:D5, 5, FALSE)  // ← #REF!エラー発生

// 修正例1：列番号を範囲内に収める
=VLOOKUP(A8, A2:D5, 4, FALSE)  // ← 正常

// 修正例2：参照範囲を広げて5列目を含める
=VLOOKUP(A8, A2:E5, 5, FALSE)  // ← 正常

修正方法：参照範囲の列数と列番号の指定が合っているかを確認します。参照範囲が A:D であれば最大4列分しか指定できないため、列番号を1〜4の範囲内に収めるか、参照範囲自体を広げるかのどちらかで対処します。

原因5：INDEX関数で範囲外の行・列を指定した

INDEX関数は指定した範囲の中から行番号・列番号でセルの値を取り出す関数ですが、指定した行番号や列番号が参照範囲を超えている場合に#REF!エラーが発生します。

// 参照範囲が B2:E5（4行×4列）なのに5行目・5列目を指定
=INDEX(B2:E5, 5, 5)  // ← #REF!エラー発生

// 修正例：範囲内の行・列番号を指定する
=INDEX(B2:E5, 4, 4)  // ← 正常（4行目・4列目を取得）

// 修正例：参照範囲を広げて5行目・5列目を含める
=INDEX(B2:F6, 5, 5)  // ← 正常

修正方法：INDEX関数の行番号・列番号が参照範囲内に収まっているかを確認します。参照範囲のサイズと行・列番号が一致するように数式を修正しましょう。

原因6：循環参照によるエラー

数式がそれ自身のセルを直接または間接的に参照している場合（循環参照）、Excelが計算できずに#REF!エラーが発生することがあります。

// A1セル自身の中でA1を参照している（直接的な循環参照）
=A1 + 1  // ← A1セルにこの数式を入力するとエラー

// C1の数式がA1を参照し、A1の数式がC1を参照している（間接的な循環参照）
// A1: =C1 + 1
// C1: =A1 + 1  ← 互いに参照し合っている

修正方法：「数式」タブの「エラーチェック」から「循環参照」を選択すると、循環参照が発生しているセルを確認できます。循環参照が発生しているセルの数式を見直し、自分自身や他のセルと相互に参照し合わない構造に書き換えましょう。

#REF!エラーの効率的な見つけ方

数式バーで#REF!を直接確認する

エラーが表示されているセルを選択し、数式バーを確認すると、どのセル参照が #REF! に置き換わっているかをひと目で特定できます。数式バー上の #REF! をクリックして選択し、正しいセル参照に書き直すことが最も直接的な修正方法です。

Ctrl + Shift + @ で数式を一括表示する

シート全体の数式をまとめて確認したい場合は、Ctrl + Shift + @ を押すと全セルの数式が表示モードに切り替わります。どのセルに#REF!が含まれているかをシート全体で一目で把握できます。もう一度同じキーを押せば元の計算結果表示に戻ります。

「検索と置換」で#REF!を一括検索する

大量のデータが入っているシートで #REF! を探す場合は、Ctrl + F で検索ダイアログを開き、「#REF!」と入力して検索します。該当セルへ素早くジャンプできるため効率的です。

検索キーワード：#REF!
検索対象：数式（オプションで「数式」を選択すること）

#REF!エラーを非表示にしたい場合のIFERROR関数

データの受け渡しなどで、#REF!エラーが発生しても画面上には何も表示させたくないケースがあります。そのような場合は IFERROR 関数を使うことでエラーを非表示にできます。

// IFERROR関数の基本構文
=IFERROR(エラーが発生する可能性のある数式, エラー時に表示する値)

// 例：VLOOKUPで#REF!が出ても空白を表示する
=IFERROR(VLOOKUP(A8, A2:D5, 5, FALSE), "")

// 例：エラー時に「データなし」と表示する
=IFERROR(VLOOKUP(A8, A2:D5, 5, FALSE), "データなし")

ただし、IFERROR関数はあくまでエラーの見た目を隠すだけであり、数式自体の問題は何も解決していません。業務データに使用する場合は、エラーの根本原因を修正したうえで必要に応じてIFERROR関数を使用するようにしましょう。

#REF!エラーを予防するためのベストプラクティス

  • カンマ区切りの個別指定よりコロンの範囲参照を使う： =SUM(B2,C2,D2) のような個別指定ではなく、=SUM(B2:D2) のような範囲参照を使うと、列の削除時に自動調整されてエラーが起きにくくなります。
  • 不要な行・列は削除せず非表示にする： 「ホーム」タブの「書式」から「非表示/再表示」で列・行を非表示にすることで、数式の参照を維持したままデータを画面から隠すことができます。
  • シートを削除する前に参照元を確認する： シートを削除する前に、そのシートを参照している数式が他のシートにないかを確認しましょう。「Ctrl + F」でシート名を検索すると参照箇所を確認できます。
  • VLOOKUPの列番号は参照範囲の列数以内に収める： 参照範囲を変更する際は列番号の指定も必ず見直す習慣をつけましょう。
  • 重要なファイルは編集前にバックアップを取る： 大規模な行・列の削除を行う前に、ファイルのコピーを保存しておくことで、エラー発生時でも元の状態に戻せます。
  • INDIRECT関数で動的な参照を実現する： 行や列が削除されても参照が崩れない仕組みが必要な場合は、INDIRECT関数のR1C1スタイルを活用することで柔軟な参照設計ができます。
// INDIRECT関数を使った例（行削除に強い参照の書き方）
// R[-1]C[0] = 1つ上の行・同じ列のセルを参照
=INDIRECT("R[-1]C[0]", FALSE)

まとめ：#REF!エラーは参照先を整えれば必ず解消できる

#REF!エラーは、数式が参照しているセル・行・列・シートが存在しなくなった場合に発生するエラーです。主な原因は以下の6つです。

  • 参照しているセル・行・列を削除した
  • 参照しているワークシートを削除した
  • カット＆ペーストで参照先のデータを上書きした
  • VLOOKUPの列番号が参照範囲の列数を超えている
  • INDEX関数で範囲外の行・列番号を指定した
  • 循環参照が発生している

エラーが発生したらまず数式バーを確認し、どの参照箇所が #REF! になっているかを特定しましょう。直後の操作であれば Ctrl + Z で元に戻すことが最も手軽な解決策です。根本原因を修正したうえで、日頃からコロン（:）を使った範囲参照を活用するなど、エラーが起きにくい数式の書き方を習慣づけることが大切です。