SUMPRODUCT関数を使った加重平均のスマートな計算法

SUMPRODUCT関数を使った加重平均のスマートな計算法

SUM関数シリーズとして、下記記事をアップしました。

記事の読み方

説明の順は以下です。
まずは、「問題」を与えて、
次に、「解答」を提示します。
最後に、「解説」です。

よくある、結論→理由の構成順に沿いました。

先に「解答」を知りたい人を考慮したためです。

「解説」の説明は不要で、とりあえずビジネス現場で使えればいいと思う方もいるためです。

また、ある程度の経験者であれば、
「問題」と「解答」だけで、関数を使いこなせてしまうためです。

そのために、即利用できるように、ダウンロードファイルを用意していますので、
活用してみてください。
(経験者であれば、ダウンロードファイルだけで、実務に応用できてしまうのではないでしょうか)

問題

下記サンプルデータの加重平均値を求めてみましょう。
加重平均値は、アンケートデータの分析等で使用されます。

注意事項
  • 回答率(%)は、小数点第1位を四捨五入しているため、合計値が必ずしも100(%)とならない場合があります
  • 加重平均値の算出方法は、知っている前提

A列:重みB列:項目C列:回答人数D列:回答率
行15購入したい2017%
行24やや購入したい4033%
行33どちらとも言えない3025%
行42やや購入したくない2017%
行51購入したくない108%
n120100%
加重平均値3.33
解答付きサンプルデータ

下記に解答付きサンプルデータも提供していますので、ダウンロードして活用ください。
なお、本記事で紹介してない問題が1題あります。
ぜひ、トライしてみてください。

解答

SUMPRODUCT関数を使用します。
なお、関数を使わず、加重平均値を求めることもできます。

間違いやすいし、大変ですが、記事後半で紹介しています。

関数式

次の関数式を記述すると、加重平均値が求まります。

=SUMPRODUCT($D$2:$D$6,$A$2:$A$6)

解説

注意事項:解説を読む前に
  • Excel関数の構文(引数や戻り値など)については、理解していること
  • また、絶対参照、複合参照、相対参照についても、理解していること
    ※これらの知識に自信がない場合は、記事末尾の参考記事をご覧ください。

ざっくり解説

関数の構文は以下です。

=SUMPRODUCT(配列, [配列], ...)

「配列って何よ」と思うかもしれません。

「配列」という言葉は、Excelの表の中で、特定の行や列にある数字や文字の集まりを指します。
これらの数字や文字は、Excelの中で一緒にまとめて扱うことができるのです。

配列に関しては下記記事を参照してください

Excel関数の引数に「配列」と書かれたら、セル範囲を指定すれば良いです。
なので、配列に関してはそこまで深く理解しなくて問題ないです。

具体的には、サンプルデータの各列を、以下のように引数にセットします:

