【Excel】#REF!エラーが表示される原因と直し方

Excelで数式や関数を使用していると、「#REF!」エラーが表示されることがあります。

原因は、数式で指定したセルや範囲を削除したこと、数式のコピーやオートフィルによって参照先がシートの範囲外になったこと、一部の関数で不適切な引数を指定したこと、存在しないシートや外部ブックを参照したことなどです。

この記事では、「#REF!」エラーが表示される主な原因と直し方を、具体的な数式を使って解説します。

原因1：参照先のセルや範囲を削除した

参照先のセルや範囲を削除した場合、「#REF!」エラーが発生します。

数式が参照しているセル・行・列を削除すると、参照先が存在しなくなり、数式の一部が「#REF!」に置き換わります。

なお、Deleteキーを押してセルの内容だけを消した場合は、セル自体は残っているため、通常は「#REF!」エラーになりません。

元の参照先を推測して数式を修正するか、自動回復ファイルやバージョン履歴から削除前の数式を確認します。

原因2：関数で引数に不適切な値を指定した

VLOOKUP関数やINDEX関数では、参照範囲を超える列番号・行番号を指定すると「#REF!」エラーが発生します。

また、INDIRECT関数では、存在しないセル・シート・ブックを参照する文字列を指定すると「#REF!」エラーが発生します。

例1：VLOOKUP関数、INDEX関数

VLOOKUP関数

VLOOKUP関数の第三引数が、第二引数の列の幅を超えると「#REF!」エラーが発生します。
第二引数の範囲を、第三引数で指定した列数まで広げるか、第三引数を第二引数の列数以下に修正してください。

失敗例

失敗例です。第三引数を第二引数の範囲外で指定してしまった場合です。

エラーサンプル
=VLOOKUP(1,A1:B4,5,FALSE)　　// 第三引数を第二引数の範囲外で指定したため、エラー

第二引数の「A1:B4」の範囲は2列しかありませんが、第三引数で「5」を指定しており、範囲を超えています。
表範囲の左端にあるA列を1列目として、5列目のデータを取得する指定になっているため、第二引数を「A1:E4」として範囲を広げるか、第三引数を「2」にして、範囲内を指定するとエラーが解消します。

INDEX関数

INDEX関数では、第二引数や第三引数に、第一引数の範囲を超える行番号・列番号を指定すると「#REF!」エラーが発生します。

失敗例①：第二引数が第一引数の範囲外を指定してしまった

エラーサンプル
=INDEX(D2:D5,5,1) 　// 第二引数を第一引数の範囲外で指定したため、エラー

第一引数の「D2:D5」は4行分の範囲ですが、第二引数で「5」を指定しているため、範囲を超えています。
「D2:D5」の範囲から5行目のデータを取得しようとしているため、第一引数を「D2:D6」まで広げるか、第二引数を「1～4」の範囲内に修正するとエラーが解消します。

失敗例②：第三引数が第一引数の範囲外を指定してしまった

エラーサンプル
=INDEX(D3:F5,1,4)　// 第三引数を第一引数の範囲外で指定したため、エラー

第一引数の「D3:F5」の範囲は3列しかありませんが、第三引数で「4」を指定しており、範囲を超えています。
「D3:F5」の範囲から4列目のデータを取得しようとしているため、第一引数を「D3:G5」まで広げるか、第三引数を「1～3」の範囲内に修正するとエラーが解消します。

例2：INDIRECT関数

INDIRECT関数は指定したセルの値を取得します。
このとき、「A0」や「A」など存在しないセルを指定すると「#REF!」エラーが発生します。
また、INDIRECT関数を使用時に他のシートを指定したときにそのシート名を変更すると、シート名が古いままになってしまい「#REF!」エラーが発生する場合があります。

失敗例1：存在しないセルを指定した

「A0」や「A」など存在しないセルを指定すると「#REF!」エラーが発生します。

エラーサンプル①
=INDIRECT("A0")　　// 存在しない「A0」セルを参照したため、エラー

エラーサンプル②（A1は未入力）
=INDIRECT("A"&A1)　// セルの指定が正しくないため、エラー

エラーの解決方法としては、セルの指定を正しくしてください。

エラーサンプル②のように数式でセルを指定している場合は、検証機能を使うと引数がどのような値か確認できます。
［数式］タブ→ ［数式の検証］（fxのアイコン）から［検証］もしくは［ステップ イン］で引数の値を確認できます。

失敗例2：シート名を変更した

INDIRECT関数の引数で、他のシートを指定したときにそのシート名を変更すると、シート名が古いままになってしまい「#REF!」エラーが発生する場合があります。

通常、参照しているシート名を変更しても動的（自動的）に参照しているシート名が変更されますが、INDIRECT関数で『文字列』として指定しているため、動的にシート名が反映されません。

