Excelを使っているときに、「#REF！」エラーが出てきて焦ったことはないでしょうか？
実はExcelではよくあるエラーで、原因をしっかり確認すれば解消できるエラーです。
ただ原因の探し方であったり、どう対応したらいいの？と迷われる方もいらっしゃると思います。
本記事では、初心者向けの基本対処法と、実務者向けの応用対処法をまとめて解説します。
- #REF!エラーが発生する原因
- #REF!エラーの基本的な対処法
- 実務で役立つエラー対策
#REF!エラーとは？
「#REF!」エラーとは、参照先がないですよというエラーになります。REF=reference（参照）という意味ですね。
ポイントは数式が壊れたのではなく、何らかの原因で参照が切れてしまったということです。
REFエラーのよくある原因
「#REF!」エラーは、参照先がないことが原因で発生するエラーです。
ここでは、REFエラーになる主な原因を紹介します。使っているファイルでエラーが発生している場合、どの原因が考えられるかチェックしてみてください。
セル削除によるエラー
よく発生しがちなのが、参照先削除によるエラーです。
数式で参照していたセル、シート、別ファイルがなくなってしまうケースですね。当然ながら参照先がなくなってしまいますので、Excelとしても「参照先はどこ？」となってしまうわけです。
コピーや移動で参照がズレた
数式のコピーが原因で、「#REF!」エラーが発生することがあります。
例えばC列にA列を参照する数式を入力したとします。このC列の数式をB列にコピーすると、以下のように参照エラーが発生してしまいます。これは相対参照によるもので、行列をずらした分参照先がずれるため、エラーが発生してしまったということです。
外部Excelファイルのリンク切れ
外部のExcelを参照している場合、その参照先がなくなることでも「#REF!」エラーは発生します。
例えば参照先のファイルがなくなったり、格納先が変わったり、シート名が変わったりすると、当然ながら「参照先がないよ」とExcelがヘルプを出すわけです。参照先のリンク先やシート名などは、当然ながら自動で変更はされないので、参照エラーが発生してしまいます。
関数の指定ミス
関数で参照範囲外を指定してしまうケースも、「#REF!」エラーが発生する原因です。
例えば、VLOOKUP関数で範囲をA:B列に指定しているのに、指定値を3にした場合などです。範囲は2列分しかないので、3列目は範囲外になってしまいますね。その場合も参照先がないということでエラーになってしまいます。
エラー発生防止のポイント
では、「#REF!」エラーが発生しないようにするにはどうしたらいいでしょうか。
ここでは、「#REF!」エラーを防止するためのポイントを説明します。
参照セルをむやみに削除しない
参照先を削除してしまうと、REFエラーが発生する原因になります。
そのため、参照セルをむやみに削除しなければ、エラーは発生しません。身も蓋もない話になってしまいますが・・・。まずはファイルの中身を確認してから、不要なセル・ファイルを削除しましょう。
参照セルをむやみに削除しないためには、「数式のトレース」を使って事前に数式を確認することをおすすめします。後述しますので、ぜひチェックしてみてください。
絶対参照と相対参照の違いを理解する
絶対参照と相対参照を使い分けることも重要になります。
絶対参照とは、（$A$1）のように行や列の前にドルマークみたいなものが付いている状況のことで、参照先を固定することができます。
参照先が固定されるので、数式をコピーしたとしても、参照外になることはありません。
行のみ・列のみで固定することもできるので、参照ズレの対策として有効な方法です。
- 「F4」キーを押すことで、参照パターンを変更できます。①絶対参照→②行の絶対参照→③列の絶対参照→④相対参照の順番に切り替わります。
絶対参照・相対参照に関してはこちらで詳しく解説していますので、チェックしてみてください。
外部のExcelファイルを参照している場合、変更内容を確認する
外部のExcelファイルを参照していたが、「#REF!」エラーが発生した場合、参照していた外部のExcelファイルにどのような変更があったかを確認することが大事です。
基本的なことですが、原因を特定しない限り、参照エラーを解消することはできません。
しかし、外部のExcelファイル参照エラーの多くは、
・参照していたファイルの格納先が変わった
・参照していたファイルの名前が変わった
というケースが多いと思います。
この場合、「ブックのリンク」という機能を活用することで、発生したエラーを解消することができます。
後述しますので、ぜひチェックしてみてください。
複雑な数式を整理する
こちらも非常に重要なエラー防止策です。
エラーが発生したときに大変なのが、原因を突き止めることです。つまり、数式が複雑であればあるほど、見にくければ見にくいほど、エラーの原因を突き止めることが困難になります。
Excelファイルは多くの人と共有すると思います。他人が作った数式を解析するのって、結構骨が折れる作業ですよね。なので、数式をシンプルにするに越したことはないんです。
過去に私も困った経験があるため、誰が見ても分かりやすい数式にすることは大きな意味があります。
具体的な対策の一つとして、「名前の定義」を使用する方法があります。
こちらも後述しておりますので、ぜひチェックしてみてください。
実務者が知っておくと便利な対処方法
上記では、「#REF!」エラーを防止するためのポイントを説明しました。
ここからは、「#REF!」エラーの発生防止/発生した場合の具体的な対策を紹介していきます。
私自身、多くのExcelファイルを扱うので、よく「#REF!」エラーを見かけます。そのエラー解消に使用しているテクニックの一部を紹介します。
エラーの影響範囲を調べる（数式のトレース）
「数式のトレース」機能を使うことで、その数式がどこを参照してるかが可視化できます。
数式がより複雑だと、何をしているのか？どこを参照しているかが分かりにくいですよね。この「数式のトレース」機能は、どのセルを参照しているか、どのセルから参照されているかが矢印が表示されるので、視覚的に理解しやすい便利な機能です。これを使うことで、ここは参照されているセルだから削除してはいけないな、とむやみなセル削除を防止することができます。
「数式のトレース」は、「数式」タブの「ワークシート分析」部分にあります。
上記の画像でも分かるとおり、「数式のトレース」は、「参照元のトレース」と「参照先のトレース」の2種類あります。
2つの違いについては、以下のとおりとなります。
・参照元のトレース：選択しているセルに影響を与えるセルを示す矢印を表示
・参照先のトレース：選択しているセルが影響を与えるセルを示す矢印を表示
この二つのトレースの違いは、以下の画像を見ていただけるとイメージしやすいと思います。
青色の矢印が「トレース」となります。
セルD4は「B4×C4」の計算結果となるため、参照元のトレースは「B4」と「C4」を示します。
逆にセルD4は、セルD7（=SUM(D2:D6））の計算結果の一部であるため、参照先のトレースは「D7」を示すことになります。
このように、「数式のトレース」を使用することにより、どのセルに影響するかが可視化できるため、数式の確認には便利な機能となります。
エラーを一時的に隠す（IFERROR関数）
エラー解消ではなく、一時的にエラー値を表示したくない場合は、IFERROR関数が便利です。
報告書や取引先資料など、人に見せる資料にエラーが表示されていると、資料内容に影響はないにしても、見栄えが良くないですよね。あくまで「#REF!」エラーを見せないだけで、根本的な参照エラーは解決していませんが、見せる資料などには便利なテクニックとなります。
IFERROR関数の書式は以下の通りです。
=IFERROR(値,エラーの場合の値)「エラーの場合の値」に、エラーの場合に表示したい値を設定します。
（私はよく「-」を設定し、自分ではエラー値かどうか分かるようにしています）
このIFERROR関数を使うことで、「#REF!」エラーが発生しても、指定した値を表示してエラー値を非表示にすることができます。
外部のExcelファイルへのリンクを変更する（ブックのリンク）
外部のExcelファイルの格納先が変わった、もしくはファイル名が変わった場合、「ブックのリンク」を使うことで、リンクしなおすことが可能です。
「ブックのリンク」は、「データ」タブの中に存在します。
「ブックのリンク」を選択すると、右側にリンクしている外部のExcelファイル一覧が表示されます。
そのExcel一覧の「・・・」を選択することでメニューが開きます。
今回は、「参照しているファイルの格納先が変わった」、もしくは「ファイル名が変わった」ケースですので、「ソースの変更」で参照するExcelファイルを変更すれば、エラーが解消します。
複雑な数式を管理するコツ（名前の定義）
エラーを発生させないためには、数式をいかに分かりやすくできるかがポイントとなります。
Excelファイルを作り込んでいると、どうしても数式が複雑になりがちですよね。
そんなときは「名前の定義」を活用することで、数式の管理が楽になります。
例えば以下のような社員名簿があり、社員番号からVLOOKUP関数で社員名を検索するとき、「A1:E13」みたいにセル番地で範囲を指定しますよね。
それはもちろん問題ないのですが、もっと複雑な数式・広い範囲になると、エラーが発生したときはそれを読み解く作業が負担になってしまいます。
ぱっと見ただけで分かる数式になっていると、エラー発生時の対応も楽になりそうですよね。
=VLOOKUP(G3,A1:E13,2,0)
「名前の定義」は、いわば「セル範囲にニックネームをつける」ようなものです。
なので、この範囲（A1:E13）にニックネームをつけて、分かりやすくしてあげます。
「名前の定義」のやり方については以下のとおりです。
1.「数式」タブの「名前の定義」を選択
2.「名前」に「社員名簿」、「参照範囲」に名簿の範囲を指定して「OK」
（分かりやすい名前であれば、何でもOKです）
これで、「A1:E13」の範囲が、「社員名簿」という名前として登録されました。
では、これで実際にVLOOKUP関数を使って、社員番号から雇用形態を引っ張ってみましょう。
=VLOOKUP(G3,社員名簿,5)
このとおり、「社員名簿」がちゃんと範囲として認識され、雇用形態を引っ張ることができました。
こうやってみると、数式もすっきりしているので、非常に分かりやすくなっています。
「便利なのはわかったけど、範囲が変わった場合はどうすればいいの？」
と思われた方もいらっしゃると思います。
そのような場合も簡単に対応することができますので、安心してください。
どのように範囲を変更するかというと、「名前の管理」から変更するだけでOKです。
１．「数式」タブ→「名前の管理」を選択
２．変更したい「名前」を選択し、「参照範囲」を変更、「閉じる」を選択
今回は参照範囲を1行増やし、「A1:E14」に変更してみます。
「閉じる」を選択すると、「保存しますか？」とメッセージが出るため、「はい」を選択します。
これで、社員名簿の「参照範囲」が変更されました。
試しに1行足したデータをVLOOKUP関数で検索すると、ちゃんと雇用形態を引っ張ってくることができています。
このように、数式をわかりやすくすることで、参照エラーが発生しにくい状況を作ることは重要です。
「名前の定義」を使うことにより、データの増減があってもメンテナンスがしやすいので、ぜひ活用してみてください。
ちなみに、「名前の定義」は様々な場面で活躍します。
以下の記事では、「名前の定義」を利用して、項目連動型プルダウンリストの作り方を紹介しています。「名前の定義」の基本的な作り方を詳しく解説していますので、こちらもぜひチェックしてみてください。
まとめ：エラーは起きるもの。ただし原因を突き止めれば必ず解決できる！
「#REF！」エラーは、参照がないときに発生するエラーで、Excelではよく発生します。
エラーが発生したときは、セル削除や参照ズレ、外部リンクや関数指定ミスを重点的にチェックし、紹介した対処法を試してみてください。
エラーが発生するのには、必ず理由があります。
初心者は特に焦ってしまいがちですが、原因を突き止めて修正すれば問題なく解決します。
この記事を読んだことで、あなたの困っている状況が解決できれば幸いです。
この記事が役に立ったと思った場合、ブックマークやシェアしてもらえると嬉しいです。また、質問やこんな方法を紹介してほしい！などありましたら、お気軽にコメントしてください。
最後まで読んでいただき、ありがとうございました。
コメント