ExcelのSUMPRODUCT関数(1)足し算と掛け算を合わせることができる

Excel 一歩先へ

スポンサーリンク

はじめに：SUMPRODUCT関数って学習する必要あるの？

今回はExcelのSUMPRODUCT関数について扱うけど、この関数は深入りしていくとキリがない、かなり用途の奥が深い関数だ。

この関数は使ったことがない人も多いだろうし、私もできる限り使わないようにしている複雑な関数だ。

もし貴方がExcel2016とか十分に新しいバージョンのExcelを普通に使えば良い環境で、基礎を重点学習している人であるなら、SUMPRODUCT関数は無理に急いで覚える必要はない。
もっと他の基礎から覚えていけばよいし、なんならVBAから先に覚えていけば良い。

しかし貴方が不幸にも、Excel2003までのバージョンしか使えない環境にあったり、あるいはそういう古いExcelを使ってる相手先に気を使わされている立場だとすると、SUMPRODUCT関数を覚えずにいるのはちょっとマズいだろう。

私は、Excel2007以降の新しいバージョンが出た後もExcel2003までしか使えない時代が長かった人間なんだけど、その時代にはこのSUMPRODUCT関数は生命線に近かった。
それを今は使わないようにしてるのは、複雑なうえに重くなりやすい関数だからなのだけどね。

SUMPRODUCT関数の用途

で、SUMPRODUCT関数は用途の奥が深いとも書いたけど、じゃあメインとなる用途は何なのかと聞かれると、上手い人の間でもかなり答えが分かれてくる。

ひとまず私の見解として、SUMPRODUCT関数の用途を優先度が高い順に並べると次のようになる。

SUMPRODUCT関数の用途
  1. 条件を複数指定して合計するのに使う。いわばSUMIF関数の拡張版。
  2. 掛け算の結果を足し算していくのに使う。
  3. その他、ROW関数とかと組み合わせた複雑な用途で使う。

もうね、必要なことだけを押さえたい人は、最初に書いた

条件を複数指定して合計するのに使う。いわばSUMIF関数の拡張版。
だけを押さえておけばOK。
私も99.9%、その用途でしか使いはしない。

ただ、そのメインとしたい使い方については、まだ今回は詳述せず、次回の記事から本格的に取り上げる。

2番めの

掛け算の結果を足し算していくのに使う
については次の項で一応解説はしとくけど、大して便利でもない。

ただ、基本となる考え方を学ぶ題材としては、押さえておくべきだろう。

3番目の

その他、ROW関数とかと組み合わせた複雑な用途で使う。
なんて小賢しい知識は、Excelを極めたいとかいうヲタクな人だけがチャレンジすれば良い。今回の記事でも取り上げはしない。

掛け算の結果を足し算する

上図でA列の単価・B列の数量を掛け合わせてC列に金額を求めるとしたら、普通にやるならC列の数式は、D列に書いておいた通りになる。

よもやこの程度の問題ができないなどと言うなら給料泥棒以前のレベルなので基礎の基礎から出直してもらうことになるが、C2セル～C9セルに掛け算を書かずとも一足飛びでC10セルの最終結果を求められるのがSUMPRODUCT関数というわけだ。

上図でF2セルに書いた計算式

=SUMPRODUCT(($A$2:$A$9)*($B$2:$B$9))

で、一気に最終的な答えの、C10セルと等しい104,000が求められている。

この、数式中で
*
と掛け算の記号を使うのが、他の関数にはなかなか見られない独自なところで、SUMPRODUCT関数が分かりにくい最大級の要因だ。

SUMとPRODUCT―足し算と掛け算

そもそもSUMPRODUCT関数とは、実は文字通り(?)SUM関数とPRODUCT関数の合わせ技の関数だ。

PRODUCT関数って聞いたこともない人が多いだろうし、私も使ったことは全くない関数だが、要はSUM関数の掛け算バージョンだ。

=A1*A2*A3*A4
という計算式なら
=PRODUCT(A1:A4)
と書き換えることができるというわけ。まあこんな、複数の数値の掛け算なんて、私の業務ではまるで縁がないが。

で、先述のSUMPRODUCT関数

=SUMPRODUCT(($A$2:$A$9)*($B$2:$B$9))

というのは、バカ正直に書くと
=SUM( ($A$2*$B$2),($A$3*$B$3),($A$4*$B$4),($A$5*$B$5),($A$6*$B$6),($A$7*$B$7),($A$8*$B$8),($A$9*$B$9) )
という感じに長ったらしくなるけど、A2～A9セルとB2～B9セルの間で同じ行どうしを掛け合わせてそれらを足すだけの単純な処理だ。

掛け算と足し算の合わせ技であるゆえ、SUMPRODUCT関数の数式中で掛け算記号「*」が使われるのも、なんとなく分かっていただけるかと思う。

またA2～A9セルとB2～B9セルを順に並べて書く記法もなんとなく分かるだろう。

半角カンマを使う方法もある

ちなみに今回の例だと、「*」記号の代わりに半角カンマ「,」を使った式

=SUMPRODUCT(($A$2:$A$9),($B$2:$B$9))

にしても良い。

しかし、次回の記事で紹介するような、複数条件による集計などにおいて半角カンマばかり使っていては通用しない(数式が正しい答えを返さない)。
ひとまずよほど慣れるまでは、半角カンマを使う方法は忘れ、SUMPRODUCT関数でセル範囲等をつなげるには掛け算記号「*」を使う方法だけを集中して覚えよう。

無理に使わなくて良い

さてここまで長々と書いてきてなんだが、今回の例題みたいなケースは実務でも割とあるけど、こんな程度の用途でわざわざSUMPRODUCT関数を使う必要は全然ない。

最初に示したように、C2セル～C9セルに掛け算を書いて、C10セルにそれらを足すSUM式を書くオーソドックスなやり方で全然問題ない。

というかそうしないと、途中の掛け算の答え・過程が分からなくて不親切だしね。
だから実際のところ、こんな足し算と掛け算の合わせ技っていう使い方そのものは、私は全然使うことはない。

先述の通り、私がSUMPRODUCT関数の最大の用途と考えるのは、SUMIF関数の拡張とでも言うべき、複数条件を指定した集計なので、それについて次回の記事で述べることにする。

スポンサーリンク