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

さて、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。