【Excel】Power Queryでピボット解除する手順!Excelの横持ちデータを縦持ちに変換する方法

Excelで横持ちのデータを縦持ちに変換したい場面は多いでしょう。特に、集計表やレポートで日付や項目が横に並んでいる場合、分析や集計が困難になります。Power Queryを使えば、この横持ちデータを簡単に縦持ちデータへ変換できます。この記事では、Power Queryを使ったピボット解除の手順を詳しく解説します。

Power Queryは、Excelに標準搭載されているデータ加工・整形ツールです。外部データソースからデータを取得し、不要な列の削除やデータ型の変更など、複雑なデータ加工をGUI操作で行えます。その中でも、「ピボット解除」機能は、横持ちデータを縦持ちデータに変換する際に非常に強力なツールとなります。

本記事を読むことで、Power Queryを使って横持ちデータを縦持ちデータに変換する具体的な手順を習得できます。これにより、データの分析や集計作業を効率化できるでしょう。

Power Queryのピボット解除機能とは

Power Queryの「ピボット解除」機能は、Excelにおける横持ちデータを縦持ちデータに変換するために設計されています。具体的には、横方向に並んだ複数の列を、2つの列(属性列と値列)に集約する処理を行います。この機能により、日付ごとの売上や、製品ごとの地域別販売数といった、分析に適した形式のデータに容易に整形できます。複雑な関数を組み合わせる必要がなく、GUI操作で直感的に実行できる点が大きなメリットです。

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

Power Queryを使用してExcelの横持ちデータを縦持ちデータに変換する手順を解説します。ここでは、例として月ごとの売上データが横に並んでいるExcelシートを縦持ちデータに変換するケースを想定します。

1. Excelファイルを開き、データをPower Queryに読み込む
   まず、変換したいデータが含まれるExcelファイルを開きます。次に、リボンメニューの「データ」タブをクリックします。「データの取得と変換」グループにある「テーブルまたは範囲から」を選択してください。
2. Power Queryエディターでデータを確認する
   「テーブルの作成」ダイアログが表示されたら、データ範囲が正しく選択されているか確認し、「OK」をクリックします。これにより、Power Queryエディターが起動し、Excelシートのデータが読み込まれます。
3. ピボット解除したい列を選択する
   Power Queryエディターで、横持ちデータになっている列(例:1月、2月、3月…といった月を表す列)を選択します。これらの列を縦持ちデータに変換します。選択するには、列ヘッダーをクリックします。複数の列を選択する場合は、Ctrlキーを押しながらクリックするか、Shiftキーを使って連続した列を選択します。
4. 「ピボット解除」機能を使用する
   選択した列を縦持ちデータに変換するには、リボンメニューの「ホーム」タブにある「列の変換」グループ内の「ピボット解除」をクリックします。ここで、「ピボット解除」の隣にある下向き矢印をクリックし、「選択した列のピボット解除」を選んでください。
5. 「属性」列と「値」列の名前を変更する
   ピボット解除を実行すると、元の月を表していた列が「属性」列と「値」列に変換されます。「属性」列には月名が、「値」列には対応する売上データが入ります。これらの列名を、より分かりやすい名前に変更しましょう。例えば、「属性」を「月」、「値」を「売上」と変更します。列ヘッダーをダブルクリックして直接編集するか、右クリックして「名前の変更」を選択します。
6. データ型を確認・変更する
   変換後の「月」列や「売上」列のデータ型が正しく認識されているか確認します。列ヘッダーの左側にあるアイコンでデータ型を確認できます。例えば、「月」がテキスト型になっている場合は、日付型や整数型に変更する必要があるかもしれません。「売上」列は数値型になっていることを確認してください。データ型を変更するには、列ヘッダーのアイコンをクリックするか、リボンメニューの「変換」タブにある「データ型」から選択します。
7. データをExcelに読み込む
   データ加工が完了したら、Power Queryエディターのリボンメニュー「ホーム」タブにある「閉じて読み込む」をクリックします。ここでは、「閉じて次に読み込む」を選択すると、データの読み込み方法を選択できます。「テーブル」として新しいワークシートに読み込むのが一般的です。

ピボット解除の応用と注意点

Power Queryのピボット解除機能は非常に便利ですが、いくつかの応用例や注意点があります。これらを理解することで、より効果的にデータを活用できます。

「ピボット解除」以外のオプション

Power Queryエディターの「ピボット解除」機能には、いくつかのオプションがあります。「ピボット解除」の隣にある下向き矢印をクリックすると、「選択した列のピボット解除」、「他の列のピボット解除」、「ピボット解除された列の属性のみ」といった選択肢が表示されます。

