【Power Query】横持ちの表を縦持ちに変換する「列のピボット解除」— 現場が横で見たい表を作り直さずに集計する

Excel
この記事は約9分で読めます。

月や日付が横に並んだ表を集計しようとして、毎回、縦の表に貼り直していないでしょうか。生産実績や計画の表は、人が見るために横で作られます。現場は横で見たいので、集計する側が作り直すことになります。

横の表を直してもらう必要はありません。Power Query の「その他の列のピボット解除」で、縦の表を別に作ります。月の列が増えても、更新ボタン1つで縦の表に加わります。

筆者の見てきた現場でも、生産実績や計画の表は横で作られ、集計する側が毎回作り直していました。この記事は、横の表はそのまま残す前提での作り方です。

この記事で分かること

  • 横持ちと縦持ちの違いと、縦持ちだと集計しやすい理由
  • 「その他の列のピボット解除」で、横の表を縦にする手順
  • 合計の列・空欄のセルがあるときの扱い
  • 月の列が増えても壊れない指定のしかた

横の表を縦に並べ替えた結果(Power Query エディター)

どんな場面で使うか

  • 月や日付が列に並んだ生産実績・計画・点検の表を、月別や品目別に集計したい
  • 集計のたびに、横の表を縦の表へ手で貼り直している
  • 現場に「縦の形で作ってほしい」と頼めない、または頼んでも戻ってしまう

横の表は、人が見るには分かりやすい形です。1行で1品目の推移が追えます。現場が横の形を手放さないのは、それが見る用途に合っているからです。

困るのは集計するときです。月が列に分かれていると、月を1つの項目として扱えません。そこで、見る表と集計する表を分けて持ちます。

基本説明

横持ちと縦持ち

形 1行に入っているもの 向いている用途
横持ち 1品目の全部の月 人が見る・推移を追う
縦持ち 1品目の1か月ぶん 集計する・ピボットテーブルに渡す

縦持ちでは、「月」が1つの列になります。ピボットテーブルやSUMIFSで、月を条件や見出しに使えます。

使うデータ

サンプルファイル(記事末尾)の「生産実績」シートです。6品目の4月〜6月の生産数が横に並び、右端に「合計」の列があります。表は A1:F7 で、テーブル(名前は「生産実績」)にしてあります。

元データ「生産実績」シート(横持ちの表)

月のマスは18個あり、そのうち3つが空欄です。空欄は、その月に作らなかった品目です。

「その他の列」を指定する理由

ピボット解除では、縦にする列か、そのまま残す列のどちらかを指定します。「その他の列のピボット解除」は、残す列(品目・ライン)を指定する方法です。

ピボット解除の式には、月の名前が入りません。そのため、あとから7月の列を足しても、7月も縦になります。ただし、エディターを開いたときに自動で入る型の設定には、月の名前が入ります。月の列を消したり、見出しを書き換えたりすると、そこで止まります(直し方はよくあるエラーで扱います)。

手順

ステップ1: テーブルから Power Query エディターを開く

  1. 「生産実績」シートの表の中のセルを1つ選ぶ
  2. 「データ」タブ →「テーブルまたは範囲から」を押す

エディターを開いた直後(横の表のまま)

Power Query エディターが開き、横の表がそのまま出ます。

ステップ2: 合計の列を消す

  1. 「合計」列の見出しを右クリックする
  2. 「削除」を選ぶ

合計の列を残すと、ステップ3で「合計」も月と同じように縦になり、数量が2倍に数えられます。

ステップ3: 「その他の列のピボット解除」で縦にする

  1. 「品目」列の見出しをクリックする
  2. Ctrl キーを押しながら「ライン」列の見出しをクリックする
  3. 「変換」タブ →「列のピボット解除」の▼ →「その他の列のピボット解除」を選ぶ

その他の列のピボット解除をした直後(属性・値の列)

月の名前が「属性」列に、生産数が「値」列に入ります。数式バーには次の式が出ます。

