【Excel】Power Queryでアンピボット(列を行に変換)する方法

Excelでデータを集計・分析する際、列に分かれたデータを1つの列にまとめる作業は頻繁に発生します。

特に、年ごとの売上や月ごとの数値などが列に並んでいる場合、そのままでは集計やグラフ化が困難なことがあります。

この記事では、ExcelのPower Query機能を使って、このような「列を行に変換」するアンピボット操作を簡単に行う方法を解説します。

Power Queryを使えば、手作業では時間のかかる煩雑なデータ整形作業を自動化できます。

Power Queryのアンピボット機能の概要

Power Queryには、データを「アンピボット」する機能があります。これは、表形式のデータにおいて、複数の列に分散している項目名を1つの列にまとめ、それぞれの項目に対応する値を別の列に格納する操作です。

例えば、Aさんの1月、2月、3月の売上がそれぞれ別々の列になっている場合、アンピボットを行うと「項目(月)」と「値(売上)」の2列に整理されます。これにより、月ごとの売上を集計したり、時系列グラフを作成したりすることが容易になります。

この機能は、特にWebサイトやデータベースから取得したデータ、あるいは他のシステムで出力されたデータなど、分析しやすい形になっていない場合に非常に役立ちます。

Power Queryで列を行に変換する手順

Power Queryエディターを開き、データソースを読み込んだ後、アンピボット操作を行います。ここでは、具体的な手順をステップごとに解説します。

1. Power Queryエディターを開く
   Excelの「データ」タブにある「データの取得と変換」グループから、「テーブルまたは範囲から」を選択します。または、既存のPower Queryクエリがある場合は、「クエリと接続」ウィンドウから該当のクエリをダブルクリックします。
2. アンピボットしたい列以外を選択する
   Power Queryエディターが表示されたら、アンピボットしたい列(例:月ごとの売上列)以外の列(例:商品名、店舗名など、保持したい列)を1つ以上選択します。Ctrlキーを押しながらクリックすると、複数列を選択できます。
3. アンピボット操作を実行する
   選択した列以外の列を右クリックします。「アンピボット解除」または「その他の列のアンピボット」というメニューが表示されます。
4. アンピボット対象の列を指定する(「その他の列のアンピボット」の場合)
   もし「その他の列のアンピボット」を選択した場合、どの列をアンピボット対象とするかを選択するダイアログが表示されることがあります。この場合は、アンピボットしたい列をすべて選択し、「OK」をクリックします。
5. 列名の変更
   アンピボットが完了すると、元の列見出しが「属性」という列に、元の列の値が「値」という列に格納されます。これらの列名を、内容が分かりやすい名前に変更します。例えば、「属性」を「月」に、「値」を「売上」に変更します。列名をダブルクリックすると編集できます。
6. データの型を確認・変更する
   必要に応じて、各列のデータ型(テキスト、数値、日付など)を確認し、正しく設定されているか確認・変更します。列見出しの左側にあるアイコンでデータ型を確認できます。
7. 閉じて読み込む
   「ホーム」タブの「閉じて読み込む」をクリックし、処理結果をExcelシートに読み込みます。

特定の列のみをアンピボットする方法

前述の手順では、選択した列『以外』の列をアンピボットしました。しかし、特定の列だけをアンピボットしたい場合もあります。その場合は、以下の手順で行います。

1. アンピボットしたい列を選択する
   Power Queryエディターで、アンピボットしたい列(例:1月、2月、3月の売上列)をすべて選択します。Ctrlキーを押しながらクリックすると、複数列を選択できます。
2. アンピボット操作を実行する
   選択した列を右クリックします。「アンピボット解除」または「選択した列のアンピボット」というメニューが表示されます。
3. 列名の変更とデータ型確認
   アンピボット後、「属性」と「値」という列が生成されるので、必要に応じて列名を変更し、データ型を確認・変更します。
4. 閉じて読み込む
   「ホーム」タブの「閉じて読み込む」をクリックして、Excelシートに結果を読み込みます。

アンピボット解除(ピボット)する方法

誤ってアンピボットしてしまった場合や、元の列形式に戻したい場合は、「ピボット」操作を行います。これはアンピボットの逆の操作です。

1. Power Queryエディターを開く
   該当のクエリをPower Queryエディターで開きます。
2. 「属性」列と「値」列を選択する
   アンピボットによって生成された「属性」列(項目名が入っている列)と「値」列(実際の値が入っている列)を選択します。
3. ピボット操作を実行する
   選択した列を右クリックし、「ピボット解除」または「その他の列のピボット解除」というメニューを選択します。
4. ピボット解除のオプション設定
   「ピボット解除」を選択すると、元の列見出しが「属性」列の値として、元の列の値が「値」列の値として復元されます。
5. 閉じて読み込む
   「ホーム」タブの「閉じて読み込む」をクリックして、Excelシートに結果を読み込みます。

アンピボット操作時の注意点とよくある失敗

Power Queryのアンピボット機能は強力ですが、いくつかの注意点やよくある失敗パターンがあります。これらを理解しておくことで、スムーズに作業を進められます。

ヘッダー行が正しく認識されていない

