今日はメインサイトのJimdoが大規模障害で編集不能のため、ファンブログを更新してみることにしました。今回はエクセルのお話です。
「Excelの別ファイル参照で#REF!や#N/Aになる原因と対処法」が分かったので、誰かの役に立てばと載せてみます。
Excelの別ファイル参照はエラーになる
エクセルで別のエクセルファイルを参照する形で数式を作ったとき、後で開き直すと#REF!エラーや#N/Aエラーになることがありませんか？
自分も先日「有休消化日数の一覧」を作った時に、次年度以降も活用するため元のデータは別のファイルにしたのですが、後で開き直したら社員全員分のセルで「#REF!」と表示されてしまいました（170人×12か月分）。
数式バーで数式がどうなっているか見ると、
\\192.168.〇.〇\#REF!〜
と、シート名が#REF!となっています。
こうなると数式を作り直さないと改善されず、保存せずに再度開き直しても同じようになることがありました。
また別のケースでは、シート名が一斉に「#N/A」になることもありました。
徹底調査を開始！
何度も何度も起こり、その都度直して手間がかかったので、とうとう頭にきて徹底的に調べてみることに。。。
まずは、３つの違う環境（ネットワーク）でそれぞれ別ファイル参照の数式を作って検証です。
サーバーの設定やNASだけダメ、オフライン環境だとダメ、といったことがないか確認するためです。
しかし、環境の違いで発生するという確証が得られませんでした。
それでも、３つの環境のうち１つだけはエラーにならず、残り２つはエラーになりました。
その違いを深堀調査。いったい何が違うんだろうと。。。
何度もやっているうちに、まずどちらのエラーも同じタイミングで起こることが分かりました。
エラーが起こる流れは、次の通りです。
1.別ファイル参照の数式を作る
※２つのエクセルファイルの保存先はローカル以外（サーバーやNASなど）
2.数式を入れたファイルと参照先のファイルを閉じる
3.数式を入れたファイルを開く
4.「リンクの更新」をクリック
5.参照先のファイルを開く
6.「コンテンツの有効化」をクリック
7.無事、シート名がエラーになる
要約すると、「『数式入りファイル』を開いて編集可能にする → 『参照先ファイル』を開いて編集可能にする」でエラーになります。
なんじゃそりゃ？
＜楽天で買うとポイントがたくさんたまる！買い物はこちら＞
エラーの違いの要因
そして、エラーの違いも分かりました。それは、「参照先のシート名」です。
デフォルトの「Sheet〇〇」だと#N/Aエラーになり、日本語の文字列だと#REF!エラーになります。ここは徹底検証したわけではないので、誤りがあるかもですが。。。
始めのエラー発生の流れは、何度も試したことで気が付きました。そしてエラーの違いは、直感でしたがプログラミングやパソコンの知識がベースです。
原因が分かった！！
さらに進めていくと、エラーが起こる環境とそうでない環境で一つの違いに気づきます。
それは、「ファイル保存先のサーバー（NAS）をコンピュータ名で指定しているか、IPアドレスで指定しているか？」です。
エラーになるほうはIPアドレスで指定していて、
\\192.168.〇.〇\〜
となっていました。
反対にエラーにならないほうは、
\\（コンピュータ名）\〜
となっています。
つまり原因は、「ファイルの保存先をIPアドレスで指定していたから」だったのです。
Excelの参照先パスはどうやって決まる？
しかしここで一つ疑問がわきます。エクセルの別ファイル参照では、「パスを自動的に付加してくれる」からです。
言い方を変えると、「自分でIPアドレスを指定したのではない」ということです。
さらに調査。なに要因でIPになったりコンピュータ名になったりするのかを調べます。
PCの設定をいじったり、レジストリを変えてみたりしましたが、結局要因はもっと簡単なことでした。
それは、「『数式入りファイルを開く』時に開くフォルダの開き方」です。
サーバーをIPアドレスで指定してフォルダ→ファイルと開くと、パスはIPアドレスになります。反対にコンピュータ名で指定してフォルダ→ファイルと開くと、パスはコンピュータ名になります。
しかも、自動付加されるパスは「その時のフォルダの開き方」で自動的に変わるため、例えばIPアドレスでフォルダを開いて数式を作って保存しても、再度開き直す時にコンピュータ名で開けば、パスは「コンピュータ名」になります。
改善策は？
なので改善策は、「これからはフォルダをコンピュータ名で開く」となります。
フォルダをコンピュータ名で開くには、サーバーやNASなどの名前が必要です。
そんな時には、コマンドプロンプトで「nbtstatコマンド」を実行しましょう。保存先のコンピュータ名を知ることができます。
ちなみに、よく開くサーバーの場合には「サーバーへのショートカット」があるはずです。そのショートカットのパスもコンピュータ名にしておきましょう。
なお、環境によってはショートカットのパスをIPアドレスからコンピュータ名に変えたときに「ネットワークパスワード」を求められることがあります。
これは管理者でないと知らないので、「何とかしてください！困ってます！！」と管理者に訴えてみてください。自分なら即座に対応します。
ということで今回はここまでです。
ではまた次回も読んでください。
なお、下に運営しているサイトのリンクを載せておきますので、よかったら見てみてください。
＝＝エクセル関数の困ったを解消できるホームページをやっています＝＝
