別ブックの複数データ(テーブル/シート)を取得する方法 [Power Query(パワークエリ)応用]
AさんAさん

前回、別のExcelブック内のテーブルやシートを取り込む方法は理解できましたが、複数のテーブルやシートを一度に取り込むこともできるんですか?

森田森田

できますよ!

その場合、途中までは単一の場合と手順は一緒ですが、ナビゲーター画面の設定以降から変わります。

それでは、具体的な手順を解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • 別ブックの複数のテーブル/シートをまとめて整形・加工したい方
  • データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
  • Power Query(パワークエリ)の使い方を習得したい方

前提条件

この記事のテクニックを使うためには、以下の条件を満たす必要があります。

  • ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
  • Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
  • Power Query(パワークエリ)の別ブックの単一データの取得方法を理解していること
    →解説記事はこちら

データ整形・加工したい対象が別ブックの複数のテーブル/シートのケース

次のように、別ブック内の複数テーブル/シートをまとめてデータ整形したいケースもあると思います。

こうした場合、従来は「別ブックを開いて、そのテーブル/シートの数だけExcelへコピペ」を繰り返す必要がありました。

しかし、Power Query(パワークエリ)ならこうしたまとめて複数の表データの取り込みを自動化できます。

では、別ブックのデータの複数データの取得手順を確認していきましょう。

別ブックの複数データの取得手順

Power Query(パワークエリ)による別ブックのデータ取得は、以下の8ステップとなります。

STEP1】リボン「データ」タブから「データの取り込み」ダイアログを起動

まずは、リボン「データ」タブをクリック(①)し、「データの取得」をクリック(②)します。

そして、「ファイルから」をクリック(③)の上、「ブックから」をクリック(④)しましょう。

STEP2】任意のブック(.xlsx/.xlsm/.xls)をインポート

「データの取り込み」ダイアログが起動しますので、データの取得元となる任意のブックを選択(⑤)し、「インポート」をクリック(⑥)します。

今回は「社員マスタ.xlsx」を選択しました。

STEP3】ナビゲーター画面から「Power Queryエディター」を起動

ナビゲーター画面が起動します。

指定したブック内のシートやテーブルが一覧で表示されますが、取得対象のブック名を指定(⑦)し、「データの変換」をクリック(⑧)しましょう。

特定のテーブル/シートを1つのみ取得したい場合はこちらの記事をご参照ください。

【参考】ナビゲーター画面のテーブル名・シート名の見分け方

ブック名の下にはそのブックに存在するテーブル名とシート名が表示されています。

なお、今回はテーブル名もシート名も「社員マスタ_2019」など同じ名称があるため、Power Query(パワークエリ)上で重複を避けるために、テーブル名の方が「社員マスタ_20191」など別名で認識されています。

テーブル名とシート名の見分け方は、アイコンの形を見てください。

アイコンの上部に色が付いているものがテーブル、アイコンの下部にタブがあるものがシートです。

もちろん、ブック内にテーブルが存在しない場合は、シートだけが表示されます。

STEP4】取り込みたいテーブル/シートをフィルターで指定

Power Queryエディターが起動します。

すると、指定したブック内にあるすべてのテーブルとシートの情報が「プレビュー」ウィンドウにレコード化されています。

まず行うのは、取り込みたいテーブル/シートをフィルターで指定(⑨)することです。

今回はテーブルとシートで表データが重複しているため、「Kind」列を「Table」のみでフィルターをかけました。

このフィルター条件は、対象ブックや作業目的によって異なりますので、ケースバイケースで指定してくださいね。

なお、クエリ名は手順⑦で指定したブック名になっているため、別名称が良ければ「クエリの設定」ウィンドウ上でクエリ名を書き換えましょう。

Power Queryエディター上のフィルター操作はこちらの記事も参考にしてみてください。

フィルター設定の注意事項

指定した別ブック内でPower Query設定以降に増えたテーブル/シートも自動的に取り込む対象にしたい場合、フィルター設定に気をつけましょう。

Kind」列だけなら心配ないですが、Name」列などで選択する際はチェックボックスでフィルター設定せず、テキストフィルターを使うことをおすすめします。

理由は、チェックボックスだと「指定の値と等しい」という条件となり、増やしたテーブル/シートが含まれない可能性があるためです。

よって、テキストフィルターで「指定の値を含む」「指定の値で始まる」などの条件にして、増やしたテーブル/シートも含まれる条件にしておきましょう。

