Power Queryのピボット解除は最強です。さよなら、マクロ。
2023-02-16
azblob://2023/02/12/eyecatch/2023-02-17-power-query-unpivot-the-most-powerful-goodbye-macro-000.jpg

こんにちは、あおいです。

さて、Power Queryとは、Excel2016以降から標準になった機能のひとつであり、主にデータの取り込み、データの整形・加工(データ変換、列の追加、並び替え等)といった一連の定型作業を自動化することができます。従来は、この一連の作業の自動化をするには、マクロを習得するしかありませんでした。



Power Queryにはさまざまな便利機能が備えられていますが、その中でもPower Queryの「ピボット解除」が最強です。

ピボット解除のメリットは、横方向に並んでいるデータを縦方向に配置することで、データ集計・分析、可視化に適した状態に変換できます。



前回の記事では、Power Queryのピボット解除で横方向に並んでいるデータのExcel表を縦方向に配置する方法を紹介しました。
Power Queryの「ピボット解除」でマトリクス形式のExcel表をリスト形式に変換する方法 | cloud.config Tech Blog



前回はシンプルな表でしたが、以下のような複雑な表の場合はどうでしょうか。




もちろん、以下のようにリスト形式のデータテーブルに変換する必要があります。




でも、なんかめっちゃ難しそうですよね……


ご安心ください。こちらも「ピボット解除」を使えば、わずか数クリックのマウス操作で、リスト形式のデータテーブルに変換できます。

手動でポチポチ地獄のコピペ作業は不要です。

マクロで小難しいプログラムを組む必要はないです。



そこで、今回はPower Queryの「ピボット解除」を習得して、マクロに別れを告げましょう。
今回は応用編なので、基礎編となる前回の記事を先にご覧になってから、こちらの応用編に挑戦されることをおすすめします。と言ってもそんな難しくないです。

[データを取得]から対象のExcelを選択して、[データの変換]をクリックします。




しばらくすると、Power Queryが起動します。

画面右側の適用したステップに「変更された型」と「昇格されたヘッダー」が表示されていますが、この時点では不要なので両方削除します。




Column1の列を選択した状態で、[フィル]を選択して空白セルに同じ値を埋め込みます。




Column1とColumn2の列を[列のマージ]で結合します。区切り記号は値として絶対入ってこないものを指定します。なぜ、結合する必要があるのかは後ほど解説します。




横方向に並んでいる年度行の空白セルも、同様に[フィル]する必要があります。

しかし、横方向への[フィル]はできないので、[入れ替え]を選択して行と列を入れ替えます。




行と列が入れ替わることで、年度列に対して[フィル]が適用できるようになります。




また、[入れ替え]を適用したことで、テーブルの1行目に結合されたエリアと支店名が並ぶので、[1行目をヘッダーとして使用]を適用します。




これでピボット解除が実行できる状態になりました。
[列のマージ]で結合した理由は、ピボット解除ができるように、列項目を1セルで作りたかったからです。[列のマージ]で列項目を結合しないと、列項目にnullが入り込んでしまいます。



それでは[その他の列のピボット解除]を実行します。[その他の列のピボット解除]を選択する理由は前回の記事で解説しています。


 



最後に、[列の分割]を選択してエリアと支店名を分割して、合計という不要な値をフィルター、列名とデータ型を適切なものに変更すれば完了です。




今回はPower Queryのピボット解除の応用編を紹介させていただきました。
Power Queryのピボット解除は最強です。これだけでも覚える価値はあります。
さよなら、マクロ。ようこそ、Power Query。