「リンク先はどこ?なぜ見つからない?なぜ消えない」をどうにか解除に近づける(マクロツールDL可)

「消せないリンク」を深堀りして探す!

ブック起動時に「リンクがないよ!」のお知らせ画面が表示されると、なんとも気持ち悪いですよね。

Excelは、親切に「リンク切れエラー」を教えてくれているつもりかもしれません。しかし、こういうときに限って「ソースが見つかりません」というツレナイというか、とても中途半端感があって対処に困るという状況になってしまいますね。

リンク切れエラー画面

当ページをご覧のみなさんは、すでに他サイトなどで色々やっても消せなかった方々を想定しています。
ですので、一般的な解決方法では対処できなかったという前提で、より深堀りした方法でリンク元になっているセルなど探すことを試みたいと思います。

なお、当ページで取り上げている「隠れリンク達」に対して、それらを表示するマクロツールを作っています。
よろしければそちらもお試しくださいませ(というか、ツールを使わないと検知できないと思います・・)。

リンク切れエラーでは、他のブックと「つながっているものすべて」を疑う

今回のリンク切れエラーの問題については、その対象はすごく幅が広いものと思います。

基本的には、

外部ブックを参照しているリンクやイコールで式を入れているものすべて

外部ブックのシートを参照できる機能とは、どんなものがあるでしょうか。

  • 名前定義
  • 関数・数式の参照
  • コピーペーストのリンク貼り付け
  • 図形(テキストボックスなど)とセル内容のリンク
  • グラフ
  • ピボットテーブル

※Excelには、私が使ったことのない機能がまだまだあるだけではなく、入力規則などINDIRECT関数を使うと外部参照できるようなので、実際にはもっとありますね。。

また、以下の「データ」タブの中の機能もリンク切れエラーが表示される可能性の「匂い」はしてきます。
データタブもリンク切れと関係あるかもしれない。
もっとも、私自身が使ったときにこのエラーを経験しているわけでなく、あくまでも匂いだけですが・・

なお、詳細は割愛しますが、「他のブック」とリンク設定ができるもの(例えば他ブックのセルの値を表示する)は、その「他のブック」のブック名を(誰かが勝手に)変えると、Excelは途端に追いかけられなくなります。

せめてリンクは自ブック内にとどめておくのが無難に思います。
※ブック同士を開いて名前を変更して保存だと追いかけてくれるケースも有りますので、混乱しかねませんね。

隠れリンクを探す(シート編)

通常リンク切れのエラーが発生すると、「検索機能」で全シートを対象に該当するセルを探すと思います。
※検索は、ショートカットでいう「CTRL + F」の検索です。

しかし、検索場所で「ブック」を指定しても、そのブック内にある非表示のシートは探してくれません

つまり、リンク切れエラーの原因となるセルが、その非表示のシートに含まれているケースだと発見できません。ですので、まずはシートを表示して検索する必要がありますね。

非表示シートの検索はできない
なお、非常にやっかいなのは、非表示シートには2種類あり、1つは手動で再表示できるシートですが、もう一つは再表示できないシートがあります
これはプログラムから行えるやり方ですが、利用者から見ると、シートの存在自体に気づけませんですね。

2つの非表示シートのうち1つは、再表示画面から見えない
この点については手動で再表示できないシートを表示するツールを作りましたので、それでお試しくださいませ。

隠れリンクを探す(名前定義編)

隠れているリンクでもう一つ挙げたいのは「名前」です。

以前、お引き受けした業務でこのケースがありました。
データ量の割にはファイルサイズがやけに大きく、重たいExcelファイルがありました。
どういう経緯で発見できたのかは忘れてしまいましたが、この名前定義も非表示にできます

そうなると、手動では表示できなくなります(多分)。

この名前については、「表示する」、「表示しない」の2種類ですが、やはりマクロから表示させる必要があります。

名前ボックスにも表示されない名前がある
※名前の管理はリボンの「数式」タブ内にあります。

この名前についてもツールで表示できるようにしました。
ちなみに先程の業務では、この方法で無数の名前が出てきて、一掃しました(笑)。

私自身の経験からですが、名前が消えてしまう傾向として、どうも古いファイル(XLSXではなく、XLS形式から)を使い続けているケースがあると思います。過去にシートのコピーや移動を繰り返した影響なのかな、とか考えますが・・どうなんでしょうね。

なお、余談ですが、名前の付け方には、有効範囲が「シート」と「ブック」の2つある点も要注意ですね。
これは「1つのブックに対してシート別に同じ名前を付けられる」という利点?なのでしょうが、多用すると(利用者を)混乱や誤解させる要因になりそうです。

その他にも隠れているオブジェクトがある

「えっ?図形ってセルとリンクできるの?」
と思われる方もいらっしゃるかもしれませんが、リンクできるのですね。

図形の外部リンク
図形は「ジャンプ(セル選択)」 – 「選択オプション」の「オブジェクト」で探せることで気づけます。したがい見つけやすいとは思いますが、図形オブジェクトも非表示にできるんですね。
※ジャンプはショートカットでいう「CTRL + G」ですね。

さらに図形の場合は、枠線と塗りつぶしを「なし」、あるいは横幅と高さを「0」にしても、実質的に消せるという点も念の為に頭に入れておきたいですね。
(ただし、これらの図形設定でも、上記の「ジャンプ・・」で探せます)

図形の場合は手動で再表示できますが、この図形にも「=Book1!A1」のような設定がされていると、外部へのリンクになります。その図形が見ているブックが行方不明になると、これもリンク切れエラーになり、アノ画面が表示されますね。

「オブジェクト表示化ツール」について

以上、上記のことを踏まえてツールを作成しました。

ツールでは、まずは上記の2点について対応する機能にしています。

  1. 非表示のシートを表示する
  2. 非表示の名前を表示する

このツールでは、リンク切れエラーのものを探すのではなく、非表示しているもの(オブジェクト:シート、名前)を表示するためのツールです。

実際に非表示のものがあれば表示化し、その内容をセルに書き込みます。
オブジェクト表示ツール化の実行結果
(オマケ的に図形も非表示の場合、表示するようにしています。「実質非表示」は面倒にて対応しておらずです・・ごめんさいです)

実行方法

事前に調べたいファイル(複数可)を開いた状態にします。
その状態で以下の画像にあるボタンを実行すると、開いているファイル全ての中身を見て非表示のシート、名前があれば表示を行います。

オブジェクト表示化ツールの実行
※実行する前に、念の為バックアップを取っておくこととおすすめします。

ダウンロード

こちらのページダウンロード用ファイル、ツールのご利用についてからダウンロードできます。

おまけファイル

今回のツールを作るに当たり、テスト的に使用したファイル「リンクテスト.xlsm」を含めています。
このファイルでは、ダウンロード時は「名前とシートを非表示」にしています。
ツールを実際のファイルで実行する前にこの「リンクテスト.xlsm」を開いた状態で試すと、ツールがどんなものかおわかりなると思います。

※リンク元シートに「非表示の状態を作る」という図形ボタンがあります。これをクリックすると人工的?に名前とシートを非表示にします。これで何度も試すことができます。
表示しているオブジェクトを一部非表示にする機能
開発中に繰り返し試すのが面倒なので作りました(笑)。

さぁ、これでいかがでしょうかっ!!

(まぁ、これでダメなら実は第2段もなくはないのですが、まだ私自身も把握しきれておらずで・・)

タイトルとURLをコピーしました