= Table.UnpivotOtherColumns(#"削除された列", {"品目", "ライン"}, "属性", "値")

式に書かれている列名は、品目とラインだけです。

行は15行です。18マスのうち空欄の3つは、行になりません。

ステップ4: 列名と型を整える

  1. 「属性」の見出しをダブルクリックし、「月」と入力して Enter キーを押す
  2. 同じように、「値」を「数量」に変える
  3. 「数量」列の見出しの左端のアイコンを押し、「整数」を選ぶ

冒頭の画像の状態になります。列は品目・ライン・月・数量の4つです。

ステップ5: シートに読み込む

  1. 「ホーム」タブ →「閉じて読み込む」を押す
  2. 新しいシートにできた表の「数量」列を選び、画面右下の合計を見る

表は15行、数量の合計は18,050です。

横の表は、何も変えずに残っています。現場はこれまでどおり横の表に書き、集計する側は縦の表を使います。

サンプルコードまたは例

例1:縦の表をピボットテーブルで集計する

  1. ステップ5で読み込んだ表のセルを1つ選ぶ
  2. 「挿入」タブ →「ピボットテーブル」を押し、出た画面で「OK」を押す
  3. 右のフィールドリストで、「ライン」を「行」へ、「月」を「列」へ、「数量」を「値」へドラッグする

ライン別・月別に集計したピボットテーブル

1ラインは4月から順に2,000・1,650・2,500、2ラインは4,100・4,350・3,450です。総計は18,050で、ステップ5の合計と合います。

横の表のままだと、月が別々の列なので「月」をピボットテーブルの列に置けません。

例2:7月の列を足して更新する

現場が横の表に7月の列を足した、という場面です。7月の生産数は、サンプルの「7月分」シートに入れてあります。

  1. 「生産実績」シートで、「合計」列のセルを右クリックし、「挿入」→「テーブルの列 (左)」を選ぶ
  2. 「7月分」シートの B1:B7 をコピーする
  3. 「生産実績」シートに戻り、足した列の見出しのセル(F1)に貼り付ける
  4. 「データ」タブ →「すべて更新」を押す

7月の列を足して更新したあとの縦の表

縦の表は21行になり、数量の合計は24,850です。クエリには手を入れていません。

ピボットテーブルに7月を出すには、ピボットテーブルの中で右クリックし、「更新」を選びます。

例3:月の列を名前で指定するとどうなるか

ステップ3で、月の列(4月・5月・6月)を選んで「選択した列のみをピボット解除」を使うと、式は次のようになります。

= Table.Unpivot(#"削除された列", {"4月", "5月", "6月"}, "属性", "値")

月の名前が式に書かれます。このまま例2のように7月を足すと、7月は縦にならず、列のまま残ります。

月を名前で指定したクエリに7月を足した結果(7月が列のまま)

行は15行のままで、7月の数量は合計に入りません。エラーは出ないので、気づきにくい形です。

動作確認

ステップ3の式が手元で動くかは、次の順に確かめます。

  1. サンプルファイルを開き、ステップ1〜5を行う
  2. 読み込んだ表の行数と、「数量」列の合計を見る
  3. 例2の手順で7月の列を足し、「すべて更新」を押す
  4. もう一度、行数と「数量」列の合計を見る

成功すると、次のようになります。

  • 手順2で、表は15行、列は品目・ライン・月・数量の4つ、数量の合計は18,050
  • 「月」列の値は 4月・5月・6月 の3種類で、数量に null の行が無い
  • 手順4で、表は21行、数量の合計は24,850、「月」列に7月が加わる

例3の式を試すときは、ステップ3の式を例3の式に差し替えます。手順4で、表は15行のまま、7月が列として残れば想定どおりです。

よくあるエラー

数量の合計が2倍になる

原因:「合計」の列を消さずにピボット解除した。「合計」が月と同じように縦になり、行が21行・合計が36,100になる。

直し方:「適用したステップ」で、ピボット解除の前に「合計」の列を削除するステップを入れる。

7月の列を足したのに、縦の表に7月が出ない

原因:更新していない、または月の列を名前で指定してピボット解除した(例3)。

見分け方:「適用したステップ」でピボット解除のステップを選び、数式バーに月の名前が書かれているかを見る。

直し方:そのステップを消し、「品目」「ライン」を選んで「その他の列のピボット解除」をやり直す。

月の列を消したり見出しを変えたりしたら「列が見つからない」というエラーが出る

原因:エディターを開いたときに自動でできる最初の「変更された型」に、4月〜6月の列名が書かれている。年度が替わって4月の列を消したり、「4月」を「4月実績」のように書き換えたりすると、そこで止まる。

直し方:「適用したステップ」の最初の「変更された型」を、左の×で消す。数量の型はステップ4で決めているので、消しても困らない。「合計」の列を消した場合は、「変更された型」を消すと、次の「削除された列」のステップで止まる。このステップも左の×で消す。

行の数が「品目の数×月の数」より少ない

原因:空欄のマスは、ピボット解除で行にならない。サンプルでは18マスのうち3つが空欄なので15行になる。

直し方:数量の合計は変わらないので、そのままでよい。件数を元の表と突き合わせるときは、空欄を除いて数える。

まとめ

  • 横持ちは見る用、縦持ちは集計する用。横の表は作り直さず、縦の表を Power Query に作らせる
  • 残す列(品目・ライン)を選んで「その他の列のピボット解除」を使う。月の名前が式に入らないので、月が増えても壊れない
  • 合計の列は、ピボット解除の前に消す
  • 空欄のマスは行にならない。件数を突き合わせるときは空欄を除く
  • 月が増えたら、横の表に列を足して「すべて更新」を押すだけ

関連記事

対応バージョン: Microsoft 365/Excel 2019 以降

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました