Excel 構成比を取得する方法

2020年5月18日

構成比を取得する関数は存在しないため

数式を作成して算出する必要があります。

この記事では、その方法を紹介します。

手順

例として下記表に支店別売上金額の構成比を追加します。

サンプル表:支店別売上金額

合計セルを作らない場合

従来型

1つめの支店のセルに下記のように数式を設定します。

=支店別の売上金額/SUM(売上金額の全範囲を絶対参照)

オートフィルで数式を他の行にコピーしたとき、

分母の範囲がズレると構成比にならないので絶対参照($)で固定します。

構成比を算出する数式を入力

先頭の数式を全行にコピーすれば完成です。

構成比の入った表の完成

スピル利用

Office365やExcel2019以降ではスピルという形式で記述可能です。

これなら一回の数式入力で完了し、絶対参照も不要なため効率的です。

今回の例でスピルを利用するにはD3に以下のように数式を入力します。

=売上金額の全範囲/SUM(売上金額の全範囲)

この場合、D3セルにだけ数式を入力すればD6まで自動展開されます。

構成比の入った表の完成(スピル)

合計セルを作る場合

先ほどの例ではSUM関数を構成比のセルにそれぞれ記述しましたが

合計値のセルを作成し、構成比のセルでは絶対参照で合計値のセルを指定します。

=支店別の売上金額/合計セル

合計値自体に表示する用事が多いですし、

数式もシンプルになるため合計値を出して問題があるような

状況でなければ合計セルを作成しましょう。

合計セルを作成して、構成比を算出する例

スピル利用

こちらもスピルで効率化可能です。

=売上金額の全範囲/合計セル
合計セルを作成して、構成比を算出する例(スピル)

関連記事

初心者向け記事一覧