【簡単エクセル/Excel】複雑な条件を指定して集計を行う方法｜SUMPRODUCT関数｜Excel/エクセル関数 #004
運営者・ポテ
いつもありがとうございます！
「わかりやすいExcel関数解説シリーズ」へようこそ。
本稿では、「SUMPRODUCT関数」を、わかりやすく解説します。
へ？SUMPRODUCT？
って思いますよね。わかります。
Excelでの集計といえば、SUMIF関数やCOUNTIF関数が定番ですが、SUMPRODUCT関数はそれらに比べて少し知名度が低く、名前からも何をする関数なのか分かりにくいですよね。
ですがこの関数は、他の関数では対応が難しいような複雑な条件の集計にも、柔軟に対応できるのが強みです。一見するととっつきにくい印象がありますが、基本的な仕組みを理解すれば、さまざまな場面で活用できます。
本稿では、SUMPRODUCT関数の基本的な使い方や、他の関数との違い、実例を使った解説などを、わかりやすく解説します。
Excel関数を使いこなして、欲しいデータをすばやく手に入れ、仕事量は半分に、成果は2倍にしていきましょう。初心者でも理解しやすいように、やさしく丁寧に解説していきます。ぜひ最後までご覧ください。
SUMPRODUCT関数とは？
SUMPRODUCT関数は、英語の「Sum（合計）」と「Product（積）」を組み合わせた名前で、その名のとおり「積」の「合計」を求める関数です。
ただ、それだけではありません。SUMPRODUCT関数の本当の強みは、複数の範囲を条件に応じて柔軟に集計できることにあります。
たとえば、「70点以上の人の人数を数える」「条件を満たすデータの合計を求める」などの処理が、IF関数を使わなくても実現できます。
基本のしくみは、「複数の範囲（配列）を、対応する要素ごとに掛け合わせ、その結果をすべて合計する」というものです。
この考え方をベースにしつつ、戻り値のTRUE/FALSEを1/0に変換するような工夫を加えることで、条件付きの件数カウントや合計にも応用できます。
SUMPRODUCT関数の2つの使い方
SUMPRODUCT関数には大きく分けて2つの使い方があります。
- 積の合計を求める
- 条件に一致するデータの個数をカウントする
それぞれ解説していきます。
1. 積の合計を求める方法
1つ目は、「積」の「合計」です。
SUMPRODUCT関数は、英語の「Sum（合計）」と「Product（積）」を組み合わせた名前で、その名のとおり、「積の合計」を求めることができます。
これは、複数の数値を「行ごとに掛け算」し、その結果をすべて合計する、という使い方です。
たとえば、次のような「数量」と「単価」が並んでいる表があるとします。
このような場合は、それぞれの行で「数量 × 単価」の計算を行い、その合計を求めるために、SUMPRODUCT関数を使うことができます。
この場合、SUMPRODUCT関数の基本的な処理の流れは次のとおりです。
まず、各行で次のような計算が行われます。
- 2 × 200 = 400（りんご）
- 5 × 100 = 500（みかん）
- 3 × 300 = 900（ぶどう）
そして、これらの値をすべて足し合わせます。つまりこの例では、400 + 500 + 900 = 1,800 となります。
こうした処理を、関数1つで簡潔に実現できるのがSUMPRODUCT関数です。文字どおり、「積（Product）の合計（Sum）」を求める関数というわけですね。
2. 条件に一致するデータの個数をカウントする
2つ目は、ある条件に合うデータの個数をカウントするという使い方です。
たとえば、「70点以上の人が何人いるか」といった条件付きのカウントをしたい場合、COUNTIF関数またはCOUNTIFS関数でも実現できますが、SUMPRODUCT関数でも同じことができます。同じことができるだけでなくむしろ優れている点も多くあります。
ポイントは、条件に合えば「TRUE」、合わなければ「FALSE」となるような条件式を使い、それを数値に変換してカウントするというところです。
TRUE は 1、FALSE は 0 として扱うことができるため、その合計が「条件を満たす件数」になります。
この方法の利点は、I複雑な条件付きのカウントが1つの式で完結するという点です。しかも、複数の条件を組み合わせることもできるため、より柔軟な集計が可能になります。
COUNTIF/COUNTIFS関数と比較した場合の利点
- 関数の構造がシンプルになる
COUNTIFSでは、条件ごとに「範囲」と「条件」をセットで書く必要があり、複雑になると式が読みにくくなります。一方、SUMPRODUCTではすべての条件を数式としてまとめて書けるため、関数の構造がスッキリし、後から見直しやすいという利点があります。 - OR条件に対応できる
COUNTIFSは、「すべての条件を満たす（AND条件）」しか扱えません。たとえば「部門がAまたはB」のようなOR条件は、COUNTIFSでは表現が難しくなります。SUMPRODUCTなら、(部門="A") + (部門="B")のようにシンプルに書くことができます。 - 範囲どうしの比較ができる
COUNTIFSは、「売上が基準値以上」といった列と列の比較には対応していません。
SUMPRODUCTであれば、売上範囲 >= 基準値範囲のように、範囲ごとの比較も可能です。 - 計算式や演算を含む条件に対応している
「売上 ÷ 単価が10以上」といった数値の計算結果を条件にしたい場合、COUNTIFSでは対応できません。SUMPRODUCTなら、こうした演算をそのまま条件に含めることができます。
SUMPRODUCT関数の基本構文と具体例
1. 積の合計を求める方法の基本構文を具体例
積の合計を求める方法の基本構文は次の通りです。
' 基本構文
=SUMPRODUCT(配列1, 配列2)具体的には次のように使います。
' 例
=SUMPRODUCT(B2:B4, C2:C4)この例では、各行でB列×C列を掛け算し、その合計を求めます。つまり B2×C2 + B3×C3 + B4×C4 のような処理が行われます。
実際に関数をセルに入力してみましょう。セルC6に次のように入力してみます。
すると次のように結果が返ります。
2. 条件に一致するデータの個数をカウントする基本構文と具体例
条件に一致するデータの個数をカウントする基本構文は次の通りです。
' 基本構文
=SUMPRODUCT(--(条件式))ここで、-- は、TRUE や FALSE を 1 や 0 に変換するための記号です。SUMPRODUCT は数値の合計を求める関数なので、条件式の結果を数値に変換する必要があります。
この関数は、具体的には、次のように使います。
' 例
=SUMPRODUCT(--(B2:B5>=70))この例では、範囲内の各セルについて B2:B5>=70 が TRUE か FALSE かを判定し、TRUE を 1、FALSE を 0 に変換して合計することで、「70点以上の人数」をカウントします。
実際に関数をセルに入力してみましょう。セルB7に次のように入力してみます。
すると次のように結果が返ります。
70点以上が2名いますので、合っていますね。
3. 【応用編】複数条件に一致するデータの個数をカウントする方法
複数条件を指定することも可能です。構文は次の通りです。
' 複数条件を指定する場合の構文
=SUMPRODUCT(--(条件1), --(条件2), ...)具体例を示します。
次の結果が返ります。
この例では、各行について2つの条件を1（TRUE）か0（FALSE）で判定し、それらを掛け合わせた結果を合計しています。
実際に、各行の計算結果どうなるか見てみましょう：
| 名前 | 点数 | クラス | 判定①：点数>=70 | 判定②：クラス="A組" | ①×② |
|---|---|---|---|---|---|
| 佐藤 | 80 | A組 | 1 | 1 | 1 |
| 鈴木 | 65 | B組 | 0 | 0 | 0 |
| 田中 | 90 | A組 | 1 | 1 | 1 |
| 山田 | 58 | A組 | 0 | 1 | 0 |
最後の列（①×②）が 1 になっている行が、両方の条件を満たしていることを意味します。つまり、1+0+1+0=2となります。
なお、8行目には、2条件目を"B組"に変更した関数を入力しています。こちらは、同様の計算の結果、①×②が0となりますので、0が返ります。
おわりに
ご覧いただきありがとうございました。
本稿では、「複雑な条件を指定して集計を行う方法｜SUMPRODUCT関数」を解説いたしました。
お問い合わせやご要望等ございましたら、「お問い合わせ／ご要望」またはコメントにて、ご連絡いただければ幸いでございます。
皆様の人生がより一層素晴らしいものになるよう、少しでもお役に立てれば幸いでございます。
なお、当サイトでは様々な情報を発信しております。もしよろしければ、トップページもご覧いただけると幸いでございます。
Excel関数スキルアップのための参考情報
Excel関数に関する書籍は数多く出版されています。もし興味がございましたら、チェックしてみてください。入門書に関しては、どの書籍も大きな違いはありません。あまり悩むことに時間をかけるよりは、手頃なものを一冊選んでみると良いでしょう。
＼チェックしてみよう／
＼チェックしてみよう／
＼チェックしてみよう／
この記事を書いた人
- ■人生を追求する凡人■日本一安全で、気の向くままに自分の時間を過ごせる、こだわりのキャンプ場を作るのが夢■光学・機械系エンジニア（歴20年、内マネジメント10年、特許数件権利化）/副業フリーランスエンジニア■読書・文学愛好■人生は時間そのもの。ひとりでも多くの人が「より良い人生にするために時間を使って欲しい」と願い、仕事のスキルの向上、余暇の充実、資産形成を研究。■VBAアプリ開発サービス提供中（業務委託 / VBA使用経験20年）■Python愛好（歴5年）■VBAエキスパート「Excel VBA スタンダード」（上級者向け資格）/ Python 3 エンジニア認定基礎（経済産業省「ITスキル標準（ITSS）」に掲載）
