Excelで売上や利益、アクセス数などを管理していると、前年と比べてどれだけ増減したのかを確認したくなる場面があります。
前年比は、単なる差額ではなく、前年の実績を基準にした変化の割合を示す重要な指標です。
ただし、数式の分母を間違えたり、前年の値がゼロのときにエラーが表示されたりすると、表が読みにくくなるかもしれません。
この記事では、前年比の基本的な計算式、IFERROR関数を使ったエラー処理、パーセント表示、条件付き書式、グラフによる見せ方までを順番に解説します。
前年比の基本は、当年の値から前年の値を引き、その結果を前年の値で割ることです。
増減率を求める数式は、当年÷前年－1でも計算できます。
表示形式をパーセントに整え、増加と減少を色分けすると、報告資料としても読みやすくなります。
1行目に見出しが入力されている表を例に、実務でそのまま使える方法を確認していきましょう。
エクセルで前年比を計算する数式
それではまず、前年比を正しく求める基本の数式について解説していきます。
| 商品名 | 前年売上 | 当年売上 | 前年比 |
|---|---|---|---|
| 商品A | 1,200,000 | 1,380,000 | 15.0% |
| 商品B | 850,000 | 765,000 | -10.0% |
| 商品C | 500,000 | 550,000 | 10.0% |
前年比と前年同月比の計算式
前年比を増減率として表示したい場合は、前年の数値を基準にして当年の増減額を割ります。
前年比の増減率を求める数式です。
=(C2-B2)/B2
この例では、B列が前年売上、C列が当年売上、D列が前年比の列です。
商品Aでは、1,380,000から1,200,000を引くと180,000となり、これを前年売上の1,200,000で割るため、結果は0.15になります。
セルの表示形式をパーセンテージに変更すると、0.15は15.0%と表示されます。
分母には必ず前年の値を入れることが、前年比計算で最も大切なポイントです。
当年の値を分母にすると、前年を基準にした変化率ではなくなるため、資料内の数字の意味が変わってしまいます。
前年同月比を計算する場合も考え方は同じで、前年同月の売上を分母にし、当月の売上との差額を分子にします。
【操作のポイント】前年比は増減額ではなく、前年実績に対する変化の割合です。前年の値を分母に固定して計算しましょう。
当年を前年の何パーセントとして示す計算
社内の帳票では、前年比を「115%」のように前年を100%とした比率で表す場合があります。
この表示では増減率ではなく、当年売上が前年売上の何倍に相当するかを求めます。
前年を100%とする前年比の数式です。
=C2/B2
商品Aの当年売上1,380,000を前年売上1,200,000で割ると1.15になり、パーセント表示では115.0%です。
115%は前年より15%増加したことを表し、90%は前年より10%減少したことを表します。
増減率の15%と、前年対比の115%は似ていますが、意味が異なる数字です。
表の列見出しには「前年比増減率」または「前年対比」のように、どちらの計算方法なのかが伝わる名称を付けると誤解を防げます。
部署間や店舗間でデータを比較する資料では、計算方式を統一しておくことも欠かせません。
【操作のポイント】100%を基準に表示する前年比は=C2/B2です。増減だけを見せたい場合は=(C2-B2)/B2を使い分けます。
数式の入力とオートフィル
前年比の数式は、最初のデータ行だけに入力してから、オートフィルで下方向へコピーすると効率的です。
今回の表では、D2セルに数式を入力します。
D2セルを選択して、数式バーまたはセル内に=(C2-B2)/B2と入力します。
Enterキーを押して結果を確認したら、選択セル右下の小さな四角であるフィルハンドルを下へドラッグします。
連続したデータがある場合は、フィルハンドルをダブルクリックしても数式をコピーできます。
コピー後のD3セルでは自動的に=(C3-B3)/B3へ変わり、行ごとの売上を使って計算されます。
相対参照の数式は、オートフィルによって行番号が自動調整される仕組みです。
途中に空白行がある表では、ダブルクリックでコピーが途中までになることがあるため、コピー範囲を確認しましょう。
【操作のポイント】最初の計算セルに正しい数式を入れた後、フィルハンドルでコピーします。コピー後は数行だけ数式バーで参照先を確認すると安心です。
前年比のエラーを防ぐIFERROR関数
続いては、前年の売上がゼロまたは空白のときにも見やすい表を作る方法を確認していきます。
| 商品名 | 前年売上 | 当年売上 | 前年比 |
|---|---|---|---|
| 新商品D | 0 | 240,000 | － |
| 商品E | 620,000 | 682,000 | 10.0% |
前年の数値がゼロの場合
前年比の分母である前年の数値が0の場合、通常の数式では0で割ることになり、#DIV/0!というエラーが表示されます。
前年に販売実績がない新商品は、増減率を通常の割合として表現できません。
このような行では、無理に0%と表示するのではなく、比較対象なしとして扱うことが実務的です。
エラーを表示しない基本の数式です。
=IFERROR((C2-B2)/B2,””)
この数式では、通常の前年比計算でエラーが発生した場合に、空白文字を表示します。
前年がゼロの新商品を0%と表示すると、変化がなかったように見えるため注意が必要です。
空白にするほか、判定しやすい記号を表示する方法もあります。
【操作のポイント】前年実績が0の行は割合を計算できません。IFERROR関数で空白や記号に置き換え、数値の意味を保ちましょう。
ハイフンを表示するIFERROR関数
空白セルではデータの入力漏れと見分けにくい場合があります。
前年比を算出できないことを明確にするなら、IFERROR関数の2番目の引数に「－」を設定します。
エラー時にハイフンを表示する数式です。
=IFERROR((C2-B2)/B2,”－”)
この式をD2セルに入力すると、計算できる行には前年比が表示され、前年が0の行には－が表示されます。
日本語の報告表では、比較対象が存在しない箇所に－を使う表現がよく用いられます。
エラーを隠すだけでなく、計算できない理由を読み手が推測できる表示にすることが大切です。
なお、前年値が空白でもExcelは0として扱う場合があるため、入力途中の表では空白セルを先に点検する習慣も役立ちます。
【操作のポイント】表の用途に合わせて、エラー時は空白、－、比較対象なしなどを使い分けます。社内資料では表記を統一しましょう。
IF関数で前年値を事前に判定する方法
前年比の計算条件をより明確に記述したい場合は、IF関数で前年売上が0かどうかを先に判定します。
前年売上が0なら比較対象なしを表示する数式です。
=IF(B2=0,”比較対象なし”,(C2-B2)/B2)
この数式では、B2が0なら比較対象なしと表示され、0以外なら前年比の増減率が計算されます。
前年値が空白の場合も別に判定するなら、=IF(B2=””,””,(C2-B2)/B2)のように設定できます。
IF関数を使うと、どの条件で数式の結果を変えるのかを表内に明示できます。
データの受け渡し先が多い業務では、IFERROR関数だけでなく、数式の意図が伝わりやすいIF関数の利用も検討しましょう。
【操作のポイント】前年が0、空白、数値のいずれかを区別したいときはIF関数が便利です。表の運用ルールに合わせて条件を決めます。
前年比をパーセントで見やすく表示する設定
続いては、計算した前年比を読み取りやすいパーセント表示に整える方法を確認していきます。
| 前年売上 | 当年売上 | 計算結果 | 表示例 |
|---|---|---|---|
| 1,000,000 | 1,120,000 | 0.12 | 12.0% |
| 1,000,000 | 930,000 | -0.07 | -7.0% |
パーセントスタイルの適用
数式を入力した直後のセルには、0.12や-0.07のような小数が表示されることがあります。
これは計算が間違っているのではなく、Excelが割合を小数として保持しているためです。
前年比のセル範囲を選択し、ホームタブの数値グループにあるパーセントスタイルをクリックすると、12%や-7%に変わります。
小数点以下の桁数を増やすボタンまたは減らすボタンを使えば、12.0%や12.35%のように調整できます。
売上前年比では小数第1位まで、件数の比較では小数なしなど、数値の性質に合わせた桁数が見やすさにつながります。
【操作のポイント】計算結果の0.12は12%です。セルの値を100倍に入力し直すのではなく、パーセント表示形式を適用します。
ユーザー定義表示形式によるプラス記号
増加した数値にプラス記号を付けると、プラスとマイナスを一目で判別しやすくなります。
前年比のセルを選択し、セルの書式設定から表示形式をユーザー定義に変更します。
増加にはプラス、減少にはマイナスを付ける表示形式です。
+0.0%;-0.0%;0.0%
この設定では、0.15が+15.0%、-0.1が-10.0%、0が0.0%と表示されます。
セルに保存される数値そのものは変わらないため、並べ替えやグラフ作成にもそのまま利用できます。
文字を直接入力して+15%と作るより、表示形式で整えるほうが集計と再利用に向いています。
【操作のポイント】プラス記号や小数点の桁数は、ユーザー定義の表示形式で整えます。数値と文字列を混在させないことが重要です。
Excel画面での表示形式の変更
表示形式の操作イメージを確認していきます。
画面上部のパーセントスタイルを使うと、選択中の前年比セルへすぐに表示形式を適用できます。
数値が十分に読める列幅を確保し、見出し行に背景色を付けると、確認作業も進めやすくなります。
【操作のポイント】ホームタブのパーセントスタイルは表示だけを変える機能です。数式はそのまま保持されます。
前年比の増減を色分けする条件付き書式
続いては、前年比のプラスとマイナスを色分けして、変化の大きい項目を見つけやすくする方法を確認していきます。
| 商品名 | 前年比 | 判定 |
|---|---|---|
| 商品A | +15.0% | 増加 |
| 商品B | -10.0% | 減少 |
プラスとマイナスのフォント色
条件付き書式を使うと、数値の条件に応じてセルの文字色や背景色を自動で変えられます。
前年比の範囲を選択してから、ホームタブの条件付き書式を開き、セルの強調表示ルールから指定の値より大きいを選びます。
値に0を入力し、増加した数値には青または緑系の書式を設定します。
次に指定の値より小さいを選び、値を0として、減少した数値には赤系の書式を設定します。
色分けは数値を読む前に傾向をつかめるため、売上分析や会議資料で特に有効です。
【操作のポイント】増加と減少の色は社内資料で統一します。赤字が減少を示す運用なら、全シートで同じルールに揃えましょう。
アイコンセットとデータバー
条件付き書式には、矢印や信号のようなアイコンセット、セル内に棒を表示するデータバーもあります。
前年比の高低を直感的に示したい場合には、3方向矢印のアイコンセットが便利です。
ただし、標準設定ではデータ全体の順位で矢印が決まることがあるため、前年比の0%を基準にしたい場合はルールの管理で基準値を調整します。
データバーは数値の大小を比較しやすい一方、マイナス値を含む前年比では軸の位置を確認する必要があります。
条件付き書式は装飾ではなく、確認すべき数値を素早く見つけるための視覚的な補助です。
【操作のポイント】アイコンセットを使う場合は、何%から上向きと判断するかを決めます。標準の順位判定のまま使わないように注意します。
大幅な変動を抽出する基準
前年比がプラスかマイナスかだけでなく、変動幅が大きい項目を目立たせたいこともあります。
たとえば、10%以上の増加を濃い緑、10%以上の減少を濃い赤にするルールを作ると、重点確認が必要な商品を抽出できます。
季節変動が大きい業種では、前年同月比が20%変化していても通常の範囲である場合があります。
そのため、条件付き書式のしきい値は、業種、商品、月次か年次かという集計単位を考慮して決めましょう。
変動を確認する目安の例です。
前年比が+10%以上なら増加要因を確認します。
前年比が-10%以下なら減少要因と対策を確認します。
数値の色だけで結論を出さず、前年実績、予算、販売時期などの背景と合わせて判断する視点が必要です。
【操作のポイント】条件付き書式のしきい値は、単なる見栄えではなく管理基準です。事業の実態に合う数値へ設定します。
前年比を比較する表とグラフ
続いては、前年比を表とグラフで伝わりやすくする方法を確認していきます。
| 月 | 前年売上 | 当年売上 | 前年比増減率 |
|---|---|---|---|
| 1月 | 980,000 | 1,050,000 | 7.1% |
| 2月 | 1,100,000 | 1,030,000 | -6.4% |
| 3月 | 1,250,000 | 1,410,000 | 12.8% |
前年と当年を並べる比較表
前年比の列だけを掲載すると変化率はわかりますが、元になった売上規模までは読み取れません。
そのため、実務用の表では前年売上、当年売上、増減額、前年比増減率を横に並べる構成が使いやすくなります。
増減額も同時に求める数式です。
=C2-B2
前年比が50%増でも、前年売上が小さければ増加額は小さい場合があります。
反対に前年比がわずか3%増でも、売上規模が大きければ重要な増加額になることがあります。
割合と金額を並べて確認すると、優先して分析すべき項目を適切に判断できます。
【操作のポイント】前年比の列に加えて増減額も表示します。比率と実額の両方を見れば、数字の重要度を判断しやすくなります。
集合縦棒グラフによる実績比較
前年売上と当年売上を月別または商品別に比較するなら、集合縦棒グラフがわかりやすい形式です。
月、前年売上、当年売上の3列を選択し、挿入タブから縦棒グラフの集合縦棒を選びます。
各月に前年と当年の棒が並ぶため、数値の差を視覚的に把握できます。
グラフタイトルは「月別売上の前年比較」など、比較対象がわかる名称に変更しましょう。
売上金額の比較には棒グラフが向き、前年比の推移には折れ線グラフが向いています。
金額と割合を同じ縦軸に表示すると単位が混ざって読みにくくなるため、基本的には別のグラフに分けることをおすすめします。
【操作のポイント】前年と当年の実績を比べるなら集合縦棒グラフを使います。グラフ内の系列名と単位を必ず確認します。
前年比推移の折れ線グラフ
前年比増減率の流れを月ごとに追うなら、前年比列だけを使った折れ線グラフが便利です。
月の列と前年比増減率の列を選択し、挿入タブから折れ線グラフを作成します。
縦軸の表示形式をパーセントに設定し、0%の位置を基準線として見えるようにすると、増加と減少の切り替わりを確認しやすくなります。
月ごとの数値が大きく上下する場合は、キャンペーン、価格改定、営業日数、在庫状況などの要因をコメントとして整理する方法もあります。
グラフは数字を置き換えるものではなく、変化の傾向を短時間で共有するための手段です。
【操作のポイント】前年比の折れ線グラフでは0%を意識します。上側は前年超え、下側は前年割れとして直感的に読めます。
前年比計算で確認したい注意点
続いては、前年比を集計するときに見落としやすい注意点を確認していきます。
| 確認項目 | 確認内容 |
|---|---|
| 比較期間 | 前年同月、前年累計、年度累計を混在させない |
| 前年値 | 0や空白を確認する |
| 集計範囲 | 対象商品や部門の範囲を揃える |
比較する期間と集計範囲
前年比を正しく判断するには、比較する期間をそろえる必要があります。
たとえば、当年が4月から9月までの累計であるなら、前年も同じ4月から9月までの累計値と比較します。
当年の月次売上を前年の年間売上と比較しても、意味のある前年比にはなりません。
商品構成や部門の統廃合があった場合も、前年と当年で集計対象が一致しているか確認しましょう。
【操作のポイント】前年比の前に、期間と対象範囲を確認します。計算式が正しくても比較条件が違えば判断を誤ります。
マイナス売上と返品データ
返品、値引き、取消などにより、前年または当年の売上がマイナスになるデータもあります。
マイナス値を含む前年比は、通常の増減率だけでは直感的に理解しにくいことがあります。
前年がマイナスで当年がプラスの場合は、数式上の結果が想定と異なる符号になる可能性があります。
このような項目は、前年比だけで評価せず、売上、返品額、純売上などを分けて表示することが重要です。
特殊な値を含むデータでは、前年比の計算結果よりも元データの内訳を優先して確認します。
【操作のポイント】返品や取消でマイナス値がある場合は、前年比の意味を個別に確認します。単純な増減判断を避けましょう。
小数点以下の丸めと表示の差
Excelではセルに保存されている値と、画面に表示されている値が異なることがあります。
たとえば、実際の前年比が12.46%で、小数第1位表示にしている場合、画面では12.5%と表示されます。
表示上の合計と、元データから計算した合計が完全に一致しない場合は、丸め処理が影響している可能性があります。
必要に応じてROUND関数で小数点以下の桁数を固定すると、計算ルールを明確にできます。
前年比を小数第3位で丸める数式です。
=ROUND((C2-B2)/B2,3)
ただし、途中計算で丸めると集計誤差が生じることもあるため、提出用の表示だけを丸めるのか、計算値自体を丸めるのかを決めておきましょう。
【操作のポイント】表示形式による丸めとROUND関数による丸めは異なります。集計に使う表では、どちらを採用するかを明確にします。
まとめ エクセルで前年比を計算・見やすく表示する方法
エクセルで前年比を計算する基本は、当年の数値から前年の数値を引き、その差を前年の数値で割る方法です。
増減率として求める場合は=(C2-B2)/B2を使い、前年を100%とする比率で示す場合は=C2/B2を使います。
前年値が0のときは#DIV/0!を避けるため、IFERROR関数やIF関数で空白、－、比較対象なしなどを表示すると表が整います。
パーセント表示、プラス記号、条件付き書式を組み合わせると、前年比の増減が読み取りやすくなります。
さらに前年売上、当年売上、増減額、前年比を並べ、棒グラフや折れ線グラフを使えば、報告先にも変化の内容を伝えやすくなるでしょう。
前年比は数式だけで終わらせず、比較条件、前年値の有無、表示方法まで整えることで、判断に使えるデータになります。
