【Excel】Power Queryの「列のピボット」で縦持ちを横持ちに変換!Excelのクロス集計準備術

Excelで縦持ちデータを横持ちデータに変換したい場合、Power Queryの「列のピボット」機能が役立ちます。この機能を使うと、集計しやすい形式のクロス集計表を作成する準備ができます。この記事では、Power Queryの「列のピボット」機能を使った縦持ちデータの横持ち変換方法を解説します。

縦持ちデータを横持ちデータに変換する基本的な手順と、その際に注意すべき点を理解することで、より効率的にデータを整形できるようになります。

Power Queryの「列のピボット」機能の概要

Power Queryの「列のピボット」機能は、縦持ち形式のデータを横持ち形式に変換するための強力なツールです。具体的には、ある列のユニークな値を行ヘッダーとして抽出し、別の列の値をその行ヘッダーに対応する列の値として配置します。この変換により、クロス集計表のような形式でデータを再構成することが可能になります。特に、集計や分析を行う際に、縦持ちデータよりも横持ちデータの方が扱いやすい場合に有効です。

縦持ちデータを横持ちデータに変換する手順

Power Queryを使用して縦持ちデータを横持ちデータに変換する手順を解説します。ここでは、例として「日付」「商品名」「売上」という列を持つ縦持ちデータを、「日付」を行、「商品名」を列、「売上」を値とする横持ちデータに変換することを想定します。

1. ExcelでデータをPower Queryに読み込む
   変換したい縦持ちデータが入力されているExcelシートを開きます。データ範囲全体を選択し、「データ」タブをクリックします。次に、「データの取得と変換」グループにある「テーブルまたは範囲から」をクリックします。これにより、Power Queryエディターが起動し、データが読み込まれます。
2. ピボットする列を特定する
   Power Queryエディターでデータが表示されたら、横持ちにしたい項目が含まれる列を特定します。この例では、「商品名」列が横持ちの列ヘッダーになります。
3. 「列のピボット」機能を選択する
   横持ちにしたい列(この例では「商品名」列)を選択した状態で、「ホーム」タブをクリックします。次に、「変換」グループにある「列のピボット」をクリックします。
4. 「列のピボット」ダイアログボックスの設定
   「列のピボット」ダイアログボックスが表示されます。「値」の項目で、ピボットされた列の値として使用したい列を選択します。この例では、「売上」列を選択します。「詳細設定」を展開すると、「集計しない」などのオプションがありますが、通常は「集計しない」を選択し、元のデータに重複がないことを確認します。重複がある場合は、合計、平均などの集計方法を選択する必要があります。設定が完了したら、「OK」をクリックします。
5. 変換結果の確認と読み込み
   「列のピボット」が実行されると、「商品名」列のユニークな値が新しい列ヘッダーとなり、対応する「売上」の値がセルに表示された横持ちデータに変換されます。必要に応じて、他の列(この例では「日付」列)の並べ替えやフィルター処理を行い、データを整形します。「ホーム」タブの「閉じて読み込む」をクリックし、変換されたデータをExcelシートに読み込みます。

「列のピボット」実行時の注意点とよくある失敗

Power Queryの「列のピボット」機能は非常に便利ですが、意図した結果を得るためにはいくつか注意すべき点があります。これらの注意点を理解しておくことで、よくある失敗を防ぎ、スムーズにデータ変換を進めることができます。

ピボットする列の選択ミス

「列のピボット」機能で最も基本的なミスは、ピボットする列の選択を間違えることです。例えば、本来列ヘッダーにしたい項目が入っている列ではなく、値が入っている列を選択してしまうケースです。この場合、意図しない結果になり、データが正しく横持ちに変換されません。必ず、横持ちの項目名(例:「商品名」「地域名」「月」など)が含まれる列を「ピボットする列」として選択してください。

値列の重複と集計方法の誤り

「列のピボット」を実行する際、「値」列に重複するデータが存在する場合、Power Queryは自動的に集計を試みます。デフォルトでは「集計しない」が選択されていますが、重複がある場合にそのまま「OK」を押すとエラーになるか、意図しない結果になることがあります。重複がある場合は、「詳細設定」から「合計」「平均」「カウント」などの適切な集計方法を選択する必要があります。例えば、同じ日付・同じ商品名の売上が複数行ある場合は、「合計」を選択して一つのセルにまとめます。どの集計方法が適切かは、元のデータの性質と最終的にどのような集計結果を得たいかによって異なります。

ピボット解除(列のアンピボット)の必要性

「列のピボット」は縦持ちから横持ちへの変換ですが、逆に横持ちデータを縦持ちデータに戻したい場合は、「列のアンピボット」機能を使用します。この機能は、「列のピボット」とは逆の変換を行います。例えば、誤って「列のピボット」を実行してしまい、元に戻したい場合や、分析の過程で縦持ちデータの方が扱いやすい場合に利用します。操作としては、「ホーム」タブの「変換」グループにある「列のアンピボット」を選択します。

データ型の一貫性

ピボットする列や値列のデータ型が不一貫な場合、予期せぬエラーが発生することがあります。「列のピボット」を実行する前に、各列のデータ型が適切であることを確認してください。例えば、数値として扱いたい列がテキスト型になっている場合は、事前にデータ型を数値に変更しておく必要があります。Power Queryエディターでは、各列ヘッダーの左側にあるアイコンでデータ型を確認・変更できます。

Power Queryの「列のピボット」とExcelのピボットテーブルの比較

Excelには、データを集計・分析するための「ピボットテーブル」機能も搭載されています。「列のピボット」とピボットテーブルは、どちらもデータの集計や形式変換に利用できますが、それぞれ得意なことや使い方が異なります。以下に両者の比較を示します。

項目	Power Query「列のピボット」	Excelピボットテーブル
主な目的	縦持ちデータを横持ちデータに変換し、クロス集計表の「準備」を行う	データの集計、分析、クロス集計表の「作成」
操作対象	Power Queryエディター内のデータ	Excelシート上のデータ
変換の特性	元のデータを整形・構造変更する。データソース自体を変更するイメージ。	元のデータを参照し、集計結果を表示する。元のデータは変更されない。
柔軟性	複雑なデータ整形や他の変換処理と組み合わせやすい。	直感的で操作しやすく、手軽に集計結果を確認できる。
自動化	一度設定したクエリは、データ更新時に自動で実行される。	元データ更新後、手動で更新操作が必要。
使用場面	データソースの構造を分析しやすい形に変えたい場合。他のデータ整形処理の前段階。	素早く集計値を確認したい場合。分析結果をレポートにまとめたい場合。

Power Queryの「列のピボット」は、あくまでデータ整形の一環として、分析しやすい横持ち形式に変換する機能です。変換されたデータは、さらにExcelのピボットテーブルで集計・分析するのに適しています。つまり、Power Queryでデータソースを整形し、ピボットテーブルで集計・分析するという連携が、より高度なデータ活用につながります。

まとめ

Power Queryの「列のピボット」機能を利用することで、縦持ちデータを効率的に横持ちデータへと変換できます。この変換は、クロス集計表の作成準備として非常に有効です。この記事で解説した手順と注意点を理解することで、データの整形作業をよりスムーズに行えるようになります。変換後のデータは、Excelのピボットテーブルなどでさらに詳細な集計・分析に活用できます。

次回のデータ分析では、Power Queryによるデータ整形とピボットテーブルによる集計を組み合わせて、より高度な分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】