Power Query で Excel マクロから卒業？！　- 列のピボットとピボット解除 -

Article
08/01/2016

Microsoft Japan Data Platform Tech Sales Team　伊藤

Power Query をご存知ですか？Excel 2010/2013 のアドインとして提供していた機能ですが、Excel 2016 では [データ] メニューの [取得と変換] として標準機能となりました。

Excel だとエディションだのバージョンだのが引っかかるという場合には、Power BI Desktop にも同じ機能があり [ホーム] メニューの [データを取得] あるいは [クエリを編集] というボタンから使用できます。

Power BI Desktop や Excel 2016、Power Query アドインのクエリエディタ (Query Editor) を使用すると、データの取り込みと変換を GUI で定義でき、その後繰り返し必要となるであろうデータ取り込みを 1 クリックで行えます。あまりに便利で、手作業でデータをコピペしたりマクロを駆使したりしてレポートを作りこんでいたのは何だったのか…という気分になります。今回はデータ加工に苦労されている方にぜひお試しいただきたい機能の一つである [列のピボット解除] と [列のピボット] について、Power BI Desktop (2016年6月バージョン) を使ってご紹介します。

例えばこのようなデータ (1行目は列名) の場合 (データソース：Wikipedia 「都道府県の人口一覧」)

「マトリックス」で都道府県別に年別の人口を表示するべく [行] に「都道府県」を、[値] に「2010年」「2005年」…を配置すると、次のように表示されます。

行と列 (縦横) を入れ替えるために [列] に「都道府県」を配置すると次のようになり、意図した表示になりません (「2010年」とか、行見出しとして表示してほしい)。

このようなデータで行と列の入れ替えを実現するには、[クエリを編集] して「2010年」「2005年」…という各列を 「月」列と「人口」列に変換 する必要があります。ここで活躍するのが [列のピボット解除] です。

目的の表現 (行と列を入れ替え)

[列のピボット解除] の使い方

[ホーム] メニューの [クエリを編集] をクリック
分解したい列 (ここでは各年) を選択し (Ctrl キーを押しながらクリックすることで複数選択が可能) 、[変換] メニューから [列のピボット解除] をクリック
※ そのまま残す列 (ここでは「都道府県」) を選択した状態で [その他の列のピボット解除] とすることも可能です。
「属性」と「値」という2つの列に変換されます。それぞれの列を適当な名前に変更します。(属性→月、値→人口)
[ホーム] メニューの [閉じて適用] をクリックし、 Query Editor を閉じます
[視覚化] から「マトリックス」を選択し、[行] に「年」、[列] に「都道府県」、[値] に「人口」を配置します

以上で目的の表現に変更できました。

[列のピボット解除] については、こちらの YouTube 動画で Excel 2013 の Power Query での使い方を紹介しています。

ここまでは「列のピボット解除」を行いましたが、逆に列を分解したい場合もあると思います。同じデータを使用して、今度は「都道府県」列の値、つまり都道府県名を列名とする列を作ります。

[列のピボット] の使い方

[クエリを編集] をクリック
分解したい列 (ここでは「都道府県」列) を選択し、[変換] メニューの [列のピボット] をクリック
以下のようなダイアログが表示されるので、[値列] に値が格納されている列 (ここでは「人口」列) を選択し、[詳細設定オプション] を開いて集計方法を設定します
以下のように変換されたことが確認できます
最後に [ホーム] メニューの [閉じて適用] をお忘れなく