Excelの外部リンク（外部参照）について調べてみた（その１）

私はシステム関連のサラリーマンなので、普段の業務でExcelをバリバリ使っており、そこそこ使える方だと思っている。ただ、自分が作る資料では他のブックのセルの参照、いわゆる外部リンクは使わないので、たまに、他人が作成したExcelを開いた時に
「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。」
などの警告が出ると、（ほとんどの場合は作成者が意図とは別にリンクが残っているケースなので）「ちゃんと後始末しておけよ」という思いでイラッとしてしまう。

ところが、最近、意図して外部リンクを使っている奴とファイルを共有することが多くなったのだが、そのファイルの改廃において、外部リンクのパスが勝手に変わったりする現象に出くわしたことから、この外部リンクなるものの仕様・挙動を自分なりに調べてみた。

本記事ではその調査結果について記載する。

なお、「外部リンクを使っているExcelファイルを含むフォルダーをコピーまたは移動した場合の外部リンクの挙動」については、別の記事「Excelの外部リンク（外部参照）について調べてみた（その２）」としているのでそちらを参照いただきたい。

調査したExcelのバーションは 365 であるが、ざっと見た限りでは 2016でも同じ動きをするようだ。

【用語について】

• 本記事ではExcelファイルしか現れないため、ブックと呼ばず全てファイルに統一する。
• パスは、ドライブ、フォルダ、ファイル名までを含む。
• ファイルAの中にファイルBを参照する式を記載していた場合、私の感覚では「ファイルAがリンク元、ファイルBがリンク先」なのだが、Excelの用語ではファイルBをリンク元と呼んでいるので、本記事の記載もそれに合わせる。一方、ファイルAを本記事では「基準ファイル」と記す。

　
【外部リンクのあるExcelファイルを開くときの警告】

本題に入る前に、外部リンクを含むExcelファイルを開くときに表示される警告について説明する。

• 「セキュリティの警告：リンクの自動更新が無効にされました」が上部に帯状に表示されることがある。
  
  
  
  ここでとり得る選択肢と動作は以下の通りである。
  
  1. 「コンテンツの有効化」ボタンを押下　⇒　リンク元のファイルの内容を参照して、基準ファイル内の外部リンクを含むセルの値を最新のものに更新する。
  2. 帯の右端の X印を押下して帯を消す　⇒　内容を更新せずに基準ファイルを開く。
     
     
• 「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。リンクを信頼できる場合、リンクを更新して最新データを取り込みます。信頼できない場合は、データをそのまま手元で処理してかまいません。」というダイアログが表示されることがある。
  
  
  ここでは以下の選択肢がある。
  
  
  1. 「更新しない」を押下　⇒　内容を更新せずに基準ファイルを開く
  2. 「更新する」を押下　⇒　リンク元のファイルの内容を参照して、基準ファイル内の外部リンクを含むセルの値を最新のものに更新する。
     ただし、外部リンクにリンク切れがある場合（リンク元のファイルが見つからない場合）
     「ブック内の一部のリンクはすぐに更新できません。値を更新せずにそのままにするか、間違っていると思われるリンクを編集してください。」のダイアログがでる。
     
     
     
     ここでは以下の選択肢がある。
     
     
     • 「続行」を押下　⇒　内容を更新せずに基準ファイルを開く。
     • 「リンクの編集」を押下　⇒　「リンクの編集」画面が表示される。（この画面の操作については後述）

　
これらの警告を乗り越えると、やっと基準ファイルを開くことができる。

それでは、外部リンクの挙動の説明に入る。

【説明のために用意したファイル】

以下の4ファイルを準備した。

G:\Excel_test\基準フォルダ　というフォルダの直下に「基準ファイル」と名付けたExcelファイルを置き、そのファイルの中に、以下をそれぞれ参照する式を記載した。

1. サブフォルダに置いたExcelファイルの参照
2. 上位フォルダに置いたExcelファイルの参照
3. 他のドライブ(E:\temp)に置いたExcelファイルの参照

