Excelで外部データ接続を見つける5つの方法|リンクの確認・管理を徹底解説

この記事では、Excelで「外部データ接続」を見つける方法を詳しくご紹介します。外部データ接続とは、現在作業中のブックに別のブックからデータを取り込む際に設定される、元データへの参照のことです。これを活用すれば、参照先のブックが更新されたときに、自分のデータセットも常に最新の状態に保つことができます。本記事では、そうした外部接続を確認・追跡する具体的な手順を解説します。

Excelで外部データ接続を見つける5つの方法

ここでは、外部データ接続を見つけるための5つの便利な方法を取り上げます。

• 「検索」機能でリンクを探す
• 「名前の管理」を使う
• 「リンクの編集」コマンドで接続を確認する
• VBAコードで検出する
• Excel 4.0マクロですべての外部接続を抽出する

サンプルとして、「Number of Sale.xlsx」と「Unit Price.xlsx」という2つの異なるシートからデータが接続されたデータセットを使用します。

1. 「検索」機能を使う

まずは、Excelの「検索」機能を使って、データセット内の各セルに含まれる外部接続を見つける方法です。「検索」機能は、必要な値やデータを探すだけでなく、外部接続の検出にも活用できます。

手順:

• まずホームタブを開きます。
• 次に編集グループを選択します。
• 表示されたオプションから「検索と選択」をクリックします。
• ドロップダウンメニューから「検索」を選びます。
• すると、ダイアログボックスが画面に表示されます。

• ダイアログの「検索する文字列」ボックスに「.xl」と入力します。
• 「対象」を「ブック」に設定します。
• 「検索場所」を「数式」に変更します。
• 最後に「すべて検索」をクリックします。

• これで、ブック内のすべてのセルの外部接続が一覧表示されます。

以上の手順で、Excel内のすべての外部データ接続を簡単に見つけることができます。

2. 「名前の管理」を使う

「名前の管理」は、ブック内のすべての名前定義(範囲名)を管理できる機能です。ユーザーは、別のブックにある名前付き範囲を自分のブックに取り込み、数式を効率的に適用することがよくあります。この例でも、「Number of Sales.xlsx」ブックから名前付き範囲を取り込んでいます。

手順:

• まず「Number of Sales.xlsx」ブックを開きます。
• セル範囲B5:B10を選択します。
• 次に、名前ボックスに「Fruits」と入力して範囲に名前を付けます。

• 続いて、元のブックに戻ります。
• セルB5に「=」を入力し、「Number of Sales.xlsx」ブックのFruits範囲を選択することで、両方のブックをリンクさせます。

• 次に数式タブを開き、「名前の定義」を選択します。
• ダイアログボックスが表示されます。

• 「名前」欄に「Fruit_Items」と入力します。
• 「参照範囲」には、「Number of Sales.xlsx」ブックのFruits範囲を指定します。
• 最後に「OK」をクリックします。

• 再び数式タブを開き、定義された名前グループの「名前の管理」を選択します。
• ダイアログボックスが表示されます。

• 「参照範囲」欄に、その名前付き範囲の外部データ接続先が表示されます。

このパスをコピーしてWindowsのエクスプローラー(アドレスバー)に貼り付ければ、「Number of Sales.xlsx」ブックの保存場所へ直接アクセスできます。

あわせて読みたい: 別のExcelファイルへのデータ接続を作成する方法

3. 「リンクの編集」を使う

「リンクの編集」オプションは、外部接続の確認・編集・変更に便利な機能です。ここでは、この機能を使ってリンクを確認してみましょう。

手順:

• まずデータタブを開きます。
• クエリと接続グループから「リンクの編集」を選択します。
• すると、ダイアログボックスが画面に表示されます。

• ダイアログの「ソース」列に、データの参照元ブックの名前が表示されます。
• 「ソースを開く」をクリックすれば、参照元のブックを直接開くことも可能です。

• 結果として、「Number of Sales.xlsx」ワークシートが開かれます。

このようにして、Excelの外部データ接続を確認し、参照元を開くことができます。

4. VBAコードを使う

VBAは、複数の処理を一度に実行できる強力なツールです。この方法では、シンプルなVBAコードを使って、ブック内のすべての外部接続を取得し、新しいシートに一覧表示します。

手順:

• まず開発タブを開きます。
• 「Visual Basic」を選択します。
• 新しいウィンドウ(VBE)が表示されます。

• Visual Basicウィンドウで挿入を選択します。
• ドロップダウンから「標準モジュール」を選びます。
• 新しいモジュールが作成されます。

• モジュールに以下のコードを記述して保存します。

Sub FindExternalConnections()
Dim am_link As Variant
am_link = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(am_link) Then
Sheets.Add
For a = 1 To UBound(am_link)
Cells(a, 1).Value = am_link(a)
Next a
End If
End Sub

• 最後に、緑色の三角ボタン(実行ボタン)をクリックしてコードを実行します。

• すると、新しいシートにブック内の外部接続の一覧が表示されます。

「リンクの編集」の場合と同様に、表示されたリンクから各ブックの保存場所にアクセスできます。

あわせて読みたい: Excel VBAですべてのデータ接続を更新する方法

5. Excel 4.0マクロですべての外部接続を抽出する

Excelの4.0マクロ(XLMマクロ)は、VBAの前身となる機能で、VBAが登場する前は繰り返し作業の自動化に使われていました。現在でも一部の4.0マクロ関数は利用可能です。ここでは、今も残されている4.0マクロ関数を使って外部データ接続を抽出し、TRANSPOSE関数(TRANSPOSE関数)で縦方向のリストに変換します。

手順:

• まず数式タブを開きます。
• 定義された名前グループから「名前の管理」を選択します。
• ダイアログボックスが表示されます。

• 「名前の管理」ダイアログで「新規作成」を選択します。

• まず、「名前」欄にマクロ名を入力します(例:「GetLinks」)。
• 次に、「参照範囲」ボックスに=LINKS()と入力します。
• 最後に「OK」をクリックします。

• 次に、セルB5を選択し、以下のように入力します。

=TRANSPOSE(GetLinks)

• Enterキーを押すと、外部データ接続の一覧が縦方向に表示されます。

表示されたリンクをWindowsの検索ボックスに入力すれば、該当する外部データファイルの場所を特定できます。

あわせて読みたい: 【解決済み】Excelで外部データ接続が無効化される問題の対処法

練習用ワークブックのダウンロード

この記事で使用した練習用ワークブックはこちらからダウンロードできます。

まとめ

この記事では、Excelで外部データ接続を見つける複数の方法をご紹介しました。これらのテクニックを活用すれば、既存のリンクの確認、更新、切断、削除などを効率的に行えるようになります。ご不明な点があれば、お気軽にコメント欄でお知らせください。

関連記事

• ファイルを開かずにExcelのデータ接続を更新する方法
• Excelの接続とクエリの違いとは
• Excelでデータソースを作成する方法
• Excelでデータ接続が更新されないときの対処法
• Excelのクエリと接続が動作しない場合の解決策
• Excelでクエリと接続ペインを閉じる方法

<< Excelのデータ接続に戻る | Excelでのデータ取り込み | Excelをもっと学ぶ

無料の高度なExcel演習問題(解答付き)を受け取る!