【Excel】Power Queryでピボット解除してデータを縦持ちに変換する方法

Excelで大量のデータを扱う際、集計や分析のためにデータを縦持ち形式に変換したい場面があります。特に、ピボットテーブルのように項目が横に並んでいるデータを、Power Queryを使って効率的に縦持ち形式へ変換する方法を知りたい方もいるでしょう。この記事では、Power Queryの「ピボット解除」機能を使って、横持ちデータを縦持ちデータに変換する具体的な手順を解説します。

Power Queryを使えば、手作業では時間のかかるデータ変換作業を自動化できます。この機能を使えば、分析に必要な形式にデータを素早く整形できるため、データ分析の効率が格段に向上します。

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

Power Queryの「ピボット解除」機能は、Excelで横持ちになっているデータを縦持ち形式に変換するための強力なツールです。例えば、月ごとの売上データが列見出しになっている場合、それを「月」と「売上」という2つの列にまとめることができます。この機能は、データの集計やグラフ化、他のデータソースとの結合など、様々なデータ分析の前処理として非常に役立ちます。

ピボット解除は、特定の列を「属性」列(元の列見出し)と「値」列(元のセルの値)に分解する操作です。これにより、横に広がっていたデータが縦に積み重ねられ、分析に適した形になります。この機能を使うことで、手作業でのデータ加工にかかる時間と労力を大幅に削減できます。

Power Queryでピボット解除する手順

Power Queryを使ってデータを縦持ちに変換する手順は、以下の通りです。ここでは、例としてA列に商品名、B列からD列に各月(1月、2月、3月)の売上データがある表を縦持ちに変換します。

1. Excelテーブルへの変換
   まず、分析したいデータ範囲をExcelテーブルに変換します。データ範囲を選択し、「挿入」タブの「テーブル」をクリックします。ダイアログが表示されたら、「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認し、「OK」をクリックします。
2. Power Queryエディターの起動
   テーブル内のいずれかのセルを選択した状態で、「データ」タブに移動します。「データの取得と変換」グループにある「テーブルまたは範囲から」をクリックします。これにより、Power Queryエディターが起動し、選択したテーブルのデータが読み込まれます。
3. ピボット解除したい列の選択
   Power Queryエディターで、縦持ちにしたいデータ項目が含まれる列を選択します。今回の例では、「1月」「2月」「3月」の列を選択します。これらの列を選択するには、最初の列(「1月」)をクリックし、Shiftキーを押しながら最後の列(「3月」)をクリックします。
4. 「ピボット解除」機能の実行
   列が選択された状態で、「ホーム」タブにある「列のピボット解除」ボタンをクリックします。このボタンは、「変換」タブにもあります。
5. 結果の確認と列名の変更
   「ピボット解除」を実行すると、選択した列が「属性」列と「値」列に変換されます。元の「1月」「2月」「3月」といった列見出しは「属性」列に、「売上」の値は「値」列に集約されます。必要に応じて、「属性」列を「月」などに、「値」列を「売上」などに列名を変更します。列名をダブルクリックすると編集できます。
6. データの読み込み
   変換が完了したら、「ホーム」タブの「閉じて読み込む」をクリックします。「閉じて読み込む」の▼をクリックし、「閉じて次に読み込む」を選択すると、読み込み設定ダイアログが表示されます。ここでは、「テーブル」を選択し、データの読み込み先(新規ワークシートまたは既存のワークシート)を指定して「OK」をクリックします。

「ピボット解除」の応用:特定列のみピボット解除

通常、「ピボット解除」機能は選択した列をすべて縦持ちに変換しますが、特定の列のみをピボット解除し、それ以外の列はそのまま保持したい場合もあります。例えば、商品IDや商品名のような識別情報は残したまま、月ごとの売上データだけを縦持ちにしたい場合などです。

このような場合は、「ピボット解除」機能のドロップダウンメニューから、より詳細なオプションを選択します。

1. ピボット解除対象外の列を選択
   まず、縦持ちに変換したくない列(例:商品名、商品ID)を選択します。
2. 「その他の列のピボット解除」を選択
   「ホーム」タブまたは「変換」タブにある「列のピボット解除」ボタンの▼をクリックし、「その他の列のピボット解除」を選択します。
3. 結果の確認
   この操作により、選択した列はそのまま保持され、選択されなかった列(例:1月、2月、3月)のみが縦持ちに変換されます。

