エクセルの別ファイル参照でREF・N/Aエラーの原因｜外部

ここでは、エクセルで「関数などにより別ファイル（外部）参照した時に#REF!や#N/Aエラーになる原因と対処法」を解説します。

ここで説明する原因は限定的ですが、発生すると対処法が分かりづらくてとても困ります。ぜひ参考にしてください。

まずは、「別ファイル（外部）参照で#REF!や#N/Aエラーになる現象」を、例を含めて確認します。

１－１．参照先のファイルを開き直すとシート名が#REF!に

この現象は、「参照先のファイルを開き直すと、数式のうち『シート名』が#REF!エラー」になります。

なので、セルの表示ももちろん「#REF!」です。

現象は、次の状態で発生します。

　⇒ 発生！！

１－２．#N/Aエラーになることも

また、ケースによっては#N/Aエラーになる時もあります。

いづれにしても、発生すると基本的に数式を手直ししないといけないので、とても大変です。

１－３．例で確認

一つ例を見てみましょう。

上は、「Book1のSheet1」です。これは、「Book2の『元データ』にある同じセル番号」の値をINDEX＋MATCH関数で持ってきています。

ちなみに、Book2の元データシートは下のようになっています。

そして、これらのファイルをいったん閉じます。

その後、数式を入れた「Book1」のほうを開きます。

Book1で「編集を有効にする」をクリックします。

Book1で「コンテンツの有効化」をクリックします。

その後、Book2を開き、Book2で「コンテンツの有効化」をクリックすると…

Book1の数式が#REF!エラーになりました。

そして、数式を見てみると「シート名が#REF!になっている」のが分かります。

このように、外部参照の数式は参照先のファイルを開くと#REF!エラーになることがあるのです。

次に、外部参照で#REF!や#N/Aエラーになる原因と確認方法を見てみましょう。

２－１．パスをIPアドレスで指定するとエラーになる

外部参照で#REF!や#N/Aエラーになる原因は、「外部保存媒体をIPアドレスで指定している」ことです。 

外部保存媒体とは、サーバーやNASなどのことです。外部保存媒体を指定することで、そのファイルがどこに保存されているかを明示します。

IPアドレスで指定した場合には、数式の入ったファイルよりも参照先のファイルを後で開くと、うまくシート名が取得できないようなのです。

２－２．パスの確認方法

パスをIPアドレスで指定しているかどうかは、数式バーを見ればわかります。

以下手順です。

（１）ファイルを両方閉じた後、数式の入ったファイルを開く

（２）数式の入ったセルをクリックする

（３）数式バーを参照する

上図の「'\\192.168.～」のように、\マークの後が数値になっていたら、パスをIPアドレスで指定していることになります。

２－３．#REF!と#N/Aの違いは「参照先のシート名」

#REF!エラーと#N/Aエラーの違いは、「参照先のシート名」です。

「元データ」などの日本語の場合には#REF!エラーになり、「Sheet1」などの場合には#N/Aエラーになります。

ただエラーの種類は違っても、どちらも原因はやはり「パスをIPアドレスで指定している」ことになります。

続いて、エラーの解消の仕方を見ていきます。

３－１．「パスを『コンピュータ名』にする」と直る

#REF!エラーや#N/Aエラーは、「パスをコンピュータ名にする」ことで解消されます。

しかし、エクセルの外部保存先のパスは自動で入力されます。

そこで、手で直すのではなく、「自動でパスがコンピュータ名になる」ようにします。

３－２．どうしてIPアドレスが自動で付加されるのか？

そこで、そもそも「どうしてIPアドレスが自動で付加されるのか？」を確認します。

エクセルでは、どうやら「フォルダをどうやって開いたか？」によって変わるようです。

例えば、サーバーにあるフォルダをIPアドレスで開けば数式のパスにはIPアドレスが、フォルダをコンピュータ名で開けばパスにはコンピュータ名が自動で付加されます。

つまり、パスをコンピュータ名にしたい場合には、そのファイルがあるフォルダの開き方を「IPアドレスからコンピュータ名に変えればいい」のです。

３－３．コンピュータ名の調べ方

フォルダをコンピュータ名で開くためには、保存先のコンピュータ名を知ってなければなりません。

そこで、コンピュータ名の調べ方を簡単に説明します。

以下手順です。

（１）ウィンドウズキーとRを押す

（２）ファイル名を指定して実行に「cmd」と入力してOKを押す

（３）「nbtstat -A IPアドレス」と入力し、Enterキーを押す

 ※ 例）nbtstat -A 192.168.1.200

（４）表示された結果からコンピュータ名を確認する

上の例で言うと、赤字の部分がそのIPアドレスのコンピュータ名です。

まずはこのコンピュータ名を控えます。

３－４．コンピュータ名からフォルダを開く方法

次に、コンピュータ名からフォルダを開く方法です。

以下手順です。

（１）なにか適当なフォルダを開く

（２）アドレスバーに「￥￥（コンピュータ名）」と入力後、Enterキーを押す

（３）ファイルが保存されているフォルダを開く

（４）エクセルファイルを開く

このように、開き方を変えるだけでエラーが出なくなります。

ちなみに開き方を変えると、パスも自動でコンピュータ名に変わります。

３－５．Windows資格情報を求められる時も

ネットワークの設定によっては、「Windows資格情報」を求められる時もあります。

その際には、ネットワーク管理者に事情を説明して対処してもらいましょう。

３－６．できればショートカットもコンピュータ名のものに変える

最後に、できればショートカットもコンピュータ名のものに変えるとよいです。

フォルダのショートカットは、フォルダ上で右クリック → ショートカットの作成でできます。

以上、参考になれば幸いです。

関数の問題解決記事一覧へはこちら