上記のリンク元の3ファイルの内容も記載しておく。

 左から、サブフォルダ、上位フォルダ、他ドライブのファイルの内容
    

【基準ファイル内の外部リンクの式の表記】

まず、基準ファイルだけを開いた場合の外部リンクの計算式の表記を見てみると、

=ドライブレター:\フォルダのパス\[ファイル名]シート名!$A$1

のように、フルパス＋シート名＋セル番地 で記載されている。

基準ファイル内の計算式

基準ファイル内の値表示

なお、上図の計算式と値の表示切替は
「ファイル」－「オプション」－「詳細設定」－「次のシートで作業する場合の表示設定」の「計算結果の代わりに数式をセルに表示する」のチェックをON/OFFすることで切り替えている。この後の図でも、特に記載なしに適宜このチェックをON/OFFして表示を切り替えているので留意して欲しい。

 「計算結果の代わりに数式をセルに表示する」 
 

【リンク元のファイルを開いた場合】

上記の状態から、リンク元のファイルも開くと、その瞬間に基準ファイルの計算式は以下のように変化する。

すなわち、フォルダ部分は表示から消え、ファイル名＋シート名＋セル番地　だけとなる。
※なお、上図は、3ファイルとも開いた場合。当然ながら、開いたリンク元ファイルを参照している式だけがこのように変化する。
リンク元のファイルを閉じると、その瞬間に元のフルパス付きの表現に戻る。

また、基準ファイルとリンク元のファイルを同時に開いた状態から、リンク元ファイルを「名前を付けて保存」で別のパスやファイル名で保存すると、その瞬間に基準ファイル内の外部リンクの計算式もそれに変更される。

　
【外部リンク内のパスやシート名を手動で変更しようとした場合の挙動】

基準ファイル内の外部リンクのパスを手で修正しようとした場合、以下のケースに分かれる。

• 修正しようとした外部リンクのファイルが存在し、かつ、シート名も存在する場合、修正は成功するとともに、その値が読み込まれ更新される。
• 修正しようとした外部リンクのファイルは存在するが、シート名が存在しない場合、条件は不明だが以下の２つのケースがあった。
  　
  
  1. シート名は変更されず（手修正が反映されず元のまま）、何も起こらない（値も変わらない）。
  2. 「シートの選択」というダイアログが現れ、実際に存在するシートの一覧が表示されるので、その中から１つ選択する。その結果、外部リンクのシート名は変更され、値は、そのシートの現在の値に変更される。
     「シートの選択」でキャンセルを選択した場合、シート名は手修正による変更が反映されたまま（存在しないシート名のまま）で、値は #REF! になる。
     　　
     シートの選択
     
     　　
     　
• 修正しようとした外部リンクのファイルが存在しない場合、「値の更新」というファイルを選択するダイアログが現れるので、以下のどれかを行う。なお、外部リンクのパスは、以下のどの場合でも、修正しようとした「存在しないパス」に変更される。
  
  
  1. ファイルダイアログでキャンセルした場合　⇒　何も起こらない。（外部リンクのパスは修正されたままで、表示（値）は #REF! になる。
  2. ファイルダイアログで、存在するファイルを指定した場合は、以下のケースに分かれる。
     
     
     • 指定したファイル内に外部リンクに記載したシート名が存在する場合　⇒　そのシートが採用され、表示（値）はその値に更新される。ただし、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名は正しい（存在する）名前に変更される。
     • 指定したファイル内にシートが１つしかない場合　⇒　（シート名の一致、不一致にかかわらず）そのシートが採用され、表示（値）はそのシートの値に更新される。その結果、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名は正しい（存在する）名前に変更される。
     • 指定したファイル内にシートが複数存在し、外部リンクに記載したシート名が存在しない場合　⇒　「シートの選択」というダイアログが現れて、実際に存在するシートの中からシートを１つ選択する。その結果、外部リンクのパス内のシート名は選択したシート名に変更される。その結果、外部リンクのパスは修正されたまま（存在しないパスのまま）、シート名はダイアログで指定したシート名に変更され、かつ表示（値）は当該シートの値に更新される。
       「シートの選択」ダイアログでキャンセルを押下した場合、シート名は変更されず存在しないシート名のままで、値は #REF! となる。

　
