クロス集計された表を元データにして、さらにピボットテーブルで集計したいですがうまくいかない時があります。
こうした場合、良い方法ありますか?
その場合、一度表をテーブル形式に直してあげると良いです。
具体的には、Power Queryの「列のピボット解除」というコマンドが有効ですよ!
具体的な使い方について解説していきますね。
解説動画:【パワークエリ#8】レイアウト変更テクニック3選 – 使いにくい表レイアウトを使いやすく整形・加工する作業を自動化する(列のピボット解除、列のピボット、入れ替え)
この記事の内容は下記の動画でも解説しています。
コメント欄の「列のピボット解除」の時間の部分をクリックすると該当の解説へジャンプできますよ!
はじめに
本題に入る前に、この記事がおすすめな方を挙げてみます。
- 元データの表レイアウトをクロス集計表→テーブル形式へ変更したい方
- データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
- Power Query(パワークエリ)の使い方を習得したい方
前提条件
この記事のテクニックを使うためには、以下の条件を満たす必要があります。
- ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
- Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
- Power Query(パワークエリ)の概要を理解していること
→まず、以下の記事で概要を把握することをおすすめします。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでブック内、あるいは別ファイルのデ …
「列のピボット解除」は表レイアウトをクロス集計表→テーブル形式に変更するのに有効
「列のピボット解除」は聞きなれないと思いますが、次のように表レイアウトを変更することが可能な機能です。
これを活用すると、元がクロス集計表のように横軸に展開されていたデータを縦方向にまとめることが可能です。
つまり、表がテーブル形式(列ごとにデータが整理された状態)になり、ピボットテーブルで集計しやすい状態にできます。
従来のワークシートでは、こうした表レイアウトの変更を自動化する手段はVBAしかありませんでした。
しかし、Power Query(パワークエリ)なら、VBAを使わずにこうした表レイアウトの変更も自動化できます。
しかも、他のデータの整形・加工作業が複数工程あるなら、なおさら便利ですよ!
では、Power Query(パワークエリ)での「列のピボット解除」の手順を確認していきましょう。
Power Query(パワークエリ)での「列のピボット解除」の手順
今回の前提として、「列のピボット解除」を行う対象テーブルはブック内にあり、事前にPower Queryエディターに取り込んでいる状態からスタートするとします。(ブック内のデータ取得について復習したい方は下記記事を参照)
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 同じブック内の表データ(テーブルまたはセル範 …
まず、ピボット解除対象の列を選択(①)します。
これは、通常のワークシート上の操作と同じ感覚で問題ありません。
続いて、「変換」タブをクリック(②)し、「列のピボット解除」をクリック(③)すれば完了です。
列の解除後、デフォルトでは「属性」と「値」という列名になるため、必要に応じて列名を修正しましょう。
手順①で複数列を選択する際、「Shift」キーを押しながら矢印キー(「→」or「←」キー)で選択範囲を広げることが可能です。
もしくは、起点となる列を選択後、「Shift」キーを押しながら終点の列見出しをクリックすると、起点から終点の範囲の全列を選択状態にできます。
基本は最初の方法が操作速度は速いですが、操作時の手の状態に応じて使い分けてください。
【参考】選択した列以外のピボット解除も可能!
ピボット解除の対象となる列が多い場合は、逆に対象外の列を選択し、選択していない列をピボット解除することも可能です。
この場合、「その他の列のピボット解除」というコマンドを使いましょう。
ケースによって「列のピボット解除」と「その他の列のピボット解除」を使い分けると良いですね。
「プレビュー」ウィンドウ上で「列のピボット解除」が確認できたらOK!
「プレビュー」ウィンドウを見ると、無事設定どおりに選択した列が縦方向にまとめられていますね!
あとは、その他の処理を行い、任意の方法でデータを出力すれば完了です。
【参考】「列のピボット解除」の内容を変更したい場合は
もし、あとで「列のピボット解除」の条件を変更したい場合は、「クエリの設定」ウィンドウの任意のステップを一旦「×」で削除し、改めて「列のピボット解除」を行えばOKです。
サンプルファイルで練習しよう!
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_PowerQueryデータ整形_列のピボット解除.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)
ファイルを開いたら、「列のピボット解除」を行う前に、Power Queryエディターを起動させてくださいね。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリックしてください。
- リボン「データ」タブをクリック
- 「クエリと接続」をクリック
- 「月別残業時間(ピボット解除)」クエリをダブルクリック
ここまで準備ができたら、次の手順を実施してください。(今までの解説のまとめです)
- ピボット解除対象の列を選択
- 「変換」タブをクリック
- 「列のピボット解除」をクリック
本記事の解説と同じ結果になればOKです!
さいごに
いかがでしたでしょうか?
「列のピボット解除」はPower Query(パワークエリ)ならではの機能です。
集計の元データが表レイアウト的に扱いにくい場合、ピボットテーブルでも集計できる表レイアウトへ自動的に変更できて、めちゃくちゃ便利ですよ!
なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
「列のピボット解除」はワークシートにはない機能なので、最初はイメージしにくいかもしれませんが、慣れるとめちゃくちゃ便利ですよ!
この機能があるだけでも、Power Query(パワークエリ)を学ぶメリットはあると言っても過言ではありません。
表レイアウトを変更したい方は、ぜひPower Query(パワークエリ)にチャレンジしてみてくださいね。