前［時点］比・伸び率・達成率・構成比・累積構成比 ―比・率・割合の計算 with Excel
比・率
前［時点］比
Case1-Step 0前の時点を基準にして 次の時点は何％？
- 前年比
- 前期比
- 前月比
- 対前年比
- 対前期比
- 対前月比
といったワード群で示されるところの計算です。
このうち前年比を，下表6つの商品売上で求めます(なお，表中の矢印は前年との比較からの推移の方向を示します。以下に同じ)。
DL
Case1-Step 1計算式
本年実績(次の時点) ／ 前年実績(前の時点)
となる式を入力します(基準［=100%］となる前年実績の方を分母に据える)。
| セルF2 | =D2/C2 |
|---|
Case1-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") ) |
|---|
伸び率
Case2-Step 0前の時点を基準にして 次の時点との差は何％？
- 伸び率
- 伸長率
- 増加率
- 成長率
- 増減率
- 前［時点］伸び率
- 前［時点］伸長率
- 前［時点］増加率
- 前［時点］成長率
- 前［時点］増減率
- 対前［時点］伸び率
- 対前［時点］伸長率
- 対前［時点］増加率
- 対前［時点］成長率
- 対前［時点］増減率
といったワード群で示されるところの計算です。また，Case 1-Step 0の語群に「xパーセント増｜減」といった字句がつづくときに，それが指し示すところの内容ともなります。
ここでは前年伸び率［伸び率，対前年伸び率］を，下表6つの商品売上で求めます。
DL
Case2-Step 1計算式
(本年実績 － 前年実績) ／ 前年実績
となる式を入力します(基準［=100%］となる前年実績の方を分母に据える)。
| セルF2 | =(D2-C2)/C2 |
|---|
Case2-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") ) |
|---|
達成率
Case3-Step 0目標に占める実績の割合は？
Case3-Step 1計算式
売上高 ／ 目標売上高
となる式を入力します。
| セルD2 | =C2/B2 |
|---|
パーセントスタイルを適用すると，ここでは(小数点以下表示桁数が1桁まで)表示上次のような値となります。商品Yは，実際には目標金額を達成しておらず，この値は適当とは言えません。
標準スタイルのもとでは，これは次のように表示されます(E列)。つまり商品Yのようなケースは，パーセントスタイルを適用したとき 表示桁数を超えた部分で丸められることによって生じます(ここでは紫色の囲みの桁)。
ときに達成率はセンシティブな性質を帯びがちなことを鑑みれば，表示上の丸めについては，はじめから対処しておいたほうがbetterかもしれません。具体的には，下のようにRoundDown関数あるいはTrunc関数を使って，パーセントスタイルを適用したときに表示させる桁以下を切り捨てて調整します。
| セルD2 | =ROUNDDOWN(C2/B2, 3) |
|---|
または
| セルD2 | =TRUNC(C2/B2, 3) |
|---|
なおいずれの場合も引数は
(数値, 桁数※)
で指定します。
※たとえば「3」としたとき，(標準スタイルのときの)小数点第4位で処理され小数点以下3桁が有効となります。
Case3-Step 2結果
両商品の達成率は下表のように求められました。
Case3-Step 3発展
達成・未達をスマートに表現する方法として、ブレットグラフが便利です。
割合
構成比
Case4-Step 0ある要素が全体に占める割合は？
Case4-Step 1計算式
各商品の売上高 ／ 全商品の売上高の合計
となる式を入力します。
2段階で計算する場合(合計の計算各要素の構成比の計算)
Sum関数で合計を求めておき，
| セルB7 | =SUM(B2:B6) |
|---|
合計(絶対参照)をもとに構成比を求めます。
| セルC2 | =B2/$B$7 |
|---|
直接計算する場合(合計を用意しない場合)
すべての商品の売上高のセル範囲を絶対参照にして求めます。
| セルC2 | =B2/SUM($B$2:$B$6) |
|---|
Case4-Step 2結果
下表のように求められました(パーセントスタイル適用)。
累積構成比
Case5-Step 0ルールにもとづく並びの順に各要素の構成比を積み上げていくと
(※ここにいうルールとは，単純な昇降の順・ヒストグラムの階級の順あるいはQCでのパレート図の項目の順といったものを想定しています)
- 累積構成比
- 累計構成比
- 構成比累計
といったワード群で示されるところの計算です。
ここでは累積売上高構成比を，下表5つの商品売上で求めます。
DL
Case5-Step 1計算式
2段階で計算する場合(売上高の参照累積構成比に次の商品の売上高を加算)
1つは最初の売上高構成比を参照，いま1つは直前の累積構成比に次の商品の売上高を加算する式を作成します(コピーは後者の式のみ)。
| セルD2 | =C2 |
|---|---|
| セルD3 | =D2+C3 |
一度に計算する場合(「構成比」列を用意してある場合)
Sum関数の引数を，開始セルのみ絶対参照にして求めます。
| セルD2 | =SUM($C$2:C2) |
|---|
一度に計算する場合(「構成比」列を用意していない場合)
Sum関数の引数を，［分子］開始セルのみ絶対参照，［分母］開始・終了両セルを絶対参照 にして求めます。
| セルC2 | =SUM($B$2:B2)/SUM($B$2:$B$6) |
|---|
Case5-Step 2結果
下表のように求められました(パーセントスタイル適用)。
その他の参照
このサイトの関連How-toです。
メインサイト「ひとりマーケティングのためのデータ分析」の構成比or累積構成比を用いたHow-toです。