BDAstyle

ビジネスデータ分析ツールの作成 with Excel

前[時点]比・伸び率・達成率・構成比・累積構成比 ―比・率・割合の計算 with Excel

ショートカット

比・率

1. 前[時点]比

Case 1-Step 0前の時点を基準にして 次の時点は何%?

  • 前年比
  • 前期比
  • 前月比
  • 対前年比
  • 対前期比
  • 対前月比

といったワード群で示されるところの計算です。

このうち前年比を,下表6つの商品売上で求めます(なお,表中の矢印は前年との比較における推移の方向を示します。以下に同じ)。

初期データ

Case 1-Step 1計算式


本年実績(次の時点)÷ 前年実績(前の時点)


となる式を入力します(基準[=100%]となる前年実績の方を分母に据える)。

セルF2 =D2/C2

Case 1-Step 2結果

下表のように求められました(パーセントスタイル適用)。ただし…

ここでのケースでは,検討の結果,

と判断して計算から除外するものと決定したとの想定をおきます

…ということで下表のように"-"などの表示に変えて,計算の対象外であることを明示しておきます。

ただ,この作業はデータが多くなると手間がかかるので,次のように はじめから計算式にそのあたりの想定を組み込んでいった方が便利かもしれません(下式,参考例)。

セルF2 =IF(NOT(ISERROR(D2/C2)),IF(OR(D2=0,OR(SIGN(C2)=-1,SIGN(D2)=-1)),"-",D2/C2),IF(ERROR.TYPE(D2/C2)=2,"-","error"))

2. 伸び率

Case 2-Step 0前の時点を基準にして 次の時点との差は何%?

  • 伸び率
  • 伸長率
  • 増加率
  • 成長率
  • 増減率
  • 前[時点]伸び率
  • 前[時点]伸長率
  • 前[時点]増加率
  • 前[時点]成長率
  • 前[時点]増減率
  • 対前[時点]伸び率
  • 対前[時点]伸長率
  • 対前[時点]増加率
  • 対前[時点]成長率
  • 対前[時点]増減率

といったワード群で示されるところの計算です。また,Case 1-Step 0の語群に「xパーセント増|減」といった字句がつづくときに,それが指し示すところの内容ともなります。

ここでは前年伸び率[伸び率,対前年伸び率]を,下表6つの商品売上で求めます。

初期データ

Case 2-Step 1計算式


(本年実績 - 前年実績)÷ 前年実績


となる式を入力します(基準[=100%]となる前年実績の方を分母に据える)。

セルF2 =(D2-C2)/C2

Case 2-Step 2結果

下表のように求められました(パーセントスタイル適用)。ただし…

ここでのケースでは,検討の結果,

と判断して計算から除外するものと決定したとの想定をおきます

…ということで下表のように"-"などの表示に変えて,計算の対象外であることを明示しておきます。

ただ,この作業はデータが多くなると手間がかかるので,次のように はじめから計算式にそのあたりの想定を組み込んでいった方が便利かもしれません(下式,参考例)。

セルF2 =IF(NOT(ISERROR((D2-C2)/C2)),IF(OR(D2=0,OR(SIGN(C2)=-1,SIGN(D2)=-1)),"-",(D2-C2)/C2),IF(ERROR.TYPE((D2-C2)/C2)=2,"-","error"))

3. 達成率

Case 3-Step 0目標に占める実績の割合は?


  • 達成率
  • 目標達成率

といったワード群で示されるところの計算です。

ここでは達成率を,下表2つの商品売上で求めます。

初期データ

Case 3-Step 1計算式


売上高 ÷ 目標売上高


となる式を入力します。

セルD2 =C2/B2

パーセントスタイルを適用すると,ここでは(小数点以下表示桁数が1桁まで)表示上次のような値となります。商品Yは,実際には目標金額を達成しておらず,この値は適当とは言えません。

[セルD3の値]100%

標準スタイルのもとでは,これは次のように表示されます(E列)。つまり商品Yのようなケースは,パーセントスタイルを適用したとき 表示桁数を超えた桁の部分で丸められることによって生じます(ここでは紫色の囲みの桁)。

[セルE3の値]0.9999

ときに達成率は,インセンティブにかかわるセンシティブな指標として扱われることに鑑みて,表示上の丸めについては,はじめから対処しておいたほうがbetterかもしれません。具体的には,下のようにRoundDown関数あるいはTrunc関数を使って,パーセントスタイルを適用したときに表示させる桁以下を切り捨てて調整します。

セルD2 =ROUNDDOWN(C2/B2,3)

または

セルD2 =TRUNC(C2/B2,3)

なおいずれの場合も引数は

(数値, 桁数※)

で指定します。

※たとえば「3」としたとき,(標準スタイルのときの)小数点第4位で処理され小数点以下3桁が有効となります。

Case 3-Step 2結果

両商品の達成率は下表のように求められました。

割合

4. 構成比

Case 4-Step 0ある要素が全体に占める割合は?

  • 構成比
  • 構成比率

といったワード群で示されるところの計算です。

ここでは売上高構成比を,下表5つの商品売上で求めます。

初期データ

Case 4-Step 1計算式


各商品の売上高 ÷ 全商品の売上高の合計


となる式を入力します。

Case 4-Step 2結果

下表のように求められました(パーセントスタイル適用)。

5. 累積構成比

Case 5-Step 0ルールにもとづく並びの順に各要素の構成比を積み上げていくと…

(※ここにいうルールとは,単純な昇降の順・ヒストグラムの階級の順あるいはQCでのパレート図の項目の順…といったものを想定しています)


  • 累積構成比
  • 累計構成比
  • 構成比累計

といったワード群で示されるところの計算です。

ここでは累積売上高構成比を,下表5つの商品売上で求めます。

初期データ

Case 5-Step 1計算式

Case 5-Step 2結果

下表のように求められました(パーセントスタイル適用)。

その他の参照