「他の列のピボット解除」は、特定の列を残して、それ以外の列をすべてピボット解除したい場合に便利です。例えば、ID列などを残し、それ以外の項目列を縦持ちにしたい場合に利用します。「ピボット解除された列の属性のみ」は、値列を必要とせず、属性列のみを抽出したい場合に用います。目的に応じてこれらのオプションを使い分けることで、より柔軟なデータ加工が可能になります。

ID列を残してピボット解除する方法

多くの横持ちデータには、各行を一意に識別するためのID列(例:商品ID、顧客IDなど)が存在します。このID列を残したまま、他の項目列をピボット解除したい場合、「他の列のピボット解除」機能が役立ちます。

手順としては、まずID列以外の、ピボット解除したいすべての列を選択します。次に、「ホーム」タブの「ピボット解除」の下向き矢印から「他の列のピボット解除」を選択します。これにより、選択されなかったID列はそのまま残り、選択された列が縦持ちデータに変換されます。

ピボット解除後のデータ型に関する注意

ピボット解除を実行した後、「属性」列や「値」列のデータ型が意図しないものになっていることがあります。特に、月が「1月」「2月」のように文字列で表記されている場合、Power Queryはこれを「テキスト」型として認識します。後で日付として扱いたい場合は、手動で「日付」型に変更する必要があります。

また、「値」列が数値であるべきなのに、テキスト型として認識されている場合も注意が必要です。これは、元のデータに数値以外の文字(例:「-」や「,」)が含まれている場合に発生しやすいです。その場合は、まずこれらの不要な文字を削除してから、数値型に変換する手順を追加する必要があります。

元のデータ構造によってはピボット解除が適さない場合

Power Queryのピボット解除は、特定の構造を持つ横持ちデータに最適化されています。例えば、各行に一意のIDがあり、そのIDに対する複数の項目(例:月ごとの値)が列として展開されている形式です。

しかし、データ構造が複雑で、複数の項目がセットになって横に展開されている場合、単純なピボット解除では意図した結果にならないことがあります。このような場合は、ピボット解除の前にデータの整形(列の分割や結合など)を行うか、Power Queryの他の機能(例:カスタム列の追加)を組み合わせて利用する必要が出てきます。データ構造をよく理解した上で、適切な変換方法を選択することが重要です。

Power Queryとピボットテーブルとの比較

Excelでデータを集計・分析する際によく使われる機能として、「ピボットテーブル」があります。Power Queryのピボット解除とピボットテーブルは、どちらもデータの整形や集計に役立ちますが、その目的と機能が異なります。

項目	Power Queryのピボット解除	Excelのピボットテーブル
主な目的	横持ちデータを縦持ちデータに変換するデータ整形	データを集計・分析するための集計表作成
操作対象	生データ、加工前のデータ	加工済みのデータ、またはPower Queryで整形したデータ
変換内容	列を「属性」「値」のペアに変換	行と列を入れ替えて集計値を表示
再現性	クエリとして保存され、再実行可能	データ更新時に手動で集計し直す必要あり(一部自動更新設定あり)
学習コスト	GUI操作中心だが、複雑な加工にはM言語の理解も必要	比較的容易に習得可能

Power Queryのピボット解除は、分析しやすい縦持ちデータを作成するための「前処理」として位置づけられます。一方、ピボットテーブルは、整形されたデータから、さらに集計・分析を行うための「集計・可視化」ツールです。両者を組み合わせて利用することで、データ分析のプロセス全体を効率化できます。例えば、Power Queryで横持ちデータを縦持ちに変換した後、その結果をピボットテーブルで集計するといった流れが一般的です。

Power Queryは、一度設定した加工手順をクエリとして保存できるため、元のデータが更新された際も、クエリを再実行するだけで最新のデータに自動で整形できます。この再現性の高さが、ピボットテーブルとの大きな違いであり、Power Queryがデータ分析の前処理において強力なツールとされる理由の一つです。

まとめ

本記事では、ExcelのPower Queryを使用して、横持ちデータを縦持ちデータに変換する「ピボット解除」の手順を解説しました。Power Queryエディターでデータを読み込み、ピボット解除したい列を選択し、実行するだけで、データ分析に適した形式に変換できます。また、ID列を残したまま解除する方法や、データ型に関する注意点、ピボットテーブルとの比較についても触れました。

この手順を習得することで、横持ちデータの集計や分析にかかる時間を大幅に短縮できます。今後は、Power Queryの他の機能も活用し、より複雑なデータ加工や自動化に挑戦してみましょう。

📊

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

🏆

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