2016/05/11

Power Query でクロス集計表・マトリックス表を表形式・テーブル形式に変換する

前回に引き続き、これも今は Power BI で簡単にできる、Excelユーザーにとっての長年の夢、というか、実務でよく直面する課題です。

クロス集計表をリスト形式・テーブル形式に変換したい、というものです。これ、Power Query だと一瞬でできるんですよ。

この変換には、Power Query の機能を使いますので、Excel 2016 であればデータタブの [取得と変換]、Excel 2013 であれば Power Query をマイクロソフトのサイトからダウンロード、Excel 2010 の Professional Plus (企業向けのライセンス)ならば Excel 2013 同様に Power Query をダウンロードし利用することで、解決できます。

以下のようなクロス集計表(マトリックス表)あります。


ここから、ある日突然「A001だけの4月の合計と、地域を数値の大きい順番で・・・」など言われるわけですよね。できないことはないんですが、関数を駆使したり、コピーしたり、と手間と時間がかかります。

もし、このデータを以下のようなリスト形式で管理していたら、ピボットテーブル操作だけで完了しますよね。


クロス集計表から、このようなリスト形式のテーブルを作成するのは手間と時間がかかる、数が多ければVBAをつかって展開していました。

Power Query (Excel 2016 では 取得と変換)を使ったテーブルへの変換はアニメーションGIFでみられるほど「一瞬です」(笑)。


手順は以下です。

1) クロス集計表をテーブルにします。テーブルにする方法はアクティブセルをクロス集計表の中にいれて Ctrl+T または L、 リボンからは同じくアクティブセルをクロス集計表にいれて、[挿入]の[テーブル]グループの[テーブル]をクリックします。

2) テーブル形式にするのは、Power Query(取得と変換)で読み込みためです。テーブル内にアクティブセルをいれて、Power Query または取得と変換の [テーブルから] をクリックします。

3) テーブルになったクロス集計表を読み込んだクエリ エディターが立ち上がります。地域の列で null になっているセルがあります。


この null へ入力するデータはその上にあるデータのコピーになります。
このような場合、[変換]タブの[任意の列]グループの[フィル]の[下]を使います。


4) 4月から9月までの列を選択します。4月の列選択は [4月] の見出しをクリックします。Shift キーを押しながら [9月] の見出しをクリックすると、4月から9月までの列を選択することができます。

5) 選択した状態で、[変換]タブの[任意の列]グループの[列のピボット解除]をクリックします。これでクロス集計表がリスト形式のテーブルに展開されます。



6) 展開された列名を「月」に変更して、[閉じて読み込む]でワークシート上にテーブルを作成します。

この手順が上のアニメーションGIFになります。

なお、このテーブルの作成に使ったクエリを削除すると、元のクロス集計表とリンクしていないテーブルになります。クロス集計表の更新はあまりお勧めできませんが、クエリを保持しておけば、クロス集計表の数値が変わっても、[更新] でクエリ結果のテーブルを更新することもできます。

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