Excelで集計作業をしていると、
「個数×単価の合計を一発で出したい」
「複数条件に合うデータだけ集計したい」
「単純な平均ではなく、件数を考慮した平均を出したい」
と思ったことはありませんか？
そんなときに便利なのが、SUMPRODUCT関数です。
SUMPRODUCT関数は、名前だけ見ると少し難しそうに感じますが、実務ではかなり使える関数です。
たとえば、
- 個数×単価の合計を出す
- 条件に合うデータだけ合計する
- 加重平均を計算する
- 複数条件のランキングを作る
といった場面で活用できます。
この記事では、SUMPRODUCT関数の基本的な使い方から、実務で役立つ複数条件の集計、加重平均の計算方法まで解説します。
SUMPRODUCT関数とは？
SUMPRODUCT関数は、複数の範囲を掛け算して、その結果を合計する関数です。
SUMPRODUCT関数の基本構文は以下のとおりです。
=SUMPRODUCT(配列1,配列2,...)たとえば、B列に「個数」、C列に「単価」がある場合、「個数」×「単価」をそれぞれ計算し、その合計を出したいことがあります。
通常であれば、D列に「個数×単価」の計算列を作ってから、最後にSUM関数で合計することが多いです。
しかし、SUMPRODUCT関数を使えば、補助列を作らずに一発で合計できます。
SUMPRODUCT関数の基本的な使い方
たとえば、以下のような表があるとします。
| 商品 | 個数 | 単価 |
|---|---|---|
| A商品 | 3 | 100 |
| B商品 | 5 | 200 |
| C商品 | 2 | 150 |
| D商品 | 4 | 300 |
このとき、SUMPRODUCT関数で合計金額を求めるには、以下の数式を使います。
=SUMPRODUCT(B2:B5,C2:C5)この数式では、
3×100
5×200
2×150
4×300をそれぞれ計算し、最後にすべて合計しています。
つまり、
300 + 1000 + 300 + 1200 = 2800となります。
SUMPRODUCT関数を使うことで、計算用の列を追加しなくても、掛け算した結果の合計を求めることができます。
SUMPRODUCT関数で複数条件の集計をする
SUMPRODUCT関数は、複数条件の集計にも使えます。
たとえば、以下のような表があるとします。
| 氏名 | 部署 | 地域 | 売上 |
|---|---|---|---|
| 佐藤 | 営業部 | 東日本 | 120 |
| 鈴木 | 営業部 | 東日本 | 150 |
| 田中 | 総務部 | 東日本 | 90 |
| 高橋 | 営業部 | 西日本 | 130 |
| 伊藤 | 営業部 | 東日本 | 110 |
この表から、「営業部かつ東日本の売上合計」を求めたい場合は、以下の数式を使います。
=SUMPRODUCT(($B$2:$B$6="営業部")*($C$2:$C$6="東日本")*$D$2:$D$6)この数式では、以下の条件を指定しています。
B列が「営業部」 かつ C列が「東日本」
その条件に当てはまる行だけ、D列の売上を合計しています。
複数条件集計の数式の仕組み
先ほどの数式をもう一度見てみます。
=SUMPRODUCT(($B$2:$B$6="営業部")*($C$2:$C$6="東日本")*$D$2:$D$6)この数式では、条件に一致するかどうかを判定しています。
たとえば、
$B$2:$B$6="営業部"は、B列が「営業部」かどうかを判定しています。
条件に合う場合はTRUE、合わない場合はFALSEになります。
条件式の結果はTRUE/FALSEになりますが、SUMPRODUCT関数の中で「*」を使って掛け算すると、TRUEは1、FALSEは0のように扱われます。
・TRUE → 1
・FALSE → 0
そのため、
「営業部」である かつ 「東日本」である
という条件を満たした行だけが1になり、条件に合わない行は0になります。
最後に売上を掛けることで、条件に合う行の売上だけが残り、条件に合わない行は0として扱われます。
つまり、SUMPRODUCT関数では、「条件に合う行だけを残して合計する」という集計ができるわけです。
SUMIFS関数との違い
複数条件の合計だけであれば、SUMIFS関数でも対応できます。
たとえば、営業部かつ東日本の売上合計であれば、以下のように書けます。
=SUMIFS($D$2:$D$6,$B$2:$B$6,"営業部",$C$2:$C$6,"東日本")このような単純な条件付き合計であれば、SUMIFS関数のほうが分かりやすいです。
一方で、SUMPRODUCT関数は、
- 条件を掛け合わせて考えられる
- 加重平均に使える
- 配列計算の考え方を応用できる
- 条件付きランキングなどにも応用できる
という特徴があります。
そのため、単純な条件付き合計ならSUMIFS、応用的な集計や計算まで広げたい場合はSUMPRODUCT、と考えると分かりやすいです。
SUMPRODUCT関数で加重平均を計算する
SUMPRODUCT関数は、加重平均を求めるときにも便利です。
加重平均とは、単純な平均ではなく、件数や重みを考慮して計算する平均のことです。
たとえば、5段階評価のアンケート結果が以下のようになっているとします。
| 評価 | 人数 |
|---|---|
| 1 | 2 |
| 2 | 4 |
| 3 | 8 |
| 4 | 10 |
| 5 | 6 |
このとき、単純に1〜5の平均を取るのではなく、各評価を選んだ人数を考慮して平均を出す必要があります。
この場合は、以下の数式を使います。
=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)この数式では、「評価」×「人数」を計算し、その合計を人数の合計で割っています。
つまり、
(1×2 + 2×4 + 3×8 + 4×10 + 5×6) ÷ 人数の合計という計算です。
これにより、回答人数を考慮した平均評価を求めることができます。
ここに「5段階評価の加重平均を計算した画像」を貼る
加重平均が役立つ場面
加重平均は、実務でもよく使います。
たとえば、以下のような場面です。
- アンケートの平均評価を出す
- サービスの平均利用期間を求める
- 商品レビューの平均点を計算する
- 単価と数量を考慮した平均単価を出す
単純な平均では、件数の多さや重みを反映できません。
一方で、SUMPRODUCT関数を使った加重平均なら、実際の件数や比率を反映した平均を出せます。
そのため、分析や集計ではかなり使いやすい計算方法です。
応用：SUMPRODUCT関数は条件付きランキングにも使える
SUMPRODUCT関数は、条件付きランキングを作るときにも使えます。
たとえば、
- 部署ごとに売上順位を出したい
- 地域と部署が同じ人の中で順位を出したい
- 同点時に利益で順位を分けたい
といったケースです。
たとえば、部署と地域が同じ人の中で売上ランキングを出す場合は、以下のような数式を使います。
=SUMPRODUCT(($B$3:$B$14=B3)*($C$3:$C$14=C3)*($D$3:$D$14>D3))+1この数式では、
「同じ部署」 かつ 「同じ地域」 かつ 「自分より売上が高い」
という条件を満たす人数を数え、最後に1を足して順位を出しています。
👉ただし、ランキングは「自分より上の人の数を数える」という考え方になるため、詳しい作り方は以下記事で解説しています。
SUMPRODUCT関数を使うときの注意点
SUMPRODUCT関数を使うときは、いくつか注意点があります。
範囲のサイズをそろえる
SUMPRODUCT関数では、指定する範囲のサイズをそろえる必要があります。
たとえば、以下のように範囲がずれているとエラーや誤計算の原因になります。
B列・D列は3行目から14行目まで、C列は3行目から15行目までになっているため、範囲の長さが違います。
正しくは、C列も14行目までに修正し、範囲をそろえることが必要です。
条件はかっこで囲み、「*」でつなぐ
複数条件で集計するときは、条件式をかっこで囲み、「*」でつなぐ必要があります。
=SUMPRODUCT(($B$2:$B$6="営業部")*($C$2:$C$6="東日本")*$D$2:$D$6)このように、
・条件式をかっこで囲む
・条件式同士を「*」でつなぐ
ようにしましょう。
数値が文字列になっていないか確認する
売上や単価などが数値ではなく文字列になっていると、正しく計算できないことがあります。
見た目は数字でも、文字列として入力されている場合があるので注意しましょう。
うまく計算できない場合は、
- セルの表示形式
- 数値が文字列になっていないか
- 余分なスペースが入っていないか
を確認してみてください。
データ量が多いと重くなることがある
SUMPRODUCT関数は便利ですが、大量のデータに対して多用すると、計算が重くなることがあります。
特に、列全体を指定するような数式は注意が必要です。
=SUMPRODUCT((B:B="営業部")*(C:C="東日本")*D:D)このように列全体を指定すると、処理する範囲が大きくなりすぎる場合があります。
できれば、必要な範囲だけを指定するのがおすすめです。
=SUMPRODUCT(($B$2:$B$1000="営業部")*($C$2:$C$1000="東日本")*$D$2:$D$1000)よくある質問（FAQ）
SUMPRODUCT関数は何に使う関数ですか？
SUMPRODUCT関数は、複数の範囲を掛け算して、その結果を合計する関数です。
個数×単価の合計、複数条件の集計、加重平均などに使えます。
SUMPRODUCT関数とSUMIFS関数はどちらを使えばいいですか？
単純な条件付き合計であれば、SUMIFS関数のほうが分かりやすいです。
一方で、加重平均や条件付きランキングなど、掛け算や配列計算を組み合わせたい場合は、SUMPRODUCT関数が便利です。
SUMPRODUCT関数で複数条件の集計はできますか？
できます。
たとえば、営業部かつ東日本の売上合計を求める場合は、以下のように書けます。
=SUMPRODUCT(($B$2:$B$6="営業部")*($C$2:$C$6="東日本")*$D$2:$D$6)条件を「*」 でつなげることで、複数条件を満たすデータだけを集計できます。
SUMPRODUCT関数で加重平均は出せますか？
出せます。
たとえば、A列に評価、B列に人数がある場合は、以下のように計算できます。
=SUMPRODUCT(A2:A6,B2:B6)/SUM(B2:B6)評価と人数を掛け合わせた合計を、人数の合計で割ることで、人数を考慮した平均を求められます。
SUMPRODUCT関数でランキングも作れますか？
作れます。
SUMPRODUCT関数を使えば、部署や地域などの複数条件に合うデータの中で、順位を出すこともできます。
ただし、ランキングは「自分より上の人の数を数える」という考え方になるため、詳しい作り方は以下記事で解説しています。
まとめ：SUMPRODUCT関数は実務の集計に便利
SUMPRODUCT関数は、複数の範囲を掛け算して、その結果を合計する関数です。
基本的な使い方としては、
- 個数×単価の合計を出す
- 複数条件に合うデータだけ集計する
- 加重平均を計算する
- 条件付きランキングに応用する
といった使い方があります。
最初は少し難しそうに見えるかもしれません。
しかし、考え方は、「条件に合うものだけを残して計算する」というイメージです。
特に、加重平均や複数条件の集計では、実務でも役立つ場面が多いです。
まずは、
=SUMPRODUCT(個数の範囲,単価の範囲)のような基本形から試してみるのがおすすめです。
慣れてきたら、複数条件の集計や加重平均にも活用してみてください。
コメント