Taste of Tech Topics

Acroquest Technology株式会社のエンジニアが書く技術ブログ

Excel関数 SUMPRODUCTを使いこなす

阪本です。

新人/若手向け、これだけは知っとけTips25
の12日目として投稿します。


仕事の中で、Excelを使って計算をすることは多いはず。

そこで、かなり万能なExcel関数「SUMPRODUCT」を知って、
効率的に計算を行えるようになりしょう。


今回は、以下のようなエリア別販売データに対して、いくつかの計算をしてみます。

1. 単純な合計

エリア全体のこの日の販売合計を計算してみましょう。
各行の「単価」と「個数」を掛けたものを、合計します。


→結果は「1195000」

掛けたいデータの範囲を指定するだけで、簡単に合計できてしまいます。

2. 条件付き合計

次に、合計する対象を絞り込んでみましょう。
A社の製品の販売合計を計算します。
メーカーが「A社」の行に対して、「単価」×「個数」を計算し、それを合計します。


→結果は「650000」

SUMPRODUCTでは、「条件」+「*」+「合計したいもの」で、
条件に合致したところだけ合計することができます。

3. 複数条件付き合計

条件は1つだけでなく、同じ要領で複数の条件を指定できます。
大阪で単価が100000以上の商品の販売個数を計算してみます。


→結果は「1」(7行目のデスクトップパソコンだけが条件を満たす)

「*」で条件をつなげていくことで、いろいろな条件で絞って合計を計算できます。

なお、Excel2007以降では、上記のようにある1列の合計のみを計算したい場合は、
SUMIFS関数も使えます。


→結果は「1」

この計算結果は、先ほどの「SUMPRODUCT((A3:A12="大阪")*・・・」と同じになります。

4. 複数条件検索

あるキーにマッチする値を検索するにはVLOOKUP関数が使えますが、
この関数は複数の条件を指定したくても、できません。

こんなとき、SUMPRODUCTが役に立つんです!

以下は、東京で販売された、B社のプリンタの型番を取得する式です。


→結果は「PRNT02」

A列の値が「東京」、かつB列の値が「B社」、かつC列の値が「プリンタ」である行の
D列を取得する式ですが、

SUMPRODUCT((A3:A12="東京")*(B3:B12="B社")*(C3:C12="プリンタ")*ROW(A3:A12))

で、A列の値が「東京」、かつB列の値が「B社」、かつC列の値が「プリンタ」である行の
行番号を取得し、INDEX関数を用いて、行番号に対応するセルの値を取得しています。

5. ということで

このように、いろんな用途に使えるSUMPRODUCT関数は使えるようになっておくとかなり便利です。
ぜひマスターしておきましょう。

では。