Excel管理表で前年同月比に加工が必要な原因。年度列と月列で比較する方法

導入
この記事で解決すること
なぜその管理表はうまくいかないのか
改善手順
Before / After
実務での注意点
Web化・スプレッドシート化との関係
1. Excel改善で足りる場合
2. スプレッドシート化・Web化を考える場合
まとめ

導入

売上管理や予算実績管理、経営報告のExcel管理表で、前年同月比のレポートを作るたびに、昨年のシートを開いて月をそろえて、別シートで突き合わせて……と毎回加工をしていませんか。5〜100人で月次の数字を見ている組織では、季節変動の影響を捉えるために前年同月比は欠かせない指標ですが、年度と月の扱いが整理されていない管理表だと、毎月の集計が面倒な手作業になります。

この記事では、年度列と月列を別々に持たせて前年同月比較がしやすい構造に整え、季節変動を確認しやすくする見直し手順を紹介します。

この記事で解決すること

項目	内容
解決する課題	前年同月比を作るたびに加工が必要
主な原因	年度と月の扱いが整理されていない
解決方法	年度列と月列を持たせ前年同月で比較する
対象業務	売上管理・予算実績管理・経営報告
対象人数	5〜100人
難易度	★★★☆☆
作成時間	60分
効果	季節変動を確認しやすい
向かないケース	過去比較が不要な表

この記事は管理表をいきなり大きく作り替えるのではなく、対象月の持ち方に「年度」と「月」を分けて持たせ、前年同月の値を関数やピボットから引き当てやすくする整理を目的にしています。60分程度を見ておきます。

なぜその管理表はうまくいかないのか

前年同月比の作成が毎月手作業になる原因は、年度と月が同じセルに混ざっていたり、シートが年度ごとに分かれていたりすることです。「2024/4」「2024年4月」のような表記で持っていると、前年同月(2023/4)を引き当てるための関数の組みづらさは変わりません。年度ごとにファイルやシートを切っている場合は、そもそも別ファイルを開かない限り比較ができません。

入力する人にも、「対象月をどう書けばよいか」「年度はどの単位(暦年・会計年度)か」が定義されていないと、データの突合が崩れます。確認する人も、毎月「前年同月のデータはどこに保存されていたか」を探すところからになりがちです。

担当者の意識ではなく、年度と月をひとつのセルに丸めて持っている、または年度ごとにデータが分断されていることが原因です。前年同月比を継続的に出すには、同じ表の中に年度列・月列を分けて持たせ、前年は「年度−1」、月は同じ、という形で機械的に引き当てられるようにする必要があります。

改善手順

ステップ1. 年度の単位を決める

最初に、対象業務で扱う「年度」が暦年(1〜12月)なのか、会計年度(例: 4〜3月)なのかを決めます。会計年度の場合は、月の並びが4月から始まる前提で集計するため、月列の値の解釈に注意が必要です。決まったら、その定義を管理表の上部に1行メモします。

ステップ2. 年度列と月列を新設する

対象月を1つの日付値(yyyy/mm/01)で持っている場合は、そこから派生する形で年度列(YEAR や IF で算出)と月列(MONTH で算出)を作ります。日付値が無い場合は、年度・月を別列として直接入力する形に切り替えます。

ステップ3. 縦持ち構造に整える

前年同月比の引き当ては、データが縦持ちで揃っていることが前提です。年度別にシートが分かれている場合は、1つのシートに統合し、年度列で識別する形にします。データが多い場合は項目ごとに段階的に統合します。

ステップ4. 前年同月の値を引く列を作る

SUMIFS で「年度が当年−1、月が同じ、項目が同じ」の合計を引いてくる列を1つ作ります。式は当月の年度から1を引いた値を参照するだけで、毎月のデータが入れば自動的に前年同月の値が出ます。差分や%は、その列をもとにさらに計算列を作るだけで済みます。

ステップ5. 前年がないデータの表示を決める

新規追加項目、廃止項目、年度初年度などは、前年同月のデータが存在しません。その場合の表示を「N/A」「-」「対象外」など1つに統一しておきます。空欄のまま放置するとレポート側で「0」と誤解されることがあるため、明示的に値を入れておきます。

Before / After

観点	Before	After
課題	前年同月比を作るたびに加工が必要	関数とピボットで自動算出
原因	年度と月の扱いが整理されていない	年度列と月列が分かれている
運用	年度ごとにシートを開いて突合	1つの表で完結
確認	季節変動が遅れて発見される	季節変動が一覧で見える
効果	前年同月の確認に時間がかかる	季節変動を確認しやすい

年度列と月列を分けるだけで、前年同月比の引き当てが SUMIFS 1本で済むようになり、毎月の集計作業が大幅に減ります。

実務での注意点

過去比較が不要な表には向かない：単発のリストや、年度をまたいで比較する必要がない管理表に年度列を持ち込む必要はありません。
年度の定義(暦年か会計年度か)を1行で明文化しておく
年度初年度や新規項目で前年同月データが無いときの表示ルールを決めておく
年度をまたぐデータ移行時に、項目名の統廃合や定義変更があった場合は注釈を残す
前年同月比は %差・額差・指数(前年=100)のどれを使うかを業務に合わせて1つに絞る
過去データの修正があった場合は、前年同月比に影響するため修正日と内容を別途記録する

Web化・スプレッドシート化との関係

Excel改善で足りる場合

5〜100人で月次の数字を扱い、過去2〜3年分のデータをExcelで持てる規模なら、本記事の年度列・月列構成と SUMIFS で十分に運用できます。ピボットテーブルの「前年比」機能を併用すると、レポート作成も短時間で済みます。

スプレッドシート化・Web化を考える場合

過去データが数万行を超える、複数事業部の前年同月比を1画面でモニタリングしたい、BIツールで定点観測ダッシュボードを作りたい、といった要件がある場合はスプレッドシートやBIツールへの移行が選択肢になります。これらのツールも年度・月が分かれていることを前提にするため、Excelの段階で構造を整えておくと移行が滑らかです。

ツールを変える前に、本記事の「年度列と月列で前年同月比較できる形にする」整理をしておくと、Excelを続ける場合にも別ツールへ移る場合にも、同じ集計軸がそのまま使えます。