数式を入力していたセルや、数式が参照していた行・列を削除した直後に#REF!というエラーが表示されて驚いた経験がある方は多いはずです。結論から言うと、#REF!エラーは「数式が参照していたセル自体が削除されて、参照先が存在しなくなった」ことが原因で発生します。この記事では発生する典型的な状況、直し方、事前に防ぐための書き方まで解説します。
#REF!エラーが起こる基本の原因
#REF!(Reference=参照、の意味)エラーは、数式が参照していたセル・行・列・シートが削除され、参照先が「存在しない」状態になったときに表示されます。数式の書き間違いではなく、後からの削除操作によって発生する点が他のエラーと異なります。
具体例：参照していた行を削除するとどうなるか
次のような売上データ（A1:B6）があり、D2に「=SUM(B2:B6)」（B列の合計）という数式が入っているとします。A列が担当者、B列が金額です。
| A（担当者） | B（金額） | |
|---|---|---|
| 2 | 佐藤 | 12000 |
| 3 | 鈴木 | 8500 |
| 4 | 田中 | 15000 |
| 5 | 高橋 | 9800 |
| 6 | 山本 | 11000 |
ここで、他のセルに「=B3」のようにB3セル(鈴木の金額)を直接参照する数式を作ったあとで、3行目(鈴木の行)を削除すると、参照先だったB3セルそのものがなくなるため、その数式は#REF!エラーになります。
一方、上記の「=SUM(B2:B6)」のように範囲を指定した集計関数は、範囲の中の1行が削除されても、範囲全体が自動的に縮まって(=SUM(B2:B5)のようになって)再計算されるため、#REF!エラーにはなりません。ピンポイントでセルを直接指定する数式ほど、削除の影響を受けやすいということです。
シートの削除や外部ブックの参照切れでも発生する
他のシートやファイルを参照している数式で、参照先のシートそのものを削除したり、参照先の外部ブックのファイル名・保存場所を変更したりした場合も、参照先が存在しなくなるため同様に#REF!エラーになります。参照先のシート名を文字列から組み立てるINDIRECT関数でシート名を可変にして参照する方法を使っている場合も、組み立てた文字列に対応するシートが存在しなければ同じく#REF!になります。
#REF!エラーの直し方
- 直後であれば元に戻す(Ctrl+Z): 削除操作の直後であれば、元に戻す操作で削除前の状態に戻し、数式を復活させるのが最も確実です。
- 数式を参照先ごと手動で直す: 元に戻せない場合は、#REF!の部分を、現在存在する正しいセル・シートへの参照に手動で書き換えます。
- 削除ではなく「値をクリア」に変える: 今後は行・列・シートを丸ごと削除する代わりに、中身の値だけをDeleteキーでクリアすれば、セル自体は残るため参照が#REF!にならずに済みます。
よくあるエラー・注意点
- コピペで参照がずれるケースとは原因が異なる: 参照がずれて違う値を拾ってしまう問題は、多くの場合「相対参照と絶対参照の混同」が原因で、#REF!エラーにはならず一見正しく計算されてしまいます。エラー表示が出ない分、実は#REF!より発見が遅れやすいので注意しましょう。
- SUMやAVERAGEなど範囲指定の関数は削除に強い: 上記の通り、範囲(B2:B6のような書き方)を指定する関数は、範囲内の行削除であれば自動的に範囲が縮まり、#REF!エラーになりません。個別セル参照が必要な場面以外は、範囲で指定しておくと事故を防げます。
- #REF!という文字列自体がコピーで複製される: エラーが発生した数式をコピーした場合、コピー先にも#REF!が含まれた数式がそのまま複製されるため、1箇所を直しても他のセルに同じエラーが残っていないか確認しましょう。
まとめ
#REF!エラーは数式の書き間違いではなく、参照先のセル・シートが削除されたことが原因で発生します。直後であればCtrl+Zで元に戻すのが最も確実で、今後は削除ではなく値のクリアを使う、個別セル参照より範囲指定の関数を使うといった書き方で予防できます。複数条件での集計を範囲指定で行う考え方はSUMIFSで複数条件のOR集計をする方法も参考になります。