エクセルデータ分析の強い味方!初心者向けSUMPRODUCT関数の使い方

★気に入ったらシェアをお願いします!


フルーツの積み上げ

みなさん、こんにちは!
毎日バナナを食べていますタカハシ(@ntakahashi0505)です。

さて、今回はSUMPRODUCT関数という超便利な関数についてお伝えしたいと思います。

とある配列の対応する要素の積の総和を返す関数です。

…言葉で説明するとサッパリわかりませんね…。

エクセルでデータの集計や分析をするときに使いこなせると大変重宝する関数で、こちらの記事で説明したような

平均って種類があるの…?!重みがある場合の加重平均とその算出方法
平均にはいくつか種類があるのはご存知ですか?カテゴリ別の平均ページ滞在時間と直帰率の算出には、いわゆる一般的な平均が使えないということと、実際に算出する方法としての加重平均についてお伝えをしていきます。

加重平均を算出したいときをはじめ、複数条件が絡んだ計算には万能的な力を発揮する関数です。

今回はSUMPRODUCT関数のはじめの一歩について具体的な例を出しながら解説をしたいと思います。

掛け算したものを足し合わせるときの例

例えばこのようなお買い物をしたフルーツの金額についてまとめた表があるとします。

SUMPRODUCT関数-フルーツの合計金額

お買い物金額の合計の計算について計算式表示にしてみますと

SUMPRODUCT関数-一般的な計算式

各行について単価と個数を掛け算したものを、全て足し合わせるという計算になりますね。

この掛け算したものを全て足し合わせる、という計算方法が出てきたらSUMPRODUCT関数を使うチャンスです。

SUMPRODUCT関数を使うと

SUMPRODUCT関数の使い方
このように合計金額を計算できます。

ご覧いただいてわかると思いますが、各フルーツごとの「金額」欄が不要になります。

SUMPRODUCT関数の使い方

SUMPRODUCT関数の書き方を再度おさらいしますね。

SUMPRODUCT関数の記述方法

=SUMPRODUCT(配列1,配列2,…)

配列1,配列2,…の対応する要素の積の総和返します。

書き方は配列を並べるだけという単純なものですね。

というか、SUMPRODUCTという関数名、ちょっと長くでわかりづらいと思われるかも知れませんが、

「sum=和」

「product=積」

ですから、「積の和」という意味です。そのまんまの関数名でしたね。

配列の指定は行全体でもOK

さて、先ほどのフルーツの例では

=SUMPRODUCT(B:B,C:C)

と記述しました。

一つ目の配列はB:B、つまり単価を記入しているB列全体です。

二つ目の配列はC:C、つまり個数を記入しているC列全体です。

B列の単価、C列の個数を同じ行で掛け算した結果を全ての行について足し算しているということになります。

この例では、配列の指定は列全体でしてしまっていますが、6行目以降のB列とC列の積は以降全ての行で0×0=0ですから、SUMPRODUCTの計算結果としては、行全体の指定で問題ありません。

指定の仕方としては楽ができますので、行全体での配列の指定がオススメですよ。

もちろんこの例でいうと

=SUMPRODUCT(B2:B5,C2:C5)

という指定の仕方でも全く同じ結果が得られます。

加重平均をSUMPRODUCT関数で出してみる

データ分析では、このSUMPRODUCT関数を使うことで加重平均を算出することができます。

冒頭で触れた記事が再び登場ですが

平均って種類があるの…?!重みがある場合の加重平均とその算出方法
平均にはいくつか種類があるのはご存知ですか?カテゴリ別の平均ページ滞在時間と直帰率の算出には、いわゆる一般的な平均が使えないということと、実際に算出する方法としての加重平均についてお伝えをしていきます。

こちらの記事では、このようなブログアクセスのデータに関する表を取り扱っていました。

ブログ記事のページ滞在時間の加重平均

ご覧の通り、D4列はページビューと平均ページ滞在時間の積の総和ですから、SUMPRODUCT関数が使えますよね。

SUMPRODUCT関数を使うと

SUMPRODUCT関数で加重平均を求める

このように算出することができます。

加重平均は、各要素に重み(この場合はページビュー数)を掛けた積の総和を、重みの総和で割り算することで算出できます。

SUMPRODUCT関数を使えば重みを乗算した結果を記入するセルを用意する必要がなくなり、たった一つのセルに全て計算式で入れ込むことができます。

まとめ

SUMPRODUCT関数の記述方法と、その使い方についてフルーツとブログアクセスの二つの例を用いて解説をしました。

掛け算の結果の足し算をするときには、このSUMPRODUCT関数の存在をピン!と思いだして頂ければ幸いです。

実はこのSUMPRODUCT関数ですが、特定の条件の行だけを計算対象とするかつその条件を複数設定できるというかなり万能的な発展がきく関数です。

次回、その発展系の使い方についてこちらで紹介しています。

条件付きSUMPRODUCT関数の使い方とよく出くわすエラーの回避方法
SUMPRODUCT関数は条件に合ったデータのみを計算するという発展をさせることができますので、今回はその基本的な方法とともにいくつか出くわすであろう罠とその回避方法についてお伝えしていきます。

合わせてご覧くださいね!

連載目次:Googleアナリティクスのデータをペタっと貼るだけの集計分析

  1. ペタっと貼るだけでGoogleアナリティクスのデータからカテゴリ別集計
  2. Googleアナリティクスのデータを特定条件で絞り込むアドバンスの使い方
  3. 平均って種類があるの…?!重みがある場合の加重平均とその算出方法
  4. エクセルデータ分析の強い味方!初心者向けSUMPRODUCT関数の使い方
  5. 条件付きSUMPRODUCT関数の使い方とよく出くわすエラーの回避方法
  6. Googleアナリティクスのデータを貼り付けるだけで瞬時にカテゴリ別分析
  7. Googleアナリティクスデータを貼るだけでカテゴリ別分析結果のグラフも出す
  8. ビジネスマン必携!あらゆる場面で活用できるパレートの法則とパレート図の作り方

The following two tabs change content below.
1976年こどもの日生まれ。東京板橋区在住。「ITで日本の『働く』の価値を上げる!」をテーマに、VBA&GASの開発、講師、コンサル、執筆本を中心に活動しています。→詳しいプロフィールはコチラ ★ご依頼・ご相談はお気軽にどうぞ!→お問い合わせはコチラ ★フォロー頂ければ嬉しいです。