2016/08/09

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

前の投稿で、複数のワークシートをまとめる、というお題を取り上げましたが、これ、「今、Excel で開いているブックにある、複数のワークシートにあるテーブルをまとめる」という処理でした。

Excel でデータを扱うのなら、Excel でブックを開くのが鉄則

ただ、この手の処理は、同じブックで処理するよりも、元データと、加工・処理するブックは「違う」パターンが多いです。元データのブックは、別の部門だったり担当者が準備したり、更新したりしていて、そこから加工するのは自分、みたいな感じです。

Excel の処理として、扱うことのできるブックは「Excelで開いている」ことが鉄則です。

そのため、基本的には Excel でデータを扱いたいのであれば、まずは Excel でブックを開く、という処理が「必須」でした。 Excel で一度開けば、Excel という Application を頂点とした、「ブック - シート - セル」という階層構造で処理することができるからです。

VBAでも同じです。ただし、裏技的にブックを開かずにデータを取り出すことが技術的には可能でしたが、広くお勧めできるものではありませんでした。

[VBA] ブックを開かないで読む (Office TANAKA)
http://officetanaka.net/excel/vba/tips/tips28.htm

Power Query でブックを開かずにデータを参照する

という前置きは置いておいて、Excel でブックを開かずに、別ブックから、そこに含まれている各ワークシートのテーブルを一気に取り込んでしまう処理は、アニメーションGIFで一気に紹介できるくらい「簡単」なんです。
 前回は、既に開いているブックのシートにあるテーブルから、それぞれクエリを作成しましたが、今回は、ブックをExcelで開くことなく、指定するだけで、そこにあるテーブルを「テーブル」という条件で絞り込み、リスト化して、一気に展開する、という流れです。


(1) ブックを選んで、テーブル選ばす

[新しいクエリ]  - [ファイルから] - [ブックから] で、Excel ブックを指定できます。このとき、ブックに複数のワークシートがあれば、ナビゲーターウィンドウにそれぞれが表示されます。テーブルも同様です。


通常はここでワークシートやテーブルを選ぶ、または、[複数のアイテムの選択] オプションをチェックして、複数のワークシートやテーブルを選びます。

複数のワークシートやテーブルを選んだ場合は、その数ぶんの「クエリ」を自動的に作成します。それぞれのクエリが作成された後の、たとえば追加(ユニオン)の処理は、前の回で紹介した手順と同じです。


このテーブルやワークシートのオブジェクトを「選択する」条件を含めてクエリ化したいのです。
もう一度、ナビゲーターウィンドウをみると「フォルダー」のようなアイコンがあります。実は、これは Excel のブックそのものを表しているんです。


このフォルダの形をした「ブックアイコン」を選択して、[編集] ボタンを押すと、ブック内のオブジェクトとその属性がテーブル形式で編集可能になります。


ここで、オートフィルターの要領で、必要なオブジェクトの絞り込みが可能になります。

アニメーションGIFでは種類(Kind)でTableを指定して、テーブルオブジェクトだけに絞り込んでいます。

もちろん、名前で「~から始まる」や「~を含む}で絞り込みをかけることも可能です。

そして、このブックだけしか選ばない方法の最大のポイントは、作成するクエリはたった1つしかない、ということです。よって、テーブルが何個あろうが、クエリは1つであり、以下で紹介するように、複数のテーブルを追加するのは、たった1回ボタンを押すだけでいいのです。

(2) 追加(Append = Union)は「同じ形式」であることだけ気を付け、展開する

絞り込んだ後、テーブルの中のデータを取りだす(展開する)のは、ボタン1つを押すだけでOKです。

ただし、展開する前に不要な列を削除します。不要な列を削除、というよりは、「必要な列だけを残す」という作業が本質になります。

アニメーションGIFでは、展開する [Data] 列と、元のデータがどこかわかる [Name] 列を残して、他の列を削除しました。

[Data] 列の先頭行にある展開・集計するボタンで、展開します。




(3) 別のシートが増えても対応可能なクエリ

このクエリはクエリ内の適用済みステップで、ワークシートやテーブルを明示的に指定していません。 「指定したブックにある、テーブルオブジェクトをすべて集めて展開する」というクエリになっているからです。

元データのブックが更新されたら、加工用に用意したクエリを更新することで、最新のデータを取り込みます。もちろん、名前の知らない新しいワークシートの新しいテーブルが追加されても、クエリの編集なしにデータを取り込むことができます。

(4) 列順序は関係なく、列名が重要

テーブルオブジェクト(ListObject)は「構造化参照」されますが、このクエリにおいても構造化参照されています。列名を使って、それぞれのテーブルの列の追加を行っているのです。
そのため、列の順序、順番は関係なく、同じ「列名」を持つオブジェクトが「追加」されます。

[日付] [番号] [数値] という列順序のテーブルと、[数値] [日付] [番号] というテーブルは、問題なく追加可能だ、ということになります。

業務のほとんどは、まとめるべきワークシートの名前が決まっていることが多いですが、日付や期間、時期のようなワークシート名だと、ブックを開くたびにワークシートの数と、新たなワークシートが追加されている、というケースも少なくありません。
VBAを使えば解決していましたが、逆にいえば VBA を使わなくても、Power Query / 取得と変換の機能でここまでできることは、知っていて損はないと思います。

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。