【Excel】「ほかのデータソースへのリンク」を解除したいのに見つからなかった話（数式だけとは限らない）

いただきもののExcelや、古から継承するExcelを開くと、「このブックには、ほかのデータソースへのリンクが含まれています。」というメッセージが表示されることがあります。
更新しようとすると、リンク先のファイルがない旨のメッセージが表示され、実質意味がないのでは？と思うものです。

リンク先を解除することは、編集機能で簡単にできます。
ただし、単純に解除してしまえばよいとは思いながら、いったいどこの値に使われているのか、念のため確認しておきたいということになります。
リンクの編集機能では、外部参照が設定されているセルまでは分からないので検索をかけるのですが、リンクの数式が見つからずに苦労したので備忘録です。

今回の確認環境は、Excel 2010 / 2013 です。2010以降は操作が変わっていないはずなので、新しいバージョンでは使える技です。

ほかのブックにリンクしているセルを探す方法

スタンダードな方法としては、『リンクの編集』の利用があります。
リボンの「データ」にある標準機能です。
しかしこれは、自分でリンクを設定して、設定したセルの場所も記憶している場合に限って、機能単体で使えます。

多くの場合、どこに外部参照の設定が入っているのか忘れていたり知らなかったりですので、以下のような流れになります。

1. 『リンクの編集』で参照先ファイル名を確認
2. 確認したファイル名でブック内の数式を検索

どうしても2つの段階を踏むことになります。
マイクロソフトの考え方なので、致し方ありません。。

『リンクの編集』で検索する文字列を確認

データソースを更新しようとして、対象のファイルが見つからないと、以下のメッセージが表示されます。
「リンクの編集」をクリックすれば、編集画面が現れます。

正式なルートは以下です。

ブック内のすべての外部ブック参照が対象になります。
ここで、ファイル名をメモしておきます。メモ帳などに書いておくと、あとでコピペできます。
直接コピーできないところがイマイチなポイントですね。。

この画面で、リンクを編集したり削除したりできますので、不要なら消してしまうのも手です。

確認した文字列で数式を検索

Excelの検索機能を使います。

リボンからの呼び出しでもよいですが、『Ctrl + F』が早いです。
覚えておくと重宝します。

「検索する文字列」には、先ほどメモしておいたデータソースのファイル名を設定します。
右下の「オプション」をクリックして検索オプションを表示し、「検索対象」が「数式」になっていることを確認します。
ブック全体を検索する場合は、「検索場所」で「ブック」を選びます。

「すべて検索」か「次を検索」をクリックして、外部ファイルが設定されているセルを探します。

これで見つかればよいのですが・・・

数式が見つからない事態

検索文字列を確認しても、検索対象を確認しても、リンクの編集で表示されたファイル名が見つからなかったのです！！

ファイル名を一部にしてみたり、
検索場所を限定してみたり、
検索対象を変更してみたり、
いろいろやってみても見つかりません。。

見えているものを検索するとちゃんとヒットしていたので、検索機能の問題ではなさそうです。

データソースの指定は、セルの数式だけではないことに気付きました。

数式以外で怪しい候補

数式以外でほかのブックを指定できるところといってすぐに思いついたのは、以下の2つです。

• 入力規則
• 条件付き書式

今回は、このうちの入力規則が犯人でした。。
ちなみに、入力規則の在り処は、リボンの『データ』の中です。条件付き書式は、リボンの『ホーム』の中にあります。

いざ、力業です。
なるべく手数が少なくて済む、効率的と思われる方法を書いておきます。
それでも、シートの規模によっては骨の折れる作業になります。が、誰かがやらねばならぬのです。
これからも使うExcelで、毎回毎回メッセージがポップアップするウザさは計り知れません。ここらで退場していただきましょう。

ここでは、『入力規則』を例にします。
経験上、入力規則のほうに数式が設定されていることが多いです。

基本思想は、怪しいセルを洗い出して、そこを調べていくというものです。

『入力規則』が設定されているセルの洗い出し

まずは怪しいところを洗い出します。
以下の手順です。

1. ジャンプ機能でセルを選択
2. 選択したセルを強調

ジャンプ機能を利用します。

なお、ジャンプの範囲選択ができるのは、表示中のシートのみです。
ブックに複数シートがある場合には、それぞれのシートで同様の操作が必要です。

さっそく力業な感じですｗ

ジャンプ機能で選択

以下の操作で、ジャンプ機能の選択オプションを使います。

「選択オプション」ウィンドウが表示されたら、『データの入力規則』のラジオボタンをオンにして『OK』を押します。
なお、ここでは『条件付き書式』も選択できますが、選択肢は排他です。

対象のセルがあれば選択状態になります。
対象セルが複数あれば、複数セルのエリア選択状態になります。飛び地もありです。

強調

複数セルが選択された場合、セルが選択されている状態で、対象セルに色を付けるなどして強調しておくと作業しやすいです。
思いっきりどぎつい色にしてしまえば、あとの作業でも目立ちますし、直しの漏れも発見しやすいです。

選択オプションでOKしたら、セルの複数選択状態を維持しておきます。
クリックしてしまうと選択状態が終わってしまうので注意です。
強調が先です。

どこでもよいので、選択されているセルで右クリックして色を付けるなり何なりします。

とにかくこれさえやっておけば、どこが選択されていたか忘れてしまうことはありません。
選択エリアの多さに凹むかもしれませんが、がんばりましょう！

個別対応か一括対応か

あとは本気の力仕事です。。

セルの設定状況を確認していきます。
入力規則なり、条件付き書式なり、設定の内容を見ていくのです。。

先に全体を見ておきましょう。
対象セルに色を付けてみると、案外法則があったりします。
入力規則がすごく細かく設定されているということは珍しいと思いますので、ざっくり見ていけば、いくつかのグループに分かれているはずです。

ひとつひとつ見ていかなければいけないと思うと大変です。
法則を探してやろうとか、ちがった観点で見てみると楽しいですよ？ｗ

同じ設定はまとめて変更できますので、がんばってやっていけば、必ずゴールにたどり着きます！

見えにくいものを見える化する考え方

ここでは、見えていないものを見えるようにする、という考え方で対応を考えました。
デフォルト提供されている機能の中で対応するという思想でもあります。

なるべく難しくないように。

どうしてもだめならマクロの力に頼ればよいのだと思っていながら、まだ何も書けていない今日この頃です。