今回は、ExcelのSUMPRODUCTを使って条件付き計算を扱う方法を紹介します。
SUMPRODUCTは複数条件の計算に使いやすい
Excelで表を集計するとき、合計ならSUM、条件付きの合計ならSUMIFやSUMIFSを使う場面が多くあります。ただし、条件ごとに掛け算を含めたい、該当する行だけ数量と単価を掛けて合計したい、条件の組み合わせを少し柔軟に扱いたい、といったときはSUMPRODUCTが役立ちます。
SUMPRODUCTは、指定した範囲同士を掛け合わせ、その結果を合計する関数です。名前だけを見ると積の合計に見えますが、条件判定と組み合わせることで、条件に合う行だけを計算対象にする使い方ができます。
たとえば売上表で、商品区分が「文具」の行だけ、数量と単価を掛けた金額を合計したい場合があります。このようなとき、作業列を追加して各行の金額を出してから集計する方法もありますが、SUMPRODUCTなら1つの式で処理できます。
基本の考え方
SUMPRODUCTを条件付き計算に使うときは、条件に合う行を1、合わない行を0として扱います。1になった行は計算に残り、0になった行は計算結果に影響しません。この仕組みを理解すると、式の意味が追いやすくなります。
条件式はTRUEとFALSEを返す
Excelでは、範囲に対して条件を指定すると、各行ごとにTRUEまたはFALSEの結果が返ります。たとえば、A列に部門名があり、A2からA20の範囲で「営業」に一致するかを調べると、行ごとに一致ならTRUE、不一致ならFALSEになります。
SUMPRODUCTの中では、このTRUEとFALSEを数値として扱える形に変換します。TRUEは1、FALSEは0として考えると、条件に合う行だけが残る理由が分かりやすくなります。
掛け算で対象行を絞る
条件に合う行だけ数量と単価を計算したい場合、条件判定の結果、数量、単価を掛け合わせます。条件に合わない行は0が掛かるため、計算結果は0になります。条件に合う行は1が掛かるため、数量と単価の計算結果が残ります。
考え方は次のようになります。
- 条件に合う行：1 × 数量 × 単価
- 条件に合わない行：0 × 数量 × 単価
- 最後に全行分を合計する
このように見ると、SUMPRODUCTは難しい特別な関数というより、行ごとの判定と計算をまとめて行う関数として理解できます。
単一条件で使う例
A列に部門、B列に数量、C列に単価がある表を考えます。営業部門だけの金額を集計したい場合は、A列の条件判定と、B列、C列の掛け算を組み合わせます。
式の形は次のような考え方になります。
- A列が営業かどうかを判定する
- 該当する行だけ数量と単価を掛ける
- 結果を合計する
SUMIFSでは、先に各行の金額列を作っておくと集計しやすくなります。一方で、作業列を増やしたくない場合や、一時的な確認で金額を出したい場合は、SUMPRODUCTを使うと表の形を変えずに計算できます。
作業列を増やすかどうかを決める
SUMPRODUCTは便利ですが、すべての計算を1つの式にまとめればよいわけではありません。同じ金額列を何度も使うなら、数量×単価の作業列を作ったほうが見通しがよくなることがあります。
一時的な分析、条件を試しながら確認したい集計、列を追加しにくい共有ファイルではSUMPRODUCTが向いています。逆に、他の人も頻繁に確認する表では、計算の途中が見える作業列を使うほうが説明しやすくなります。
複数条件で使う例
SUMPRODUCTは複数条件にも対応できます。部門が「営業」で、商品区分が「文具」の行だけを計算したい場合、2つの条件判定を掛け合わせます。両方の条件を満たす行だけ、1
× 1 になります。どちらか一方でも満たさない行は、0が含まれるため対象外になります。
AND条件は掛け算で考える
複数条件の中でも、すべての条件を満たす行だけを対象にする場合は、条件同士を掛け算します。これはAND条件として考えると分かりやすいです。
- 部門が営業である
- 商品区分が文具である
- 数量と単価を掛ける
この3つをまとめることで、該当する行の金額だけを集計できます。条件が増えるほど式は長くなりますが、各条件を括弧で分けると読みやすくなります。
OR条件は足し算を使う
「営業」または「企画」のように、どちらかに該当すれば対象にしたい場合は、条件判定を足し算で考えます。ただし、同じ行が複数の条件に同時に当てはまる場合は、重複して数えないよう注意が必要です。
部門名のように1つのセルに1つの値しか入らない条件なら、営業と企画が同じセルで同時に成立することはありません。この場合は足し算で扱いやすくなります。複数列をまたいでOR条件を作るときは、同じ行が二重に対象にならないか確認します。
条件付き計算で起きやすいミス
SUMPRODUCTの式が合わないときは、関数そのものより範囲や条件の指定に原因があることが多いです。特に、範囲の行数がそろっていない、数値が文字列として保存されている、空白に見えるセルに余分な文字が入っている、といった点を確認します。
範囲の大きさをそろえる
SUMPRODUCTでは、掛け合わせる範囲の大きさをそろえる必要があります。A2:A100、B2:B100、C2:C100のように、開始行と終了行を合わせます。片方だけA2:A99のようになっていると、正しく計算できません。
表の途中までを指定すると、新しい行を追加したときに集計から漏れることがあります。継続して使う表なら、Excelのテーブル機能と組み合わせて、列名で範囲を指定する方法も検討できます。テーブルを使うと行の追加に追随しやすくなります。
数値に見える文字列を確認する
外部システムから取り込んだデータでは、数量や単価が文字列として入っていることがあります。見た目は数字でも、計算に使うと期待した結果にならないことがあります。左上に小さな印が出ていたり、表示形式を変えても反応が不自然だったりする場合は確認します。
文字列の数字を数値に直すには、区切り位置、値貼り付け、数値変換などの方法があります。SUMPRODUCTの式を直す前に、元データが計算できる状態かを見ると原因を切り分けやすくなります。
空白と余分なスペースを確認する
条件に指定した文字と、表の中の文字が一致していないと対象になりません。見た目では同じでも、末尾に空白があると別の文字列として扱われます。部署名や商品名を手入力している表では、全角スペースや半角スペースが混ざることがあります。
条件付き計算の結果が少ないと感じたら、フィルターで対象列を確認します。似た名前が複数に分かれていないかを見るだけでも、入力の揺れに気づけます。
見やすい式にするコツ
SUMPRODUCTは1つのセルに多くの条件を入れられるため、式が長くなりやすい関数です。後から見返すことを考えるなら、式を短くする工夫より、意味を追える形にすることを優先します。
条件セルを別に用意する
式の中に「営業」や「文具」と直接書くこともできますが、条件を入力するセルを用意して参照すると変更が楽になります。たとえばE2に部門名、F2に商品区分を入れ、SUMPRODUCTの条件部分でそのセルを参照します。
この形にすると、条件を変えるたびに式を編集する必要がありません。集計表として使う場合にも、入力欄を変えるだけで結果を確認できます。
範囲を広げすぎない
列全体を指定すると手軽ですが、ファイルが重くなる原因になることがあります。SUMPRODUCTは範囲内の各行を計算するため、必要な範囲に絞るほうが扱いやすくなります。
行数が増える表では、テーブル化して必要な列を指定する方法が向いています。列全体を参照するよりも、表として管理されている範囲を使うほうが式の意味も読み取りやすくなります。
SUMIFSと使い分ける
条件付き合計だけなら、SUMIFSのほうが読みやすい場面が多くあります。すでに金額列がある表で、部門や日付を条件に合計するならSUMIFSで十分です。SUMPRODUCTは、行ごとの掛け算や条件の組み合わせを同時に扱いたいときに選ぶと効果的です。
使い分けの目安は次のとおりです。
- SUMIFS：集計対象の列がすでにあり、条件で合計したいとき
- SUMPRODUCT：条件に合う行だけ、複数列を掛けて計算したいとき
- 作業列：計算の途中を見せたい、他の人に説明したいとき
関数を使い分けることで、式の読みやすさと表の管理しやすさを保てます。複雑な式を作る前に、作業列を使ったほうが見通しがよいかも考えると、後から直しやすい表になります。
まとめ
ExcelのSUMPRODUCTは、条件に合う行だけを対象にして、数量と単価のような複数列の計算をまとめて行える関数です。条件判定を1と0として考えると、単一条件も複数条件も理解しやすくなります。
使うときは、範囲の大きさをそろえること、数値が文字列になっていないか確認すること、条件文字列の空白や表記揺れを見ることが大切です。SUMIFS、作業列、テーブル機能と使い分けながら、目的に合った形で条件付き計算を組み立てると、集計表を扱いやすくできます。