Excelの#REF!エラーの原因と直し方|行・シート削除で消えた参照を戻す

スポンサーリンク

Excelで行やシートを消したとたん、集計セルが #REF! エラーだらけになった。人から受け取ったブックを開いたら、最初から #REF! が並んでいた。そんな場面で手が止まっていませんか。

#REF! は「数式が指していたセルが、もう存在しない」というサインです。慌てて置換で消したり、勘で打ち直したりすると、エラーは消えても数字だけが狂ったまま残ることがあります。

この記事では、直前の操作ごとに数式がどう壊れるかを「元の数式 → 壊れた後の数式」で並べました。今すぐ戻せるのか、書き直すしかないのか。まずそこを判断してから手を動かしましょう。

対応バージョン: #REF! の出方は、Microsoft 365・Excel 2021 以降と Web版で基本的に共通です。違うのは操作の場所。

「条件を選択してジャンプ」はデスクトップ版の手順で、Web版で見当たらない場合は検索(Ctrl+F)を使ってください。過去の版に戻す「バージョン履歴」は、OneDrive・SharePoint に保存したブックが対象です。

XLOOKUP は Microsoft 365・Excel 2021 以降と Web版で使えます。Excel 2019 以前は INDEX+MATCH で代用しましょう。

Excelの#REF!エラーとは|何をした直後かで壊れ方が違う

#REF! の REF は reference(参照)の略。数式が参照しているセルが削除されたり、別のセルで上書き貼り付けされたりすると表示されます。

厄介なのは、削除が原因の場合に数式の文字そのものが書き換わる点です。たとえば =A1+A2 の入ったブックで1行目を削除すると、数式は =#REF!+A1 に変わります。

たとえば、A1〜A3 に 100・200・300、C3 に =A1+A2 を入れて1行目を削除してみます。

元のセル番地 A1 は数式から消え、#REF! という文字に置き換わりました。あとから数式を見ても、何を参照していたのか読み取れません。

一方、VLOOKUP や INDEX の引数が範囲外を指している場合は、数式の文字は元のままです。セルに #REF! が表示されるだけ。原因が「削除による参照の消失」か「関数の引数」かで、直し方が分かれます。

30秒で分かる判断フロー(Ctrl+Z → バージョン履歴 → 書き直し)

いまの状況に近い行を探して、左から順に試してください。

いまの状況まずやること詳しい手順
行・列の削除や貼り付けの直後Ctrl+Z で元に戻す直前の操作別の節
シートを削除してしまったCtrl+Z を試す。戻らなければ保存せずに閉じるシートを削除したら…
保存した後・自動保存で保存済みOneDrive・SharePoint のバージョン履歴で戻す戻せないときの書き直し方
数式の文字は無事で、セルだけ #REF!関数の引数を直す関数の引数が原因の#REF!
戻すと他の作業まで消えてしまう#REF! の部分だけ書き直す戻せないときの書き直し方

判断のコツは「戻せるうちに戻す」こと。Ctrl+Z は直後ほど確実で、別の編集を重ねるほど巻き戻す手数が増えます。数式バーに #REF! の文字が入っているかどうかも、最初に確かめておきましょう。

直前の操作別|どこが#REF!に置き換わるか

操作ごとの壊れ方を一覧にしました。数式のセル自体も、行や列の削除に合わせて上や左へずれます。

