スプレッドシートのSUMPRODUCT関数の使い方｜配列の積の和

スポンサーリンク

スプレッドシートのSUMPRODUCT関数の使い方｜配列の積の和

「単価と数量をいちいち掛け算して、さらにSUM関数で合計するのが面倒…」そんな経験、ありませんか？

作業列が増えるとシートがゴチャゴチャしますし、行が増えたときの範囲修正も手間ですよね。

そんなときに使えるのがSUMPRODUCT関数です。掛け算と合計をまとめて1つの数式で処理できます。

この記事では基本の使い方から条件付き集計・加重平均の計算まで紹介します。

SUMPRODUCT関数とは？

SUMPRODUCT関数（読み方: サムプロダクト関数）は、複数の配列の対応する要素を掛け算し、その合計を返す関数です。

名前は「SUM（合計）」と「PRODUCT（積・掛け算）」の組み合わせです。文字どおり「掛け算の合計」を求めます。

たとえば商品の「単価」と「数量」が別々の列にあるとします。通常なら作業列で「単価 x 数量」を1行ずつ計算しますよね。さらにSUM関数で合計する必要があります。SUMPRODUCT関数なら1つの数式で済みます。

SUMPRODUCT関数にできることをまとめると、次のとおりです。

  • 複数の範囲を要素ごとに掛け算して合計する
  • 条件に合うデータだけを集計する（条件付き集計）
  • 複数の条件をAND・ORで組み合わせて集計する
  • 加重平均（重み付き平均）を計算する
  • 作業列なしでスッキリ計算できる

「掛けてから合計」を1つの数式で実行する関数です。

NOTE

SUMPRODUCT関数はGoogleスプレッドシートの全バージョンで使えます。Excelとの互換性も完全なので、ファイルのやり取りでも安心です。

SUMPRODUCT関数の書き方（構文と引数）

基本構文

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

カッコの中に、計算したい配列やセル範囲を指定します。

引数の説明

引数必須/任意説明
配列1必須計算の対象となる最初の配列またはセル範囲
配列2, …任意掛け合わせたい追加の配列やセル範囲（最大30個）

引数が1つだけの場合は範囲の合計を返します。SUM関数と同じ動作です。

引数を2つ以上指定すると、対応する位置の要素同士を掛けてから合計します。

ここで大事なポイントがひとつ。すべての配列は同じサイズ（行数・列数）にする必要があります。サイズが違うと #VALUE! エラーになります。

SUMPRODUCT関数の基本的な使い方

単価×数量の合計を求める

実際にSUMPRODUCT関数を使ってみましょう。よくある「単価 x 数量」の合計を求める例です。

次のような商品リストがあるとします。

 A列（商品名）B列（単価）C列（数量）
1行目商品名単価数量
2行目ノート20050
3行目ペン15080
4行目消しゴム10030
5行目ファイル30020

全商品の売上合計を出すには、次の数式を入力します。

=SUMPRODUCT(B2:B5, C2:C5)

この数式が内部で行っている計算はこうなります。

(200×50) + (150×80) + (100×30) + (300×20)
= 10000 + 12000 + 3000 + 6000
= 31000

結果は 31,000 です。

SUMPRODUCT関数を使わない場合を考えてみましょう。D列に =B2*C2 と作業列を作り、さらに =SUM(D2:D5) で合計する2ステップが必要です。SUMPRODUCT関数なら作業列なしで済みます。

配列が1つだけの場合

引数を1つだけ指定するとSUM関数と同じ動作をします。

=SUMPRODUCT(B2:B5)

この場合は 200+150+100+300 = 750 です。あまり使う場面はありませんが、仕組みとして覚えておきましょう。

SUMPRODUCT関数で条件付き集計する方法

SUMPRODUCT関数の真価が発揮されるのは条件付き集計です。SUMIFS関数では対応しにくいOR条件にも使えます。

1つの条件で集計する

商品リストにカテゴリ列を追加したデータで考えてみます。

 A列（商品名）B列（カテゴリ）C列（単価）D列（数量）
1行目商品名カテゴリ単価数量
2行目ノート文房具20050
3行目ペン文房具15080
4行目マウスPC周辺機器200010
5行目ファイル文房具30020

「文房具」だけの売上合計を出す数式はこちらです。

=SUMPRODUCT((B2:B5="文房具")*C2:C5*D2:D5)

