LWP | 月別の列をピボット解除で縦に並べる
月別の列をピボット解除で縦に並べる
図解1|月が増えるたび列が増えていく

図1|月の追加で横へ広がる表を、集計しやすい行の形へ組み替える。
解説1|月が増えるたび列が増えていく
ストーリー
コイ丸先輩「来月も右へ一列足すつもりなんだ。月が増えるたびに、集計の式も見直しているよ。」
シャケモン「月を列名ではなく、行の値にできます。どの列を残すかから考えましょう。」
全国規模の中華食材卸・淡水社の、商品別・月別受注金額という架空の集計例を使う。金額は税・送料・値引きを含めない受注額で、売上確定額とは区別する。今回はP001が干ししいたけ500g、P002が豆板醤1kgである。
入力例
商品コード |
8月 |
9月 |
|---|---|---|
P001 |
7200 |
3600 |
P002 |
3200 |
1600 |
単位は円。一行が一商品を表し、月ごとの金額が別の列に入る。元の集計表を残して、Power Queryで一行が商品と月の組になる表を作る。10月の追加は、まず現在の2か月を変換してから試す。
図解2|商品と月と受注金額の関係を分ける

図2|変換後に一行が何を表すかを先に決める。
解説2|商品と月と受注金額の関係を分ける
ストーリー
シャケモン「結果は商品コード・月・受注金額の3列です。P001の8月と9月は、別の行になります。」
コイ丸先輩「商品の行は2行のままではなく、商品と月の組で4行になるんだね。」
横長表の月名は列の見出しに入っている。ピボット解除では、その列名を月列の値へ移し、各セルの金額を受注金額列へ移す。商品コードは各行に繰り返して持たせる。
期待する結果
商品コード |
月 |
受注金額 |
|---|---|---|
P001 |
8月 |
7200 |
P001 |
9月 |
3600 |
P002 |
8月 |
3200 |
P002 |
9月 |
1600 |
今回はすべての金額セルに数値が入っているため、2商品×2か月で4行になる。元表の合計も結果の合計も15600円である。行が増えることと、金額が重複して増えることは別であり、この変換では同じ金額セルを一つの行へ移している。
図解3|月の列名を月の値にする

図3|固定する商品、列名から作る月、セルから取る金額を追う。
解説3|月の列名を月の値にする
ストーリー
コイ丸先輩「P001の9月なら、商品コードP001と、列名9月と、金額3600を一緒に取り出すわけだ。」
シャケモン「はい。この三つで、変換後の一行が決まります。」
元表でP001の行と9月の列が交わるセルは3600円である。ピボット解除後は、商品コードP001・月9月・受注金額3600という一行になる。同じように、残りの三つの金額セルも行へ移る。
変換する情報
元表での位置 |
変換後の役割 |
|---|---|
商品コード列の値 |
どの商品かを示す値として残す |
8月・9月という列名 |
月列の値になる |
月列の金額セル |
受注金額列の値になる |
月列の「8月」「9月」は元の列名から作った文字列であり、この操作だけで日付の値になるわけではない。今回は仕組みの確認に絞り、文字列の月を使う。年をまたぐ実務表では、対象年を区別できる表現や日付への変換条件も必要になる。
図解4|固定列を選んでその他の列を解除する

図4|残す列を指定する方法と、変換する列を直接指定する方法を区別する。
解説4|固定列を選んでその他の列を解除する
ストーリー
シャケモン「今回は商品コード列を選んで、『その他の列のピボット解除』を使います。」
コイ丸先輩「8月と9月を直接選ぶ方法とは、翌月の列が増えたときの対象が違うんだね。」
Excelの入力範囲を見出し付きのテーブルにし、Power Queryへ読み込む。商品コード列を選び、変換メニューから「その他の列のピボット解除」を選ぶ。生成された属性列を月、値列を受注金額へ変更し、金額列の型を数値にそろえる。
選び方の違い
選ぶ列 |
操作 |
対象の考え方 |
|---|---|---|
8月・9月 |
列のピボット解除 |
指定した月列を変換する |
商品コード |
その他の列のピボット解除 |
商品コード以外の列を変換する |
今回の入力では、商品コード以外がすべて月の金額列である。翌月も同じ形の月列だけが増えるという条件なら、後者で新しい列も対象にできる。これから増える列を無条件に月と扱ってよいわけではなく、入力表の構成を合わせて確認する。
図解5|翌月の列を追加して結果と合計を確かめる

図5|列の追加後は、入力テーブルとクエリ結果の両方を確認する。
解説5|翌月の列を追加して結果と合計を確かめる
ストーリー
コイ丸先輩「10月はP001が2400円、P002が800円だよ。右へ列を足したら、更新してみよう。」
シャケモン「6行で18800円になりました。新しい月が結果に入っています。」
練習用テーブルに10月列を追加し、P001へ2400、P002へ800を入れて更新する。新しい列がExcelテーブルの範囲に含まれることも確認する。表の外側へ見た目だけ列を足しても、読み込み範囲に含まれるとは限らない。
更新前後の期待結果
観点 |
2か月の入力 |
10月追加後 |
|---|---|---|
商品数 |
2 |
2 |
月数 |
2 |
3 |
数値の金額セル |
4 |
6 |
変換後の行数 |
4 |
6 |
受注金額合計 |
15600円 |
18800円 |
増えた金額は2400+800=3200円。15600+3200=18800円となる。結果が4行のままなら、変換対象を元の二列に固定していないか、前のステップで新列を除いていないかを調べる。試験後は元の2か月へ戻し、4行・15600円へ戻ることも確かめる。
図解6|メモ列と欠損を含めてよいか判断する

図6|自動的に列を含めることと、入力が正しいことは別である。
解説6|メモ列と欠損を含めてよいか判断する
ストーリー
コイ丸先輩「右側にメモ列も足したくなったよ。これも月として取り込まれてしまうのかな。」
シャケモン「商品コード以外を解除する設定なら、メモも対象になります。固定する列や、取り込む列の条件を見直しましょう。」
商品コード以外を変換する設定でメモ列を増やすと、ピボット解除直後には、月列に「メモ」、受注金額列にその文章が現れる。図6は数値型に変更する前の状態を示している。その後、受注金額列を数値型に変換すると、メモの文章が入ったセルは変換エラーになる。列の追加を自動で拾う設定でも、何が増えてよいかという条件は必要である。メモも固定属性として残すか、月の列だけを対象にするかを、使う表の目的から決める。
欠損も別に確認する。金額がnullのセルはピボット解除後の行として残らず、数値の0は0円の行として残る。元の商品数×月数が必ず結果の行数になるとは考えない。不明な金額を、理由なく0へ変えて行数を合わせない。
まとめと今回の仕掛け
固定する属性、列名から作る月、セルから取る受注金額を対応させた。更新時は列の構成、金額の型、行数と合計、nullと0の意味を確認する。
出典
『図解と実習で学ぶ Power Query GUI』V052、7.2.1〜7.2.3「列のピボット解除」。Microsoft公式 ピボット解除。教材の数値は独立した架空例。
