Excelの#REF!エラーはなぜ出る？原因と直し方

#REF!は、数式がそこにないセルを参照しているという意味です。よくある原因は行、列、シートの削除です。C列を削除すると、Excelは=B2*C2を=B2*#REF!に書き換え、それ以降の結果は#REF!になります。削除した直後にCtrl+Z(MacではCmd+Z)を押すと、列と数式が元に戻ります。

数式リセット

D2fx

=B2*#REF!

	A	B	C	D
1	Product	Price	Qty	Total
2	Apple	1.2	10	#REF!
3	Pear	1.5	20	#REF!
4	Plum	0.8	15	#REF!
5	Bread	2.4	5	#REF!

#REF! 存在しないセルを参照しています。

数量の列は削除された後に入力し直されていますが、数式はまだ#REF!のままです。一度失われた参照をExcelが直すことはありません。D2をクリックして#REF!をC2に置き換え、Enterを押します。列全体がそれに合わせて変わり、D2は12を表示します。

#REF!が数式に入る仕組み

数式が使っていたセルがなくなると、Excelはいつでも数式に#REF!を書き込みます：

範囲の中のセルを削除するのは安全です。C列を削除すると、=SUM(B2:D2)は=SUM(B2:C2)になります。範囲の最初や最後のセルを削除しても、範囲が縮むだけです。そのため、=B2+#REF!+C2になってしまう=B2+C2+D2より、=SUM(B2:D2)のほうが安全です。

VLOOKUPが#REF!を返す理由

VLOOKUPの3つ目の引数は、表の範囲の中の列を数えます。それが範囲の列数より大きいと、結果は#REF!になります。

数式リセット

F2fx

=VLOOKUP(E2,A2:C6,4,FALSE)

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Pear	#REF!
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

#REF! 存在しないセルを参照しています。

A2:C6は3列なので、4列目は存在しません。4を3に変えると、F2は25を表示します。これは検索する表から列を削除した後によく起こります。範囲は縮みますが、直接書いた列番号は変わらないからです。XLOOKUPやINDEXとMATCHの組み合わせは、=XLOOKUP(E2,A2:A6,C2:C6)のように返す列を直接指定するので、この問題を避けられます。そのほかの引数についてはVLOOKUPを参照してください。

INDEXやOFFSETでの#REF!

INDEXは行番号や列番号が範囲の外にあると、OFFSETは1行目より上やA列より左に移動すると#REF!を返します。

数式リセット

B2fx

=INDEX(A2:A6,6)

	A	B	C
1	Score	Result	What it asks for
2	88	#REF!	6th value of 5
3	72	95	3rd value of 5
4	95	#REF!	2 rows above A2
5	64	81	4 rows below A2
6	81

#REF! 存在しないセルを参照しています。

A2:A6には5つの点数があるので、INDEX(A2:A6,6)は#REF!ですが、INDEX(A2:A6,3)は95を返します。0行目は存在しないのでOFFSET(A2,-2,0)は#REF!になり、OFFSET(A2,4,0)はA6に行き着いて81になります。位置がほかの数式(MATCHやCOUNT)から来ているときは、先にその数式を確かめてください。詳しくはINDEXのページを参照してください。

INDIRECTも、文字列が有効な番地でないとき(最後の列はXFDなので=INDIRECT("ZZZ1"))や、閉じているブックを指しているときに#REF!になります。

数式をコピーしたときの#REF!

相対参照は数式と一緒に動きます。十分に上や横にコピーすると、参照がシートからはみ出します：

C3:  =B2*2        (one row up, one column back)
copy C3 to B2:  =A1*2
copy C3 to A2:  =#REF!*2    (there is no column before A)

別のシートやブックにコピーした数式が、そこにないセルを指しているときも同じことが起こります。動いてはいけないセルは$で固定する(=$B$2*2)か、セルではなく数式バーから数式の文字列をコピーします。$については絶対参照で説明しています。

ブックの中の#REF!をすべて探して取り除く

1. Ctrl+F(MacではCmd+F)を押して#REF!と入力し、オプションを開いて検索対象を数式にし、すべて検索をクリックします。壊れた参照を含むすべての数式が一覧になります。
2. 多くを一度に直すにはCtrl+H(MacではControl+H)を使います。#REF!を検索して正しい参照に置き換えます。ただし、見つかったものすべてに同じセルを入れるべきときだけです。
3. 数式 > 名前の管理を確認します。参照範囲の列に#REF!と表示される名前は、それを使うすべての数式を壊します。
4. 削除したデータがもう戻らず、数式も要らないなら、セルを選択して数式をその値に置き換えます(コピーしてからホーム > 貼り付け > 値)。エラー値はエラーのまま残るので、その後でそれらのセルを削除します。

#REF!を返す検索を直す

数式リセット

F2fx

	A	B	C	D	E	F
1	Product	Price	Stock		Look for	Stock
2	Apple	1.2	40		Plum
3	Pear	1.5	25
4	Plum	0.8	60
5	Bread	2.4	12
6	Milk	1.1	30

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: =VLOOKUP(E2,A2:C6,4,FALSE)は#REF!を返しました。F2に、E2の商品の在庫を返す動く検索の数式を書いてください。

答え合わせ答えを見る

ここで60を返し、データに合わせて変わる検索ならどれでも合格です。列番号3のVLOOKUP、=XLOOKUP(E2,A2:A6,C2:C6)、=INDEX(C2:C6,MATCH(E2,A2:A6,0))のどれでも構いません。