STEP5】「Data」列を展開

取り込みたいテーブル/シートを選択できたら、Data」列のボタンをクリック(⑩)します。

これで、指定したテーブル/シートから取り込む列を選択できます。

あとは、取り込みたい列を選択(⑪)し、「元の列名をプレフィックスとして使用します」のチェックを外す(⑫)ことまで完了したらOK」ボタンをクリック(⑬)してください。

手順⑪はSTEP4で指定したテーブル/シートに含まれる列がすべて表示されます。(表ごとに列名が異なる場合含む)

手順⑫のチェックを付けておくと、列名の頭に「Data.」が付加されてしまいます。
例)プレフィックスとして使用した場合:「Data.社員番号」

STEP6】展開した列のデータ型の変更と不要な列を削除

展開した列のデータ型は「すべて」になっているため、各列のデータに適したデータ型へ変更(⑭)しましょう。

今回は「社員番号」「氏名」のデータ型は「テキスト」、「入社日」のデータ型は「日付」に変更します。

あとは、不要な列を削除(⑮)しておきましょう。

今回はSTEP5で展開した列以外は不要なので削除します。

その他、データ整形・加工を行いたい方は、ここで任意の作業を順次設定してくださいね。

データ型の変更についてはこちらの記事も参考にしてみてください。

列の削除についてはこちらの記事も参考にしてみてください。

STEP7】「Power Queryエディター」を閉じてデータを読み込み

すべての作業を設定できたら、最後に「閉じて読み込む」をクリック(⑯)し、「閉じて次を読み込む」をクリック(⑰)すればOKです。

STEP8】「データのインポート」ダイアログでデータの返し先を選択

「データのインポート」ダイアログが起動しますので、どこにデータを返すかを指定(⑱)しましょう。

今回は、既存ワークシートのA1セルを起点にデータを返すように設定しました。

設定が完了次第、OK」ボタンをクリック(⑲)します。

これにて作業は完了です!

ワークシート上に指定ファイルのデータが取り込まれればOK

無事A1セルを起点に指定した別ブック内の複数テーブルのデータが取り込まれました!

そして、ワークシートの右側に「クエリと接続」ウィンドウが表示されます。

そのウィンドウ上に「社員マスタ」というクエリが作成されていることがわかりますね。

なお、クエリ名の下を見ると、9行のデータが読み込まれたことがわかります。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_PowerQueryデータ取得_ブックから(複数).zip

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. リボン「データ」タブをクリック
  2. 「データの取得」をクリック
  3. 「ファイルから」をクリック
  4. 「ブックから」をクリック
  5. 任意のExcelブックを選択
  6. 「インポート」をクリック
  7. 取得対象のブック名を指定
  8. 「データの変換」をクリック
  9. 取り込みたいテーブル/シートをフィルターで指定
  10. Data」列のボタンをクリック
  11. 取り込みたい列を選択
  12. 「元の列名をプレフィックスとして使用します」のチェックを外す
  13. OK」ボタンをクリック
  14. 各列のデータに適したデータ型へ変更
  15. 不要な列を削除
  16. 「閉じて読み込む」をクリック
  17. 「閉じて次を読み込む」をクリック
  18. データの返し先を既存ワークシートのA1セル設定
  19. OK」ボタンをクリック

本記事の解説と同じ結果になればOKです!

動画の解説もどうぞ!

上記の内容を動画でも解説しています。

文字だけではわかりにくかった方は動画を参考にしてくださいね。

もし、参考になった方はチャンネル登録ならびに高評価をよろしくお願いいたします!

さいごに

いかがでしたでしょうか?

本テクニックはPower Query(パワークエリ)のデータ取得の中では応用に当たる範囲なので、少々難易度が上がります。

別ブックから複数データをまとめて整形・加工したい方はぜひチャレンジしてみてください。

ちなみに、別な表形式(列の構成)でも複数取り込むことはできますが、データ整形の難易度が上がるため、なるべく同じ列構成の表に絞った方が良いですね。

なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

別ブックの複数データの取り込みを自動化するには、従来はマクロ(VBA)が必須でした。

なので、プログラミングが必要なので挫折する人も多かったですが、Power Query(パワークエリ)ならマウス操作中心で自動化できるのが素敵ですね!

別ブックを扱う頻度が高い人は、ぜひマスターしてくださいね。