Excelで負の値を使用して分散パーセントを計算する

Excel内の分散率を計算する式は、ほとんどの場合、うまく機能します。ただし、ベンチマーク値が負の値の場合、式は崩れます。

たとえば、あなたがビジネスを始めていて、最初の年に損失を被ると予想していると想像してください。したがって、マイナス10,000ドルの予算を自分に与えます。ここで、最初の1年後に、実際に12,000ドルを稼いでお金を稼いだと想像してみてください。実際の収益と予算収益の差異の割合を計算すると、–220％になります。電卓で試すことができます。12,000マイナス–10,000を–10,000で割ると、–220％になります。

明らかにお金を稼いだとき、あなたの分散率は–220％であるとどのように言うことができますか？問題は、ベンチマーク値が負の数の場合、数学によって結果が反転し、数値が奇抜に見えることです。これは、予算がしばしば負の値になる可能性がある企業の世界では実際の問題です。

修正は、ABS関数を利用して、負のベンチマーク値を無効にすることです。

=（C4-B4）/ ABS（B4）

この図では、セルE4でこの数式を使用しており、標準のパーセント分散数式と改善されたパーセント分散数式を使用した場合に得られるさまざまな結果を示しています。

Excelで負の値を使用して分散パーセントを計算する

ExcelのABS関数は、渡された数値の絶対値を返します。セルA1に= ABS（-100）を入力すると、100が返されます。ABS関数は、基本的に任意の数を非負数にします。この式でABSを使用すると、負のベンチマーク（例では負の10,000予算）の影響が無効になり、正しいパーセント分散が返されます。

この式は、すべての分散パーセントのニーズに安全に使用できます。正の数と負の数の任意の組み合わせで機能します。

負の数値を使用した変化率の代替計算

ここでは、負の数の存在をテストし、別の結果を得る方法をいくつか紹介します。

方法 #1: 陰性の場合は結果なし

最初にできることは、いずれかの数値が負であるかどうかを確認し、その後、パーセンテージの変化の計算が実行できなかったことを読者に伝えるテキストを表示することです。

次の数式は、IF 関数と MIN 関数を使用してこれを実行します。

=IF(MIN(古い値, 新しい値)<=0,"--",(新しい値/古い値)-1)

数式の仕組みは次のとおりです。

IF 関数の論理テスト (MIN(古い値, 新しい値)<=0) は 2 つの値の最小値を見つけ、その値が 0 以下であるかどうかをテストします。結果は TRUE または FALSE になります。

結果が TRUE の場合、負の数 (またはゼロ) が存在します。この場合、読者に伝えるテキストを表示できます。これは何でも構いません。ダッシュ 2 つ「-」を使用しただけです。 NA() 関数を使用して N/A エラーを返すようにすることも、変化率を計算できなかったことを読者に知らせるその他のテキストを返すようにすることもできます。

結果が FALSE の場合、パーセント変化の式を使用して、2 つの正の数値のパーセント変化が返されます。

いずれかの数値が負の場合、パーセント変化の式はテキストを返します