業務Excel ツール
困りごとの解説

Excel の外部リンク・壊れた参照の見つけ方

Excel のブックは、ほかのブックのセルを参照できます。便利な反面、ファイルを移したり、名前を変えたり、サーバを入れ替えたりした瞬間に、この参照が切れます。参照が切れても、Excel はすぐには教えてくれません。前回の値を覚えたまま表示し続けるからです。移行の前に、外部リンクを洗い出しておく必要があります。

外部リンクの正体

数式に [売上.xlsx]Sheet1!A1 や 'C:\共有\[売上.xlsx]Sheet1'!A1 のように、角かっこ [ ] でファイル名が入っていれば外部リンクです。リンク先の値は、ブックの中に「前回の値」として保存されています。そのため、リンク先が消えても、開いたときは古い値が見えます。

画面から見つける方法

  1. 「データ」タブ →「リンクの編集」:使える状態なら、外部リンクがあります。リンク元のファイル名と場所、状態(OK/エラー)が一覧に出ます。
  2. 数式の検索:Ctrl+F →「オプション」→「検索場所」を「ブック」、「検索対象」を「数式」にして、[ や .xls を探します。
  3. 名前の管理:「数式」タブ →「名前の管理」。名前の定義の中に、他ブックのセルを指しているものが隠れていることがあります。
  4. グラフ・条件付き書式・入力規則:セルの数式には出てこない参照です。グラフの元データ、条件付き書式のルール、入力規則のリストも順に確かめます。

壊れた参照(#REF!)の探し方

参照先のセルや行・列を削除すると、数式に #REF! が入ります。Ctrl+F で「検索場所」を「ブック」、「検索対象」を「数式」にして #REF! を探すと、シートをまたいで一覧にできます。ただし、数式の中の #REF! は、エラー表示が IFERROR などで隠されていると、画面の値からは見えません。「値」でなく「数式」を探すのはそのためです。

画面から見つけにくいもの

  • INDIRECT で組み立てた参照:文字列からセルを作るため、リンクの編集には出てきません。ファイル名を文字で書いている数式は、数式の検索で見つけます。
  • VBA の中で開くブック:マクロが Workbooks.Open でパスを指定している場合、ブックを見ても参照は見えません。コードの中を見る必要があります。
  • 1つのリンクが複数の書き方で持たれていること:絶対パス、相対パス、ドライブ名なし、などが並んで保存されている場合があります。

ファイルの中から見つける方法

.xlsx は、中身が ZIP です。拡張子を .zip にしたコピーを開くと、xl/externalLinks/ というフォルダがあれば外部リンクを持っています。中の _rels に、リンク先のパスが書かれています。大量のブックを調べるときは、この構造を機械で読むのが現実的です。

ブックを開いたときのメッセージの意味

外部リンクを持つブックを開くと、「このブックには、安全性が確認できない外部ソースへのリンクが含まれています」という趣旨の帯が出ます。「更新する」を押すと、リンク先のファイルの現在の値で、数式の結果が置き換わります。リンク先が移動・削除されていれば、エラーになるか、古い値のまま残ります。「更新しない」で開き、先に「リンクの編集」で状態を確かめるのが安全です。リンク先が別の人のパソコンや、すでにない共有フォルダの場合は、値を見て判断せず、必ず元の資料と照らしてください。

見つけたあとの扱い

  • リンク先が今も生きているか、新しい場所へ張り替えるか、値として固定するかを決める。
  • 「リンクの編集」の「リンクの解除」は、数式が値に置き換わって元に戻せません。必ずコピーで試します。
  • 共有フォルダやサーバの移行前に、どのブックがどのブックに寄りかかっているかを一覧にしておく。

機械で一覧にする

当サービスの「業務Excel 健康診断」は、1つのブックの外部参照と #REF! を一覧にします。フォルダ全体で「この1つを消すと、どれが壊れるか」を知りたいときは、「野良Excelの地図(依存マップ)」が、ブック同士の参照を地図にします(集計ツールはお客様の端末の中だけで動き、ファイルは送りません)。

このツールで調べる

業務Excelの診断は、ブックを送るだけで始められます。無料の方は 1 回までお試しいただけます。

健康診断を使う マクロの仕様書を使う 依存マップを使う

ほかの解説

お送りいただいたファイルは、診断が終わった時点でサーバから消しています。保存も再利用もしません。
© 合同会社IKACHI