#REF!エラーが出たら、最初に見る3つの場所と直し方

「昨日まで動いていた表が、開いたら #REF! だらけになっている…」

集計表のあちこちに #REF! が並ぶと、どこから手を付ければいいのか分からなくなりますよね。

#REF! は「参照していた場所が見つからない」という意味のエラーです。数式が見に行っていたセルや列、シートがなくなったときに出ます。原因の多くは、表のどこかを消したり動かしたりしたこと。落ち着いて順番に見ていけば、たいていは元に戻せます。今日は、最初に見るところを3つにしぼってご紹介します。

1. 数式の中の「#REF!」を見る

まず、エラーが出ているセルを1つ選んで、上の数式バーを見てみてください。

たとえば、よくある例として

・=B3*#REF!

・=SUM(C2:#REF!)

のように、数式の中に #REF! という文字そのものが入っていたら、その部分の参照先が消えてしまった印です。数式の中の #REF! が、消えた場所の目印になります。

このとき、消してしまった直後なら「元に戻す」（Ctrl＋Z）がいちばん早い直し方です。時間がたっていて戻せないときは、#REF! の部分を正しいセル（たとえば D3）に打ち直します。

表が大きくてエラーの場所が多いときは、検索（Ctrl＋F）で「#REF!」を探すと、壊れた数式をまとめて見つけられます。Excelなら検索の対象を「数式」に、スプレッドシートなら「数式内も検索」にしておくのがポイントです。どの列の数式が壊れているかが分かると、何が消えたのかの見当もつけやすくなります。

2. 最近「消したもの」を思い出す

数式の中に #REF! があったら、次は「何が消えたのか」を考えます。よくあるのは次の3つです。

・列や行を削除した（使っていない列だと思って消したら、計算に使われていた）

・シートを削除した（別のシートから合計を引っぱっていた）

・切り取ったセルを、ほかの数式が見ているセルの上に貼り付けた

ほかの人と同じファイルを使っている場合は、「最近この表で列を消した人はいませんか？」と聞いてみるのも近道です。

エラーが1か所だけなら、そこを直せば終わりです。ただ、同じ列の数式を下までコピーしている場合は、一番上のセルを直してから、下までコピーし直すと早く片付きます。

3. 数式が指している「範囲」を見る

何も消していないのに #REF! が出ることもあります。この場合は、数式が指している範囲が、表の外にはみ出していないかを見ます。

・VLOOKUP の「何列目を取り出すか」の数字が、範囲の列の数より大きい

・1つ上のセルを見る数式（2行目の =A1 など）を、1行目にコピーした（それより上のセルはないのでエラーになる）

VLOOKUP の場合は、範囲を A:C の3列にしているのに「4列目」を指定すると #REF! になります。範囲を広げるか、数字を見直してください。

表を作り直すときに列を消すと、別の場所の数式が壊れることがあります。「この列は使われていないかな？」と迷ったら、消す前にその列のセルを選んで、Excelなら数式タブの「参照先のトレース」を押してみてください。矢印が出たら、どこかの計算に使われている印です。

ここまで見ても原因が分からない、表が大きくてどこから手を付けたらいいか分からない、という場合は、今の表の関数エラーの修正もお手伝いしています。

まとめ：#REF! は「どこが消えたか」を探すエラー

・数式の中の #REF! を見つける

・最近消した列・行・シートを思い出す

・範囲が表の外にはみ出していないかを見る

#REF! は、表が壊れたように見えて驚きますが、原因は「参照先がなくなった」ことだけです。消す前に、使われていないか確かめる習慣があると、同じエラーにはぐっと出会いにくくなります。

関数で作った表を、毎月の自動集計まで広げたいときは、Pythonでの自動化もご相談いただけます。