Googleスプレッドシート初心者脱出！SUMPRODUCT関数を使ってみよう

最近私の周りではGoogleスプレッドシートを使用する機会が増えてきました。
Googleスプレッドシートは「WEBでExcel並みの機能が使える」「共同作業が容易」などなど、メリットが盛りだくさんです。

今回はSUMPRODUCT関数の使い方を解説していきます。
使い慣れれば、かなり便利で応用がきく関数です。
ケーススタディ的に、サンプルを見ながら学習していきましょう。

今回作ったサンプルは以下のリンクから確認できます。
SUMPRODUCT関数のサンプル

SUMPRODUCT関数とは、その名の通りProduct（積）をSum(和)してくれる関数です。
配列の対応する要素同士を掛け算して、その後にそれらの和を計算してくれるわけです。
SUMPRODUCT関数では条件式も使えるので、応用すると色んな集計がこの関数で可能になります。

SUMPRODUCT(配列1, [配列2], ...)

構文はこのようになっています。

参照：Google公式のSUMPRODUCT関数のヘルプ

では、基本の使い方からです。
以下の在庫の合計金額を求めてみましょう。

=SUMPRODUCT(B3:B5,C3:C5)

E3のセルに上記の式を入れています。

=（100 x 1) + (200 x 2) + (50 x 3)

分解すると、SUMPRODUCT関数は上記の計算をしているわけです。
これで、一度に計算結果を求めることができました。

商品が多数ある場合に、特定商品の数量合計を求めることもできます。

=SUMPRODUCT(A3:A6="ペン",B3:B6)

D3のセルに上記の式を入れています。

「A3:A6=”ペン”」の部分で、各セルが「ペン」かどうかを判定しています。
この結果では[TRUE, FALSE, FALSE, TRUE]のような配列が返ってきます。
TRUEは１、FALSEは0として計算されるため、結果としてペンだけの数量合計が求められます。

上記のサンプルでは１つの商品だけが対象でしたが、複数商品あっても数量合計を簡単に求められます。

=SUMPRODUCT($A$3:$A$8=$D3,$B$3:$B$8)

E3のセルに上記の式を入れています。

このセルをコピーして、下のセルにペーストすることで、「パイナップル」「アップル」についても同様に合計が求められます。
「$」マークを付けずにコピー＆ペーストすると対象範囲がズレますが、「$」マークの付けると対象範囲を固定できます。
行と列のどちらに「$」マークが必要か考えながら式を書きましょう。

さらに、D3セルにはUNIQUE関数を使って、重複なく商品一覧を出力する工夫もしています。

=UNIQUE(A3:A8)

UNIQUE関数は、対象範囲のデータを、重複なく出力する関数です。

日付を条件として追加する場合のサンプルです。

=SUMPRODUCT($A$3:$A$9=$E6,$E$3<=$C$3:$C$9,$B$3:$B$9)

F6のセルには上記の式を入れています。

「$E$3<=$C$3:$C$9」の部分で、E3セルの日付以降の項目だけを対象とするように条件を設定しています。

特定の文字列を含むことを条件にすることもできます。

=SUMPRODUCT(ISNUMBER(FIND("ペン",A3:A7)),B3:B7)

D3のセルには上記の式が入っています。

ここのポイントは２点あります。

まず１点目はFIND関数を使って、対象のセルに「ペン」という文字列があるかどうかの判定を行なっている点です。
FIND関数は「ペン」という文字列があれば数値を返してくれますが、「ペン」がない場合は#VALUE!というエラーが出ます。

ここでポイント２点目です。
FIND関数の結果をISNUMBER関数を使って、判定しています。
ISNUMBER関数は数値であればTRUE、そうでなければFALSEを返します。

結果的に「ペン」がある場合だけTRUEとなるわけです。

「または」の条件で判定するサンプルを見てみましょう。

=SUMPRODUCT((A3:A7="アッポーペン")+(A3:A7="パイナッポーペン"),B3:B7)

D3のセルには上記の式が入っています。

「(A3:A7=”アッポーペン”)+(A3:A7=”パイナッポーペン”)」で、２つの条件を足し合わせることでOR条件にしています。
２つの条件のどちらか１つでも条件を満たしたら、TRUEと判定されるわけです。

OR関数を使えるかと思いましたが、OR関数は戻り値が配列になっていなかったため、このケースでは使用できませんでした。

最後に順位を出力する例を見てみましょう。

=SUMPRODUCT($B$3:$B$7>$B3)+1

D3のセルには上記の式が入っています。

「SUMPRODUCT($B$3:$B$7>$B3)」の部分で、「アッポーペン」の販売個数より多い商品の数を計算しています。
これに１を足すことで、順位を算出することができます。

今回はSUMPRODUCT関数を使った、様々な利用ケースのサンプルを作成してみました。
他の関数と組み合わせることで、さらに応用が効くと思いますので、ぜひ使ってみてください。