Googleスプレッドシート初心者脱出!SUMPRODUCT関数を使ってみよう | SiTest (サイテスト) ブログ

メニューボタン閉じるボタン

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

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

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

今回作ったサンプルは以下のリンクから確認できます。
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は1、FALSEは0として計算されるため、結果としてペンだけの数量合計が求められます。

条件がある場合の合計 その2

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

=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のセルには上記の式が入っています。

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

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

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

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

OR条件を使う場合

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

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

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

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

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

順位を出したい場合

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

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

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

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

まとめ

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