私も以前に紹介したことのある、上記のブログのポイントである「ピボットの解除」などは、たしかにこれまで VBA で行うことになる難しいお題でした。これがクリックだけでできるようになるのは、「これからは Power Queryか？」とか思いますよね。
特にブックやワークシートを扱う系の処理では、これまで VBA の独壇場だったエリアに Power Query の適用が可能になっているので、ますます VBA卒業？とか言われそうです（笑）。（まぁ、卒業の前に入学してから・・・みたいな話もあるのだけど）
今回はブックにある複数のワークシートを扱う（まとめる）方法をご紹介したいと思います。
2つのケースを説明しますが、いずれもデータを含む表は「テーブル」になっているものとします。もちろん、Power Query ではテーブルになっていない「セル範囲」の表も扱うことができますが、その場合必要な列や行を指定して、データだけにするステップが追加する必要があります。
テーブルにしていれば、データだけを絞り込むステップは必要ありません。
ワークシートの数も名前もわかっている場合
考え方として、各ワークシートにあるテーブルから必要なデータを取り出すクエリを作成し、それらを「追加（Append)」します。データベースの世界や Access の世界では、この「追加」を「ユニオン(Union)」と呼びます。追加やユニオンは、基本的には同じ構造（列名を含む）のテーブルをつなげるイメージです。4つの支店や部門みたいなグループがあって、そのデータをワークシート毎に分けている、といったサンプルです。札幌、東京、大阪、福岡などのワークシートがある、という感じですね。
それぞれのワークシートに「日付」、「番号」、「数値」のデータがあって、札幌、東京、大阪、福岡からデータを抜き出す、みたいな感じですよね。（最初から、地域みたいな札幌～福岡を入れる列をいれて１つのテーブルに・・・というのはわかります。が、ここではシンプルなサンプルを使いたいので、あしからず。）
名前がわかっている（＝固定されている）ので、それぞれのワークシートから必要なデータ（もしくは全件）を条件を付けないで、全件取り込むクエリを作ります。これらから、4つのテーブルを追加（ユニオン）するクエリを作ってみます。実務では必要な「列」だけにしたり、条件を付けて行の絞り込みをすることになるでしょうね。
また、この例では、絞り込み条件なしで、各ワークシートにあるテーブルを全件読み取るクエリを「接続のみ」で作成します。元データと同じテーブルのワークシートを追加しても仕方ないですからね。さらに、どのシートから読み込んだかをわかるように新たに列を追加して、ワークシート名を追加します。
今は複数のクエリの追加(Append)は簡単になりました。以前は、2つしか追加できなかったので、2つを追加してから、その追加クエリにクエリを追加して・・・を繰り返したのですが、今は、3つ以上のクエリを追加するを選ぶと、一気に複数のクエリをAppendすることができます。
Append1というクエリが、4つのテーブルのクエリを追加＝Unionしたクエリです。
ワークシートにクエリ結果を全く展開していません。このクエリの使い方は、ピボットテーブルのデータソースにして、欲しい形でピボットテーブル レポートを作成することになります。
ここで、Excel のピボットテーブルの機能としての知識が必要になります。
ピボットテーブルの作成は「挿入」タブの「テーブル」グループにある「ピボットテーブル」から行いますが、作成のダイアログで [外部データ ソースを使用] を選び、[接続の選択] で「Append1」の接続（クエリ）を選びます。この指定で、作成するピボットテーブルの元データに作成したクエリを指定することが可能になります。
はい、これ、複数のテーブルからピボットテーブル レポートを作ることになりますが、データ モデルを使っていません。よって、データ モデルを使ったピボットテーブルの「制限」がありません。
使うかどうかは別として、「集計フィールド」と「集計アイテム」が使えます。
でも、正直なところ、今から Power Query + ピボットテーブルで集計フィールドと集計アイテムを覚える必要はないと思います。集計フィールドは Power Queryの追加の列そのものですし、Power Query とデータ モデルもしくは Power Pivot でメジャーを使うのが主流になりそうです。
このサンプルは、1回きりだと意味がありません。それぞれのテーブル データが更新される、追加されるなど、元データに変更がある場合、一度このクエリとピボットテーブルを作れば、クエリ＋ピボットテーブル更新で、最新データにすることができます。
そう考えると、このケースは「別ブック」である場合のほうが、より実務的ですよね。
次は、別ブックの場合の手順を紹介したいと思います。このとき、ワークシートの数は名前を知らなくても、取り込むことができます。となると、なんかVBAっぽいでしょ（笑).
0 コメント:
コメントを投稿