2016/08/06

[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる

私もよく参考にする日本マイクロソフトの「Data Platform Tech Sales Team Blog」ですが、最近の投稿で「Excelマクロから卒業?」というタイトルのブログがありました。

私も以前に紹介したことのある、上記のブログのポイントである「ピボットの解除」などは、たしかにこれまで VBA で行うことになる難しいお題でした。これがクリックだけでできるようになるのは、「これからは Power Queryか?」とか思いますよね。

特にブックやワークシートを扱う系の処理では、これまで VBA の独壇場だったエリアに Power Query の適用が可能になっているので、ますます VBA卒業?とか言われそうです(笑)。(まぁ、卒業の前に入学してから・・・みたいな話もあるのだけど)

今回はブックにある複数のワークシートを扱う(まとめる)方法をご紹介したいと思います。
2つのケースを説明しますが、いずれもデータを含む表は「テーブル」になっているものとします。もちろん、Power Query ではテーブルになっていない「セル範囲」の表も扱うことができますが、その場合必要な列や行を指定して、データだけにするステップが追加する必要があります。

テーブルにしていれば、データだけを絞り込むステップは必要ありません。

ワークシートの数も名前もわかっている場合

考え方として、各ワークシートにあるテーブルから必要なデータを取り出すクエリを作成し、それらを「追加(Append)」します。データベースの世界や Access の世界では、この「追加」を「ユニオン(Union)」と呼びます。追加やユニオンは、基本的には同じ構造(列名を含む)のテーブルをつなげるイメージです。

4つの支店や部門みたいなグループがあって、そのデータをワークシート毎に分けている、といったサンプルです。札幌、東京、大阪、福岡などのワークシートがある、という感じですね。


それぞれのワークシートに「日付」、「番号」、「数値」のデータがあって、札幌、東京、大阪、福岡からデータを抜き出す、みたいな感じですよね。(最初から、地域みたいな札幌~福岡を入れる列をいれて1つのテーブルに・・・というのはわかります。が、ここではシンプルなサンプルを使いたいので、あしからず。)

名前がわかっている(=固定されている)ので、それぞれのワークシートから必要なデータ(もしくは全件)を条件を付けないで、全件取り込むクエリを作ります。これらから、4つのテーブルを追加(ユニオン)するクエリを作ってみます。実務では必要な「列」だけにしたり、条件を付けて行の絞り込みをすることになるでしょうね。

また、この例では、絞り込み条件なしで、各ワークシートにあるテーブルを全件読み取るクエリを「接続のみ」で作成します。元データと同じテーブルのワークシートを追加しても仕方ないですからね。さらに、どのシートから読み込んだかをわかるように新たに列を追加して、ワークシート名を追加します。


今は複数のクエリの追加(Append)は簡単になりました。以前は、2つしか追加できなかったので、2つを追加してから、その追加クエリにクエリを追加して・・・を繰り返したのですが、今は、3つ以上のクエリを追加するを選ぶと、一気に複数のクエリをAppendすることができます。


Append1というクエリが、4つのテーブルのクエリを追加=Unionしたクエリです。
ワークシートにクエリ結果を全く展開していません。このクエリの使い方は、ピボットテーブルのデータソースにして、欲しい形でピボットテーブル レポートを作成することになります。

ここで、Excel のピボットテーブルの機能としての知識が必要になります。

ピボットテーブルの作成は「挿入」タブの「テーブル」グループにある「ピボットテーブル」から行いますが、作成のダイアログで [外部データ ソースを使用] を選び、[接続の選択] で「Append1」の接続(クエリ)を選びます。この指定で、作成するピボットテーブルの元データに作成したクエリを指定することが可能になります。


はい、これ、複数のテーブルからピボットテーブル レポートを作ることになりますが、データ モデルを使っていません。よって、データ モデルを使ったピボットテーブルの「制限」がありません。
使うかどうかは別として、「集計フィールド」と「集計アイテム」が使えます。


でも、正直なところ、今から Power Query + ピボットテーブルで集計フィールドと集計アイテムを覚える必要はないと思います。集計フィールドは Power Queryの追加の列そのものですし、Power Query とデータ モデルもしくは Power Pivot でメジャーを使うのが主流になりそうです。

このサンプルは、1回きりだと意味がありません。それぞれのテーブル データが更新される、追加されるなど、元データに変更がある場合、一度このクエリとピボットテーブルを作れば、クエリ+ピボットテーブル更新で、最新データにすることができます。

そう考えると、このケースは「別ブック」である場合のほうが、より実務的ですよね。

次は、別ブックの場合の手順を紹介したいと思います。このとき、ワークシートの数は名前を知らなくても、取り込むことができます。となると、なんかVBAっぽいでしょ(笑).

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018