知らないと損！Excel2019「Power Query」活用必須の便利機能編

この連載では「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」と題し、Excel2019（Office365）の新機能や活用方法を、13回にわたり紹介しています。

今回は、ついに最終回。連載第10回からは、Excel2019で進化した便利機能「Power Query」の使い方をご紹介してきました。今回は、Power Queryの活用において、知っておきたい便利な機能をご紹介します。

「Power Queryでこんなこともできるのか！」と知っていただき、あなたの作業効率化に活かしてもらえたらと思います。

データの取込み・集計に最適！PowerQueryの使い方
（1）概要とデータの取込み（連載第10回）
（2）データの整形（連載第11回）
（3）複数データの結合（連載第12回）
（4）フォルダ内複数データの結合から集計まで（連載第13回） ←今回はココ

複数ブックを1回で取り込む方法｜フォルダからの取り込み

連載第10回では、Power Queryでデータ（1つのブック）を取り込む方法についてご紹介しました。
しかし会社によっては、月ごとや担当者ごと、支店ごとなど、ブックを複数に分けることがよくあると思います。ブックが複数に分かれていると、データを1つ1つ取り込むのは手間ですよね。

そこで今回は、下図のように、フォルダ内に複数のブックを保存するだけで、簡単に取り込める方法について紹介していきます。これができるだけで、今後の作業効率がかなり違いますよ。

事前準備

フォルダからデータを取り込むといっても、保存場所や列の名前などがバラバラでは、取り込めずエラー表示が出てしまいます。

まず、取り込むファイルは、必ず同一フォルダ内に保存しておきましょう。

そして、各ブックの列名は、A列は「日付」、B列は「社員ID」、C列は「数量」などのように、同じ名前で統一します。また、シート名も統一しておきましょう。

フォルダからデータを取り込む

ではさっそく、フォルダからデータを取り込んでみましょう。今回は、売上明細データ4月分・5月分・6月分を一気に取り込みます。

① Excelを開き、「データ」タブの「データの取得」→「ファイルから」→「フォルダーから」と選択します。

② 取り込みたいフォルダ を指定 し「開く」を選択しましょう。

③ フォルダの中のブックの リストが表示されます。 「データの変換」を選択しましょう。

④ ブックを1つ取り込む場合は、先の手順で「データの変換」をすれば、中のデータが即表示されていましたが、フォルダから取り込むときは、各ブック名の表示になっています。

左端の列にある「Content」の「Fileの結合」ボタンをクリックすると、各データの結合が開始されます。

⑤ 「Fileの結合」画面が表示されます。左側にある「売上明細」を選択後、「OK」をクリックしましょう。

⑥ 左端の列「Source.Name」列は、データが保存されているブック名 です。ここでは不要なので、「Source.Name」列を選択し、 「列の削除」をクリックして削除しましょう。

⑦ 「日付」列が「数値」になっているので、「日付」列の列名の左端をクリックして、「日付」型を選択し、表示を整えます。

⑧「閉じて読み込む」を選択しましょう。

⑨ 以上で取り込み完了です。フォルダ内にある複数ブックのデータを、 一度に取り込むことが できましたね。

例えばExcelの売上データに翌月の売上データを追加する場合、Excel2013のときは、最後の行の下にコピー＆貼り付けして対応していたと思います。

これが、Excel2019のPower Queryを使えば、フォルダに複数データを入れるだけで、簡単に結合できるようになったのです。
作業的にもかなり時短化できるのではないでしょうか。ぜひ一度やってみてください。

複数ブック取り込み後に、追加データを取り込みたい場合

先ほどはフォルダ内の4月から6月までの売上明細ブックを取り込みましたが、翌月になればまた新たなデータを取り込み、更に翌月も取り込み…と、延々と取り込まなければいけないと思うと、面倒ですよね。

しかしExcel2019のPower Queryでは、一度フォルダからの取り込み作業が済んでいれば、“更新”するだけで、追加のデータも簡単に取り込めます。

これも画期的な機能ですよね！「売上明細7月」データを追加するやり方を、以下詳しく紹介します。

行方向にデータを追加する

① 取り込むデータの「列名」と「シート名」は、これまで取り込んだブックのそれと同じ名前にしておく必要があります。もし違う名前になっていたら、ここで変更しておきましょう。

② 4月から6月までの売上明細と同じフォルダ内に、「売上明細7月.xlsx」と名前を付けて保存しておきましょう。

③ 前章で作成したワークシートにある、テーブルの上で右クリックし、「更新」を選択。

④ 先程のテーブルの下に、7月分が追加されました。これで完成です。

列方向にデータを追加する

4月から7月までの売上明細が完成しましたが、今度は社員リストを取り込んで結合しようと思います。

4月から7月までは、行が増える結合でしたが、社員リストの結合は、列が増える結合になります。
「社員ID」から紐づけされる「社員名」を結合するため、社員IDだけで分かりにくかった売上明細テーブルがわかりやすくなる想定です。

ここで注意事項！行方向にデータを追加するときは、取り込むデータは取り込み先と同じフォルダ内に保存していました。しかし列方向にデータを追加するときは、「売上明細」と同一フォルダに保存するとテーブルの形が変わってしまうため、別の場所へ保存をする必要があります。

下図のように、取り込むデータは、取り込み先と異なるフォルダに保存してください。

そして、操作に入る前に復習になりますが、「社員リスト」の取り込み方法について簡単に解説します。

