導入
予算実績管理表を作ったとき、4月・5月・6月…と月ごとに列を増やしていったら、年度末には列が30本以上に膨れ上がり、ピボット範囲を毎回再設定しないと集計できない。新しい部門を追加したら別シートをコピーして、結局5シートそれぞれを毎月更新する羽目になった、というケースはありませんか。
これは設計が古いのではなく、年月を「列」として持つ横持ち構造が拡張に弱いことが原因です。本記事では、年月を1つの列にまとめて1行1実績の縦持ち構造に変え、ピボットと月別集計が安定する管理表に整える手順をまとめます。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 月が増えるたびに列が増えて集計が壊れる |
| 主な原因 | 年月を列として持っている |
| 解決方法 | 年月列を作り1行1実績の縦持ち構造にする |
| 対象業務 | 売上管理・予算実績管理・月次報告 |
| 対象人数 | 3〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 30分 |
| 用意するもの | 対象のExcelファイル／編集権限 |
| 効果 | 月別集計やピボットが安定する |
| 向かないケース | 印刷だけが目的の帳票 |
縦持ちは「データが下に伸びる」形なので、月を追加するたびに表構造を変える必要がなくなります。ピボット範囲を列全体（A:E）で指定しておけば、新しい月の追加は1行追加で済みます。
なぜその管理表はうまくいかないのか
- 月ごとに列を追加していて、列数が制御不能になっている
- 部門×月の二次元表になっており、ピボットが「部門の縦」「月の横」を認識できない
- 新年度に切り替わったら表全体を作り直している
- 月別集計のたびにSUMが「先月〜今月」の参照範囲をズラさないと動かない
- 過去月と当月のフォーマットがズレて統一感がない
担当者の操作ミスではなく、表の構造が拡張に向いていないことが原因です。見直しは、年月を行の値として持つ縦持ち構造へ転換するところから始めます。
完成イメージ
直す前 — 横持ち（年月が列）:
| 部門 | 2024-04 | 2024-05 | 2024-06 | 2024-07 |
|---|---|---|---|---|
| 営業 | 1,200 | 1,500 | 1,800 | 2,100 |
| 経理 | 800 | 900 | 950 | 1,000 |
新しい月を追加するたびに列が増え、ピボット範囲を毎回広げる必要がある。
直した後 — 縦持ち（年月が列の値）:
| 年月 | 部門 | 金額 |
|---|---|---|
| 2024-04 | 営業 | 1,200 |
| 2024-04 | 経理 | 800 |
| 2024-05 | 営業 | 1,500 |
| 2024-05 | 経理 | 900 |
| 2024-06 | 営業 | 1,800 |
| 2024-06 | 経理 | 950 |
新しい月の実績は下に1行ずつ追加するだけで完了。ピボットは「行＝部門／列＝年月／値＝金額」で月次推移と部門比較を同時に出せます。
改善手順
ステップ1. 年月列を1つ作る
縦持ち構造の中心となる「年月」列を1つ用意します。
操作: 新しい縦持ち表のA列を「年月」とし、書式を yyyy-mm（テキスト）または日付値（2024-04-01）に統一する。テキストと日付の混在が一番のトラブル要因なので、最初に決める。
記入例:
| 列名 | 形式 | 例 |
|---|---|---|
| 年月 | テキスト | 2024-04 |
| 年月（代替案） | 日付値（月初） | 2024-04-01 |
ステップ2. 1行1実績の縦持ちに並べ替える
横持ちの1セル＝1実績を、縦に展開します。
操作: 縦持ち表のB列以降に「部門」「金額」などの軸を並べ、横持ちの行×列のクロス値を、1行ずつA〜C列に書き出す。少量なら手作業、量が多い場合はPower Query（「データ」→「データの取得と変換」→「列のピボット解除」）で自動化する。
記入例:
| 年月 | 部門 | 金額 |
|---|---|---|
| 2024-04 | 営業 | 1,200 |
| 2024-04 | 経理 | 800 |
| 2024-05 | 営業 | 1,500 |
✗悪い例: 年月を残したまま縦持ち列も並行運用 → 同じ値を二重管理 ◎良い例: 縦持ちに完全移行し、横持ちはピボットで再現
ステップ3. 過去データを縦持ちに変換する
過去月分の横持ちデータを、ステップ2の形式に変換します。
操作: 横持ち表をテーブル化（Ctrl+T）→「データ」→「データの取得と変換」→「テーブル/範囲から」→Power Queryエディタで月の列を選択→「変換」→「列のピボット解除」を実行。属性列が「年月」、値列が「金額」になる。
記入例（Power Query前後）:
| 操作前（横持ち） | 操作後（縦持ち） |
|---|---|
| 部門／2024-04／2024-05 の3列 | 部門／属性／値 の3列。属性に年月、値に金額 |
ステップ4. 集計はピボットや関数で組み直す
縦持ちになったら、横持ちと同じ見た目の集計はピボットで再現します。
操作: 縦持ち表を選択→「挿入」→「ピボットテーブル」→行ボックスに「部門」、列ボックスに「年月」、値ボックスに「金額」をドロップ。横持ちと同じレイアウトが瞬時に出る。
記入例: ピボットレイアウト
| 部門 \ 年月 | 2024-04 | 2024-05 | 2024-06 |
|---|---|---|---|
| 営業 | 1,200 | 1,500 | 1,800 |
| 経理 | 800 | 900 | 950 |
ステップ5. 表示用シートはピボットで作る
報告書や閲覧用の表が必要なら、ピボットそのものを表示用に使うか、別シートにGETPIVOTDATAで参照して固定レイアウトに整えます。
操作: 表示用シートのセルに =GETPIVOTDATA("金額",ピボット範囲!$A$3,"部門","営業","年月","2024-04") のように参照式を入れる。月が増えたらピボットを更新すれば表示用シートも自動反映される。
実務での注意点
- 印刷だけが目的の帳票（紙で配る決算書、報告書など）は横持ちのままで構いません。CSV化やピボット集計の対象外なので。
- Power Queryでの変換は1回設定すれば、データ更新時に「すべて更新」で再実行されます。手動変換だと差分が出ます。
- 年月の書式（テキスト vs 日付値）はファイル全体で統一します。混在するとピボットで月が分裂します。
- 縦持ち構造に慣れていない閲覧者には「ピボットで見る」「表示用シートで見る」の2通りを用意します。raw を見せると「分かりにくい」と言われます。
- 部門マスタや勘定科目マスタなど、縦持ちと組み合わせるマスタはあらかじめ別シートに整備します。
まとめ
月が増えるたびに列が増える原因は、年月を列として持っている横持ち構造です。年月を1列にまとめた縦持ちに整えれば、月の追加は1行追加で済み、ピボットも安定して動きます。
次にやることは、対象ファイルで「年月の列がいくつあるか」を数えることです。3カ月以上の列が横並びになっていれば、Power Queryで縦持ち変換を試す価値があります。あわせて、1行1データに整える基本は1行1データに整える手順、ピボット前提のデータ整理全般はピボット用にテーブルを整える手順も参考になります。
