割合と増減率
変化率(増減率)の数式:増加・減少の計算と基準値ゼロのエラー対策
(新 - 旧) / 旧 で増減率を計算し、初期値が0または負数の場合に #DIV/0! や誤解を招く結果を防ぐ方法を解説します。
計算の前に基準となる元の値を確認する
あるワークショップの予約数が、4月は 80 件、5月は 100 件だったとします。B2 に4月の値、C2 に5月の値を入力します。増減幅は 20 件です。この変化を4月を基準とした割合で表すには、差分の 20 を4月の値である 80 で割ります。
この例では、通常の増減率計算として非負の件数と正の基準値を扱います。赤字利益やマイナス残高を基準にする場合の会計上の解釈ルールとは区別してください。
| 期間の比較 (A) | 元の予約数 (B) | 新しい予約数 (C) |
|---|---|---|
| 4月から5月 | 80 | 100 |
| 5月から6月 | 100 | 80 |
| 新規ワークショップ | 0 | 20 |
元の値を引いてから、元の値で割る
数式 =(C2-B2)/B2 は 0.25 を返します。ワークブック上でパーセント表示書式を適用すると 25% と表示されます。下の演習パネルでは、後続の計算で実際に使われる生の小数値を確認できるよう、そのまま小数で表示しています。
=(C2-B2)/B2同じ幅の増加と減少でもパーセンテージは異なる
80 から 100 への変化は、基準 80 に対する 20 の増加なので 25% です。逆に 100 から 80 へ戻る変化は、基準 100 に対する 20 の減少なので -20% になります。増減の絶対値は同じでも、分母が異なるため比率は一致しません。
増加分を新しい値で割っても 20% という数字は出ますが、それは「新しい値に占める増加分の構成比」という別の問いへの答えになってしまいます。前の期間からの変化率を報告するときは、必ず元の期間を分母に置いてください。
100を二重に掛けないよう注意する
パーセント書式は 0.25 を自動的に 25% として表示します。もし数式の中で 100 を掛けて 25 を算出し、さらにセルにパーセント書式を設定すると、表示は 2500% になってしまいます。パーセント書式を使うセルでは小数のまま計算結果を保持してください。
また、「変化なし(0%)」と「定義不能」は別物です。両方の期間とも予約数が 80 件であれば、数式は 0 を返し、0% と表示されます。
基準値がゼロのケースを明示的に処理する
新規ワークショップは予約数が 0 件から 20 件に増えました。実数としては 20 件の増加ですが、ゼロを基準とした通常の増減率は数学的に定義できません。ここで 0% と表示すると「変化がなかった」という誤解を与え、100% と表示するのも不正確です。
基準値が 0 のときは IF 関数で "No percentage base"(基準値なし)のような明示的なメッセージを返し、実数の増減幅を別掲しましょう。両方の値が 0 の場合も、分母が 0 であることに変わりはないため同じメッセージが返ります。
=IF(B4=0,"No percentage base",(C4-B4)/B4)「パーセント」と「パーセントポイント」を区別する
予約の成約率が 20% から 25% に上がった場合、差は 5 パーセントポイントです。一方、元の成約率に対する相対的な伸び率は 5 / 20 = 25% となります。読み手が誤解しないよう、どちらの指標なのかを見出しや注記に明記してください。
また、複数の拠点の増減率を単純平均すると件数の重みが失われます。全体の予約数の増減率を出したいときは、まず元の予約数の合計と新しい予約数の合計を求め、その合計同士で増減率を計算してください。
レポートを公開する前に入力値を見直す
比較する2つの期間は、日数の長さや「予約1件」の定義が揃っている必要があります。未入力の空白セルは自動的にゼロを意味するわけではないため、計算前に欠損値を確認しましょう。
演習テーブルで C2 を 100 から 80 に変更すると結果は 0 になり、60 に変更すると -0.25 になります。分母を変えずに「変化なし」と「減少」を切り分けられることを確認してください。
サンプルを試す
初期値のまま計算するか、値を変更して動作を確認できます。