Excelワザ_SUMPRODUCT関数

b0186959_9383481.jpg

売上合計出すのに
ふつうは、=単価×数量で金額を出し、
それをSUM関数で合計する、という手順ですが、

『Sumproduct(サムプロダクト)関数』の使い方

1.単純な合計の出し方
セルD13には、
『=SUMPRODUCT(D2:D11,E2:E11)』
という式を立てます。
それぞれの行の単価と個数を掛けたものを、合計します。
掛けたい範囲を指定するだけで合計が出ます。

2.条件付き合計
「担当者が井上」の合計金額は、
SUMIF関数なら
F列に金額欄を作り、
=SUMIF(B2:B11,"井上",F2:F11)
という式で出ますが、
b0186959_11342855.jpg

金額欄を作らずに一度に出す場合、
Sumproduct関数で、条件付き合計が出ます。
ここでのポイントは、
条件となる範囲を「×」でつなぎます。
これで、「AND」条件の意味になります。
=SUMPRODUCT((B2:B11="井上")*D2:D11*E2:E11)
b0186959_11344491.jpg


3.複数の条件付き合計
「担当者が井上で、かつ単価が100000以上のものの合計」
同様に「×」で条件をつなぐ式を作ります。
=SUMPRODUCT((B2:B11="井上")*(D2:D11>=100000)*D2:D11*E2:E11)
b0186959_11583282.jpg

これも金額欄があると、
SUMIFS関数で出るけど…(^-^;

4.複数の条件付きの検索
合計ではなく、条件に合ったものを検索する場合
「支店名が舞鶴で、担当者が井上で、
単価が100000以上の個数」を調べたい場合、
該当する行をIndex関数で取り出します。
=INDEX(E:E,SUMPRODUCT((A2:A11="舞鶴")*(B2:B11="井上")
*(D2:D11>=100000)*ROW(A2:A11)))

b0186959_12164238.jpg

「支店名が舞鶴で、かつ担当者が井上で、
かつ単価が100000以上である行の行番号を
ROW関数で取り出し、INDEX関数でE列の行番号に
対応するセルの値を取得する。

使いこなすとなかなか便利な関数です。
で、これを範囲が可変のものに
応用すると、…
次に…
[PR]

by school-pc | 2015-01-28 11:02 | Comments(0)