日付が増えるたびに表がどんどん横に長くなって、見やすく作ったつもりなのに集計しようとすると急に難しく感じます…
それは“横持ちデータ”だね。見た目は分かりやすいけど、集計や分析では少し扱いにくくなることがあるんだよ。
いろいろなデータを扱っていると、項目が横にずらっと並んだ「横持ちデータ」に出会うことがあります。見た目はきれいに整理されていても、いざピボットテーブルで集計しようとすると、「あれ、思ったように集計できない…」と困ることがあるんですよね。
そこで今回は、そんな少し扱いづらい横持ちデータを、集計しやすい形にサッと整える方法を紹介します。Power Query の「ピボット解除」を使えば、一見面倒そうな変換も意外なくらいスムーズに進められます。
横持ちデータとは
横持ちデータとは、簡単にいうと「人が見て比べやすいように、項目を横に並べた表形式のデータ」のことです。テストの点数表や月ごとの売上表など、いわゆる「エクセルらしい表」をイメージすると分かりやすいです。
たとえば、テストの点数を管理する場合は次のような形になります。
- 行：生徒
- 列：教科（国語・数学・英語…）
- 値：点数
このように、教科のような項目が列として横に並んでいる状態が横持ちデータです。「Aさんの教科ごとの点数が横一列に並んでいる」イメージですね。
売上管理の表でも、同じような形になることがあります。
- 行：会社名
- 列：日付
- 値：売上金額
この場合は、1社ごとの売上が日付ごとに横へ並んでいきます。
横持ちデータは、とにかく見た目が分かりやすく、人が確認しやすいのがメリットです。
その一方で、ピボットテーブルで集計したり、データ分析に使ったりするときには少し扱いにくいことがあります。
横持ちデータはなぜダメなのか
横持ちデータは一見きれいに並んでいて分かりやすく見えますが、集計や分析の場面では少し困ることがあります。
見出しと値の関係ができていない
横持ちデータでは、列名そのものがデータの意味を持っている ことがよくあります。
先ほどの 「生徒 × 教科 × 点数」 の横持ちデータを例にすると、見出し行には「生徒」という項目名と、「国語」「数学」「英語」などの教科名が並びます。
ここでポイントなのは、教科名そのものが見出しに入っていて、本来データとして扱いたい情報が列名になっている ことです。一方で、各セルに入っている数値は点数ですが、そこには「点数」という見出しがありません。
ピボットテーブルは、見出し（項目名）をもとに行・列・値へ振り分けて集計する仕組み です。そのため、横持ちデータのように 「見出しの中にデータが入り込んでいる形」 だと、うまく扱えないことがあります。
たとえば、
- 生徒ごとの合計点を出したい
- 平均点をまとめたい
- 教科が増えても自動で集計したい
といった処理をするときに、ピボットテーブルでは少し使いづらくなります
項目が増えるたびに列がどんどん増える
横持ちデータは、日付や項目が増えるたびに 列が右へどんどん伸びていく という特徴があります。
たとえば先ほどの売上データでは、1/1〜1/10までの10日分の売上が横に並んでいます。日付が増えれば横にどんどん伸びていきます。
この形だと、集計するときに 横方向の複数列を毎回まとめて指定する必要がある のが少し面倒です。
たとえば 「A社の1週間分の合計売上」 を出したい場合は、
- 1/1〜1/7 の7列を選ぶ
- 次の週なら 1/8〜1/14 の列を選び直す
というように、その都度対象列を変えなければなりません。
日付が増えるたびに列も増えるので、表の幅がどんどん広がっていき、扱いにくくなりやすいです。
そのため、
- 日付ごとの推移を見る
- 会社ごとに比較する
- 週別・月別にまとめる
といった分析がしづらくなります。
特に、日付を1つのフィールドとしてまとめて扱えないので、ピボットテーブルでも使いにくさが出やすいです。
データモデル（Power Query / Power BI）で扱いにくい
横持ちデータのままだと、Power Query や Power BI のようなデータモデルでも扱いにくくなります。
たとえば売上データでは、日付が列名になっている のが大きなポイントです。
この形だと、
- 日付そのものがデータではなく列名になる
- 日付が増えるたびに列が増える
- 表の構造そのものが変わる
という状態になります。
データ分析では、列の構造が変わらないこと がとても重要です。ところが横持ちデータは、日付や項目が増えるたびに列が追加されるため、構造が変わりやすくなります。
その結果、
- 更新時にエラーが出る
- 自動処理が止まる
- 毎回列を追加して調整する
といったトラブルにつながりやすくなります。
つまり横持ちデータは、
- 管理しづらい
- 集計しづらい
- 自動化しづらい
という三重苦になりやすいわけです。
分析の切り口を増やしにくい
横持ちデータは、分析の切り口（軸）を増やしたいときにも不便 です。
たとえば「会社 × 日付 × 売上」の売上データでは、日付が列名になっているため、日付を1つのデータ項目として扱えません。
そのため、次のような分析がやりにくくなります。
- 曜日別の売上傾向
- 月初と月末の違い
- 週ごとの売上推移
- 特定期間の平均売上
本来なら「日付」列を使えば簡単に集計できますが、横持ちでは日付が列に固定されているため、自由に使えなくなります。
- 1/1〜1/7 の合計売上
- 1/8〜1/13 の平均売上
を計算したい場合でも、毎回横方向に複数列を選び直す必要があります。期間が変わるたびに指定し直すので、分析のたびに手間が増えてしまいます。
さらに、売上以外に
- 来店客数
- 営業時間
- 曜日
- キャンペーンの有無
といった別のデータを組み合わせたくなったときも困ります。本来なら「日付」をキーにして他のデータと結合できますが、横持ちでは日付が列名になっているため、結合しづらくなります。
このように、見る分には分かりやすくても、分析用の元データとしては横持ちデータはかなり不便 です。
横持ちデータを解消する
Power Query には、横持ちデータを扱いやすい形に変える 「ピボット解除」 という機能があります。これを使うと、横に広がったデータを 縦持ちデータへ一瞬で変換 できます。
１．Power Query エディターに該当テーブルを読み込む
まずは、変換したい表を Power Query に取り込みます。
取り込む表がテーブルになっている場合は、Excel の ［データ］タブ → ［テーブルまたは範囲から］ を選ぶと、Power Query エディターが開きます。
２．ピボット解除したい列を選択する
次に、縦に並べたい列（横に広がっている列）をまとめて選択します。たとえば、教科や日付など、横方向に増えている列を選ぶイメージです。
３．右クリック → 「列のピボット解除」を選択する
列を選択した状態で右クリックし、［列のピボット解除］ を選びます。
または、上部メニューの ［変換］タブ→［列のピボット解除］ からでも実行できます。
列が多いときは「その他の列のピボット解除」が便利
ピボット解除したい列がたくさんある場合は、逆に 残したい列だけを選ぶ 方法のほうがラクです。
2．ピボット解除しない列を選択する
変換する列ではなく、そのまま残したい列 を選択します。たとえば「生徒名」や「会社名」など、固定して残したい項目です。
３．右クリック → 「その他の列のピボット解除」を選択する
選択した状態で右クリックし、［その他の列のピボット解除］ を選びます。すると、選んでいない列がまとめてピボット解除されます。
４．見出しを分かりやすく変更する
変換後は、列名が「属性」「値」などになるので、内容が分かる名前に変更しておきます。
たとえば、
- 属性 → 教科
- 値 → 点数
のように変更すると見やすくなります。
変更後は、「生徒」「教科」「点数」 の関係がはっきりした表になります。
これで、ピボットテーブルでも自然に集計できる形になります。
売上データの場合も同じです。
横に並んでいた日付が縦に並び、「会社名」「日付」「売上」という形になります。
こうしておくと、
- 曜日
- 売上個数
- 商品名
などの列も追加しやすくなり、分析の幅がぐっと広がります。
メモ：3つの「ピボット解除」メニューの違い
Power Query には、似た名前のピボット解除メニューが3つあります。
- 列のピボット解除
- その他の列のピボット解除
- 選択した列のみをピボット解除
見た目は似ていますが、実は 内部で使われるM関数が少し違います。
違いを理解するには、実行後に作られる式を見るのが分かりやすいです。
列のピボット解除
= Table.UnpivotOtherColumns(ソース, {"生徒"}, "属性", "値")その他の列のピボット解除
= Table.UnpivotOtherColumns(ソース, {"生徒"}, "属性", "値")選択した列のみをピボット解除
= Table.Unpivot(ソース, {"国語", "数学", "英語", "理科", "社会"}, "属性", "値")この式を見ると分かるように、「列のピボット解除」と「その他の列のピボット解除」は同じ関数になっています。つまり、この2つは結果として 「指定した列以外をまとめてピボット解除する」 動きになります。
違いが出るのは後から列が増えたとき
たとえば後から 「情報Ⅰ」 という教科が追加されたとします。
この場合、
- 列のピボット解除 → 新しい列も自動で対象になる
- その他の列のピボット解除 → 新しい列も自動で対象になる
- 選択した列のみをピボット解除 → 最初に指定した列だけが対象なので、新しい列は対象にならない
という違いが出ます。
今後、ピボット解除する列が増える可能性があるなら、「列のピボット解除」または「その他の列のピボット解除」 を選ぶほうが安心です。一方で、決まった列だけを必ず対象にしたい場合 は、「選択した列のみをピボット解除」 が向いています。
- 列が増える可能性がある → 自動追従できる方法
- 対象列を固定したい → 明示的に指定する方法
という考え方で選ぶと分かりやすいです。
まとめ
横持ちデータは、見た目が整っていて確認しやすい反面、集計・分析・自動化には少し不向きなことが多いです。
特に、
- 項目が増えるたびに列が増える
- ピボットテーブルで扱いにくい
- Power Query や Power BI で自動処理しづらい
- 分析の切り口を増やしにくい
といった点で、あとから少しずつ扱いづらさが出てきます。
そんなときに便利なのが Power Query の「ピボット解除」 です。
横に並んだ項目を縦に並べ替えるだけで、
- 項目名と値の関係がはっきりする
- ピボットテーブルでそのまま集計できる
- 列（データ種類）が増えても対応しやすい
- 他のデータとも結合しやすい
というように、分析しやすい形へぐっと近づきます。
見た目は少し地味な機能ですが、横持ちデータを整える場面ではかなり出番が多いので、覚えておくと後々かなりラクになります。
見やすい表がそのまま分析しやすいとは限らないんですね。まずは形を整えるのが大事だと分かりました！