SUMPRODUCT関数の使い方
配列の対応する要素の積を合計します。
200720102013201620192021365
- 構文
-
=SUMPRODUCT(配列1, [配列2], ...) - 引数
-
- 配列1必須
- 配列2省略できる
このあとも、同じように続けて渡せます。
- どんな関数か
SUMPRODUCT は、複数の範囲を同じ位置どうしで掛けてから、全部を足します(単価×数量の合計が一発で出ます)。もう1つの使い道が条件つきの集計で、
=SUMPRODUCT((A2:A100="東京")*(B2:B100))のように書きます。SUMIFSと違って範囲を加工した結果にも条件を当てられる(年や月を取り出して絞るなど)のが強みです。範囲の大きさは必ずそろえます。古い Excel でも配列として扱えるため、いまも広く使われています。- 使用例
-
C2 に
=SUMPRODUCT(A2:A4, B2:B4)を入れると、こうなります。A単価 B数 C 1 売上総計 2 1000 5 35000 3 5000 4 4 2000 5 数式を入れたセル
結果: 合計金額(35,000)
つまずきやすいところ
- 掛け合わせる範囲は大きさをそろえる
- 条件を掛け合わせると、SUMIFS に近いこともできる(
=SUMPRODUCT((A:A="東京")*(B:B))) - 列ぜんぶ(
A:A)を渡すと重くなる。要る範囲だけにする
よくあるエラーと、その直し方
#VALUE!- 範囲の大きさが違う／文字が混ざっている行数をそろえる。文字は
--などで数値に直す
SUMPRODUCTを使う実例レシピ（8件）
- 重み付き平均（加重平均）を計算する単価の違う商品の平均価格を、販売個数を考慮して正しく出したい。
- 条件に合うものだけを掛け合わせて合計する「会員かつ5月以降」の「単価×数量」の合計を、作業列なしで一気に出したい。
- 縦と横の両方の条件が合うセルの値を取り出す「4月」の「東京支店」という2軸の交差する場所の数値を抜き出したい。
- 商品ごとの重要度を加味した客単価を出す安価なものの販売数が多いのか、高価なものが多いのかを考慮した平均値。
- 製品番号の「入力ミス」を末尾のチェックデジットで防ぐ打ち間違えるとエラーが出るような、少し賢い製品コードを作りたい。
- 標準単価と実際の仕入価格の「差額（コスト損益）」を出す予算（100円）より高く買ってしまった分が、月額で累計いくらか把握したい。
- 不良の種類に「重み（重要度）」を付けて品質スコア化致命的エラーは10点、軽微な傷は1点として、ロット全体のヤバさを数値化。
- 「A列が○ かつ B列が×」の合計（究極の柔軟性）SUMIFSでは複雑すぎて書けない条件の合計を、1つの数式で。