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

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

このブックには、ほかのデータソースへのリンクが含まれています。

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

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

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

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

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

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

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

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

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

このブックには更新できないリンクが1つ以上含まれています。

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

リンクの編集

リボンの「データ」 → リンクの編集

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

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

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

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

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

検索

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

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

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

数式が見つからない事態

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

検索条件に一致するデータが見つかりません。

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

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

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

数式以外で怪しい候補

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

  • 入力規則
  • 条件付き書式
コメントでご指摘いただいたので追加です!

以下でも、他のブックを指定されている場合があります。

  • 名前の定義
  • オートシェイプのセル参照

「名前の管理」はリボンの「数式」タブの中です。
「オートシェイプのセル参照」とは、オートシェイプ(図形)にセル値を引用することです。図形はリボンの「挿入」タブの中ですが、参照設定は数式バーです。
可能性の候補ですね。

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

マウスとキーボード

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

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

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

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

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

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

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

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

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

ジャンプ機能で選択

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

ジャンプで選択

リボンの「ホーム」 → 検索と選択 → 条件を選択してジャンプ

条件を選択してジャンプ
選択オプション

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

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

強調

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

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

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

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

個別対応か一括対応か

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

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

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

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

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

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

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

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

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

最後まで読んでくださりありがとうございます!
もしよろしければ、公式Twitter(@wnkhs_net)のフォローもお願いいたします。

当ブログの更新情報+αをお届けします!!
この投稿を書いたのは・・・
Blog Admin

ガジェットが大好きで、ほぼ毎月何かしら調達しております。
無駄遣い扱いされたくないのと、何かの役に立つかと思い、記録を書くことにしたのでした。

お出かけのときには、スマホを複数台とタブレットとパソコンを持ち歩きます。
両手首にはスマートウォッチです。
こんなスタイルで生活している中での備忘録を書いています。

「Blog Admin」をフォローしてみる
Excel関連
「Blog Admin」をフォローしてみる
wnkhs.net

コメント

  1. ふじわらかつき より:

    解決しました!ありがとうございました。

  2. オフロスキー より:

    色々なサイトを見ても解決できなかったのに解決出来ました!本当に助かりました!

  3. 佐生豊 より:

    とてもすっきりしました!詳しい方にわかりやすく説明していただくのはもとより、図解入りであることがとても助かりました。

  4. モロコシ より:

    ここに挙がっていないケースとして、名前の定義の参照範囲や
    オートシェイプのセル参照もブック間コピペで外部参照になります。

  5. パチ より:

    このページで解決できました、ありがとうございます。
    入力規則は盲点でした。表の場合は大抵、列ごとに同じ規則が設定されているので、一行目にフォーカスあてて、「同じ入力規則」で引っかけるとおかしいセルがすぐ見つかりました。

    • Blog AdminBlog Admin より:

      具体的な操作も記載いただきありがとうございます!
      お役に立てて何よりです!!

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