関数辞典

#REF!エラーの直し方｜参照先が消えたときに元へ戻す手順

執筆者:

関数辞典編集部

カテゴリ:

#REF!エラーの直し方のイメージ

目的別

#REF!エラーの直し方

Excel関数辞典

#REF! は 「参照していた場所が、もう無い」という状態です。 他のエラーと決定的に違うのは、数式の中身が書き換わってしまっていることです。

=VLOOKUP(A2,#REF!,3,FALSE) のように、範囲そのものが #REF! という 文字に置き換わります。元がどこを指していたかは、数式からはもう読み取れません。

このページの中身（22項目）
  1. いちばん先にやること：取り消す
  2. 何をすると出るのか
  3. 取り消せないときの直し方
  4. 二度と起こさないための書き方
  5. #REF! は IFERROR で隠してはいけない
  6. 症状から原因を引く
  7. 関数別：#REF! が出る場面
  8. 実際に来そうな相談と、その読み解き
  9. 直す前のチェックリスト
  10. よくある質問
  11. 実際の表で追ってみる
  12. 壊れた場所をまとめて洗い出す
  13. 参照が消えても壊れない作りにする
  14. 共同編集での予防
  15. 直った後に確認すること
  16. IMPORTRANGE で起きる場合（スプレッドシート）
  17. ピボットテーブルやグラフが壊れる場合
  18. #REF! を出さずに列を消す手順
  19. 壊れたまま気づかない形に注意する
  20. 復旧できなかったときのために
  21. この3つのエラーは、どこが違うのか
  22. エラーを消す前に

いちばん先にやること：取り消す

直前の操作が原因なら、Ctrl+Z（取り消し）で戻せます。 まず手を止めて、これを試してください。

他の操作をいくつも重ねると、取り消しでは戻れなくなります。 共同編集のシートなら、ファイル → 版の履歴（Excelはバージョン履歴）から、 消す前の状態に戻せます。この2つを試す前に数式を書き直さないでください。 書き直してから履歴で戻すと、直した内容のほうが消えます。

何をすると出るのか

1. 参照していた行や列を削除した

もっとも多い形です。=SUM(C2:C10) と書いてあるところで C列 を削除すると、 数式は =SUM(#REF!) になります。 非表示にしただけなら起きません。削除したときだけです。

2. 参照していたシートを削除した、または名前を変えた

シートを消すと、そのシートを見ていた数式はすべて #REF! になります。 名前の変更は通常追随しますが、INDIRECT("'4月'!A1") のように 文字列で組み立てた参照は追随しません。名前を変えた瞬間に壊れます。

3. 別のブックを参照していて、そのブックが移動・削除された

Excelで外部ブックを参照している場合に起きます。 Googleスプレッドシートでは IMPORTRANGE の アクセス許可が外れた場合が同じ状況にあたります。

4. 貼り付け先が足りない

3列ぶんの数式を、右端から2列しかない場所に貼ると、はみ出した参照が #REF! になります。 表の右端・下端での貼り付けで起きます。

5. INDEXやOFFSETで、範囲の外を指した

=INDEX(A2:A10,15) のように、9行しかない範囲の15行目を求めると #REF! です。 行番号を数式で出しているときに、想定より大きい数が入ると起きます。

取り消せないときの直し方

数式から元の参照は読み取れないので、正しい参照を自分で書き直します。 1つずつ直す前に、まず全体で何か所あるかを把握してください。

まとめて見つける

検索（Ctrl+F）で #REF! を検索し、 検索対象を「数式」に切り替えます。値だけを見ていると、 数式の中に埋まっている分を見落とします。

まとめて直す

同じ形の壊れ方が並んでいるなら、置換が使えます。 #REF! を正しい範囲（例：$F$2:$H$100）に置換します。 置換は元に戻しにくいので、先にシートを複製してから行ってください。

二度と起こさないための書き方

名前付き範囲を使う

範囲に名前を付けておくと、行や列を消しても名前の定義が残ります。 数式側は名前を見ているので、参照が丸ごと消えることが減ります。

列の削除ではなく非表示にする

作業用の列は、消さずに非表示にしておけば #REF! は起きません。 どうしても消すなら、その列を参照している数式が無いか先に確かめます。

INDIRECTを乱用しない

INDIRECT は参照を文字列で組み立てるため、 シート名を変えても追随せず、参照先を消しても警告が出ません。 壊れたことに気づきにくいという意味では、#REF! より厄介です。

列番号ではなく見出しで引く

VLOOKUPの「3列目」は、間に列を1本足すだけでずれます。 XLOOKUP や MATCH で 見出しの名前から列を探す形にしておくと、列の増減に強くなります。

#REF! は IFERROR で隠してはいけない

#N/A は「無かった」という報告ですが、#REF! は数式が壊れた合図です。 IFERROR で空欄にしてしまうと、 壊れた集計が「空欄」として静かに通ります。

隠すのではなく、直してください。 どうしても表示だけ整えたい場合でも、別の場所に =SUMPRODUCT(--ISERROR(範囲)) で件数を出し、 0以外なら気づけるようにしておきます。

症状から原因を引く

見えている症状疑うところ
行や列を消した直後に出た削除（原因1）— まず取り消す
シートを整理した直後に出たシート削除・名前変更（原因2）
ファイルを開いた瞬間から出ている外部ブックの移動（原因3）
表の右端・下端だけ出ている貼り付け先が足りない（原因4）
行数が増えたときだけ出るINDEXやOFFSETの範囲外（原因5）
数式バーに #REF! という文字が見える参照が書き換わっている。復元が要る

いちばん下の行が重要です。数式バーに #REF! という文字が入っていたら、 そのセルはもう元の参照を持っていません。 表示だけ直す方法はありません。参照を書き直すか、履歴から戻すかの二択です。

関数別：#REF! が出る場面

関数出る場面予防
VLOOKUP列番号が、範囲の列数を超えた 列を消したら列番号も見直す
INDEX範囲より大きい行番号・列番号を指した 行数を ROWS で取って上限を作る
OFFSETずらした先がシートの外に出た 基準セルと移動量を確かめる
INDIRECT文字列で作った参照が存在しない シート名を変えない、名前付き範囲にする
IMPORTRANGE参照先のアクセス許可が外れた 共有設定を確認する
TRANSPOSE結果を書き出す先が足りない 行数と列数を入れ替えた分の余白を空ける

VLOOKUP の列番号は要注意です。範囲を A:C にしていて列番号を4と書くと、 削除していなくても最初から #REF! になります。

実際に来そうな相談と、その読み解き

「不要な列を消したら、集計表が全部 #REF! になった」

典型例です。まず Ctrl+Z。もう他の操作をしてしまったなら、版の履歴から 削除前の版に戻します。数式を書き直す前に履歴を確認してください。 書き直してから戻すと、書き直した内容が消えます。

戻したあとは、消す前に「その列を参照している数式が無いか」を確かめる手順を挟みます。 Ctrl+F で列名（例：C:C や $C$）を数式検索すれば分かります。

「シート名を月ごとに付け替えたら壊れた」

通常の参照はシート名の変更に追随しますが、 INDIRECT で組み立てた参照は追随しません。 =INDIRECT("'"&A1&"'!B2") のような書き方をしている場合、 A1の値とシート名が一致しなくなった時点で壊れます。

月ごとにシートを増やす運用なら、シート名をセルから読む形にして、 シート名そのものは変えない設計にしておくと安定します。

「他の人が編集したら、自分の数式が #REF! になった」

共同編集では、誰かが行や列を消すと、その瞬間に全員の数式が壊れます。 版の履歴で「誰が・いつ・何をしたか」まで追えるので、 まず履歴を開いて、壊れた時刻の前の版に戻してください。

再発を防ぐなら、集計用のシートを保護して、 編集できる範囲を入力欄だけに限定します。

直す前のチェックリスト

  • Ctrl+Z を試した（他の操作を重ねる前に）
  • 版の履歴で、壊れる前の版を確認した
  • 検索対象を「数式」にして #REF! の件数を数えた
  • 置換で直す前に、シートを複製した
  • 直したあと、元の参照が正しいかを1件ずつ確かめた

よくある質問

#REF! を IFERROR で消してもいいですか

やめてください。#REF! は数式が壊れた合図です。 消すと、壊れた集計が空欄として通ります。 IFERROR は「起きて当然のエラー」にだけ使うものです。

行を非表示にしただけでも出ますか

出ません。非表示は参照を壊しません。 SUBTOTAL や AGGREGATE を使えば、 非表示行を除いた集計もできます。

元の参照が何だったか、後から分かりますか

数式からは分かりません。#REF! という文字に置き換わっているためです。 版の履歴か、バックアップからしか復元できません。

フィルタで並べ替えても壊れますか

並べ替えでは #REF! は出ません。ただし、 行番号を直接指定している数式は、指す先が変わって結果がずれます。 エラーが出ないぶん気づきにくいので、並べ替える表では相対参照を避けてください。

スプレッドシートとExcelで違いはありますか

意味は同じです。復元の方法が違います。 スプレッドシートは「ファイル → 版の履歴」で自動保存された版に戻れます。 Excelは自動保存が有効な場合のみ「バージョン履歴」が使えます。 無効なら、保存前であれば閉じずに Ctrl+Z を続けるしかありません。

実際の表で追ってみる

次のような集計シートがあったとします。

セル元の数式C列を削除した後
E2=SUM(C2:C100)=SUM(#REF!)
E3=AVERAGE(C2:C100)=AVERAGE(#REF!)
E4=VLOOKUP(A2,A2:D100,3,FALSE)=VLOOKUP(A2,A2:C100,3,FALSE)
E5=B2+C2=B2+#REF!

注目してほしいのは E4 です。#REF! になっていません。 範囲が1列縮んだだけで、3列目という指定はそのまま残りました。 つまりエラーは出ないのに、別の列の値を返しています。

#REF! が出たときは、出ていないセルも疑ってください。 列を消すと、列番号で引いている数式は静かにずれます。

壊れた場所をまとめて洗い出す

Googleスプレッドシートの場合

  1. Ctrl+F を押し、検索窓の右の「…」を開く
  2. 「検索」を数式内も検索に切り替える
  3. #REF! で検索し、件数を見る
  4. 「ファイル → 版の履歴 → 版の履歴を表示」で、壊れる前の版を探す

Excelの場合

  1. Ctrl+F → オプション → 検索対象を「数式」にする
  2. #REF! で「すべて検索」
  3. 一覧をクリックすると該当セルへ移動する
  4. 自動保存が有効なら、ファイル → 情報 → バージョン履歴

件数を控えてから直し始めてください。 途中で分からなくなったとき、いくつ残っているかが分かります。

参照が消えても壊れない作りにする

名前付き範囲にする

スプレッドシートは「データ → 名前付き範囲」、Excelは「数式 → 名前の定義」。 単価表 のような名前を付け、数式では =VLOOKUP(A2,単価表,2,FALSE) と書きます。 名前の定義が残るので、範囲がまるごと #REF! に化けることが減ります。

テーブルにする（Excel）

Ctrl+T で表をテーブルにすると、 =SUM(テーブル1[金額]) のように列名で参照できます。 列を挿入しても削除しても、名前で追随します。

見出しから列番号を出す

VLOOKUPを使い続けるなら、3列目という数字を書かずに、 =VLOOKUP(A2,$F$1:$J$100,MATCH("単価",$F$1:$J$1,0),FALSE) のように MATCH で見出しから探します。 列が増えても減っても、見出しさえ変わらなければ動きます。

共同編集での予防

  • 集計用のシートを保護し、編集できる範囲を入力欄だけにする
  • 行や列の削除ではなく、非表示か、不要フラグの列で運用する
  • 元データと集計を別シートに分け、元データ側だけを触る決まりにする
  • 大きな整理をする前に、シートを複製しておく

「消していいですか」と聞ける相手がいない環境ほど、 消せない作りにしておく価値があります。

直った後に確認すること

  • 数式検索で #REF! の件数が0になっているか
  • エラーが出ていない数式も、正しい列を指しているか （列番号で引いている数式は静かにずれます）
  • 合計や件数が、壊れる前の値と一致するか
  • 同じ操作で再発しない作りに変えたか

IMPORTRANGE で起きる場合（スプレッドシート）

IMPORTRANGE は、別のスプレッドシートから範囲を持ってきます。 ここで出る #REF! は、セルを消したわけではないことが多く、 原因はほとんどがアクセス許可です。

よくある3つ

  • 初回の許可をしていない：セルをクリックすると 「アクセスを許可」というボタンが出ます。押すまで #REF! のままです。
  • 参照先の共有設定が変わった：相手が共有を外すと、 翌日から突然 #REF! になります。数式は正しいままです。
  • 参照先のシート名が変わった： IMPORTRANGE(URL,"4月!A1:D100") のシート名部分は文字列なので、 名前を変えると一致しなくなります。

切り分け方

参照先のURLを直接ブラウザで開いてください。 開けないなら権限、開けるならシート名か範囲の指定です。 URLはセルに置いて =IMPORTRANGE($A$1,"…") と参照する形にしておくと、 URLが変わったときに1か所直すだけで済みます。

ピボットテーブルやグラフが壊れる場合

元の表から列を消すと、その列を使っていたピボットテーブルは 項目を失い、グラフは系列が空になります。 セルには #REF! が出ないので、気づくのが遅れます。

確認の順番

  1. ピボットテーブルを右クリック → 更新。 消えた項目が「フィールドリスト」から無くなっていないか見る
  2. グラフを選び、データ範囲を確認する。 範囲に #REF! が入っていれば、そこで壊れている
  3. 元の表をテーブル（Excelは Ctrl+T）にしておくと、 行の増減にはピボットが自動で追随する

#REF! を出さずに列を消す手順

消す前に、その列が使われていないことを確かめます。

  1. 消したい列の文字（例：C）を控える
  2. Ctrl+F を開き、検索対象を「数式」にする
  3. C:C、$C$、C2 の3通りで検索する
  4. ヒットが0なら消してよい。ヒットしたら、その数式を先に書き換える
  5. 不安なら、消す前にシートを複製しておく

VLOOKUPの列番号は検索に引っかかりません。 範囲に消したい列が含まれている数式は、列番号も見直してください。 =VLOOKUP(A2,A:D,3,FALSE) でC列を消すと、 エラーは出ないまま4列目だったものが3列目になります。

壊れたまま気づかない形に注意する

#REF! は目立つので、まだ気づけます。 本当に怖いのは、列がずれてもエラーにならない数式です。

書き方列を消したとき
=VLOOKUP(A2,A:D,3,FALSE)エラーは出ず、別の列の値を返す
=VLOOKUP(A2,A:D,MATCH(“単価”,A1:D1,0),FALSE) 見出しを探すので、正しい列を返し続ける
=INDEX(D:D,MATCH(A2,A:A,0))D列を消せば #REF!。気づける
=XLOOKUP(A2,A:A,D:D)D列を消せば #REF!。気づける

「エラーが出る書き方」のほうが安全なことがあります。 列番号を数字で書く形は、静かに間違えるという点で分が悪いです。

復旧できなかったときのために

  • 月に一度、シートを複製して日付を付けて残す
  • 重要な集計は、値だけのスナップショットを別シートに保存する
  • スプレッドシートの版の履歴は「名前を付けて保存」でき、 名前を付けた版は自動削除されない
  • Excelはブックを閉じると取り消し履歴が消える。 大きな整理の前に別名で保存する

この3つのエラーは、どこが違うのか

見た目は同じ「エラー」でも、原因の層が違います。ここを取り違えると、 直らない場所をいくら触っても直りません。

エラー壊れている場所まず見るところ
#N/A探した値が、探した範囲に無い 検索値の表記ゆれ、範囲の指定
#REF!参照先そのものが消えた 直前に消した行・列・シート
#VALUE!渡した値の種類が合わない 数値のはずのセルに入っている文字

7種類すべての一覧は スプレッドシートのエラー一覧 にまとめています。

エラーを消す前に

IFERROR で包めば表示は消えます。ただし 原因は残ったままです。 金額や件数の集計で使うと、本来あるはずの数字が静かに欠けます。

消してよいのは「エラーが出るのが正常な場合」だけです。 たとえば、まだ入力していない行が空欄でエラーになる、といった場面です。 それ以外は、消す前に原因を潰してください。