【Excelの変換機能で外部リンクの計算式内のパスやシート名を変更しようとした場合の挙動】

Excelの変換機能で、外部リンクのパスを存在しないパスやシート名にに変換しようとした場合の挙動は、基本的には手修正の挙動と同じで、それが変換個所の数だけ繰り返される。
ただし、途中のダイアログでキャンセルを選択すると、そこで変換処理は中断され、残った変換個所は処理されない。

　
【リンクの編集】

以下では、外部リンクに関するExcelの機能「リンクの編集」について記載する。
他の人が送ってきたファイルなど、構造はわからないけど外部リンクの警告がでるようなものについては、まずこの機能で外部リンクの状況を把握すると良い。
「リンクの編集」は以下の操作で表示される。

 「データ」－「クエリと接続」グループ－「リンクの編集」
 

リンクの編集ダイアログを開くと、使用している外部リンクの一覧が表示され、最初は「状態」は全て「不明」となっている。
ここで右側のボタン押下で以下の操作が行える。
1 ～ 4 は選択した行に対する操作で、5 は全行に対する操作となる。

1. 値の更新
   外部リンクが示すファイルを参照して値を更新しようとし、成功すれば「状態」は「OK」となる。
   ファイルやシートが存在しなかった場合の挙動は、【外部リンクの計算式内のパスやシート名を手動で変更しようとした場合の挙動】と同じである。例えば、ファイルが存在しない場合は、「値の更新」というファイルダイアログが現れるので、ファイルの指定を行う。ただし、ファイルを指定しても、外部リンクのパスが変わらない点も同じである。
2. リンク元の変更
   「リンク元の変更」というファイルダイアログがでてくるので、リンク元のファイルを指定する。また、外部リンクに記載したシートが存在しない場合は、「シートの選択」ダイアログでシートを選択する。その結果、外部リンクのパスやシート名は指定されたものに変更され、値が更新されて状態が「OK」となる。
3. リンク元を開く
   リンク元のファイルを開く。リンク切れの場合（リンク元ファイルが存在しない場合）は、「申し訳ございません。[外部リンクのパス名の表記]が見つかりません。名前が変更されたか、移動や削除が行われた可能性があります。」というエラーが表示される。
4. リンクの解除
   外部リンクを値に変更する。（外部リンクの情報は削除されるので注意）
   リンク切れの場合（リンク元ファイルが存在しない場合）は、値が「#REF!」に変更される。
5. 状態の確認
   外部リンクの状態を確認して（ファイルやシートの存在を確認して）、「状態」欄を以下の通り更新する。　
   
   • ファイルが存在しない場合　⇒　「エラー：ソースが見つかりません」
   • ファイルは存在するが、シートが存在しない場合　⇒　「エラー：シートが見つかりません」
   • ファイルとシートが存在し、そのファイルが開かれている場合　⇒　「リンク元は開かれています」
   • ファイルとシートが存在するが、まだ値を読み込んでいない（更新していない）場合　⇒　「警告! 他のブックを参照している値は更新されませんでした」
   • ファイルとシートが存在し、既に以前の操作で値を読み込んでいる場合　⇒　「OK」

　
利用者目線で言うと、その外部リンクを使用している場所（セルなど）を知りたいのだが、残念ながら検索で調べる以外に方法はないようだ。

ブック内のリンク (外部参照) を検索する

　
では。

【2023年7月1日追記】
本記事は2022年1月にアップしたものであるが、その中の「フォルダーをコピーまたは移動した場合の挙動」の内容が一部正しくなかったので、その部分を本記事から削除し、再度アップした。
「フォルダーをコピーまたは移動した場合の挙動」は再整理し、新しい記事「Excelの外部リンク（外部参照）について調べてみた（その２）」としてアップした。