ちょっと不思議な書き方に見えますよね。ポイントは (B2:B5="文房具") の部分です。

この条件式は各セルが「文房具」と一致するかを判定します。一致すれば TRUE(1)、不一致なら FALSE(0) です。

掛け算に含めることで、条件に合わない行は自動的に0になります。

(TRUE×200×50) + (TRUE×150×80) + (FALSE×2000×10) + (TRUE×300×20)
= (1×200×50) + (1×150×80) + (0×2000×10) + (1×300×20)
= 10000 + 12000 + 0 + 6000
= 28000

結果は 28,000 です。

複数条件（AND条件）で集計する

条件を2つ以上組み合わせることもできます。「文房具」で、かつ「単価が200以上」の売上合計を出す場合です。

=SUMPRODUCT((B2:B5="文房具")*(C2:C5>=200)*C2:C5*D2:D5)

条件部分をカッコで囲んで でつなぐだけです。 は「AND（かつ）」の意味になります。

条件を増やしたいときも同じパターンで追加できますよ。

OR条件で集計する

「文房具またはPC周辺機器」のようなOR条件にも対応できます。ここがSUMPRODUCT関数の強みです。

=SUMPRODUCT(((B2:B5="文房具")+(B2:B5="PC周辺機器"))*C2:C5*D2:D5)

条件を + でつなぐとOR条件になります。

  • *（掛け算） = AND条件（すべて満たす）
  • +（足し算） = OR条件（いずれか満たす）

SUMIFS関数でOR条件を書くと、関数を何個も足し算する必要があります。SUMPRODUCT関数なら1つの数式にまとまります。

TIP

OR条件で + を使うと、両方の条件に一致する行は2重カウントされます。重複を防ぎたい場合は ((条件1)+(条件2)>0) のように >0 を付けましょう。

SUMPRODUCT関数の実務活用パターン

基本がわかったところで、仕事でよく使うパターンを紹介します。

パターン1: 見積書の合計金額を一発計算

見積書で「単価 x 数量」の合計を出すパターンです。

=SUMPRODUCT(C2:C20, D2:D20)

C列に単価、D列に数量が入っている見積書ならこれだけです。範囲を広めにとっておけば、行を追加しても修正不要です。

パターン2: 加重平均の計算

テストの点数に重み（配点）を付けて平均を出すパターンです。

 A列（科目）B列（点数）C列（配点比率）
2行目国語801
3行目数学702
4行目英語901.5

配点を加味した加重平均を求めるにはこう書きます。

=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)

計算の中身はこうなります。

(80×1 + 70×2 + 90×1.5) / (1 + 2 + 1.5)
= (80 + 140 + 135) / 4.5
= 355 / 4.5
= 78.9（小数点以下四捨五入）

単純な平均 (80+70+90)/3 = 80 とは異なる結果です。配点の大きい数学（70点）の影響で、加重平均のほうが低くなっています。

NOTE

加重平均は成績評価だけでなく、仕入れ先ごとの加重平均単価の計算にも使えます。仕入量の多い先の単価をより強く反映できます。

パターン3: 複数条件のOR集計

「営業部または総務部」の売上合計を求めるパターンです。

=SUMPRODUCT(((B2:B100="営業部")+(B2:B100="総務部")>0)*E2:E100)

条件が3つ以上でも同じ書き方で拡張できます。

=SUMPRODUCT(((B2:B100="営業部")+(B2:B100="総務部")+(B2:B100="企画部")>0)*E2:E100)

SUMIFS関数の足し算で書くと3つの関数が並んで長くなります。SUMPRODUCT関数なら1つの数式にまとまりますよ。

パターン4: 特定条件の件数カウント

SUMPRODUCT関数は条件に合うデータの件数を数えることもできます。

=SUMPRODUCT((B2:B100="営業部")*(D2:D100="プランA"))

条件式の結果（TRUE=1/FALSE=0）を掛け算するだけです。合計範囲を指定しないため、1の合計=件数になります。

COUNTIFS関数と同じ結果が得られますが、OR条件にも対応できるのが利点です。

よくあるエラーと対処法

SUMPRODUCT関数で困ったときの原因と対策をまとめました。

