基礎
基本的な発生方法
Excelは別のワークブックのセルを参照することができる。
また、Webなどのデータベースに接続することができる。
しかし、こうした設定をしたあと、参照するファイルを削除、フォルダ名を変えるとエラーになる時がある。
数式
一番わかりやすい例は、数式である。
Excel で外部参照へのリンクを解除する
- 外部参照のソース ブックへのリンクを解除し
破った場合、ソース ブックの値を使用する数式はすべて、現在の値に変換されます。 たとえば、外部リファレンス =SUM([Budget.xls]Annual へのリンクを破った場合です。C10:C25) の場合は、SUM 数式が計算値に置き換えられる場合があります。 また、この操作を 元に戻すことはできませんので、コピー先のブックのバージョンをバックアップとして保存することもできます。※解除は英語ではBreakらしい。 - When you break a link to the source workbook of an external reference, all formulas that use the value in the source workbook are converted to their current values. For example, if you break the link to the external reference =SUM([Budget.xls]Annual!C10:C25), the SUM formula is replaced by the calculated value—whatever that may be. Also, because this action cannot be undone, you may want to save a version of the destination workbook as a backup.
- 外部データ範囲を使用する場合、クエリ内のパラメーターが別のブックのデータを使用している可能性があります。 このような種類のリンクはいずれもチェックして削除することが必要な場合があります。
- If you use an external data range, a parameter in the query may be using data from another workbook. You may want to check for and remove any of these type of links.
他には
Excel では、他のブックへのリンクは非常に一般的な作業ですが、Excel が存在すると伝えても見つからなかったリンクを含むブックが表示される場合があります。 ブックで使用されているすべての外部参照を自動的に検索する方法はありません。ただし、それらを検索するために使用できる手動の方法は複数あります。
- 数式
- 定義された名前
- オブジェクト (テキスト ボックスや図形など)
- グラフ タイトル
- グラフ データ系列
この記事は検索方法を示しながら同時にどのようなものがエラーを起こすかを示している。今回はこのなどに含まれているものの一つがわかった。
リンクエラーを気づかせない方法
実際にこのエラーは非常に凶悪で
10枚以上のワークブックの微妙にことなる位置に散在。
さらに列をグループ化して折りたたみ
さらにフィルターをかけて行が見えない。
そして以下のエラーそのものを表示させない。という設定をしてあった。
外部参照 (リンク) の更新方法を制御する
このブック内のリンクは、ユーザーの設定に従ってファイルを開く際に更新するか、ファイルを開く際に更新されないか、ユーザーにメッセージを表示せずに自動的に更新されるのかを決定できます。
警告: このオプションはブックのすべてのユーザーに影響します。 リンクを更新せず、メッセージを表示しないことを選択した場合は、ブックのユーザーは、データが最新ではないことを認識できません。
リンクを更新するためのスタートアップ プロンプトを制御する
[リンクの 編集] > 接続&データ > に移動します。
[起動時の確認] をクリックします。
次の 3 つのオプションのいずれかを選択します。
ユーザーが通知を表示するか表示しないかの選択を許可する
アラートを表示しない、自動リンクを更新しない アラートと更新リンクは表示されません。
データへのリンクを再設定する
ブックに、他の場所に移動されたブックまたは他のファイルのデータへのリンクが含まれている場合は、その ソース ファイルのパスを更新してリンクを修正することができます。 最初にリンクしたドキュメントが見つからないか、アクセスできない場合は、自動更新をオフにするか、リンクを削除することで、Excel がリンクを更新しないようにすることができます。
「などに含まれているデータの入力規則
今回「など」で片付けられていて、このいずれでもないものが発見された。
これはデータの入力規則である。
データの入力規則で、ドロップダウンリストで値を入力するようにする。
そのリストを別のシートに設定する。
これはよくあることだ。しかし、以下のとおりこれが厄介な現象を引き起こしうる。
推測される発生機序
ブック1
- Sheet1
- List
このリストシートからデータを引っ張って入力規則
と同時にブック２
ブック2
- Sheet2
- List
このリストシートからデータを引っ張って入力規則
２つのリストシートが同じものだとする。
ここで次の操作をする。
ブック1とブック2を同時に開く
Sheet2をブック１に移動かコピーする。
もしかするとリンクのあるセルや行でも良い。
ブック1
- Sheet1
- Sheet2
- List
この時、Sheet２は何の変化もないが、Sheet２の入力規則はブック２のListを参照したままになるときがある。つまり意図せず外部参照となる。
これを保存してブック２を消すと参照できないエラーのある外部参照が発生する。いつまでもListシートがあるファイルを参照しようとすることをやってしまう。
これは入力規則自体を消去しない限り消えない。というか気づけない。
定義された名前にも出てこないし、非表示でもない。
解除もできない。
メッセージを出さず自動更新しなければエラーに気づかない。
ドロップダウンリストも機能しない。
ということになる。
Sharepointなどが噛むとIDとパスワードを要求されて大変怪しい挙動となる。
Sheet1は正常なため、エラーが出るシートを特定することも困難となる。
マイクロソフトが解説しているオブジェクト系の現象もワークシートを別のワークブックに複写、移動をしたときに生じるようだ。
しかし、データの入力規則は上記の手法では検索もできない。
解決策としては一旦データの入力規則を解除か、エラーを見つけ出して設定し直すしかない。
しかしもとのリストがなにか類推できなければ設定し直すこともできない。
今回はたまたま正常なシートやリストを参照しているということから類推して修正できたが、これも運が良かっただけでそんな怪物を超えた怪物は存在しないので、100%お手上げとなり、別のワークブックに値と書式を複写して再度作り直すことになる。
## おそらく条件付き書式も
今回の観察された現象から、数式が使える条件付き書式も要注意だと思われる。
## 補足
このリンクのエラーと似たものに、接続のエラーがある。最初に紹介した解説で、
外部データ範囲を使用する場合、クエリ内のパラメーターが別のブックのデータを使用している可能性があります
とあるのはデータの接続を行っている場合である。
外部データ範囲とそのプロパティを管理する
上記の設例でいえば、参照しているリストシート自体が、外部データ接続を行っているというケースである。
データの接続は通常は終わったら必ず削除するべきであるが、うっかり残したまま他人の渡すことは考えられる。
Webのほかテキスト（CSV）、Excel Accessもできるが、これはかなり特殊なケースだと思われる。
そもそもSQLが使えるならAccessで処理するべきである。まあAccess自体が使いづらいので共感は得られないが。
Webから持っくる場合はExcelを使うが、これも特殊な使い方である。
こんな使い方をする人間は株価とかそういうのを扱うか、HTMLパースができるか、常識的な人間がデータ接続をすることはないだろう。