Excelで複数の配列を掛け算し、その結果を合計したい場合に便利なのがSUMPRODUCT関数です。この関数は、配列の各要素を掛け合わせ、その積を合計することができ、さまざまな場面でデータ集計や分析に役立ちます。この記事では、SUMPRODUCT関数の基本的な使い方から、応用的な活用法までを詳しく解説します。
SUMPRODUCT関数とは？
SUMPRODUCT関数は、指定した範囲や配列の要素ごとに掛け合わせ、その積を合計する関数です。複数の範囲や配列を掛け合わせて、その合計を求める際に非常に便利です。条件付きの合計や集計も可能で、応用範囲が広い関数です。
SUMPRODUCT関数の基本的な構文
=SUMPRODUCT(配列1, [配列2], …)
- 配列1, 配列2, …: 掛け合わせたい配列や範囲を指定します。複数の範囲を指定する場合、同じサイズの範囲を指定する必要があります。
SUMPRODUCT関数の動作イメージ
SUMPRODUCT関数は、指定した各配列の要素ごとに掛け算を行い、その積をすべて合計します。例えば、配列Aと配列Bが次のような数値を持つ場合、
| A | B |
| 2 | 3 |
| 4 | 5 |
| 6 | 7 |
それぞれの要素ごとの掛け算を行い、
- 2 × 3 = 6
- 4 × 5 = 20
- 6 × 7 = 42
その積を合計すると、68となります。
=SUMPRODUCT(A1:A3, B1:B3)
結果は、68となります。
SUMPRODUCT関数の実際の例
たとえば、次のようなデータがあるとします。
| 商品コード | 単価 | 数量 |
| A101 | 100円 | 2 |
| A102 | 150円 | 4 |
| A103 | 200円 | 3 |
このデータを使って、各商品の単価と数量を掛け合わせ、その合計金額を求めるには、次のようにSUMPRODUCT関数を使います。
=SUMPRODUCT(B2:B4, C2:C4)
結果として、1450円が返されます。
- (100 × 2) + (150 × 4) + (200 × 3) = 1450円
| 商品コード | 単価 | 数量 | 合計 |
| A101 | 100円 | 2 | |
| A102 | 150円 | 4 | =SUMPRODUCT(B2
, C2 ) → 1450円 |
| A103 | 200円 | 3 |
SUMPRODUCT関数の応用例
条件付きの合計計算
SUMPRODUCT関数は、条件付きの計算にも活用できます。例えば、単価が150円以上の商品のみ合計金額を求めたい場合、条件を加えることで処理が可能です。条件式を配列に加えて次のようにします。
=SUMPRODUCT((B2:B4>=150) * B2:B4 * C2:C4)
この場合、単価が150円以上の商品に該当するものだけが計算され、結果は1050円となります。
- (150 × 4) + (200 × 3) = 1050円
AND条件やOR条件での合計計算
複数の条件を組み合わせて合計を計算することも可能です。例えば、「単価が150円以上で、数量が3以上」の商品の合計金額を求める場合、次のように条件を組み合わせます。
=SUMPRODUCT((B2:B4>=150) * (C2:C4>=3) * B2:B4 * C2:C4)
これにより、条件に一致する商品だけが計算され、合計が求められます。
複数条件の掛け合わせによる集計
SUMPRODUCT関数を使って、複数条件の掛け合わせによる集計も簡単に行えます。たとえば、ある商品の特定の条件下での売上高を計算する場合に、商品単価、売上数量、地域別の条件を掛け合わせて売上集計を行うことが可能です。
条件付き配列の使い方
SUMPRODUCT関数では、条件付きの配列を作成することで柔軟に集計が可能です。たとえば、商品コードが「A102」で、かつ数量が3以上の場合に限って合計金額を計算したいときは、次のように記述します。
=SUMPRODUCT((A2:A4=”A102″) * (C2:C4>=3) * B2:B4 * C2:C4)
このように、複数条件を組み合わせることで、より柔軟な集計が可能です。
SUMPRODUCT関数の便利な豆知識
配列のサイズに注意
SUMPRODUCT関数を使用する際、指定する配列のサイズが一致している必要があります。異なるサイズの範囲を指定すると、計算が正しく行われずにエラーが発生することがあります。すべての配列のサイズが同じであることを確認して使用しましょう。
条件を掛け算で扱う
SUMPRODUCT関数では、条件を掛け算として処理します。条件が真（TRUE）の場合は1、偽（FALSE）の場合は0として扱われます。この特性を利用して、条件に一致するデータだけを集計することができるため、条件付きの集計を行う際に非常に便利です。
他の関数との組み合わせ
SUMPRODUCT関数は、他の関数（IF関数やABS関数など）と組み合わせてさらに強力に使えます。たとえば、絶対値で掛け合わせた結果を集計する場合は、次のようにABS関数と組み合わせることができます。
=SUMPRODUCT(ABS(A2:A4) * B2:B4)
これにより、絶対値を使った掛け合わせの合計が簡単に求められます。
条件付きの集計とフィルタ機能の代替
SUMPRODUCT関数は、条件付きでデータを集計する際に非常に便利です。フィルタ機能を使わずに、条件を設定してそのまま集計が行えるため、データのフィルタリングやサブセットの作成を効率的に行いたい場合に有効です。
SUMPRODUCT関数のよくあるエラーと対処法
#VALUE!エラー
#VALUE!エラーは、指定した配列のサイズが一致していない場合や、数値データ以外のものが含まれている場合に発生します。各配列のサイズを確認し、データに誤りがないかチェックしましょう。
0や空白セルの処理
配列内に0や空白セルが含まれている場合、SUMPRODUCT関数ではそのセルが掛け算されるため、結果に影響を与えることがあります。これを防ぐために、空白セルやゼロを無視したい場合は、条件を付けるなどの対策を行います。
=SUMPRODUCT((B2:B4<>0) * B2:B4 * C2:C4)
このようにして、0を除外した結果を取得することができます。
まとめ
ExcelのSUMPRODUCT関数は、配列の掛け算と合計を効率よく行うための強力なツールです。単純な掛け算に加えて、条件付きの集計や複数条件での計算にも対応でき、データ分析や集計作業に大いに役立ちます。複数の関数と組み合わせて、さらに複雑なデータ処理を行うことも可能です。
SUMPRODUCT関数を活用して、Excelでの集計作業を効率化し、データ分析を強化しましょう！
