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 に保存したブックなら、削除する前の版を復元できます。
- OneDrive のフォルダーで、ファイル名を右クリック
- [バージョン履歴] を選ぶ
- 戻したい日時の版の [その他] メニューから [復元] を選ぶ
デスクトップ版なら、ブックを開いて [ファイル] → [情報] も確認してみてください。過去の版がある場合は、[バージョン履歴] のボタンが表示されます。
復元すると今の版は置き換わり、その版より後の変更は失われる点に注意しましょう。削除の後にほかの作業もしていたなら、復元前に今のブックのコピーを残しておくと安心です。
#REF!の部分だけ正しい参照に書き直す
過去の版にも戻せないときは、数式の #REF! を正しいセル番地に書き換えます。手順は次の3つ。
#REF!が出ているセルを選び、数式バーで#REF!の文字を探す- 本来参照したかったセルを、表や見出しから特定する
#REF!の部分を選んでから、そのセルをクリックするか番地を入力する
数式バーに #REF! の文字がないのに #REF! と表示されるセルもあります。その多くは、#REF! を含む別のセルを参照しているだけです。元の数式を直せば連鎖して消えるので、文字が入っているセルから順に直しましょう。
シートを削除した場合は、同じ名前のシートを作り直してから、置換で正しいシート名を入れ直す方法もあります。Ctrl+H を押し、「検索する文字列」に #REF!、「置換後の文字列」に Sheet2! を入力。これで =#REF!B2 が =Sheet2!B2 に戻ります。
ただし、ほかの原因でできた #REF! まで同じシート名に書き換わってしまいます。削除したシートが1枚だけで、ほかに #REF! がないことを確かめてから使ってください。
ブック内の#REF!を検索で見つける
#REF! がどこにあるか分からないときは、Ctrl+F で #REF! を検索します。デスクトップ版なら [オプション] を開き、次のように設定してから [すべて検索] を押しましょう。
- 検索場所: ブック(全シートを対象にする)
- 検索対象: 数式
検索対象を「数式」にすると、数式の中に #REF! の文字が入ったセル、つまり壊れた元のセルが見つかります。Web版では検索の画面がデスクトップ版と異なり、同じオプションが見当たらない場合もあるようです。
エラーのセルをまとめて選びたいなら、デスクトップ版の「条件を選択してジャンプ」が便利です。
- [ホーム] タブ → [検索と選択] → [条件を選択してジャンプ] を選ぶ
- [数式] を選び、下のチェックを [エラー値] だけにする
- [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! の文字の位置を確認してください。どの参照が消えたかが分かれば、戻すか書き直すかはすぐ決まります。
