ExcelのSUMPRODUCT関数の使い方をお探しですね。
広告
Excelの「SUMPRODUCT関数」を使いこなそう!掛け算の合計から複数条件の集計まで
Excelで「単価×数量」のような計算をして合計を出したいとき、わざわざ補助列を作ってSUM関数で足し算していませんか?
実は、SUMPRODUCT関数を使えば、複数の列を掛け算して、その合計を一発で出せるんです。
しかも、条件に合うデータだけを集計したり、複数の条件でカウントしたり、加重平均を計算したりと、いろんな使い方ができます。
この記事では、SUMPRODUCT関数の基本から、ちょっと複雑な集計方法、エラーを防ぐコツまで、実務で役立つ使い方をわかりやすく解説します。
SUMPRODUCT関数の基本的な使い方
SUMPRODUCT関数は、複数の列の値を行ごとに掛け算して、その結果を全部足してくれる関数です。
「SUM」は合計、「PRODUCT」は掛け算という意味なので、「掛け算の合計」を求める関数だと覚えておくとわかりやすいですよ。
使い方はシンプルで、「=SUMPRODUCT(範囲1, 範囲2, …)」と書くだけです。
例えば、B列に単価、C列に数量が入っているとき、「=SUMPRODUCT(B2:B10,C2:C10)」と入力すれば、B2×C2、B3×C3…というふうに各行の売上を計算して、最後に全部合計してくれます。
これって「=B2*C2+B3*C3+B4*C4…」と手入力するのと同じなんですが、SUMPRODUCT関数を使えば数式がスッキリして、データが増えても管理しやすくなります。
それに、「売上」みたいな補助列をわざわざ作らなくても総売上が出せるので、表をシンプルに保ちたいときにも便利です。
売上データ、在庫金額、見積書、原価計算など、数量と単価を掛け算して合計する場面ではとても使いやすい関数なんです。
ただし、指定する範囲は同じ大きさにしないといけません。
B2:B10とC2:C9みたいに行数が違うと「#VALUE!」エラーが出るので注意してくださいね。
SUMPRODUCT関数は、掛け算だけじゃなくて、足し算、引き算、割り算の結果を合計することもできます。
例えば「=SUMPRODUCT(B2:B10-C2:C10)」とすれば、各行でB列からC列を引いた値を合計できます。
でも、一番よく使うのはやっぱり「複数の掛け算をまとめて合計する」使い方です。
まずは単価と数量のようなシンプルな例で練習して、慣れてきたら条件付き集計にチャレンジしてみましょう。
複数の条件で絞り込んで集計する方法
SUMPRODUCT関数のすごいところは、単なる掛け算の合計だけじゃなくて、条件を組み込んだ集計ができることです。
Excelでは、条件に合っているかどうかの判定は、内部的にTRUE(真)かFALSE(偽)として扱われます。
SUMPRODUCT関数では、このTRUEを1、FALSEを0として計算に使えるので、条件を満たす行だけを集計対象にできるんです。
例えば、A列に地域、B列に商品名、C列に売上がある表で、地域が「東京」の売上だけを合計したいときは「=SUMPRODUCT((A2:A100=”東京”)*C2:C100)」と入力します。
複数の条件を全部満たすデータだけを集計したいときは、条件式を「*」でつなぎます。
例えば「東京」で「商品A」の売上を合計するなら、「=SUMPRODUCT((A2:A100=”東京”)*(B2:B100=”商品A”)*C2:C100)」と書きます。
この式では、A列が東京の行は1、違う行は0になって、B列が商品Aの行も同じように1か0になります。
両方の条件を満たす行だけが1×1=1になって売上がそのまま合計されますが、どちらか一方でも条件に合わない行は0が掛けられるので集計から外れるんです。
SUMPRODUCT関数は、条件に合う件数を数えることもできます。
例えば、A列が「東京」でB列が「商品A」の行が何件あるか数えたいときは「=SUMPRODUCT((A2:A100=”東京”)*(B2:B100=”商品A”))」と入力します。
合計したい金額の範囲を付けなければ、条件を満たす行で発生した1の合計、つまり件数が返ってきます。
COUNTIFS関数でも似たような集計はできますが、SUMPRODUCT関数のほうが条件式を自由に組み立てられるので、複雑な条件や計算を組み合わせた集計に向いています。
「東京または大阪」みたいに、どちらかに当てはまればOKという条件(OR条件)では、条件式を「+」でつなぎます。
例えば「=SUMPRODUCT(((A2:A100=”東京”)+(A2:A100=”大阪”))*C2:C100)」とすれば、東京または大阪の売上を合計できます。
ただし、複数のOR条件が同時に成立する可能性があるときは、同じ行が二重に加算されることがあります。
そういうときは「=SUMPRODUCT(–(((条件1)+(条件2))>0),合計範囲)」みたいに、最終的に1か0に整える書き方を使うと安全です。
加重平均やSUMIFS関数との使い分け
SUMPRODUCT関数は、加重平均を求めるときにも便利です。
加重平均というのは、全部の値を同じ重みで平均するんじゃなくて、重要度や数量などの重みを反映して平均する計算方法です。
例えば、テストの点数に科目ごとの単位数を掛けて総合成績を出したり、商品の単価に販売数量を掛けて平均販売単価を求めたりするときに使います。
基本の式は「=SUMPRODUCT(値の範囲,重みの範囲)/SUM(重みの範囲)」です。
例えば、B列に点数、C列に単位数がある場合、「=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)」とすれば、単位数を考慮した平均点が出せます。
普通のAVERAGE関数だと、単位数が多い科目も少ない科目も同じ比重で平均されちゃいます。
でも、SUMPRODUCT関数を使えば、実際の重要度に応じた平均値が出せるので、より現実に近い分析ができるんです。
売上分析なら、単純な平均単価じゃなくて「販売数量を考慮した平均単価」を出すことで、実態に近い数値がわかります。
SUMPRODUCT関数とよく比較されるのが、SUMIF関数やSUMIFS関数です。
単純に「部署が営業部の売上を合計する」「商品がAで地域が東京の売上を合計する」みたいな条件付き合計なら、SUMIFS関数のほうが読みやすいこともあります。
一方で、条件判定と掛け算を同時にやりたいとき、加重平均を求めたいとき、条件式を自由に組み合わせたいときはSUMPRODUCT関数が便利です。
また、集計対象のブックを閉じた状態で参照するようなケースでは、SUMIFS関数でエラーが出ることがあるので、SUMPRODUCT関数が選択肢になることもあります。
実務では、「シンプルな条件付き合計はSUMIFS関数」「掛け算を含む集計や条件が複雑な集計はSUMPRODUCT関数」と使い分けると効率的です。
SUMPRODUCT関数は万能に見えますが、数式が長くなると他の人が見たときに意味がわかりにくくなることがあります。
社内で共有するファイルでは、条件の意味がわかるように見出しを整えたり、条件値をセル参照にしたりすると管理しやすくなります。
例えば「=SUMPRODUCT((A2:A100=F2)*(B2:B100=G2)*C2:C100)」みたいに条件をF2やG2に入力する形にすれば、数式をいじらずに集計条件を変更できますよ。
エラーを防ぐ注意点と実務で使うコツ
SUMPRODUCT関数でよくあるエラーは、指定した範囲の大きさが合っていないことです。
「=SUMPRODUCT(B2:B10,C2:C9)」みたいに、片方は9行、もう片方は8行という指定になっていると、対応する要素同士を計算できないので「#VALUE!」が表示されます。
条件付き集計でも同じで、条件範囲と合計範囲は必ず同じ開始行・終了行にそろえる必要があります。
表に新しい行を追加したときは、数式の参照範囲がずれていないか確認しましょう。
数値に見える文字列にも注意が必要です。
例えば、半角の「100」じゃなくて全角の「１００」が入力されていたり、数値の前後に不要なスペースが入っていたりすると、期待どおりに計算されないことがあります。
SUMPRODUCT関数では、文字列が含まれていてもエラーにならず0として扱われることもありますが、結果が正しくないことに気づきにくいのが問題です。
集計する前に、数値列が本当に数値形式になっているか、条件に使う文字列に表記ゆれがないかをチェックすることが大切です。
処理速度にも気を配る必要があります。
SUMPRODUCT関数は、指定した範囲の各セルを1つずつ判定・計算するので、データ量がすごく多い表で列全体を指定すると動作が重くなることがあります。
例えば「A:A」みたいな列全体参照は便利ですが、SUMPRODUCT関数では不要な行まで計算対象になりやすいので、実際のデータ範囲に合わせて「A2:A5000」みたいに指定するのがおすすめです。
頻繁に更新する大きなファイルでは、Excelテーブルを使って範囲を管理すると、行追加に対応しながら数式も見やすくなります。
SUMPRODUCT関数を使いこなすコツは、まず「条件式が1と0の配列を作って、それを金額や数量に掛けている」と理解することです。
この仕組みがわかると、複数条件のAND条件は「*」、OR条件は「+」で表現できる理由も自然に理解できます。
最初は数式が難しく見えるかもしれませんが、単価×数量の合計、条件付き合計、複数条件カウント、加重平均の順に練習すると実務で使える場面が一気に広がります。
Excelで複雑な集計を効率化したいなら、SUMPRODUCT関数は絶対に覚えておいて損はない関数ですよ。
広告
