Excelが#REF!だらけになったとき、直す前に確認する4つのこと

シートを1枚消したら、他のシートが一斉に #REF! になった。そういう相談をよく受けます。あわてて数式を打ち直す前に、確認しておくと復旧が早くなることが4つあります。

■ #REF! は「参照先が消えた」という意味です

#REF! は計算の失敗ではありません。数式が見ようとしていたセルが、この世から無くなったという意味です。よくある原因は次の3つです。

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

・参照していたシートを削除した

・別ブックを参照していて、そのブックが移動・改名された

大事なのは、#REF! になった時点で元の参照先の情報は数式から消えているということです。数式バーを見ても「どこを見ていたか」は書かれていません。だから、後から推測で直すことになります。ここが厄介なところです。

■ 1. まず Ctrl+Z で戻せないか試す

いちばん確実なのは、消す前に戻すことです。ファイルを閉じていなければ、Ctrl+Z で削除を取り消せます。

保存してしまった場合でも、まだ望みはあります。「ファイル」→「情報」→「バージョン履歴」（OneDriveやSharePointに置いている場合）や、「ブックの管理」→「保存されていないブックの回復」から、削除前の状態が残っていることがあります。

新しい数式を書き始めるのは、この確認が終わってからにしてください。書き直しながら上書き保存すると、戻せる可能性が減ります。

■ 2. #REF! が何個あるかを先に数える

直す前に規模を知ります。Ctrl+F で「検索する文字列」に #REF! と入れ、「検索場所」を「ブック」、「検索対象」を「値」にして「すべて検索」を押します。

これで、ブック全体のどのシートのどのセルが壊れているかが一覧で出ます。件数も表示されます。

3件なら手で直せます。300件なら、手で直すべきではありません。規模が分かると、この後の判断が変わります。

■ 3. 「壊れた数式」と「壊れた結果」を区別する

見落とされやすいところです。#REF! と表示されているセルには、2種類あります。

・数式そのものが壊れている：=SUM(#REF!) のように、数式の中に #REF! が埋まっている。これは書き直しが必要です

・参照先が壊れているだけ：=A1 と書いてあって、A1 が #REF! を表示している。これは A1 を直せば連鎖的に直ります

後者は、元をたどって1か所直せば全部戻ります。検索結果を見て、数式バーに #REF! の文字が入っているセルだけを拾ってください。そこが本当の発生源です。

「300件壊れた」と思っていたものが、実際は発生源3か所ということはよくあります。

■ 4. 同じことが起きない形に変えておく

直したあと、できれば次の一手まで入れておくと安心です。

行や列の削除で壊れやすいのは、=SUM(B2:B100) のようにセル番地を直接書いている数式です。次の書き方にしておくと、削除に強くなります。

・テーブル（Ctrl+T）にして、=SUM(テーブル1[金額]) のように名前で参照する

・名前の定義を使い、範囲に名前を付けて参照する

・別シート参照が多い場合は、参照元を1枚のシートに集約しておく

全部を書き換える必要はありません。壊れて困ったところだけで十分です。

■ VLOOKUPの #REF! は少し事情が違います

=VLOOKUP(A2, B:D, 4, FALSE) のような数式で #REF! が出る場合、参照先は消えていません。列番号が範囲の外を指しているだけです。この例では範囲がB〜Dの3列なのに、4列目を取ろうとしています。

この場合は範囲を広げるか、列番号を直せば済みます。削除の巻き添えではないので、Ctrl+Z を探す必要はありません。

■ まとめ

1. 数式を書き直す前に、戻せないかを確認する

2. Ctrl+F で件数と場所を把握する

3. 発生源だけを拾う（連鎖しているセルは自然に直る）

4. 直したあと、壊れにくい参照の仕方に変えておく

この順番で進めると、300件の #REF! が数か所の修正で片付くことがあります。逆に、いきなり端から打ち直すと、直したつもりの数式がまた別の #REF! を生みます。

原因の特定から直したファイルのお渡しまで、1か所だけお引き受けする出品を用意しています。

「Excelの数式・マクロのエラーを直します」3,000円

どこが発生源か分からない状態でも構いません。ファイルと、困っている箇所をお送りいただければ、こちらで追いかけます。