![別ブックの複数データ(テーブル/シート)を取得する方法 [Power Query(パワークエリ)応用]](https://i0.wp.com/excel-master.net/wp-content/uploads/2021/01/PowerQueryデータ取得_別ブック複数データ01-2.png?fit=750%2C422&ssl=1)
![Aさん](https://i0.wp.com/excel-master.net/wp-content/uploads/2016/12/businessman_sleep.png?resize=229%2C300&ssl=1)
前回、別のExcelブック内のテーブルやシートを取り込む方法は理解できましたが、複数のテーブルやシートを一度に取り込むこともできるんですか?
![森田](https://i0.wp.com/excel-master.net/wp-content/uploads/2018/02/sns02201.jpg?resize=400%2C400&ssl=1)
できますよ!
その場合、途中までは単一の場合と手順は一緒ですが、ナビゲーター画面の設定以降から変わります。
それでは、具体的な手順を解説していきますね。
解説動画:【パワークエリ#4】別ブックを対象としたデータインポート手順 – 他の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
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)
ファイルを開いたら、次の手順を実施してください。(今までの解説のまとめです)
- リボン「データ」タブをクリック
- 「データの取得」をクリック
- 「ファイルから」をクリック
- 「ブックから」をクリック
- 任意のExcelブックを選択
- 「インポート」をクリック
- 取得対象のブック名を指定
- 「データの変換」をクリック
- 取り込みたいテーブル/シートをフィルターで指定
- 「Data」列のボタンをクリック
- 取り込みたい列を選択
- 「元の列名をプレフィックスとして使用します」のチェックを外す
- 「OK」ボタンをクリック
- 各列のデータに適したデータ型へ変更
- 不要な列を削除
- 「閉じて読み込む」をクリック
- 「閉じて次を読み込む」をクリック
- データの返し先を既存ワークシートのA1セル設定
- 「OK」ボタンをクリック
本記事の解説と同じ結果になればOKです!
さいごに
いかがでしたでしょうか?
本テクニックはPower Query(パワークエリ)のデータ取得の中では応用に当たる範囲なので、少々難易度が上がります。
別ブックから複数データをまとめて整形・加工したい方はぜひチャレンジしてみてください。
ちなみに、別な表形式(列の構成)でも複数取り込むことはできますが、データ整形の難易度が上がるため、なるべく同じ列構成の表に絞った方が良いですね。
なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
![森田](https://i0.wp.com/excel-master.net/wp-content/uploads/2018/02/sns02201.jpg?resize=400%2C400&ssl=1)
別ブックの複数データの取り込みを自動化するには、従来はマクロ(VBA)が必須でした。
なので、プログラミングが必要なので挫折する人も多かったですが、Power Query(パワークエリ)ならマウス操作中心で自動化できるのが素敵ですね!
別ブックを扱う頻度が高い人は、ぜひマスターしてくださいね。