「月別×支店別の集計表をもらったけど、これをピボットテーブルで分析しようとしたらうまくいかない……」という経験はありませんか？
配布資料として作られた集計済みの表は、見やすい代わりに 再集計には向かない形 になっています。月が列方向に並び、支店が行方向に並んだ、いわゆるクロス集計表です。この形のままだと、ピボットテーブルやグラフにそのまま流し込めません。
そこで活躍するのが、Power Query の ピボット解除（アンピボット） という機能です。横に広がった集計表を、分析しやすい「縦持ち（タテ型）データ」に一発で変換してくれます。しかも一度作れば、翌月以降は更新ボタン1クリックで使い回せます。
この記事では、Power Query が初めての事務職の方でも迷わないように解説します。月別×支店別のクロス集計表を縦持ちに戻す手順を、操作画面つきで紹介します。
なお本記事は Windows 版 Excel（Microsoft 365 / 2016 以降） での操作を前提にしています。
Power Query のピボット解除とは？横持ちを縦持ちに変える機能
Power Query のピボット解除とは、 横持ちの表を「縦持ち」に変換する機能 です。行方向と列方向に広がった値を、1列1項目の形に並べ替えてくれます。英語の操作名から「アンピボット」とも呼ばれます。
まずは、横持ちと縦持ちが具体的にどう違うのかを見ておきましょう。ここがイメージできると、後の操作がぐっとわかりやすくなります。
横持ち（クロス集計表）の例
配布資料でよく見るのが、次のような形です。1行が1支店で、各月の売上が列方向に並んでいます。
| 支店 | 4月 | 5月 | 6月 |
|---|---|---|---|
| 東京 | 120 | 135 | 142 |
| 大阪 | 98 | 102 | 110 |
| 名古屋 | 75 | 80 | 88 |
人が読むぶんにはとても見やすい表です。ただし「月」という情報が列見出しに散らばっているため、分析ツールから見ると扱いにくい構造になっています。
縦持ち（タテ型データ）の例
これをピボット解除すると、次のような形になります。
| 支店 | 月 | 売上 |
|---|---|---|
| 東京 | 4月 | 120 |
| 東京 | 5月 | 135 |
| 東京 | 6月 | 142 |
| 大阪 | 4月 | 98 |
| 大阪 | 5月 | 102 |
| … | … | … |
「月」が1つの列にまとまり、「売上」も1つの列にまとまりました。これが縦持ちです。1行が1つの実績を表す形になっているのがポイントです。
なぜ縦持ちにする必要があるのか
ピボットテーブルやグラフ、Power BI などの分析ツールは、 縦持ちのデータを前提に作られています 。縦持ちなら「行ラベルに支店、列ラベルに月、値に売上」と自由に組み替えられます。
横持ちのままだと、月が増えるたびに集計範囲を直す必要があり、メンテナンスが大変です。「分析の前処理として、まず縦持ちに直す」という流れを覚えておくと、いろいろな場面で応用が効きます。
Power Query そのものの全体像をまだ押さえていない方は、 Excel Power Query入門｜コピペ集計を卒業する4つの自動化レシピ や Power Query完全ガイド もあわせて読んでみてください。
Power Query でピボット解除する手順【4ステップ】
それでは、先ほどの月別×支店別のクロス集計表を、実際にピボット解除していきます。やることはシンプルで、大きく4ステップです。
ゴールは「支店・月・売上の3列からなる縦持ちの表」を作ることです。完成イメージを頭に置きながら進めていきましょう。
前提として、Excel シートに次のクロス集計表が入っているものとします。A1セルに「支店」、B1〜D1に「4月」「5月」「6月」、A2以降に各支店のデータが入っている状態です。
ステップ1：表をテーブルにして Power Query に取り込む
まず、変換したい表を Power Query に読み込みます。
- クロス集計表の中のセルをどこか1つクリックする
- 「データ」タブを開く
- 「データの取得と変換」グループの 「テーブルまたは範囲から」 をクリックする
- 範囲の確認ダイアログが出たら、範囲と「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して「OK」を押す
これで Power Query エディターが別ウィンドウで開きます。中央のプレビューに、さきほどのクロス集計表がそのまま表示されているはずです。
ステップ2：基準にする列（支店）を選択する
次に、 縦に残したい列 を選びます。今回は「支店」を縦に残し、月の列だけを縦持ちに変換します。
Power Query エディターのプレビューで、 「支店」列のヘッダーをクリック して選択します。列全体が緑色などでハイライトされればOKです。
ここで選んだ列は、変換後もそのまま縦に残ります。「分析の軸になる列」を選ぶイメージです。
ステップ3：「その他の列のピボット解除」を実行する
支店列を選択した状態のまま、変換を実行します。
- エディター上部の 「変換」タブ を開く
- 「列のピボット解除」ボタンの右側にある下向き矢印（▼） をクリックする
- メニューから 「その他の列のピボット解除」 を選ぶ
たったこれだけです。実行すると、選択していた「支店」以外の列（4月・5月・6月）が一気に縦持ちに変換されます。
変換後のプレビューは、次のような形になっているはずです。新しく「属性」と「値」という2つの列ができています。
「属性」列に月（4月・5月・6月）が、「値」列に売上が入りました。これでもう縦持ちのデータができています。
ステップ4：列名をわかりやすく変更する
最後に、自動でついた「属性」「値」という列名を、内容に合った名前に変えておきます。このひと手間で、あとの分析がぐっとラクになります。
- 「属性」列のヘッダーを ダブルクリック して、 「月」 と入力する
- 同じように「値」列のヘッダーをダブルクリックして、 「売上」 と入力する
これで「支店・月・売上」の3列がそろいました。あとは左上の 「閉じて読み込む」 をクリックすれば、Excel の新しいシートに縦持ちの表が出力されます。
ここまでが基本の流れです。慣れれば1分もかからず終わります。
「その他の列のピボット解除」を使うべき理由
ステップ3で「列のピボット解除」ではなく、あえて 「その他の列のピボット解除」 を選んだのには理由があります。ここは実務でとても効いてくるポイントなので、少し詳しく説明します。
Power Query のピボット解除には、似たメニューが3種類あります。それぞれの違いを押さえておきましょう。
3つのピボット解除メニューの違い
- 列のピボット解除: 選択した列を縦持ちに変換する
- その他の列のピボット解除: 選択した列「以外」をすべて縦持ちに変換する
- 選択した列のみピボット解除: 選択した列だけを縦持ちにする（他の操作の影響を受けない）
一見すると「列のピボット解除」で月の列（4月・5月・6月）を選べばよさそうに見えますよね。実際それでも変換はできます。
翌月の列が増えても自動で対応できる
問題は、 翌月以降に「7月」の列が増えたとき です。
「列のピボット解除」で4月〜6月を名指しで選んでいると、新しく増えた7月の列は変換対象から漏れてしまいます。毎月クエリを直す羽目になり、自動化の意味が薄れてしまうのです。
一方「その他の列のピボット解除」なら、 「支店以外は全部縦持ちにする」 という指定になります。そのため、月の列がいくつ増えても、更新ボタンを押すだけで自動的に取り込まれます。
「軸になる列を選ぶ」という発想で「その他の列のピボット解除」を使う。これがメンテナンスに強いクエリを作るコツです。
縦持ちにしたあとの活用テクニック
ピボット解除で縦持ちにしたデータは、ここからが本番です。分析の前処理が終わった状態なので、いろいろな集計に展開できます。代表的な活用例を紹介します。
ピボットテーブルで自由に集計し直す
縦持ちにした表を Excel シートに出力したら、その表を元に ピボットテーブル を作ってみましょう。
「挿入」タブ →「ピボットテーブル」を選びます。行に「支店」、列に「月」、値に「売上の合計」を配置すれば、元のクロス集計表が再現できます。さらに行と列を入れ替えたり、月だけで合計を出したりと、自由自在に組み替えられます。
「最初の横持ち表に戻すこともできるし、別の切り口でも見られる」という状態になるわけです。
Power Query 上でそのまま集計する
ピボットテーブルを使わず、Power Query の中で集計まで済ませてしまう方法もあります。縦持ちにした後に「グループ化」を使えば、支店ごとの合計や平均をクエリ内で計算できます。
グループ化の詳しい手順は、 Power Queryのグループ化で集計する方法 で解説しています。「縦持ちに変換 → グループ化で集計」という流れは、定例レポート作成の鉄板パターンです。
複数の表を結合してから縦持ちにする
支店ごとにファイルが分かれている場合は、まず複数の表をまとめてから縦持ちにする、という応用もできます。
表どうしをキーで突き合わせる方法は Power Queryのマージで複数テーブルを結合する方法 を、データの表記ゆれや空白を整える方法は Power Queryのデータクレンジング入門 を参考にしてください。
ピボット解除でつまずきやすいポイント3選
最後に、ピボット解除を始めたばかりの人がよくつまずく3つのポイントと、その対処法をまとめておきます。
基準にする列まで一緒に縦持ちになってしまう
「支店の値まで属性列に入ってしまった」というケースです。これは、ステップ2で支店列を選ばずにピボット解除を実行したときに起こります。
対処法はシンプルです。右側の「適用したステップ」で、ピボット解除のステップを削除してやり直します。そのうえで、 必ず先に「支店」列を選択してから 「その他の列のピボット解除」を実行してください。
月の列が「数値」ではなく「文字列」になっている
縦持ちにした後、「値」列が文字列のままで合計できないことがあります。元の表で金額がテキストとして入っていた場合に起こりがちです。
対処法は、 「値」列のヘッダー左にあるデータ型アイコンをクリックし、「整数」または「10進数」に変更する ことです。これで合計や平均が計算できるようになります。
不要な合計行・合計列まで縦持ちに含まれてしまう
元のクロス集計表に「合計」の行や列が含まれていると、それも縦持ちに変換されて二重集計の原因になります。
対処法は、Power Query に取り込んだ直後に 不要な合計行・合計列を削除しておく ことです。合計列はヘッダーを右クリックして「列の削除」、合計行は「行の削除」やフィルターで除外します。元データを整えてからピボット解除するのが安全です。
データの整え方をもっと知りたい方は、 Power Queryのデータクレンジング入門 もチェックしてみてください。
まとめ：クロス集計表は「その他の列のピボット解除」で縦持ちに
Power Query のピボット解除を使えば、横持ちのクロス集計表を、分析しやすい縦持ちデータに数クリックで変換できます。
今回の手順のポイントは次の3つです。
- 基準にする列（支店）を先に選択 してから変換する
- 「その他の列のピボット解除」 を使えば、月の列が増えても自動で対応できる
- 縦持ちにした後は ピボットテーブルやグループ化 で自由に集計し直せる
「配布資料の集計表をもらったけど分析できない」という場面は、事務の仕事でよく出てきますよね。そんなときに、まずピボット解除で縦持ちに戻すクセをつけておくと、その後の作業が一気にラクになります。
一度クエリを作っておけば、翌月のデータも更新ボタン1クリックで縦持ちに変換できます。Power Query の全体像は Excel Power Query入門｜コピペ集計を卒業する4つの自動化レシピ でも紹介しているので、まだの方はあわせて読んでみてください。
まずは手元のクロス集計表で、今日のうちにピボット解除を試してみてください。
