こんにちは、blueです。
上記のように横にひたすら長く並んだ表について
- 縦に並び替えれれば集計しやすいのに
- 項目ごとに列にまとめられたらいいのに
と思ったことはないでしょうか？
そんな時はPower Queryの機能を使えば可能です。
今回は縦に並べ替える方法について２通りの方法を説明します。
今回は以下の質問に対する備忘録も含め記事にしています。@ksgiksg さん、@Crescent_Excelさんありがとうございました）
Power Queryに関してどの書籍を買ったらいいの？と思われている方には以下がお勧めです。簡単なボタン操作から、関数を使った処理まで、サンプルデータを一式操作してみるだけでPower Queryが使えるようになります。購入して間違いのないお勧めの一冊です。
とりあえず標準機能だけでも使いこなせるようになりたいと思われている方には以下がお勧めです。関数などの難しい記載はほとんどなくボタン操作のみでPower Queryを扱えるようになります。
結論
今回の方法は以下の２つになります
- 商品ごとに列をマージしてピボット解除する
- ピボット解除した後項目ごとにまとめてからピボットする
今回のテーブルは以下の様に品名1,品名2,品名3・・・と一つのレコードの中で同じ項目が繰り返される形となっています。これを排除する方法をデータベース用語では第一正規化と言います。
データベースの用語に関しては以下をどうぞ
商品ごとに列をマージしてピボット解除する
この方法は商品ごとに情報をまとめてしまい、ピボット解除（縦に並び替える）する方法です。
1 クエリに接続する
2 <商品番号1,品名1,単価1,個数1・・・>、<商品番号2,品名2,単価2,個数2・・・>と１つの商品ごとにそれぞれ「列のマージ」をする（ここでは任意の区切り文字を「,(カンマ)」にしています）
3 マージした列に対して「列のピボット解除」（縦に並べる）を行う
4 先ほどの区切り文字で「列の分割」を行う
5 各列に適切な名前を付ける
結果として以下の様になります。
この方法は１商品ごとに情報をまとめることでピボット解除した際に縦に並ぶようにしているのがポイントです。情報が多いと一つ一つまとめるのが大変ですが、一番わかりやすい方法です。
ピボット解除した後項目ごとにまとめてからピボットする
この方法は一度ピボット解除した後で項目ごとをまとめて再度ピボットする（横に並べる）方法です。
1 クエリに接続する
2 日付列、名前列、総額列以外で「列のピボット解除」を行う。属性列と値列が生成される
3 属性列に対して「列の分割」にて数字以外から数字による分割を実施し、項目と数値に分ける
4 「属性.1」列に対して「列のピボット」を行う。値列は「値列」とする
結果として以下の様になります。
この方法は属性を一つの列にまとめることでピボット解除した際に列名になるようにしていることポイントです。
1,2,3と多くの繰り返しの項目があっても一度に処理ができるので非常に便利です。
今回のまとめ
今回は横に並んだ表について
- 商品ごとに列をマージしてピボット解除する
- ピボット解除した後項目ごとにまとめてからピボットする
方法について説明しました。
ピボット解除や列のピボットについては以下の書籍に詳しく記載されていますので是非読んでみてください。サンプルデータを一式操作してみるだけでPower Queryが使えるようになります。購入して間違いのないお勧めの一冊です。
とりあえず標準機能だけでも使いこなせるようになりたいと思われている方には以下がお勧めです。関数などの難しい記載はほとんどなくボタン操作のみでPower Queryを扱えるようになります。
コメント