=SUMPRODUCT(重み, 回答率

では、実際のセル範囲を指定してみます

=SUMPRODUCT($D$2:$D$6, $A$2:$A$62

となります。

Excel上は以下のイメージとなります。

SUMPRODUCT,加重平均値

くわしく解説

SUMPRODUCT関数とは

記事『Excel関数の検索テクニック』で説明した通り、
Excel標準機能の[関数の挿入]ダイアログ/[関数の引数]ダイアログを大体のことは書かれています。

活用して詳しく解説します。

ダイアログの使い方は、上記記事を参照していただければと思います。

SUMPRODUCT,加重平均値

範囲または配列の対応する要素の積を合計した結果を返す

[関数の挿入]ダイアログより

「sum」は「和」を意味し、「productus」は「積」を意味します。

つまり、

SUM関数は範囲内のすべての数値の合計を計算し、

PRODUCT関数は範囲内のすべての数値の積を計算します。

SUMPRODUCT関数は、
これらの2つの関数を組み合わせて、範囲内のすべての数値の積の合計を計算します。

Excel関数名が分からない場合は日本語訳してみると、Excel関数の機能を推察できるかもしれません。

SUMPRODUCT関数の構文

SUMPRODUCT,加重平均値
=SUMPRODUCT(配列1, 配列2, ...)

引数「配列」同士を掛け算し、合計を計算します。
実際のイメージを見てみましょう。

Step1「積(PRODUCT)」とStep2「和(SUM)」が組み合わさり、

SUMPRODUCT関数を構成しています。

SUMPRODUCT,加重平均値
第1引数:配列1
SUMPRODUCT,加重平均値

配列1: 配列1,配列2,… には、要素の積の合計を求めたい配列を2~255個まで指定できます。引数となる配列は、行数と列数が等しい配列である必要があります。

[関数の挿入]ダイアログより

配列1 には、セル範囲を指定すればOKです。

先程配列の説明をしましたが、難しいことは考えてなくよいです。

ここでは、配列1は回答率「$D$2:$D$6」のセル範囲を指定しいます。

SUMPRODUCT,加重平均値
第2引数:配列2
SUMPRODUCT,加重平均値

配列2: 配列1,配列2,… には、要素の積の合計を求めたい配列を2~255個まで指定できます。引数となる配列は、行数と列数が等しい配列である必要があります。

[関数の挿入]ダイアログより

配列2も、配列1と同様です。

ここでは、配列2は重み「$A$2:$A$62」のセル範囲をしています。

第1引数「回答率」、第2引数「重み」を引数にセットしましたが、逆の順番でも問題ありません
なぜなら、これらの引数は単に掛け算されるだけだからです。

SUMPRODUCT,加重平均値
引数のセット時の注意点

ダイアログボックスに注意点が記載されています。

分かりづらいので、簡潔に整理します。

  • 配列1と配列2の要素数は同じである必要があること
    ※なぜなら、要素数が異なると計算ができないから
  • 第1引数と第2引数は省略できない
    ※なぜなら、1つのデータのみでは掛け算ができないから

要素数が異なると掛け算できず、1つのデータのみでは掛け算ができないからです。

掛け算の仕組みを考えれば、いずれも当たり前の考えです。

SUMPRODUCT関数でエラーが発生した場合は、掛け算に立ち戻って考えてみましょう。

別解1

回答率を使用せず、加重平均値を算出することができます。
回答率がデータにない場合に便利ですが、関数式が複雑になります。

具体的には、サンプルデータの各列を、以下のように引数にセットします:

=SUMPRODUCT(重み, 回答人数)/ n

では、実際のセル範囲を指定してみます

=SUMPRODUCT($D$2:$D$6, $A$2:$A$62)/ C7

となります。

SUMPRODUCT,加重平均値

別解2

関数を使用せずとも、加重平均値の算出することができます。

=A2*D2+A3*D3+A4*D4+A5*D5+A6*D6

実は、こちらの別解2こそ、SUMPRODUCT関数のロジックそのものなのです。

具体的には・・・

=重み*回答率+重み*回答率+....

としているだけで、単純です。

ただ、関数を使用せずに加重平均を算出するのは間違いやすいし、面倒ですし、データ数が増えると、さらに大変です。

小話

外観上は数値のケース

SUMPRODUCT関数では、引数に文字列を設定してならないとルールがあります。

ただし、外観上は数値の場合には正常に動作します。

例えば、以下の2つのケースではどちらも「999」という値が数値に見えます。

書式は文字列だが、外観上は数値



しかし、2行目の「999」は書式が文字列です。

本来ならばルール違反であり、SUM関数はエラーを返すべきですが、
実際には正常に動作します。

おそらく、裏で数値変換が行われているものと考えられます。

そのため、書式が文字列でも外観上数値であれば、SUMPRODUCT関数は正常に動作するようです。

最後に

本記事では、SUMPRODUCT関数を詳しく解説しました。
この関数は、Excelで複数の要素を掛け算し、それらの合計を計算する際に強力です。

データの加重平均値や合計値を効率的に計算することができます。
ぜひこの強力な関数を活用して、Excelでの作業を効率化しましょう。

解答付きのサンプルデータも提供していますので、実際に手を動かして練習してみてください。

それでは、SUMPRODUCT関数を活用して、データ処理のスキルを高めていきましょう!
今後、SUMPRODUCT関数の活用記事もアップしてきたいです。

参考記事