導入
売上管理や予算実績管理、月次報告のExcel管理表で、毎月新しい月が始まるたびに列を1つずつ右に追加していて、表がどんどん横に長くなって見づらくなっていませんか。3〜50人で扱う売上や実績の管理表は、月をまたぐたびに「2024年5月」「2024年6月」と列を増やしていくと、半年もすれば横スクロールが必須になり、ピボットや集計関数の指定範囲もずれて壊れやすくなります。
この記事では、月別列を増やし続けるのではなく、対象月列と金額列の2列にまとめた縦持ち構造に整える見直し手順を紹介します。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 月が増えるたびに列が増える |
| 主な原因 | 年月を列として持っている |
| 解決方法 | 対象月列と金額列の縦持ち構造にする |
| 対象業務 | 売上管理・予算実績管理・月次報告 |
| 対象人数 | 3〜50人 |
| 難易度 | ★★☆☆☆ |
| 作成時間 | 45分 |
| 効果 | 月が増えても表が壊れにくい |
| 向かないケース | 印刷専用の帳票 |
この記事は管理表をいきなり大きく作り替えるのではなく、月別の横持ち構造を「対象月」と「金額」の2列に組み替えて、データを縦に積む形に整理することを目的にしています。45分程度を見ておくと安心です。
なぜその管理表はうまくいかないのか
月を列として持つ横持ち構造は、表を「目で見て確認する」用途では分かりやすいのですが、月が増えるたびに毎回列を追加する作業が発生します。列を増やすと、集計関数の範囲や、ピボットの参照、条件付き書式の範囲もそのつど直す必要があり、メンテナンスが追いつかなくなります。
入力する人にも、毎月「どの列に書けばよいのか」を探す手間が発生します。列ヘッダーが「24/4」「24年4月」「2024年4月」など担当者ごとにバラついていると、書き先のずれや表記ゆれが起きます。確認する人も、「今年の累計だけ」「先月だけ」を抜き出そうとすると、月別列をひとつずつ拾う作業が必要になります。
これは表の作り方が「人が紙で見るレイアウト」のままなのが原因です。データとして扱うなら、対象月を縦軸に積み、金額を1列で持つ形のほうが、集計・抽出・追加に強くなります。
改善手順
ステップ1. いまの横持ち構造を棚卸しする
最初に、現状の月別列を一覧で書き出します。列ヘッダーの表記、いつから列を追加したか、合計列や前年比列など派生列がどこにあるかを把握しておきます。あわせて、この表を毎月使う人と、それぞれの確認したい単位（年累計／月単独／前年比 など）も洗い出します。
ステップ2. 縦持ち構造の新シートを用意する
別シートに「対象月」「金額」「項目」「担当」「区分」など、必要な列だけを並べた縦持ちの空テーブルを用意します。1行1月1項目の形にし、対象月は yyyy/mm/01 のような日付値で持ちます。後でフィルタや並び替えがしやすくなります。
ステップ3. 既存データを縦持ちに移し替える
横持ちの各月列から、対象月・項目・金額を抜き出して縦持ちシートに転記していきます。Power Queryのアンピボット機能や、Excelのコピー＆ペースト＋手作業で対応可能です。データ量が多い場合は項目ごとに分けて進めると安全です。
ステップ4. 集計用のピボットや関数を縦持ち前提に組み直す
縦持ちデータをもとに、ピボットテーブルや SUMIFS で月別・項目別の集計を組み直します。新しい月のデータは表の下に行を足していくだけで自動的に集計に反映されます。横持ちのときに必要だった「列追加」作業がなくなります。
ステップ5. 入力の入り口を縦持ち側に一本化する
横持ち表を残す場合は、表示専用と割り切り、入力は必ず縦持ち側に行うルールにします。横持ち表が必要なときは縦持ちデータからピボットで自動生成する形にし、二重入力にならない構造にします。
Before / After
| 観点 | Before | After |
|---|---|---|
| 課題 | 月が増えるたびに列が増える | 行を足すだけで対応 |
| 原因 | 年月を列として持っている | 対象月列と金額列で縦に積む |
| 運用 | 毎月列追加と数式範囲修正 | 行追加のみ |
| 確認 | 月別列をひとつずつ拾う | ピボットや関数で抽出 |
| 効果 | 列が増えるたびに表が崩れる | 月が増えても表が壊れにくい |
縦持ち構造に直すと、月が増えても列構造は変わらないため、関数やピボットを毎月修正する必要がなくなります。
実務での注意点
- 印刷専用の帳票には向かない：上司やクライアントに紙で月別の表を提出することがゴールの帳票は、見た目重視で横持ちを残したほうがよい場合があります。
- 縦持ちと横持ちを両方残す場合は、入力の入り口を1つに絞る
- 対象月は文字列ではなく日付値で持つと、並び替え・抽出が安定する
- 既存データの移行はカテゴリ単位で分けて進めると、途中で止まっても再開しやすい
- ピボットや SUMIFS は範囲をテーブル機能で指定すると、行追加に自動追従する
- 縦持ちになると見た目が「データの羅列」になるため、確認用には別シートで集計表を作る
Web化・スプレッドシート化との関係
Excel改善で足りる場合
3〜50人で売上や実績を集計しているケースでは、本記事の縦持ち構造とピボットテーブルの組み合わせで十分です。年単位で件数が増えても、列構造が固定なので集計関数が壊れません。
スプレッドシート化・Web化を考える場合
複数拠点でリアルタイムに入力する、外部システムから自動で月次データを取り込みたい、BIツールでダッシュボード化したい、といった要件が出てきた場合はスプレッドシートやkintone・AppSheetなどへの移行が選択肢になります。これらのツールも縦持ちデータを前提にしているため、Excelの段階で縦持ちに整えておくと、移行時の手戻りが少なくなります。
ツールを変える前に、本記事の「対象月列と金額列の縦持ち」整理をしておくと、Excelを続ける場合にも別ツールへ移る場合にも、そのまま使える土台になります。
まとめ
月を列として持っていると、月が増えるたびに列追加と数式修正が発生し、表が壊れやすくなります。対象月列と金額列の縦持ち構造に組み替え、入力の入り口を一本化するだけで、月が増えても表が壊れにくくなり、集計や抽出もピボットで楽になります。
