阪本です。
「新人/若手向け、これだけは知っとけTips25」
の12日目として投稿します。
仕事の中で、Excelを使って計算をすることは多いはず。
そこで、かなり万能なExcel関数「SUMPRODUCT」を知って、
効率的に計算を行えるようになりしょう。
今回は、以下のようなエリア別販売データに対して、いくつかの計算をしてみます。
1. 単純な合計
エリア全体のこの日の販売合計を計算してみましょう。
各行の「単価」と「個数」を掛けたものを、合計します。
掛けたいデータの範囲を指定するだけで、簡単に合計できてしまいます。
2. 条件付き合計
次に、合計する対象を絞り込んでみましょう。
A社の製品の販売合計を計算します。
メーカーが「A社」の行に対して、「単価」×「個数」を計算し、それを合計します。
SUMPRODUCTでは、「条件」+「*」+「合計したいもの」で、
条件に合致したところだけ合計することができます。
3. 複数条件付き合計
条件は1つだけでなく、同じ要領で複数の条件を指定できます。
大阪で単価が100000以上の商品の販売個数を計算してみます。
→結果は「1」(7行目のデスクトップパソコンだけが条件を満たす)
「*」で条件をつなげていくことで、いろいろな条件で絞って合計を計算できます。
なお、Excel2007以降では、上記のようにある1列の合計のみを計算したい場合は、
SUMIFS関数も使えます。
この計算結果は、先ほどの「SUMPRODUCT((A3:A12="大阪")*・・・」と同じになります。
4. 複数条件検索
あるキーにマッチする値を検索するにはVLOOKUP関数が使えますが、
この関数は複数の条件を指定したくても、できません。
こんなとき、SUMPRODUCTが役に立つんです!
以下は、東京で販売された、B社のプリンタの型番を取得する式です。
A列の値が「東京」、かつB列の値が「B社」、かつC列の値が「プリンタ」である行の
D列を取得する式ですが、
SUMPRODUCT((A3:A12="東京")*(B3:B12="B社")*(C3:C12="プリンタ")*ROW(A3:A12))
で、A列の値が「東京」、かつB列の値が「B社」、かつC列の値が「プリンタ」である行の
行番号を取得し、INDEX関数を用いて、行番号に対応するセルの値を取得しています。
5. ということで
このように、いろんな用途に使えるSUMPRODUCT関数は使えるようになっておくとかなり便利です。
ぜひマスターしておきましょう。
では。