Excelのブックを開いたときに、外部ソースへのリンクについての警告が出て困ったことはないでしょうか。これは、ブックの中に別のExcelファイルを参照する「外部リンク」が残っているという知らせです。どのセルにリンクがあるのか分からないまま、毎回警告を閉じている人も多いと考えられます。
外部リンクは、「データ」タブの機能で一覧を確かめ、「リンクの解除」で数式を値に変えるのが基本のやり方です。ただし、数式以外の場所に参照が残っていると、解除したはずなのに警告が消えないことがあります。解除できない原因は、リンクが残りやすい場所を順に確かめていけば見つけられます。
この記事では、外部リンクの探し方と、リンクが消えないときの原因と対処法を初心者向けに説明します。数式、名前の定義、入力規則、条件付き書式、グラフや図形、シートの保護の順に、確かめる場所を具体例で紹介します。社外にファイルを送る前の確認にも使える手順です。
外部リンクは、ファイルを開く動作を遅くする原因にもなります。開くのに時間がかかるブックでは、画像や書式とあわせて見直すと効果的です。Excelが重い原因と対処法は、別の記事でまとめています。
外部リンクとは?探し方と「リンクの解除」のやり方
外部リンクとは、別のブックのセルを参照している数式などのことです。現在のExcelでは「ブックのリンク」という機能で管理し、以前は外部参照と呼ばれていました。参照される側のブックは、リンク元と呼ばれます。
外部参照があるブックを開くと警告が出る
外部参照を含むブックを開くと、リンクの更新についての警告が表示されることがあります。メッセージバーに、リンクの自動更新を止めたという内容の警告が出る場合があります。この場合は「コンテンツの有効化」を押すと、リンクが更新されます。
ダイアログの場合は、「このブックには、安全ではない可能性のある外部ソースへのリンクが1つ以上含まれています。」と表示されます。リンク元が信頼できるファイルなら「更新する」を、分からなければ「更新しない」を選びます。
「更新しない」を選ぶと、リンク元の最新の値を取り込まないまま使い続けられます。どちらを選んでも、外部リンクそのものはブックに残ります。
「ブックのリンク」でリンク元の一覧を見る
どのブックを参照しているかは、「データ」タブの「クエリと接続」グループにある「ブックのリンク」で確かめます。クリックすると作業ウィンドウが開き、リンク元のブックが一覧で表示されます。古いバージョンでは、同じ場所に「リンクの編集」というボタンが表示されます。
古い「リンクの編集」は、ブックに外部リンクがないときは薄い色になり、押せません。押せる状態なら、少なくとも1つはリンクが残っていると判断できます。
検索と置換で外部参照のセルを探す
一覧でリンク元のブック名が分かったら、そのブックを参照しているセルを探します。セルの場所は、検索機能を次の手順で使うと一度に見つけられます。
1. 「ホーム」タブの「検索と選択」から「検索」を選ぶ(Ctrlキー+Fキーでも開ける)
2. 「検索する文字列」に「.xl」と入力し、「オプション」をクリックする
3. 「検索場所」を「ブック」、「検索対象」を「数式」にして、「すべて検索」をクリックする
「.xl」は、Excelのファイルの拡張子(.xlsxや.xlsなど)の先頭部分です。ダイアログの下に、外部参照を含むセルの一覧が表示され、クリックするとそのセルが選ばれます。角かっこの「[」で探す方法もありますが、テーブルの列名にも角かっこが使われるため、結果を1つずつ確かめます。
「リンクの解除」で数式を値に変えるやり方
例として、大阪支店の売上を、集計用のブックで参照している場面を考えます。リンク元の「大阪支店.xlsx」のSheet1には、次の表が入っています。
| A | B | |
|---|---|---|
| 1 | 商品 | 4月売上 |
| 2 | 商品A | 120000 |
| 3 | 商品B | 80000 |
| 4 | 商品C | 50000 |
| 5 | 合計 | 250000 |
B5には次の数式が入っていて、250000と表示されています。
=SUM(B2:B4)
商品A〜商品Cの4月の売上を合計した値です。
集計用のブック「支店別集計.xlsx」は、次のような表です。
| A | B | |
|---|---|---|
| 1 | 支店 | 4月売上 |
| 2 | 大阪支店 | 250000 |
B2には、大阪支店のB5を参照する次の数式が入っていて、250000と表示されます。
=[大阪支店.xlsx]Sheet1!$B$5
これは、別のブック「大阪支店.xlsx」のSheet1にあるB5の値を取り出す数式です。リンク元を閉じると、数式バーには保存場所のフォルダーを含む形で表示されることがあります。
このリンクを解除するときは、次の手順で操作します。
1. 「ブックのリンク」を開き、リンク元のブック名の横にある「…」をクリックする
2. 「リンクの解除」を選ぶ
古い「リンクの編集」では、一覧でリンク元を選んで「リンクの解除」をクリックします。解除すると、B2の数式は250000という値に置き換わります。この操作は元に戻せないため、解除する前にブックのコピーを保存しておきます。
実務では、社外に送るブックのリンクを解除しておくと、相手先で警告が出るのを防げます。数式にはリンク元の保存場所が含まれるため、社内のフォルダー名を外に出さないためにも役立ちます。
初心者がつまずきやすいのは、「リンクの解除」がリンク元ごとに働く点です。そのブックを参照している数式は、選んだセルに限らずすべて値になります。一部のセルだけを値にしたい場合は、そのセルをコピーし、「形式を選択して貼り付け」の「値」で同じ場所に貼り付けます。
外部リンクが解除できない原因①|名前の定義に外部参照が残っている
名前の定義は、セル範囲に「税率」などの名前を付けて、数式の中で使う機能です。この名前が別のブックのセルを指していると、それも外部リンクとして扱われます。名前の定義の中のリンクは、「リンクの解除」では消えないため、警告が残る原因になります。
「名前の管理」で外部参照の名前を探す
名前の中の参照は、「数式」タブの「名前の管理」で確かめます。一覧の「参照範囲」の列を見て、角かっこでブック名が入っている名前を探します。別のブックを指す名前は、「[設定.xlsx]」のようにブック名が表示されるのが目印です。
例えば、名前「税率」の参照範囲が、次のようになっているとします。
=[設定.xlsx]Sheet1!$B$1
設定.xlsxのB1には、0.1が入っています。集計用のブックのC2に次の数式を入れると、B2が250000なら25000と表示されます。
=B2*税率
売上に税率を掛けて、税額を求める計算です。C2の数式にはブック名が見えませんが、名前を通して設定.xlsxを参照しています。この場合は、税率を集計用のブックのセルに入力し、「編集」で名前の参照範囲をそのセルに変更します。
もう1つの例は、どの数式にも使われていない名前が残っている場合です。別のブックからシートをコピーすると、そのブックで使っていた名前が一緒に持ち込まれることがあります。参照範囲に「[単価マスタ.xlsx]」などが入った「単価表」のような名前があれば、一覧で選んで「削除」をクリックします。
名前を削除する前に数式で使われていないか確かめる
数式で使っている名前を削除すると、その数式は#NAME?エラーになることがあります。#NAME?は、数式の中の名前をExcelが見つけられないときに出るエラーです。削除する前に、検索と置換で名前を探し、使われていないかを確かめます。
例えば「単価表」を消す前に、「検索する文字列」に「単価表」と入れ、「検索対象」を「数式」にして「すべて検索」を押します。一覧に何も出なければ、セルの数式には使われていないと判断できます。セルが見つかった場合は、先に数式を直してから名前を削除します。
実務では、ほかの部署から受け取ったシートを何度もコピーするうちに、使われない名前がたまっていくことがあります。名前の数が多いブックでは、「参照範囲」の列の見出しをクリックして並べ替えます。ブック名の入った名前がまとまり、見つけやすくなります。
初心者がつまずきやすいのは、「名前の管理」に表示されない名前が原因になっている場合です。こうした名前は、標準の画面からは見つけられません。ここまで確かめても警告が消えないときは、ファイルを作った人や社内の担当者に相談するのが安全です。
名前の付け方や、「名前の管理」での参照範囲の変え方は、名前の定義の使い方の記事で詳しく説明しています。
外部リンクが解除できない原因②|入力規則や条件付き書式に残っている
「リンクの解除」が働くのは、セルに入力された数式の中のリンクです。入力規則や条件付き書式の設定に残った参照は、値に変わらず、リンク元が一覧に残ることがあります。特に、別のブックからシートをコピーしたときに、こうした参照が持ち込まれやすくなります。
入力規則の「元の値」がリンク元を指している
プルダウンリストの選択肢を、別のブックの表から読み込んでいる場合があります。プルダウンのあるセルを選び、「データ」タブの「データの入力規則」をクリックして、「元の値」を確かめます。「元の値」に別のブックのセル範囲や、別のブックを指す名前が入っていれば、それが外部リンクです。
例えば、担当者を選ぶプルダウンの「元の値」が、名前「担当者一覧」になっていたとします。「名前の管理」で「担当者一覧」を確かめると、参照範囲に「[担当者.xlsx]」が入っていました。この場合は、担当者の一覧を集計用のブックの別のシートに貼り付け、「元の値」をそのセル範囲に指定し直します。
条件付き書式のルールの数式に外部参照が残っている
条件付き書式のルールの数式にも、別のブックへの参照が残ることがあります。「ホーム」タブの「条件付き書式」から「ルールの管理」を開き、数式にブック名が入ったルールを探します。初めは選んでいるセルのルールだけが表示されるため、ダイアログ上部の表示の切り替えで、シート全体のルールを表示します。
例えば、B列の売上が目標を下回る行に色を付けるルールが、次の数式になっていたとします。
=$B2<[売上目標.xlsx]Sheet1!$B$1
この数式は、別のブック「売上目標.xlsx」のB1にある目標の値と比べています。目標の値を集計用のブックのセルに入力し、ルールの数式をそのセルを参照する形に直すか、不要ならルールを削除します。
実務では、条件付き書式のルールはシートごとに保存されているため、1つのシートを確かめただけでは見落とします。シートが多いブックでは、1枚ずつ「ルールの管理」を開いて確かめます。
初心者がつまずきやすいのは、入力規則や条件付き書式を直したあと、そのままにしてしまうことです。参照を消したら、もう一度「ブックのリンク」を開き、リンク元が一覧から消えたかを確かめます。残っていれば、「リンクの解除」をやり直します。
プルダウンリストの作り方や、「元の値」の指定のやり方は、データの入力規則の使い方の記事で解説しています。
外部リンクが解除できない原因③|グラフ・図形やシートの保護
外部リンクは、セルだけでなく、グラフや図形の中にも残ることがあります。また、シートが保護されていると、リンクの解除そのものがうまく働かないことがあります。ここでは、セル以外の場所と保護の確かめ方を説明します。
グラフの系列の数式にリンク元が残っている
グラフの棒や線をクリックすると、数式バーにSERIES関数の数式が表示されます。SERIES関数は、グラフがどのセルのデータを使っているかを表す数式です。この数式に「[大阪支店.xlsx]」のようなブック名があれば、グラフがリンク元を参照しています。
例えば、支店別集計.xlsxに、大阪支店.xlsxの商品別の売上から作った棒グラフがあるとします。セルの数式のリンクを解除しても、グラフの系列が大阪支店.xlsxを指したまま残る場合があります。この場合は、商品別の売上を集計用のブックに貼り付け、グラフの元データをそのセル範囲に選び直します。
グラフのタイトルも、セルの値を表示する設定にしていると、別のブックを参照していることがあります。タイトルをクリックし、数式バーにブック名が出ていないかもあわせて確かめます。
図形やテキストボックスの外部参照を探す
図形やテキストボックスに、セルの値を表示させていることがあります。図形をクリックすると数式バーに参照が表示され、そこにブック名があれば外部参照です。1つ選んだあとにTabキーを押すと、次の図形に順に移れます。
例えば、報告用のシートのテキストボックスに、大阪支店.xlsxの合計を表示しているとします。テキストボックスを選んで、数式バーの参照を消すか、同じブックのセルを参照する形に直します。
シートの保護で数式が値に変わらない
リンクの解除をしても、保護されたシートでは数式を値に変えられないことがあります。保護されているシートでは、セルの内容を変更できないためです。「校閲」タブの「シート保護の解除」で保護を外してから、もう一度「リンクの解除」を試します。
保護にパスワードが設定されている場合は、解除するときにパスワードの入力を求められます。パスワードが分からないときは、ファイルを作った人に確認します。リンクを解除し終えたら、必要に応じてシートを保護し直します。
実務では、グラフの系列は1本ずつ確かめる必要があります。複数の系列があるグラフでは、棒や線をそれぞれクリックして、数式バーを見比べます。
初心者がつまずきやすいのは、グラフや図形のリンクが、検索と置換で見つからない点です。検索で見つかるのはセルの数式だけのため、グラフと図形は1つずつクリックして確かめます。
シートの保護の仕組みや、パスワードの扱いは、シートの保護で編集できない原因の記事で説明しています。
外部リンクの探し方まとめ|数式の外に残る参照まで確かめて解除する
外部リンクは、まず「ブックのリンク」でリンク元を確かめ、「リンクの解除」で数式を値に変えるのが基本です。それでも警告が消えない場合は、数式以外の場所に参照が残っています。確かめる場所と直し方は、次の表のとおりです。
| 残っている場所 | 確かめる画面 | 直し方 |
|---|---|---|
| セルの数式 | 検索と置換(「.xl」を検索) | 「リンクの解除」か値で貼り付け |
| 名前の定義 | 「名前の管理」の「参照範囲」 | 参照範囲を変更するか削除 |
| 入力規則 | 「データの入力規則」の「元の値」 | 同じブックのセル範囲に指定し直す |
| 条件付き書式 | 「ルールの管理」の数式 | 数式を直すかルールを削除 |
| グラフ・図形 | クリックしたときの数式バー | 元データや参照を選び直す |
| 保護されたシート | 「校閲」タブ | 保護を外してから解除 |
どの場所も、直したあとに「ブックのリンク」を開き、リンク元が一覧から消えたかを確かめます。リンクの解除は元に戻せないため、作業の前にブックのコピーを保存しておきます。社外に送るブックは、送る前にこの表の順で確かめておくと、相手先での警告を防げます。
外部リンクのほかにも、Excelの基本機能は使い方を知っておくと、困ったときに原因を見つけやすくなります。Excelでできること一覧では、主な機能と使い方をまとめて紹介しています。