エラー/症状原因対処法
#VALUE!配列のサイズ（行数・列数）が不一致すべての配列を同じサイズに揃える
結果が0セルが「数値に見える文字列」VALUE関数で数値に変換、またはデータクリーンアップを実行
結果が0全角・半角の不一致ASC関数（半角化）またはJIS関数（全角化）で統一
結果がおかしい条件部分のカッコ忘れ条件式は必ずカッコで囲む
結果がおかしいOR条件で2重カウント>0 を付けて重複を防ぐ

#VALUE!エラーが出る場合

最も多い原因は配列のサイズ不一致です。

=SUMPRODUCT(A1:A5, B1:B3)

この数式はA列が5行、B列が3行で行数が合いません。すべての配列を同じ範囲に揃えてください。

=SUMPRODUCT(A1:A5, B1:B5)

データを追加した後は特にズレやすいので確認しましょう。

結果が0になる場合

数値が入っているのに結果が0になるときは「文字列型の数値」が原因です。CSVやWebからコピーしたデータでよく起きます。

セルの表示が左揃えなら文字列型のサインです。次の方法で修正できます。

  1. 対象セルを選択 → メニュー「データ」→「データクリーンアップ」で一括変換
  2. 数式内でVALUE関数を使って変換
=SUMPRODUCT(VALUE(B2:B5)*C2:C5)

条件付き集計の結果がおかしい場合

条件式のカッコ忘れが原因のことが多いです。

=SUMPRODUCT(B2:B5="文房具"*C2:C5*D2:D5)

上の数式ではカッコがないため、計算順序がおかしくなります。

=SUMPRODUCT((B2:B5="文房具")*C2:C5*D2:D5)

条件式は必ず () で囲むのがルールです。

似た関数との違い・使い分け

SUMPRODUCT関数と似た機能を持つ関数を比較します。

関数名用途条件の扱い
SUM無条件で合計条件なし
SUMIF1条件で合計1つだけ
SUMIFS複数条件で合計AND（すべて満たす）
SUMPRODUCT配列の積を合計AND・OR両対応

SUM→SUMIF→SUMIFS→SUMPRODUCTの使い分け

集計関数は段階的に使い分けるのがおすすめです。

  1. 条件なしの合計 → SUM関数
  2. 条件が1つ → SUMIF関数
  3. 条件が2つ以上（AND） → SUMIFS関数
  4. OR条件や掛け算を含む集計 → SUMPRODUCT関数

条件の複雑さに合わせてステップアップするイメージです。シンプルな集計にSUMPRODUCT関数を使う必要はありません。

SUMIFS関数とSUMPRODUCT関数の比較

どちらも複数条件で合計できます。使い分けのポイントを整理しましょう。

比較項目SUMIFSSUMPRODUCT
AND条件得意（標準機能）得意（条件を*で連結）
OR条件苦手（足し算が必要）得意（条件を+で連結）
掛け算を含む集計できない得意（配列の積の和）
数式の読みやすさシンプル慣れが必要
処理速度速いやや遅い
ワイルドカード使える使えない

AND条件だけならSUMIFS関数がシンプルで高速です。OR条件や掛け算を含む集計にはSUMPRODUCT関数を選びましょう。

Excelとの違い

SUMPRODUCT関数の動作はExcelとほぼ同じです。

項目GoogleスプレッドシートExcel
基本動作同じ同じ
引数の上限最大30個最大255個
配列サイズ不一致#VALUE!#VALUE!
条件付き集計の書き方同じ同じ
空白セルの扱い0として処理0として処理

引数の上限に違いはありますが、実務で30個を超えることはまずありません。ExcelのSUMPRODUCT関数と同じ書き方で使えますよ。

まとめ

SUMPRODUCT関数のポイントをおさらいしましょう。

  • SUMPRODUCT関数は「掛け算してから合計」を1つの数式で実行できる
  • 条件付き集計は (条件式)*集計範囲 の形で書く
  • AND条件は *、OR条件は + でつなぐ
  • 加重平均は =SUMPRODUCT(値, 重み)/SUM(重み)
  • 配列のサイズは必ず揃える（揃っていないと#VALUE!エラー）
  • シンプルな条件にはSUMIFS、OR条件や掛け算集計にはSUMPRODUCT

まずは =SUMPRODUCT(単価範囲, 数量範囲) の基本形から試してみてください。作業列が不要になりますよ。

この記事で紹介した関数・関連記事

スポンサーリンク
タイトルとURLをコピーしました