【パワークエリ】「列のピボット」の使い方｜1列の数値データを複数列に変換する方法【動画あり】

Aさん

残業管理をしている表で、残業時間の「予実」の数値が1列にまとまっています。
これを「予定」と「実績」の2列にしたいですが、何か良い方法ありますか？

森田

その場合、Power Queryエディター上の「列のピボット」コマンドを使えば良いですよ！
では、詳細を解説していきますね。

目次[閉じる]

解説動画

コメント欄の目次の時間部分をクリックすると該当の解説へジャンプできますよ！

この記事はパワークエリの概要を把握していることが前提です。

パワークエリの概要は以下の記事をご参照ください。

【Excel】パワークエリ（Power Query）とは？できることや使い方入門【動画あり】

Excelで集計前のデータの収集や整形に苦労している方は、パワークエリ（Power Query）を活用することで、マウス操作を中心に一連の作業を自動化できます。この記事では、Excel本著者が初心者向けにパワークエリの概要や基本の使い方を図解・動画付きで解説します。

「列のピボット」コマンドは、クエリの新規作成ステップのうち、Step 2の「取得データを整形/加工（Transform）」に該当する機能です。

クエリ内の一連の整形作業の中で、1列の数値データを複数列に変換したい場合に「列のピボット」コマンドを使いましょう。

「列のピボット」コマンドを活用することで、Power Queryエディター上で属性列のデータ（アイテム）を列名にし、1列の数値データを複数列に変換できます。

一例として、「列のピボット」コマンドを使用し、「予実」列を列名にし、「残業時間」列を2列に変換したものが以下です。
「予実」列が属性列、「残業時間」列が値列。

この通り、Power Queryエディター上で列をピボットしたステップを記録できました。
「列のピボット」コマンドの場合、デフォルトのステップ名は「ピボットされた列」になる（クエリ内で2つ目以降の「ピボットされた列」ステップは連番が付加）。

後は、クエリ新規作成ステップのStep 2とStep 3を行いましょう。

これにより、クエリに記録した「列のピボット」コマンドのステップを自動化できます。
「列のピボット」ステップを記録後、対象の列名を変更すると、クエリ更新時にエラーになるため注意（対処法は後述）。

クエリの新規作成ステップのStep 2に役立つ各種コマンドや、Step 3の手順の詳細を知りたい方は、パワークエリの概要記事をご参照ください。
【Excel】パワークエリ（Power Query）とは？できることや使い方入門【動画あり】

「列のピボット」コマンドは、以下の手順で使用します。

手順①は、連続する列は「Shift」キーを押しながら「←」・「→」キー、離れた列は「Ctrl」キーを押しながら別の列名をクリックで複数列を選択すると効率的。
手順①は、属性列（変換後の列名）→値列の順番に選択すること。

「列のピボット」ダイアログに用意されている「詳細設定オプション」をクリックすると、「値の集計関数」ボックスで任意の集計関数を設定することが可能です。

今回は属性列に「月」列、値列に「売上」列を指定し、月ごとの売上を複数列に変換するケースを例に、各集計関数を解説します。

集計関数を「カウント（すべて）」にした場合、変換後のレコードと属性列のアイテムの組み合わせごとに、空白（null）を含めたデータの個数がカウントされます。

使用手順①で選択した値列のデータ型が数値関連以外の場合、「カウント（すべて）」がデフォルトの集計方法になります。

それ以外では、レイアウト変更というよりも集計機能になるため、実務での利用機会は少ないです。
パワークエリで集計するより、処理結果のテーブルを使い、ピボットテーブル等で集計した方が良いため。

集計関数を「カウント（空白なし）」にした場合、変換後のレコードと属性列のアイテムの組み合わせごとに、空白（null）を除いたデータの個数がカウントされます。

こちらも集計機能になるため、実務での利用機会は少ないと思います。