受注金額の構成比と累積構成比で確認候補を絞る

LWP | 受注金額の構成比と累積構成比で確認候補を絞る

受注金額の構成比と累積構成比で確認候補を絞る

図解1|商品別の受注金額から確認候補を選ぶ

同じ期間の6商品の受注金額を大きい順に並べ、対象と合計を確かめる図

図1|一行の意味と対象期間をそろえ、金額と全体への寄与を見分ける。

解説1|商品別の受注金額から確認候補を選ぶ

ストーリー

ナマズさん「来月の施策に向けて、先に詳しく見る商品を絞りたいな。」

ココさん「まず、同じ期間の商品別金額を確認しましょう。」

全国規模で中華食材を扱う架空の卸売会社・淡水社を舞台に、商品別の集計済み教材を使う。対象期間は2026/4/1〜4/30。一行が対象期間の一商品を表し、受注金額の大きい順に並んでいる。

入力例

A

B

1

商品名

受注金額(円)

2

餃子(冷凍)

426600

3

四川麻婆豆腐の素

422400

4

小籠包(冷凍)

324000

5

回鍋肉の素

100800

6

担々麺スープ

69000

7

エビチリソース

58080

8

合計

1400880

B8に =SUM(B2:B7) を入力すると、全体は1,400,880円になる。これはこの6商品の対象期間の受注額で、実際の取引、利益、会社全体の確定売上を示すものではない。

今回は受注額をどのように集計するかではなく、その全体に各商品がどれだけ寄与し、どの商品から詳しく確認するかを考える。順位だけでなく、共通の全体を分母にした割合へ進む。

図解2|同じ全体を分母にして構成比を求める

各商品の受注金額を同じ総額1400880円で割り、構成比を求める図

図2|分子は商品ごとに変わり、分母は6商品の合計で固定する。

解説2|同じ全体を分母にして構成比を求める

数式の意味

構成比は、各商品の金額が全体の何割かを表す。C1を「構成比」とし、C2へ次の式を入力してC7までコピーする。

=B2/SUM($B$2:$B$7)

分子のB2は行とともにB3、B4へ移る。分母の $B$2:$B$7 はコピーしても同じ6商品を参照する。各行で分母を変えると、同じ全体への寄与を比較できなくなる。

入力と結果の対応

A

B

C

1

商品名

受注金額(円)

構成比

2

餃子(冷凍)

426600

30.45%

3

四川麻婆豆腐の素

422400

30.15%

餃子(冷凍)は426600÷1400880、四川麻婆豆腐の素は422400÷1400880である。C列はパーセント、小数点以下2桁で表示する。表示を30.45%にしても、計算では割り算で求めた値を保持する。ROUND関数で計算値そのものを丸める必要はない。

金額の差と構成比の差を対応させると、上位2商品の金額が近いことに加え、それぞれが全体の約3割を占めることが分かる。この割合の分母は、今回の6商品の金額合計である。

図解3|先頭からの累積受注額を固定した総額で割る

降順表の先頭から現在行までの金額を足し、共通の総額で割って累積構成比を求める図

図3|先頭行を固定し、現在行までの金額を積み上げる。

解説3|先頭からの累積受注額を固定した総額で割る

数式と途中計算

累積構成比は、金額の大きい商品から現在の商品までで、全体のどれだけを占めるかを表す。D1を「累積構成比」とし、D2へ次の式を入力してD7までコピーする。

=SUM($B$2:B2)/SUM($B$2:$B$7)

分子は先頭B2を固定し、終わりを現在行へ動かす。D3ではB2:B3の849000円、D4ではB2:B4の1173000円が分子になる。分母は毎行同じ1400880円である。

先頭からの累積

現在の商品

累積受注金額(円)

累積構成比

餃子(冷凍)

426600

30.45%

四川麻婆豆腐の素

849000

60.60%

小籠包(冷凍)

1173000

83.73%

エビチリソース(最終行)

1400880

100.00%

D列もパーセント、小数点以下2桁で表示する。未丸めの構成比を =SUM($C$2:C2) で積み上げても、同じ全体への累積を求められる。表示した30.45と30.15を手で足す方法とは区別する。

この表では、先頭の2商品までで約60.60%、3商品までで約83.73%を占める。商品順を変えると途中の累積も変わるため、金額の大きい順という前提を保って読む。

図解4|80%と95%の境界を決めて分類する

未丸めの累積構成比を80%以下、95%以下、それ以外の順に判定し、A・B・Cへ分ける図