エラーサンプル（シート名「Sheet1」を「仕入表」に変更した）
=INDIRECT("Sheet1!A1")　　// 存在しない「Sheet1」を参照したため、エラー

エラーの解決方法としては、INDIRECT関数の引数のシート名を修正します。

自動的に反映する方法もありますが、やや難しいです。

※下の見出しをクリックするとアコーディオンが開きます。

失敗例3：参照先のブックを閉じた

INDIRECT関数で他のブックを参照しているとき、そのブックを閉じてしまうと「#REF!」エラーが発生する場合があります。

解消方法としては、閉じてしまったファイルを開いてください。

失敗例4：存在しないブックを参照した

INDIRECT関数で他のブックを参照しているとき、参照先のブック名に誤りがあると「#REF!」エラーが発生する場合があります。

解消方法としては、正しいブック名を指定してください。

原因3：数式のコピーやオートフィルで参照先が範囲外になった

数式をコピー＆ペーストしたり、オートフィルでコピーしたりすると、相対参照はコピー先に合わせて変化します。

このとき、変更後の参照先がシートの範囲外になると、「#REF!」エラーが発生します。

例えば、B1に「=A1」と入力されているとします。
B1の数式をA1へコピーすると、参照先も1列左へ移動しようとします。しかし、A列より左には列が存在しないため、「=#REF!」となってしまいます。

エラーを解消するには、数式の参照先を正しいセルに修正します。コピーやオートフィルをしても参照先を変えたくない場合は、「=$A$1」のように絶対参照を使用してください。

原因4：参照先のシートを削除した

参照先のシートを削除した場合、「#REF!」エラーが発生します。

シートを削除した場合は、数式の参照先を設定し直すか、保存済みのファイルやバージョン履歴から削除前のシートを復元します。

シートの削除は、［Ctrl］+［Z］で操作の取り消しができないため、削除前はブックを保存すると安全です。

シートを削除すると数式が「=#REF!A1:D13」のように参照先のセルや範囲が表示されますが、エラーが発生したブックを閉じてしまうと「=#REF!」のみになってしまいます。

削除前のシートが自動回復ファイルや保存済みのファイルに残っている場合は、そのシートをコピー＆ペーストし、改めて数式の参照先を指定します。

「#REF!」となる元の参照先は分かる？

結論から書きますと、確実に「#REF!」となってしまった元の参照先は分かりません。

そのため、周辺の数式から推測するか、自動回復ファイルやバージョン履歴から削除前の数式を確認します。

エラーが発生したセルや周辺のセルから推察する

操作を元に戻して、確認する

セル・行・列を削除した直後であれば、［Ctrl］＋［Z］を押して削除前の状態に戻します。

数式が元に戻った場合は、削除したセルを残すか、数式の参照先を変更してから改めて削除してください。

なお、シートの削除は［Ctrl］＋［Z］では元に戻せないため、バージョン履歴や保存済みのファイルなどから復元します。

クイックアクセスツールバーに「元に戻す」がある場合、「元に戻す」の右にある▼で、過去の操作一覧が表示されます。

「#REF!」のセルの数式バーを確認する

数式バーが「=#REF!A1:D13」のようにシート名が『#REF!』となっている場合、シートを削除したことが原因です。直近で削除したシートを疑いましょう。

数式バーが「=A11+#REF!+A13+A14」となっていたら、「#REF!は、A12では？」と予測できます。

自動回復ファイルや履歴から確認する

自動回復ファイルが残っている場合は、そのファイルを開き、「#REF!」となっているセルの元の数式を確認します。

なお、自動回復ファイルは、［Ctrl］＋［S］などでファイルを通常保存すると消える場合があります。

OneDriveやSharePointに保存している場合は、「バージョン履歴」から通常保存後も過去の状態を確認できるため、元の数式を特定する際に便利です。

自動回復ファイルが残っている場合

自動回復ファイルが残っている場合は、［ファイル］タブ→［情報］→［ブックの管理］から開くことができます。

自動回復ファイルの保存場所は、［ファイル］タブ→［オプション］→［保存］にある［自動回復用ファイルの場所］で確認できます。

OneDrive や SharePoint 上のファイル

OneDriveまたはSharePointに保存しているブックは、［ファイル］→［情報］→［バージョン履歴］から以前の状態を開き、変更前の数式を確認できます。

まとめ

#REF!エラーは、数式が存在しないセル・範囲・シートなどを参照している場合に表示されます。

参照先のセル・行・列を削除した直後であれば、［Ctrl］＋［Z］で元に戻せます。コピーやオートフィル後に表示された場合は、相対参照によって参照先が範囲外になっていないか確認してください。関数を使用している場合は、参照範囲や行番号・列番号、INDIRECT関数で指定した参照文字列を確認します。

元の参照先が分からない場合は、周辺の数式から推測するか、自動回復ファイルやバージョン履歴から削除前の数式を確認しましょう。