先ほど作成した売上明細（結合後）のExcelを開き、「データ」タブ＞「データの取得」＞「ファイルから」＞「ブックから」、指定されたフォルダ内にある「社員リスト.xlsx」を選択し「インポート」。
次に、インポートした「社員リスト.xlsx」にある、今回使用するワークシートを選択し、「データの変換」＞「閉じて読み込む」を選択します。
※データの取り込みに関しては、連載10回で詳しく解説しているので、ご覧ください。

取り込んだ社員リストが、こちらになります。

①「売上明細2021」をメインに「社員リスト」を結合するので、「売上明細2021」を選択し、「クエリのマージ」から「新規としてクエリをマージ」をクリックしましょう。

② マージの画面では、上から2番目の下向き三角をクリックし、「社員リスト」を選択。ここでは、結合の種類を「左外部（最初の行すべて、及び2番目の行のうち）」のままにしておきます。つまり「売上明細2021」をメインとして、社員リストを紐づけするのです。

③ 売上明細2021の「社員ID」と社員リストの「社員ID」を選択し、「OK」をクリック。

④ これで結合は完了です。次に「社員リスト」の右側の展開ボタンをクリックしましょう。

⑤「社員ID」のチェックと、「元の列名をプレフィックスとして使用します」のチェックを外しま す。

⑥「社員名」を「社員ID」の右に移動し、クエリ名を「売上明細結合後」に変更すれば完成です。

これで4月から7月までの売上明細に社員名が入り、わかりやすいテーブルが完成しました。

列の結合は、Excel2013以前ではVLOOKUP関数で行っていた作業にあたります。
VLOOKUP関数では、新しい月が追加されれば、関数が入力されたセルをコピーで貼り付けしなければなりませんでした。それに比べると、Excel2019のPower Queryによる手間軽減の違いは明らかではないでしょうか。

Power Queryでグループ集計

連載第10回以降、Excel2019のPower Queryを使った、データ取り込みから編集・変換までの作業を行ってきました。ここからは、集計の方法についてのご紹介です。

集計に関しては、Excel2013にあるピボットテーブルやExcel2019にあるPower Pivotを使う方法もあります。
しかしPower Queryを使えば、前回紹介したような「単価」列に「数量」列を掛け合わせる計算だけでなく、列の上から下までを、グループごとに合計や、平均、最大などの集計ができるのです。

ここでは、社員ごとの数量合計を集計するテーブルを作成したいと思います。

① 集計する前の準備として、先程作成した「売上明細結合後」クエリの複製を作ります。複製したクエリを使って、「社員ごとの数量合計」のクエリを作成します。

「売上明細結合後」クエリの上で右クリックし、「複製」を選択しましょう。「売上明細結合後（2）」ができあがるので、名前を画面右側にあるクエリの設定で、名前を「社員ごとの数量合計」に変更します。

② 集計するにあたり、まずは「グループ化」を選択します。

③ グループ化の画面の上の部分では、どの列をグループにするかを選択します。
ここでは、社員名単位で合計を出したいため、「詳細設定」を選び、「社員ID」と「社員名」を選択してください。
「社員ID」は並べ替えに必要なため、「社員名」は分かりやすくするために選択をしています。

④グループ化の下の部分では、グループ化した単位ごとに新たに数量を合計する列が追加されます。そのため、新しい列名は「数量合計」、操作は「合計」、列は「数量」としましょう。
更に平均や最大などの集計列を追加する場合は、「新しい列名」の下側に「集計の追加」をクリックすることで追加が可能です。
設定できれば「OK」をクリックしましょう。

⑤ 最後に「社員ID」を選択し、「並べ替え」の昇順をクリックし完成です。

この作業で、社員ごとの集計が簡単にできあがりましたね。

同じ作業を、Excel2013などで関数を使って集計をする場合、範囲の選択間違いといったミスが起きるかもしれません。
Excel2019のPower Queryの集計機能を使うことによって、計算の範囲を間違うようなことは無くなります。

補足事項

今回は社員ごとの数量合計を出すクエリを作成しましたが、社員ごとの数量平均や、最大最少など、他の集計パターンを 参照したい場合もあるでしょう。

その場合、「売上明細結合後」を複製し、何の集計なのか わかりやすい名前を付けてからクエリを作成していきます。こうする と、様々な集計パターンを簡単に作ることが可能です。

データを追加した場合の集計の更新

先ほどは、4月から6月までの売上明細に、7月分を追加・更新の処理後、行が追加されるまでを追って見ていただきました。
ここでは、8月分の追加・更新の処理後、集計結果に反映されるパターンも見ていただきましょう。

下図の左下にあるのが、8月分（2行のデータ）です。これをフォルダ内に追加して、“更新”するだけで、集計の結果がすぐに反映されます。

翌月以降も9月分や10月分といった売上明細を作って、同じ様に保存し更新をするだけで、最新の集計結果が簡単に得られるようになります。

今回は、Power Queryの使い方のご紹介のラストとして、一連の作業における便利な機能を紹介しました。

Power Queryを使うと、フォルダに保存するだけで複数データを一度に取り込めることや、グループごとに集計を行えることは、Excelでのデータ集計作業の効率化に大いに役立つと思います。ぜひ使ってみてください。

さて、連載「Excel2013で止まっている人必見！業務効率がUPするExcel2019活用術」は、今回で最終回となります。
記事を読んでいただいた皆さま、大変ありがとうございました。

PCの入れ替えに伴いExcel2019になったが、Excel2013までの機能しか使っていない…という方に、一段と使いやすくなり業務効率化につながるExcel2019の様々な機能を知っていただけたと思います。
この連載中にご紹介した内容は、明日からの仕事で活用できるものばかりです。
ぜひ活用し、業務効率アップに役立ててください。

これだけマスター！Excel講座