「ピボット解除」の応用:すべての列をピボット解除

逆に、ID列などを残さず、すべてのデータ列を縦持ちに変換したい場合もあります。その場合は、「すべての列のピボット解除」を選択します。

1. すべての列を選択
   Power Queryエディターで、すべてのデータ列を選択します。
2. 「すべての列のピボット解除」を選択
   「ホーム」タブまたは「変換」タブにある「列のピボット解除」ボタンの▼をクリックし、「すべての列のピボット解除」を選択します。
3. 結果の確認
   この操作により、ID列などの識別情報も「属性」列と「値」列に変換され、データ全体が縦持ち形式になります。

Power Queryのピボット解除機能でよくある質問と回答

Q1:ピボット解除後、列名が「属性」と「値」のまま変わらないのですが?

Power Queryエディター上で、変換された「属性」列や「値」列の列名をダブルクリックすることで、任意の名前(例:「月」「売上」)に変更できます。変更後、Excelに読み込むことで、新しい列名でデータが反映されます。

Q2:ピボット解除したデータをExcelの元の表に戻すことはできますか?

Power Queryエディターでの操作は、元のExcelテーブルに影響を与えずに新しいクエリとして実行されます。Power Queryエディターを閉じ、「閉じて読み込む」を選択することで、変換されたデータが新しいシートにテーブルとして出力されます。元のデータはそのまま残るため、いつでも再編集や再実行が可能です。元のExcelテーブルに戻すというよりは、変換結果を別に出力する形になります。

Q3:ピボット解除の操作を間違えてしまった場合、どうすればよいですか?

Power Queryエディターの右側にある「適用したステップ」ペインで、間違ったステップを選択し、削除することができます。または、ステップを元に戻す(Undo)機能(Ctrl+Z)も利用できます。間違ったステップを削除または元に戻した後、再度正しい操作を行ってください。

Q4:ピボット解除できない列があるのですが、なぜですか?

ピボット解除できない列がある場合、その列に意図しないデータ型が設定されているか、あるいはデータ自体に問題がある可能性があります。Power Queryエディターで、各列のデータ型を確認し、必要であれば適切なデータ型(例:テキスト、数値)に変更してください。また、列名に特殊文字が含まれていないかも確認してください。

Power QueryとVBAでのデータ変換の比較

Excelでデータを縦持ちに変換する方法として、Power Query以外にもVBA(Visual Basic for Applications)によるマクロを作成する方法があります。それぞれの特徴を比較してみましょう。

項目	Power Query	VBAマクロ
操作の容易さ	GUI操作が中心で、直感的でわかりやすい	VBAの知識が必要で、コードの記述・デバッグが必要
処理速度	大量データでも高速に処理できる場合が多い	コードの書き方によっては遅くなることがある
柔軟性・拡張性	豊富な変換機能があり、複雑な処理も可能	プログラミング次第で、ほぼ全ての処理が可能
再実行性	「更新」ボタン一つで、元のデータが更新されても再実行できる	マクロを実行するたびに処理が走る
学習コスト	比較的低い	高い
エラー処理	エラー発生時にステップを遡って確認・修正しやすい	コードのデバッグが必要

Power Queryは、GUI操作で直感的にデータ変換を行えるため、Excelユーザーにとって学習コストが低く、非常に強力なツールです。特に、定期的に同じ形式のデータ変換を行う場合に、その威力を発揮します。一方、VBAはより高度で複雑な処理や、Excelの他の操作との連携が必要な場合に適していますが、プログラミングの知識が求められます。

まとめ

この記事では、ExcelのPower Query機能を用いて、横持ちのデータを縦持ち形式に変換する「ピボット解除」の方法を詳しく解説しました。Power Queryエディターを起動し、対象列を選択して「ピボット解除」を実行するだけで、データ分析に適した形にデータを整形できます。また、特定列のみをピボット解除する応用的な使い方や、よくある質問とその回答にも触れました。

この「ピボット解除」機能を使いこなすことで、データの前処理にかかる時間を大幅に短縮し、より高度な分析に時間を割けるようになります。今後は、Power Queryの他の変換機能(列の分割、グループ化など)と組み合わせることで、さらに複雑なデータ加工も効率的に行えるようになるでしょう。

📊

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

🏆

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