症状: アンピボットしようとすると、データの一部が列見出しとして扱われてしまったり、意図しない形でデータが展開されたりする。

原因: 元のデータソースで、1行目がヘッダー(列名)として正しく設定されていない場合。

対処法: Power Queryエディターで、最初のステップ(通常は「ソースの変更」や「ナビゲーション」)を確認します。もしヘッダーが正しく認識されていない場合は、「ホーム」タブの「先頭行をヘッダーとして使用」をクリックして、ヘッダー行を正しく設定し直してください。すでに「ヘッダーの昇格」ステップが適用されている場合は、そのステップを削除または修正します。

アンピボットしたい列の特定が難しい

症状: 列数が非常に多く、どの列をアンピボットすべきか判断に迷う。

原因: データ構造が複雑で、アンピボット対象の列が連続していなかったり、複数のグループに分かれていたりする。

対処法: まず、アンピボットしたい列の共通点(例:すべて「2023年」で始まる、すべて数値データであるなど)を把握します。Power Queryエディターの列ヘッダーを右クリックし、「テキスト フィルター」や「数値フィルター」で絞り込みを行うと、対象の列を見つけやすくなります。また、列を並べ替えて、アンピボットしたい列をまとめてから選択すると作業が容易になります。

意図しない列がアンピボットされてしまう

症状: 「その他の列のアンピボット」を選択したが、一部の保持したい列までアンピボットされてしまう。

原因: 「その他の列のアンピボット」は、選択した列『以外』のすべてをアンピボット対象とするため、保持したい列が選択されていない状態で操作を進めてしまう。

対処法: この場合は、一度アンピボット操作を取り消し(「適用したステップ」からアンピボットのステップを削除)、改めて「選択した列のアンピボット」機能を使用してください。アンピボットしたい列を正確に選択することが重要です。

「属性」列と「値」列のデータ型が不適切

症状: アンピボット後の「値」列が数値として認識されず、集計などができない。

原因: 元の列に数値以外の文字(例:「-」「N/A」など)が混在している場合、Power Queryがその列全体をテキスト型として認識してしまう。

対処法: アンピボット操作の前に、アンピボット対象となる各列のデータ型を事前に確認し、必要であれば「ホーム」タブの「データ型変更」機能を使って数値型に変換しておきます。もし、数値以外の文字が含まれていて変換できない場合は、それらの文字を削除または置換するステップをアンピボット操作の前に追加する必要があります。

アンピボット解除(ピボット)でデータが重複する

症状: アンピボット解除(ピボット)を実行すると、意図せずデータが複製されてしまう。

原因: 元のデータに、ピボット操作で復元したい列見出しに対応する一意の識別子(キー)が存在しない場合。例えば、同じ商品名で複数の月データがある場合、ピボット操作だけではどの月データなのかを区別できない。

対処法: ピボット操作の前に、復元したい列見出し(例:月名)と、それに対応する元のデータ(例:売上)を組み合わせるための「キー」となる列が必要です。もしキーとなる列がない場合は、アンピボット解除の前に、「列の追加」タブから「インデックス列の追加」などで一意のIDを生成しておくと、ピボット操作が正しく行えるようになります。あるいは、アンピボット解除の際に、「詳細オプション」で「行の値の集計」を設定できる場合があります。通常は「集計しない」を選択しますが、必要に応じて「合計」などを選択します。

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

Power Queryのアンピボット機能は、Excelのピボットテーブル機能と似た目的で使われることがありますが、それぞれ得意なことが異なります。

項目	Power Queryのアンピボット	Excelピボットテーブル
主な目的	データ整形・前処理(列を行に変換)	データの集計・分析・可視化
操作対象	生データ、クエリ結果	Excelシート上のデータ、Power Pivotデータモデル
実行タイミング	データ取得後、分析前にPower Queryエディター内で行う	データ整形後、分析段階でシート上または別ウィンドウで行う
データ変換	永続的なデータ変換。クエリとして保存され、更新可能	集計・表示形式の変更。元のデータは変更されない
複雑なデータ整形	得意。複数ステップで高度なデータ加工が可能	苦手。主に集計処理に特化
更新性	データソース変更時にクエリを更新するだけで整形済みのデータが得られる	元のデータ変更時にピボットテーブルの更新が必要

Power Queryのアンピボットは、元データの構造を分析しやすい「縦持ち」形式に変換するための強力な前処理ツールです。一方、ピボットテーブルは、整形されたデータを元に、様々な切り口で集計・分析を行うためのツールと言えます。

多くの場合、Power Queryでデータをアンピボットして整形し、その結果をExcelシートに読み込んでからピボットテーブルで集計・分析するという流れが、効率的なデータ活用につながります。

まとめ

Power Queryのアンピボット機能を使えば、列に分散したデータを簡単に1つの列にまとめることができます。

これにより、データ集計やグラフ作成が格段にしやすくなり、業務効率が大幅に向上します。

まずは、簡単なサンプルデータでアンピボット操作を試してみてください。

慣れてきたら、実際の業務データに適用し、Power Queryでのデータ整形とExcelでの分析を組み合わせて活用してみましょう。

📊

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

🏆

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