Power Queryでテーブルをピボット化(クロス集計)する方法|列のピボット解除についても解説

Power Query_ピボット

Power Query(パワークエリ)の[列のピボット]機能は、データの羅列(縦方向)をクロス集計(横方向)に変更したいときに便利です。この記事ではPower Queryエディターを使って、簡単にピボットテーブルのようなマトリクス集計を実現する方法を解説します。加えて、後半パートでは列のピボットを解除する方法も紹介します。

こんな方におすすめ
  • テーブル形式の表からクロス集計表に加工したい
  • クロス集計表からテーブル形式にレイアウトを戻したい
  • [列のピボット][列のピボット解除]の機能を知りたい
目次

Power Queryでテーブル形式のデータをクロス集計する方法(列のピボット)

今回は縦方向にまとまっているコーヒー豆の価格表(テーブル)を、「種類」と「量」を掛け合わせたクロス集計表に変更します。サンプルファイルもダウンロードできます。

1.Power Queryエディターを起動する

最初にテーブル形式の表をPower Queryエディターに取り込みます。

操作手順

練習ファイルの[列のピボット]シートを開いておく
手順①[データ]タブをクリック
手順②[テーブルまたは範囲から]をクリック
手順③データ範囲を指定して[OK]をクリック

2.テーブルをピボット化する

ピボット化をするには、どの列を選択するかが肝です。今回は「量」を列の見出しにしたいので、「量」列を基準として設定します。値列には「価格」を設定しましょう。

操作手順

手順①「量」列をクリックして選択
手順②[変換]タブ→[列のピボット]をクリック
[列のピボット]画面が表示される
手順③値列に「価格(円)」を選択
手順④[詳細設定オプション]をクリック
手順⑤値の集計関数に[集計しない]を選択
今回は数値だけ表示したいので[集計しない]を選択
手順⑥[OK]をクリック
「量」と「価格」のクロス集計量が作成できた

完成系のクロス集計表はこちらです。

【POINT】値がない場合は「null」が表示される

Null(ヌル)は、プログラミング言語で「何もない」という意味です。今回の事例の場合、ブルーマウンテンには200gがないので「null」と表示されています。
参考記事:PowerQueryにテーブルを読み込めない|エラーを防ぐ正しいデータソースの作り方を紹介!

【POINT】思い通りの表ができなかった場合…

列や値の設定を間違ってしまい思い通りの集計表にならなかった場合は、画面右の[適用したステップ]からステップを削除しましょう。[ピボットされた列]の[×]をクリックすると、ピボット化する前の表に戻ります。

3.データを出力する

ピボット化できたらExcelのワークシートに取り込みましょう。今回は元データと同じシートに出力していますが、使いやすい場所に出力してOKです。

操作手順

手順①[ホーム]タブ→[閉じて次に読み込む]
手順②[テーブル]を選択
手順③[既存のワークシート]を選択してセルE1を選択
手順④[OK]をクリック
Excelのシートに出力された

Power Queryでクロス集計をテーブル形式にする方法(列のピボット解除)

ここまで、Power Queryの[列のピボット]を利用して、テーブルをクロス集計表に変更しました。今度は逆にクロス集計表に対して[列のピボット解除]を実行すると、クロス集計からテーブルにレイアウト変更できます。その方法も紹介しましょう。

操作手順

練習ファイルの[ピボット解除]シートを開いて、Power Queryエディターを起動しておく
手順①「種類」列を選択
手順②[変換]タブをクリック
手順③[列のピボット解除]の[▼]→[その他の列のピボット解除]をクリック
列のピボットが解除されて、テーブルに変換された

【POINT】 [その他の列のピボット解除]を選択する理由

ピボット解除は「200g」「500g」「1000g」の列を選択して、手順③で[列のピボット解除]をクリックしても解除できます。しかし今後「1500g」という項目が追加されるかもしれません。その際、「1500g」の列のピボットを解除する手間が発生します。データが追加されること見越して[その他の列のピボット解除]をクリックしておきましょう。

【まとめ】ピボット化で思い通りのレイアウト

Power Queryで列のピボット(またはピボット解除)を行う際は、

  • ピボット化(ピボット解除)したい列を選択
  • [変換]タブ>[列のピボット]([列のピボット解除])をクリック

することがポイントでした。Power Queryのピボット化をマスターしておくとレイアウト変更が簡単です。

「パワークエリのようなExcel最新機能を学びたい」
「日々の業務を効率化できるExcel技を知りたい」

そんな方にはユースフルの【無料体験版】ExcelPro講座がオススメです!元伊藤忠商事経理でExcel実務のプロ、長内孝平(おさ)のExcel技を無料で8時間分も学習できるオンライン動画講座です。

気に入ったらシェア!
  • URLをコピーしました!
目次