当サイトはアフィリエイト広告を利用しています。
今回は、Excelで違うファイルからデータを引っ張る方法をご紹介します。
この方法を知っていると、たとえば「違うファイルを対象にVLOOKUP関数で検索し、一致するデータを抽出する」といったことができるようになり便利です。
違うファイルからデータを引っ張ろうとするとエラーが発生することもあるので、エラーへの対処法もあわせてご紹介します。
Excelで違うファイルからデータを引っ張る方法
Excelで違うファイルからデータを引っ張る方法には、以下のようなものがあります。
- 外部参照を使う
- データの取得機能を使う
方法1：外部参照を使う
違うファイルの一部のデータを引っ張りたい場合は、外部参照を使う方法がおすすめです。
外部参照を使って違うファイルからデータを引っ張るには、参照先のセルに以下のような数式を入力します。
='参照元ファイルのパス[参照元ファイル名]参照元シート名'!参照元セル番地たとえば、Book1のA1に、Cドライブのデスクトップに保存されているBook2.xlsxのSheet1のA1の内容を反映させたい場合は、Book1のSheet1のA1に='C:¥Users¥＜ユーザー名＞¥Desktop[Book2.xlsx]Sheet1'!A1と入力します。
数式は手入力でも良いですが、参照元ファイルを開いた状態で、参照先のセルに「=」と入力した後、参照元のセルをクリックして［Enter］を押すというやり方のほうが簡単です。Book1のA1に「=」と入力した後、Book2のSheet1のA1をクリックして［Enter］を押すといった感じです。ただし、この場合セル範囲は「$A$1」のように絶対参照になります。
なお、クリックで参照元セルを指定した場合、数式は=[参照元ファイル名]参照元シート名!参照元セル番地のようになりますが、参照元ファイルを閉じると参照元ファイルのパス付きの数式になります。
外部参照設定後に参照先ファイルを再起動すると、初回のみ以下のような「セキュリティの警告」メッセージが表示されるので、「コンテンツの有効化」ボタンをクリックして上書き保存しましょう。これで作業は完了です。
参照元ファイルと参照先ファイルの両方を開いた状態で参照元ファイルのデータを変更すると、そのデータはリアルタイムで参照先ファイルに反映されます。
参照元ファイルが閉じた状態で参照先ファイルを開くと、以下のようなメッセージが表示されます。この画面で「更新」ボタンをクリックすると、参照元ファイルのデータに変更があった場合、その変更が反映され、「更新しない」ボタンをクリックすると変更前のデータが保持されます。
方法2：データの取得機能を使う
違うファイルのデータをシートごと引っ張りたい場合は、データの取得機能を使う方法がおすすめです。
データの取得機能を使って違うファイルからデータを引っ張るには、まず参照先ファイルで「データ」タブ⇒「データの取得」⇒「ファイルから」⇒「Excelブックから」をクリックします。
「データの取り込み」画面が表示されたら、参照元ファイルを選択して「インポート」ボタンをクリックします。
「ナビゲーター」画面が表示されたら、引っ張りたいデータがあるシートを選択して「読み込み」ボタンをクリックします。
すると、新しいシートで参照元シートのデータが読み込まれます。
なお、参照元シートのデータを変更した場合は、参照先の読み込みデータ内にカーソルを置いて「クエリ」タブ⇒「更新」をクリックすると、変更が反映されます。
Excelで違うファイルからデータを引っ張る際の注意点
Excelで違うファイルからデータを引っ張る際は、以下のような点に注意しましょう。
- 参照元ファイルの保存場所・ファイル名・シート名を変更しない
- 参照元ファイルが閉じていると外部参照を使えない関数もある
注意点1：参照元ファイルの保存場所・ファイル名・シート名を変更しない
外部参照やデータの取得機能を使って違うファイルからデータを引っ張る場合、参照元ファイルの保存場所やファイル名を変更するとエラーになるので注意しましょう。データの取得機能に関してはシート名の変更もエラーの原因になります。
注意点2：参照元ファイルが閉じていると外部参照を使えない関数もある
関数の中には、参照元ファイルが閉じた状態だと外部参照が機能しないものもあります。代表的な関数について、参照元ファイルが閉じた状態で外部参照が機能するかどうかを表にまとめてみたので参考にしてみてください。
| 関数名 | 外部参照 |
|---|---|
| COUNTA |  |
| COUNTIF |  |
| DCOUNTA | |
| DSUM | |
| IF | |
| INDEX | |
| INDIRECT | |
| MATCH | |
| SUMIF | |
| SUMPRODUCT | |
Excelで違うファイルからデータを引っ張るとエラーになるときの対処法
Excelで違うファイルからデータを引っ張るとエラーになるときの対処法には、以下のようなものがあります。
- 参照元ファイルのパスを「コンピューター名」で指定する
- 参照元ファイルのパス・ファイル名・シート名を正しいものに修正する
- 常に参照元ファイルを開く
- 数式を値に置き換える
- 参照元ファイルを入手してパスを修正する
対処法1：参照元ファイルのパスを「コンピューター名」で指定する
参照元ファイルのパスを指定する際、「192.168.x.x」のような「IPアドレス」で指定するとエラーが発生することがあります。この場合、参照元ファイルのパスは「コンピューター名」で指定しましょう。
コンピューター名を調べるには、Windows11の場合、まずWindowsキー＋［R］を押して「ファイル名を指定して実行」画面を表示し、「cmd」と入力して「OK」ボタンをクリックします。
コマンドプロンプトが起動したら、nbtstat -A ＜IPアドレス＞と入力して［Enter］を押します。
すると以下のようにNetBIOS名情報が表示されます。枠で囲った部分がコンピューター名ですので、数式のIPアドレスの部分をコンピューター名に置き換えましょう。
IPアドレスをコンピューター名に置き換えるだけではエラーが解消されない場合、以下の手順で参照元ファイルを開いてパスを指定し直しましょう。
まずWindowsキー＋［E］を押してエクスプローラーを起動してアドレスバーをクリックし¥¥＜コンピューター名＞と入力して［Enter］を押します。
「Windowsセキュリティ」画面でネットワーク資格情報の入力を求められたら、ユーザー名とパスワードを入力して「OK」ボタンをクリックします。
するとコンピューター名でフォルダが開きます。あとはこの画面から参照元ファイルを探して開けばOKです。
対処法2：参照元ファイルのパス・ファイル名・シート名を正しいものに修正する
参照元ファイルのパス・ファイル名・シート名が実際の参照元ファイルのパス・ファイル名・シート名と異なっているとエラーになります。異なる場合は正しいものに修正しましょう。
保存場所を変更した覚えも、ファイル名やシート名を変更した覚えもない場合、「ファイルの上位フォルダ名」を変更してしまっている可能性があります。この場合もパスが変更されたことになるのでエラーになります。
その他には、「xlsx→xlsm」のように拡張子が変更されていることも考えられます。
対処法3：常に参照元ファイルを開く
参照元ファイルを開くとエラーが解消される場合は、常に参照元ファイルとセットで開くようにしましょう。
毎回手動で開くのが面倒な場合は、「参照先ファイルを開くと参照元ファイルも自動で開くマクロ」を使うと良いでしょう。このマクロを使えば、いちいち自分でファイルを探して開く手間が省けます。
対処法4：数式を値に置き換える
取引先など外部から送られてきたファイルがエラーになる場合は、外部参照を使っている数式を値に置き換えてもらってからファイルを送ってもらうと良いでしょう。
数式を値に置き換えるには、まず数式が入力されたセルを［Ctrl］＋［C］でコピーします。
「ホーム」タブ⇒「貼り付け」の下の「∨」⇒「値の貼り付け」グループの「値」をクリックします。
これで数式が値に置き換わります。
対処法5：参照元ファイルを入手してパスを修正する
外部から送られてきたファイルがエラーになる場合で、参照元ファイルが入手可能なときは、参照元ファイルを自分のパソコンに保存したうえでパスを修正すると良いでしょう。
参照先ファイルのデータを自動更新する方法
外部参照の場合、参照元ファイルを閉じた状態で参照先ファイルのデータを更新するには、通常参照先ファイル側で上記のようなメッセージが表示されたときに「更新する」ボタンをクリックしなければなりません。
いちいち「更新する」ボタンをクリックするのが面倒な場合は、メッセージを表示せずに自動更新する設定に変更しましょう。
そのためには、まず参照先ファイルで「データ」タブ⇒「ブックのリンク」をクリックします。
シート右側に「ブックのリンク」が表示されたら、歯車アイコンをクリックして「常に更新する」にチェックを付けます。
参照先ファイルを上書き保存して閉じたら作業完了です。これで次回以降は参照先ファイルを開くとデータが自動更新されます。
参照先ファイルを開くと参照元ファイルも自動で開くマクロ
参照先ファイルでVBEを起動し、ブックモジュール（ThisWorkbook）に以下のようなコードを書くと、参照先ファイルを開くと同時に参照元ファイルも自動で開くようになります。
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Workbooks.Open "C:¥Users¥＜ユーザー名＞¥Desktop¥Book2.xlsx"
ActiveWindow.Visible = False
Application.ScreenUpdating = True
End SubC:¥Users¥＜ユーザー名＞¥Desktop¥Book2.xlsxの部分には、参照元ファイルのパス＋ファイル名を入力してください。参照元ファイルのパス＋ファイル名は、参照元ファイルを右クリックして「パスをコピー」をクリックするとコピーできます。
マクロの使い方がわからない方は先に以下の記事をご覧ください。
まとめ
今回は、Excelで違うファイルからデータを引っ張る方法をご紹介しました。
Excelで違うファイルからデータを引っ張るには、外部参照やデータの取得機能を使います。引っ張りたいのが一部のデータの場合は前者が、シート全体の場合は後者がおすすめです。
エラーが発生する場合は、参照元ファイルのパスは「コンピューター名」で指定されているか、参照元ファイルのパス・ファイル名・シート名は正しいか、参照元ファイルは開かれているか、といったことを確認してみてください、外部から送られてきたファイルについては、数式を値に置き換えるか、参照元ファイルを入手してパスを修正すると良いでしょう。