月別の列をピボット解除で縦に並べる

LWP | 月別の列をピボット解除で縦に並べる

月別の列をピボット解除で縦に並べる

図解1|月が増えるたび列が増えていく

商品ごとの月別金額を横に並べた表と、一行が商品と月の組になる縦長の表の違い

図1|月の追加で横へ広がる表を、集計しやすい行の形へ組み替える。

解説1|月が増えるたび列が増えていく

ストーリー

コイ丸先輩「来月も右へ一列足すつもりなんだ。月が増えるたびに、集計の式も見直しているよ。」

シャケモン「月を列名ではなく、行の値にできます。どの列を残すかから考えましょう。」

全国規模の中華食材卸・淡水社の、商品別・月別受注金額という架空の集計例を使う。金額は税・送料・値引きを含めない受注額で、売上確定額とは区別する。今回はP001が干ししいたけ500g、P002が豆板醤1kgである。

入力例

商品コード

8月

9月

P001

7200

3600

P002

3200

1600

単位は円。一行が一商品を表し、月ごとの金額が別の列に入る。元の集計表を残して、Power Queryで一行が商品と月の組になる表を作る。10月の追加は、まず現在の2か月を変換してから試す。

図解2|商品と月と受注金額の関係を分ける

横長表の二つの商品と二つの月を、商品コードと月と受注金額の4行へ対応させる図

図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|月の列名を月の値にする

列名8月と9月が月列の値になり、その交点の金額が受注金額列に移る対応

図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|翌月の列を追加して結果と合計を確かめる

10月列に2400円と800円を追加し、6行18800円になる更新の確認

図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|メモ列と欠損を含めてよいか判断する

月以外のメモ列が誤って変換される場合と、nullと0では変換後の行が異なることを示す図

図6|自動的に列を含めることと、入力が正しいことは別である。

解説6|メモ列と欠損を含めてよいか判断する

ストーリー

コイ丸先輩「右側にメモ列も足したくなったよ。これも月として取り込まれてしまうのかな。」

シャケモン「商品コード以外を解除する設定なら、メモも対象になります。固定する列や、取り込む列の条件を見直しましょう。」

商品コード以外を変換する設定でメモ列を増やすと、ピボット解除直後には、月列に「メモ」、受注金額列にその文章が現れる。図6は数値型に変更する前の状態を示している。その後、受注金額列を数値型に変換すると、メモの文章が入ったセルは変換エラーになる。列の追加を自動で拾う設定でも、何が増えてよいかという条件は必要である。メモも固定属性として残すか、月の列だけを対象にするかを、使う表の目的から決める。

欠損も別に確認する。金額がnullのセルはピボット解除後の行として残らず、数値の0は0円の行として残る。元の商品数×月数が必ず結果の行数になるとは考えない。不明な金額を、理由なく0へ変えて行数を合わせない。

まとめと今回の仕掛け

固定する属性、列名から作る月、セルから取る受注金額を対応させた。更新時は列の構成、金額の型、行数と合計、nullと0の意味を確認する。

出典

『図解と実習で学ぶ Power Query GUI』V052、7.2.1〜7.2.3「列のピボット解除」。Microsoft公式 ピボット解除。教材の数値は独立した架空例。