直前の操作元の数式壊れた後の数式
1行目を削除(C3 の数式)=A1+A2=#REF!+A1
C列を削除(E2 の数式)=SUM(B2,C2,D2)=SUM(B2,#REF!,C2)
範囲の途中の行(2行目)を削除=SUM(A1:A3)=SUM(A1:A2)(エラーにならない)
範囲の行をすべて削除=SUM(A1:A3)=SUM(#REF!)
Sheet2 を削除=Sheet2!B2=#REF!B2
別のセルを切り取って A1 に貼り付け=A1*2=#REF!*2

一度 #REF! に置き換わった参照は、あとで同じ位置に行やシートを作り直しても自動では戻りません。戻す手段は Ctrl+Z か、過去の版から復元するか、手で書き直すかの3つです。

行・列を削除したら#REF!になった

行の例は冒頭で見たとおりです。削除前の C3 の =A1+A2 は、100+200 で 300 を返していました。

A2 を指していた部分は、行が上に詰まったので A1 に自動で付け替わりました。消えた1行目を指していた部分だけが #REF! になった、というわけです。

列も同じ仕組みで壊れます。B2〜D2 に 10・20・30 を入れ、E2 に合計を出した例で確かめましょう。

元の数式(E2):  =SUM(B2,C2,D2)   → 60
C列を削除した後(D2):  =SUM(B2,#REF!,C2)   → #REF!

一方、範囲で書いた =SUM(A1:A3) は、途中の2行目を消しても =SUM(A1:A2) に縮むだけです。A1〜A3 が 100・200・300 なら、結果は 600 から 400(100+300)に変わります。消したデータの分だけ合計が減るのは、正しい動き。

ただし範囲の行をまるごと削除すると、指す先がなくなり =SUM(#REF!) になります。

直し方: 削除の直後なら Ctrl+Z で戻すのが最短です。戻せないときは、消した行のデータが必要だったかで分けて考えます。

  • 消したデータが不要だった: #REF! と、その前後の演算子を消す(=#REF!+A1 → =A1)
  • 消したデータが必要だった: データを入れ直してから、そのセル番地で #REF! を書き換える

シートを削除したら=#REF!B2になった

別シートを参照する =Sheet2!B2 は、Sheet2 を削除すると =#REF!B2 になります。シート名の部分だけが #REF! に置き換わり、セル番地の B2 は残るのが特徴です。

注意したいのは、シートの削除は Ctrl+Z で戻せないことが多い操作だという点。まず Ctrl+Z を試し、戻らなかったら次の手順に進みましょう。

  • デスクトップ版で、削除後に保存していない: 保存せずに閉じて開き直すと、最後に保存した状態に戻る(その後の編集も消える)
  • Web版や自動保存がオンのデスクトップ版: 変更がすぐ保存されるため、バージョン履歴から戻す

戻せない場合でも、=#REF!B2 の B2 が手がかりになります。削除したシートを作り直せたなら、書き直しの節で紹介する方法でシート名を入れ直せます。

切り取り→貼り付けしたら#REF!になった

参照先のセルに、別のセルを「切り取り → 貼り付け」で上書きしても #REF! になります。B1 に =A1*2、A1 に 50 が入っている例で見てみましょう。

元の数式(B1):  =A1*2   → 100
C1 を切り取って A1 に貼り付けた後:  =#REF!*2   → #REF!

貼り付け先の A1 が、切り取ったセルに置き換わった扱いになるためです。コピー → 貼り付けで値を上書きしただけなら、通常は参照がそのまま残ります。

直し方: 直後なら Ctrl+Z。データを移したいときは、参照されているセルへの切り取りを避けましょう。「コピー → 貼り付け → 元のセルの値を Delete キーで消す」の順なら、参照を壊しにくくなります。

数式をコピーしたら参照がはみ出して#REF!になった

相対参照の数式をコピーすると、参照先も同じ方向へずれます。ずれた先が1行目より上や A列より左になると、指すセルがないので #REF! です。

B2 の数式:  =A1
B1 にコピー → 1行目より上を指す → =#REF!
A2 にコピー → A列より左を指す → =#REF!

直し方: 同じセルを指し続けたいなら、$A$1 のように絶対参照にしてからコピーします。参照先がずれるのが目的なら、コピー先の位置が正しいかを見直してください。

別ブックの元ファイルを動かしたら#REF!になった

別ブックを参照する数式は、元ブック側の変化で壊れることがあります。はっきり #REF! になるのは、元ブックで参照先のシートや行・列を削除した場合です。

元ファイルを移動・削除しただけなら、#REF! にならないこともあります。リンクの更新を求める警告が出たり、前回の値が表示されたままになったりするケースです。どちらにしても、リンク先の場所を正しく指定し直しましょう。

  • 元ファイルを移動した: デスクトップ版の [データ] タブ → [リンクの編集] → [リンク元の変更] で新しい場所を指定する。表示名は版によって異なる場合がある
  • INDIRECT やテーブル名で別ブックを参照している: 元ブックが閉じていると #REF! になるため、元ブックを開く

関数の引数が原因の#REF!

数式バーを見ても #REF! の文字がないのに、セルには #REF! が出ている。参照先のセルも正常なら、関数の引数が範囲の外を指していると考えられます。

VLOOKUPの列番号が範囲を超えている

VLOOKUP の第3引数(列番号)が、第2引数の範囲の列数より大きいと #REF! になります。

=VLOOKUP(A8,A2:D5,5,FALSE)   → 範囲は4列なのに5列目を指定 → #REF!
=VLOOKUP(A8,A2:D5,4,FALSE)   → 列番号を範囲内に直す
=VLOOKUP(A8,A2:E5,5,FALSE)   → または範囲を5列に広げる

見落としやすいのが、範囲の途中の列を削除したケースです。=VLOOKUP(A8,A2:E5,5,FALSE) から C列を消すと、範囲は A2:D5 に縮みます。ところが列番号の 5 はただの数字。自動では変わりません。

その結果、正しく動いていた数式が #REF! に変わります。列番号の数え方はVLOOKUP関数の使い方で確認できます。

INDEXの行番号・列番号が範囲外

INDEX も、指定した行番号・列番号が範囲の外だと #REF! を返します。

=INDEX(B2:E5,5,5)   → 4行×4列の範囲に5行目・5列目を指定 → #REF!
=INDEX(B2:E5,4,4)   → 範囲内の番号に直す

MATCH と組み合わせているなら、MATCH の検索範囲が INDEX の範囲より長くないかも確かめましょう。MATCH が返す位置が INDEX の範囲を超えると、同じく #REF! になります。引数の詳しい指定はINDEX関数の使い方にまとめています。

OFFSETが範囲外へ移動している

OFFSET は、基準のセルから指定した行数・列数だけ移動したセルを返す関数です。移動先がシートの外になると #REF! になります。

=OFFSET(A1,-1,0)   → A1 から1行上(シートの外)→ #REF!
=OFFSET(B2,0,-2)   → B2 から2列左(A列より左)→ #REF!

行数・列数をほかのセルの計算結果で渡しているなら、その値で移動先がシートの外に出ていないかを見直しましょう。OFFSET の引数の考え方はOFFSET関数の使い方で解説しています。

INDIRECTの参照先がない(詳細は専用記事へ)

INDIRECT は、文字列で書いたセル番地を参照に変える関数。その文字列が有効な参照になっていないと #REF! です。

=INDIRECT("集計!A1")   → 「集計」という名前のシートがない → #REF!

シート名の打ち間違いや、参照先のシートの削除が典型です。別ブックを参照している場合は、そのブックが閉じているだけでも #REF! になります。文字列の組み立て方はINDIRECT関数の使い方を参考にしてください。

戻せないときの書き直し方と、#REF!の探し方

Ctrl+Z が効かないときは、まず過去の版に戻せないかを確かめます。それも無理なら、#REF! の部分だけを書き直しましょう。

バージョン履歴で保存前の状態に戻す

OneDrive や SharePoint に保存したブックなら、削除する前の版を復元できます。

  1. OneDrive のフォルダーで、ファイル名を右クリック
  2. [バージョン履歴] を選ぶ
  3. 戻したい日時の版の [その他] メニューから [復元] を選ぶ

デスクトップ版なら、ブックを開いて [ファイル] → [情報] も確認してみてください。過去の版がある場合は、[バージョン履歴] のボタンが表示されます。

復元すると今の版は置き換わり、その版より後の変更は失われる点に注意しましょう。削除の後にほかの作業もしていたなら、復元前に今のブックのコピーを残しておくと安心です。

#REF!の部分だけ正しい参照に書き直す

過去の版にも戻せないときは、数式の #REF! を正しいセル番地に書き換えます。手順は次の3つ。

  1. #REF! が出ているセルを選び、数式バーで #REF! の文字を探す
  2. 本来参照したかったセルを、表や見出しから特定する
  3. #REF! の部分を選んでから、そのセルをクリックするか番地を入力する

数式バーに #REF! の文字がないのに #REF! と表示されるセルもあります。その多くは、#REF! を含む別のセルを参照しているだけです。元の数式を直せば連鎖して消えるので、文字が入っているセルから順に直しましょう。

シートを削除した場合は、同じ名前のシートを作り直してから、置換で正しいシート名を入れ直す方法もあります。Ctrl+H を押し、「検索する文字列」に #REF!、「置換後の文字列」に Sheet2! を入力。これで =#REF!B2 が =Sheet2!B2 に戻ります。

ただし、ほかの原因でできた #REF! まで同じシート名に書き換わってしまいます。削除したシートが1枚だけで、ほかに #REF! がないことを確かめてから使ってください。

ブック内の#REF!を検索で見つける

#REF! がどこにあるか分からないときは、Ctrl+F で #REF! を検索します。デスクトップ版なら [オプション] を開き、次のように設定してから [すべて検索] を押しましょう。

  • 検索場所: ブック(全シートを対象にする)
  • 検索対象: 数式

検索対象を「数式」にすると、数式の中に #REF! の文字が入ったセル、つまり壊れた元のセルが見つかります。Web版では検索の画面がデスクトップ版と異なり、同じオプションが見当たらない場合もあるようです。

エラーのセルをまとめて選びたいなら、デスクトップ版の「条件を選択してジャンプ」が便利です。

  1. [ホーム] タブ → [検索と選択] → [条件を選択してジャンプ] を選ぶ
  2. [数式] を選び、下のチェックを [エラー値] だけにする
  3. [OK] を押すと、エラーが出ているセルがまとめて選択される

こちらは #REF! 以外のエラーや、連鎖して #REF! になったセルも選ばれます。Web版でこの機能が見当たらない場合は、上の検索で探してください。

検索と置換で#REF!を消してはいけない理由

置換で #REF! を空欄にすると、エラーは消えます。しかし正しい参照は戻らず、間違った計算結果のまま残ってしまいます。

=#REF!+A1          → 置換後 =+A1          → A1 の値だけが表示される
=SUM(B2,#REF!,C2)  → 置換後 =SUM(B2,,C2)  → 10+30 で 40

列削除の例では、本来の合計は 60 でした。それが置換後は 40 と表示されるのに、エラーは出ません。

数字が合っていないことに気づきにくいのが、いちばんの問題です。#REF! は消すのではなく、上の手順で正しい参照に直しましょう。

再発防止|削除しても壊れにくい数式にする

範囲参照にする(SUM(A1:A3)は縮むだけ)

=SUM(B2,C2,D2) のように1セルずつ並べると、どれか1つを消しただけで #REF! になります。=SUM(B2:D2) のように範囲で書けば、途中の列を消しても範囲が縮むだけです。

連続したセルを足す・数えるときは、範囲参照を基本にしましょう。ただし範囲をまるごと削除すれば #REF! になる点は、範囲参照でも変わりません。

テーブルの構造化参照・名前付き範囲を使う

表をテーブルに変換すると、=SUM(売上表[金額]) のように列名で参照できます。行の追加や削除に合わせて範囲が自動で伸び縮みするため、範囲の端を消して壊すミスが減ります。

ただし、数式が指している列そのものを削除すると #REF! になります。参照されている列を消す前に、どの数式が使っているかを確認しておきましょう。テーブルの作り方はExcelのテーブル機能の使い方で紹介しています。

名前付き範囲も、壊れたときの直しやすさで役立ちます。数式は名前を参照しているので、名前の参照範囲を1か所直せば、その名前を使う数式にまとめて反映されます。設定方法は名前付き範囲の使い方をどうぞ。

XLOOKUP / INDEX+MATCHに置き換える(Excel 2019以前はINDEX+MATCH)

VLOOKUP の #REF! は、列番号を数字で持っていることが原因でした。検索列と戻り値の列を範囲で指定する XLOOKUP なら、間の列を削除しても参照が自動で付け替わります。

=VLOOKUP(A8,A2:E5,5,FALSE)   → 間の列を消すと #REF! になりうる
=XLOOKUP(A8,A2:A5,E2:E5)     → 間の列を消しても参照が追従する

XLOOKUP は Microsoft 365・Excel 2021 以降と Web版の関数です。Excel 2019 以前なら、同じ考え方で INDEX+MATCH を使います。

=INDEX(E2:E5,MATCH(A8,A2:A5,0))

どちらも、戻り値の列そのものを削除すれば #REF! になります。3つの関数の違いはVLOOKUP・XLOOKUP・INDEX+MATCHの比較で詳しく比べました。古い版で XLOOKUP が #NAME? になる場合は、XLOOKUP・FILTERの#NAME?エラーの対処を確認してください。

よくある質問とまとめ

IFERRORで#REF!を表示しないようにしてもいい?

おすすめしません。=IFERROR(VLOOKUP(…),"") のように包むと #REF! は空欄になりますが、参照が壊れた原因はそのまま残ります。数式が壊れているのか、本当に該当データがないのかの区別もつかなくなるのが困りどころ。

Microsoft も、エラーは隠さずに原因を直すよう案内しています。検索で見つからない #N/A だけを空欄にしたいなら、#N/A 以外は表示したままにできる IFNA を使う手もあります。IFERROR の使いどころはIFERROR関数の使い方で確認できます。

シート名を変えただけでも#REF!になる?

通常の参照(=Sheet2!B2 など)は、シート名を変えると Excel が自動で書き換えるため壊れません。#REF! になるのはシートを削除した場合です。

例外は INDIRECT。=INDIRECT("Sheet2!B2") のように文字列で書いたシート名は、自動では変わりません。そのため名前を変えると #REF! になります。

シート名を変えた直後に出たなら、INDIRECT の文字列を疑ってください。

#REF!と#VALUE!・循環参照の違いは?

#REF! は「参照先がない」、#VALUE! は「データの型が合わない」というサインで、原因がまったく異なります。#VALUE! なら#VALUE!エラーの直し方を見てください。

エラー値ではなく警告が出る循環参照は循環参照エラーの原因と解消方法で解説しています。#NAME? などほかの数式エラーをまとめて見たいときは、Excel参照エラーの直し方が入り口です。

まとめ

  • #REF! は、数式が指していたセル・行・列・シートが消えたか、関数が範囲の外を指しているサイン
  • 削除や貼り付けの直後なら Ctrl+Z。保存後は OneDrive・SharePoint のバージョン履歴から戻す
  • シートの削除は Ctrl+Z で戻せないことが多いので、保存前なら閉じて開き直すのも選択肢
  • 戻せないときは、数式バーの #REF! の部分を正しい参照に書き直す(置換や IFERROR で消さない)
  • VLOOKUP・INDEX・OFFSET・INDIRECT は、列番号や行番号、移動先、文字列が範囲内かを確認する
  • 再発防止は範囲参照・テーブル・名前付き範囲、列番号を使わない XLOOKUP / INDEX+MATCH

#REF! が出たら、まず数式バーで #REF! の文字の位置を確認してください。どの参照が消えたかが分かれば、戻すか書き直すかはすぐ決まります。

タイトルとURLをコピーしました