表で縦方向にまとめられたデータを表の横軸へ自動的にレイアウト変更する方法(列のピボット)[Power Query(パワークエリ)基礎]
AさんAさん

残業管理をしている表で、残業時間の「予実」が縦方向にまとまっているんですが、これを「予定」と「実績」で横に並べたいですが、何か良い方法ありますか?

森田森田

その場合、Power Query「列のピボット」というコマンドが有効ですよ!

具体的な使い方について解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • 元データの表レイアウトの一部分を縦軸→横軸へ変更したい方
  • データ整形・加工の工程が複数あり、かつその作業が定期的に発生する方
  • Power Query(パワークエリ)の使い方を習得したい方

前提条件

この記事のテクニックを使うためには、以下の条件を満たす必要があります。

  • ExcelのバージョンはExcel2010以降(Microsoft365含む)が必須
  • Excel2010/2013ユーザーの場合、事前にMicrosoft社公式HPよりPower Query(パワークエリ)のダウンロードが必要(Excel2016以降は標準機能)
  • Power Query(パワークエリ)の概要を理解していること
    →まず、以下の記事で概要を把握することをおすすめします。

「列のピボット」は表レイアウトの一部を縦軸→横軸へ変更するのに有効

「列のピボット」は文章だけの説明ではイメージしきれないかもしれませんが、次のように表レイアウトを変更することが可能な機能です。

これを活用すると、表の縦軸でまとめられたデータを横軸(複数列)に展開することが可能です。

列を横に並べて、差異や比率を計算したい場合に便利な機能ですね。

従来のワークシートでは、ピボットテーブルで別表を作成すれば同じようなことはできますが、自動化まではできません。

こうした表レイアウトの変更を自動化したいなら、今まではVBAしかありませんでした。

しかし、Power Query(パワークエリ)なら、VBAを使わずにこうした表レイアウトの変更も自動化できます。

しかも、他のデータの整形・加工作業が複数工程あるなら、なおさら便利ですよ!

では、Power Query(パワークエリ)での「列のピボット」の手順を確認していきましょう。

Power Query(パワークエリ)での「列のピボット」の手順

今回の前提として、「列のピボット」を行う対象テーブルはブック内にあり、事前にPower Queryエディターに取り込んでいる状態からスタートするとします。(ブック内のデータ取得について復習したい方は下記記事を参照)

「列のピボット」は以下の2ステップとなります。

STEP1】リボン「変換」タブから「列のピボット」をクリック

まず、新たな横軸の列名と値にしたい列を選択(①)します。

これは、通常のワークシート上の操作と同じ感覚で問題ありません。

続いて、「変換」タブをクリック(②)し、「列のピボット」をクリック(③)してください。

手順①で複数列を選択する際のポイント

手順①で複数列を選択する際、Shift」キーを押しながら矢印キー(「→」or「←」キー)で選択範囲を広げることが可能です。

もしくは、起点となる列を選択後、「Shift」キーを押しながら終点の列見出しをクリックすると、起点から終点の範囲の全列を選択状態にできます。

基本は最初の方法が操作速度は速いですが、操作時の手の状態に応じて使い分けてください。

STEP2】「列のピボット」ダイアログ

「列のピボット」ダイアログが起動します。

まず、新たな横軸の列名と値にしたい内容となっているか確認(④)し、問題なければOK」をクリック(⑤)して完了です。

「プレビュー」ウィンドウ上で「列のピボット」が確認できたらOK

「プレビュー」ウィンドウを見ると、無事設定どおりに選択した列が横軸として展開されていますね!

あとは、その他の処理を行い、任意の方法でデータを出力すれば完了です。

【参考】「列のピボット」の内容を変更したい場合は

もし、あとで「列のピボット」の条件を変更したい場合は、「クエリの設定」ウィンドウの任意のステップを一旦「×」で削除し、改めて「列のピボット」を行えばOKです。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_PowerQueryデータ整形_列のピボット.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、「列のピボット」を行う前に、Power Queryエディターを起動させてくださいね。
ブック起動時、「セキュリティの警告」メッセージが表示された場合は「コンテンツの有効化」をクリックしてください。

  1. リボン「データ」タブをクリック
  2. 「クエリと接続」をクリック
  3. 「残業予実管理(列のピボット)」クエリをダブルクリック

ここまで準備ができたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 新たな横軸の列名と値にしたい列を選択
  2. 「変換」タブをクリック
  3. 「列のピボット」をクリック
  4. 新たな横軸の列名と値にしたい内容となっているか確認
  5. OK」をクリック

本記事の解説と同じ結果になればOKです!

動画の解説もどうぞ!

上記の内容を動画でも解説しています。

文字だけではわかりにくかった方は動画を参考にしてくださいね。

コメント欄にタイムコードを用意しました。
時間の部分をクリックすることで「列のピボット」の解説へジャンプできますよ!

もし、参考になった方はチャンネル登録ならびに高評価をよろしくお願いいたします!

さいごに

いかがでしたでしょうか?

「列のピボット」はPower Query(パワークエリ)ならではの機能です。

集計の元データが表レイアウト的に扱いにくい場合でも、レイアウト変更を自動化できるのが便利ですね。

別記事の「列のピボット解除」とセットで覚えておくことをおすすめします。

なお、Power Query(パワークエリ)の各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

「列のピボット」はピボットテーブルみたいなことを自動化できるので、覚えておくと便利です。

表レイアウトの自動化が実務で必要な方は、ぜひPower Query(パワークエリ)にチャレンジしてみてくださいね。