図4|分類の順序と境界を明示し、3番目の商品がBになる理由を確かめる。

解説4|80%と95%の境界を決めて分類する

分類の条件

この演習では、累積構成比が80%以下をA、80%を超えて95%以下をB、95%を超えるものをCとする。E1を「分類」とし、E2へ次の式を入力してE7までコピーする。

=IFS(D2<=80%,"A",D2<=95%,"B",TRUE,"C")

IFSは最初に成立した条件に対応する値を返す。まず80%以下かを調べ、当てはまらなかった行で95%以下かを調べる。残りをCとするため、最後にTRUEを置く。分類には、表示用に丸めた文字ではなく、D列が保持する値を使う。

この基準での結果

商品

累積構成比(表示)

分類

餃子(冷凍)

30.45%

A

四川麻婆豆腐の素

60.60%

A

小籠包(冷凍)

83.73%

B

回鍋肉の素

90.93%

B

担々麺スープ

95.85%

C

エビチリソース

100.00%

C

3番目の小籠包は、その行までの累積が80%を超えるためBとなる。Aの2商品だけで80%を占める、という意味ではない。ちょうど80%はA、ちょうど95%はBに入る。基準を報告に添え、全ての業務に共通する固定ルールとは扱わない。

図解5|合計と最後の累積を保持値で確かめる

表示された構成比の合計100.01%と、未丸めの計算値による100%を区別して検算する図

図5|構成比は合計し、累積構成比は最終行の値を確認する。

解説5|合計と最後の累積を保持値で確かめる

検算する三つの値

商品別金額の合計、構成比の合計、累積構成比の最終値を確認する。B8・C8・D8に次の式を置く。D8は累積列の合計ではなく、最後のD7を参照する。

セル

数式

期待する結果

B8

=SUM(B2:B7)

1400880

C8

=SUM(C2:C7)

100.00%

D8

=D7

100.00%

C2:C7を小数点以下2桁で表示すると、30.45%、30.15%、23.13%、7.20%、4.93%、4.15%となる。この表示値だけを足すと100.01%になる。それぞれの表示で生じる丸めの差であり、計算値をその表示へ手直しする理由にはならない。

読むポイント

ココさん「表示は2桁でも、分類と検算にはセルが保持する値を使います。」

ナマズさん「合計の見え方に合わせて、商品の割合を直さないんだね。」

検算ではコピー範囲や分母も確かめる。最終累積が100%でも、商品名と金額の組合せが正しいことまで証明できるわけではない。今回の入力表と各行を照合し、途中の60.60%や83.73%も、先頭からの金額に対応しているかを見る。

図解6|分類を確認候補へつなぎ、判断の条件を残す

A・B・Cの分類を追加確認の候補として使い、粗利や顧客別の内訳など、次に確かめる条件を分ける図

図6|金額による分類から、採用する施策を自動で決めない。

解説6|分類を確認候補へつなぎ、判断の条件を残す

ストーリー

ナマズさん「上位の商品から、顧客別の内訳や粗利も確かめたいね。」

ココさん「今回の分類基準と対象期間を添えれば、どこまで分かったかも伝えられます。」

今回の表から分かるのは、この期間・6商品の受注金額に基づく寄与と分類である。金額が大きいことだけで利益への貢献を断定したり、Cの商品を不要と決めたりはできない。

結果と追加確認を分ける

今回の表で分かること

次に確認したいこと

Aの2商品までの累積は約60.60%

顧客別の内訳、粗利

小籠包までの累積は約83.73%

特定の受注への偏り、継続性

指定した80%・95%基準での分類

業務の目的に合う基準か

上位からどこまでで受注金額の何割になるかを見て、詳しく調べる商品の候補を選ぶ。施策を決める前に、粗利や顧客別の内訳などを追加で確かめる。報告には、対象期間、対象商品、受注額という金額の基準、使用した分類条件を添える。

まとめ

一行と対象期間をそろえた降順表から、共通の総額を分母にして構成比を求める。累積は先頭から現在行までとし、分類と検算には未丸めの値を使う。

今回の仕掛け

金額、構成比、累積構成比を並べると、規模と全体への寄与を分けて読める。分類の役割を確認候補の整理に限定することで、計算で分かったことと、営業として次に確かめることをつなげた。

出典

著者原稿『Googleスプレッドシートの教科書』V056の11.4.1・11.4.2を主題に、11.3.2と11.1.1を補助として構成。分類式はGoogle公式のIFSを参照した。