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

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

SUM関数：使い方と解説
【SUMIF関数】検索条件に一致したデータの合計値を求める
【SUMIFS関数】複数条件を同時に満たすデータの合計を出す

本記事は、SUMPRODUCT関数を紹介します。

SUM関数シリーズとして扱うかを悩みましたが、
合計(SUM)に加えて、積(PRODUCTS)という概念が登場するため、別途紹介することにしました。

そのため、姉妹関数として考えてもらっても問題ありません。

記事の読み方

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

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

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

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

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

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

問題

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

注意事項

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

	A列：重み	B列：項目	C列：回答人数	D列：回答率
行1	5	購入したい	20	17％
行2	4	やや購入したい	40	33％
行3	3	どちらとも言えない	30	25%
行4	2	やや購入したくない	20	17%
行5	1	購入したくない	10	8%
		n	120	100%

加重平均値

3.33

解答付きサンプルデータ

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

excel_func013.xlsx 解答付きサンプルデータ

解答

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関数とは

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

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

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

範囲または配列の対応する要素の積を合計した結果を返す
[関数の挿入]ダイアログより

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

つまり、

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

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

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

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

SUMPRODUCT関数の構文

=SUMPRODUCT(配列1, 配列2, ...）

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

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

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

第1引数：配列1

配列1：配列1,配列2,… には、要素の積の合計を求めたい配列を2～255個まで指定できます。引数となる配列は、行数と列数が等しい配列である必要があります。
[関数の挿入]ダイアログより

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

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

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

第2引数：配列2

配列2：配列1,配列2,… には、要素の積の合計を求めたい配列を2～255個まで指定できます。引数となる配列は、行数と列数が等しい配列である必要があります。
[関数の挿入]ダイアログより

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

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

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

引数のセット時の注意点

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

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

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

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

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

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

別解1

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

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

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

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

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

となります。

別解2

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

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

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

具体的には・・・

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

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

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

小話

外観上は数値のケース

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

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

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

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

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

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

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