Hiyoco Words Blog

「親友にちょこっとオススメしたい本音の情報をお届けします」

最強のExcel関数【SUMPRODUCT関数】の使い方を具体例で紹介!(初心者~中級者向け)

 公開日:2018/09/17  更新日:2020/01/21 

f:id:relax-words:20180913235142j:plain

 

最強のExcel関数【SUMPRODUCT関数】の使い方を具体例で紹介!(初心者~中級者向け)

 

皆様はじめまして!従業員が少ない中小企業で毎日Excel漬けな日々を送っている社会人ひよこです。

さて、今回はExcel関数【SUMPRODUCT関数】についておすすめの使い方を紹介させて頂きます。

そもそもSUMPRODUCT…サムプロダクト?関数って何ですか?

…と感じている方も多いかもしれません。SUM関数とかVLOOKUP関数のように、メジャーな関数ではないですし、認知度が低いためです。

ですが、使い方を覚えたらこれほど便利な関数はありません!

「Excelでより楽に計算をしたい…、」

「SUM関数やSUMIF関数を複数組み込んでいる、COUNTIF関数を普段使用してる…、

関数を整理して作業効率を上げたい…、」

そんなあなたこそ、ぜひともSUMPRODUCT関数をマスターしてください!

 

1.SUMPRODUCT関数とは

 

 Excel説明:「配列の対応する要素の積を合計した結果を返します」

…何を言っているのかわかりませんね(笑)

というわけで、具体的にできることを紹介していきます。

①複数条件の件数をカウントできる

(「COUNTIF関数」の強化版)

②さらに、複数条件を組み合わせて合計できる

(「SUMIF関数」の強化版)

大まかに上記の2点です。

もう少し突っ込みますと使い方は広がります(加重平均を算出する等)。ですが、最初にSUMPRODUCT関数を使用するときはこの2つを確実にマスターすることが重要だと思います。汎用性が非常に高いですので!

 

2.数式

 

 ①件数をカウントする場合(条件は2つ以上!)

=SUMPRODUCT((❶検索条件=❶’検索範囲)*(❷検索条件=❷’検索範囲))

 

②条件を組み合わせて合計する場合

=SUMPRODUCT((検索条件=a検索範囲)*(合計したい数値))

 

あまり馴染みがないかもしれませんけど、慣れてしまえば簡単です。

 

ちなみに、SUMPRODUCT関数は条件を2つ以上設定可能です。

=SUMPRODUCT(( )*( )*( )*( )...) ・・・と条件を入れる( )を増やすだけで、条件を増やしていくことができます。

 

3.実際に使ってみましょう(具体例)

 

 下記のような表を用意しました。

f:id:relax-words:20180913003259p:plain

 

ケースⅠ(→「COUNTIF関数」の強化版)

左の売上表(商品、属性が順不同、重複しているデータ)から、

商品・属性が一致している個数を 数えて、

右の表に個数を入力したい!

 

 実際に考えていきます。

まずは、H4セル(赤←)に数式を入れましょう。

一つ入れてしまえばあとはコピーして貼り付けで完了ですので!

…。

……。

………どんな数式を入力するのか、わかりましたか? 

(参考)

「①件数をカウントする場合(条件は2つ以上!)

=SUMPRODUCT((❶検索条件=❶’検索範囲)*(❷検索条件=❷’検索範囲))」

 

ではお伝えします。

 

今回入力すべき数式はこちらです!

=SUMPRODUCT(($B$4:$B$20=F4*$C$4:$C$20=G4))

 

数式だけだとわかりにくいと思いますので、日本語に訳します。

「❶左の売上表の商品(B4セル~B20セル)の中からから、

 右の一覧表の「スポーツ特集」(F4セル)という言葉と合致している。

 かつ(✽:アスタリスク)

 ❷左の属性項目の表(C4セル~C20セル)の中からから、

 右の一覧表の「書籍」(G4セル)という言葉と合致している。

 そして、上記の条件を満たしている個数を数えますよ!」 

 ……という内容の数式になっています。

 (日本語訳の該当箇所に色を付けました。)

 

実際のExcelはこちら↓

f:id:relax-words:20180913005705p:plain

 しっかり4つと数えることができてますね!

(該当の商品は橙色をつけました。)

 あとはH11セルまでコピー&ペーストで大丈夫です。

 

ちなみに、 数式の中身は順不同で問題ないので、

以下の式でも計算できます!

 

=SUMPRODUCT((F4$B$4:$B$20*G4$C$4:$C$20))

(”$”ってなに?という方は脚注をどうぞ!)*1

 

では続いてのケースはこちら。

 

ケースⅡ(→「SUMIF関数」の強化版)

よし!次は、商品ごとにいくら売れたのかを右の表へ金額を入力したい!

 

f:id:relax-words:20180913235619p:plain

 

個数の次は金額です。

商品ごとの売上は誰しも気になりますよね!

 

I4セルにどんな数式を入れれば良いでしょうか?

 

前半紹介した②の数式になります。

「②条件を組み合わせて合計する場合

 =SUMPRODUCT((検索条件=a検索範囲)*(合計したい数値))」

 

…。

……。

………お時間はよろしいでしょうか?

 

このケースの数式はこちらです!

 

=SUMPRODUCT(($B$4:$B$20F4*$C$4:$C$20G4*$D$4:$D$20))

 

条件が例より一つ多いので、難しかったかもしれません。

またまた日本語訳をしていきます。

 

「❶左の売上表の商品(B4セル~B20セル)の中からから、

 右の一覧表の「スポーツ特集」(F4セル)という言葉と合致している。

 かつ(✽:アスタリスク)

 ❷左の属性項目の表(C4セル~C20セル)の中からから、

 右の一覧表の「書籍」(G4セル)という言葉と合致している。

 そして(✽:アスタリスク)

 ❸ ❶と❷の条件を満たす左の売上表の金額(D4セル~D20セル)を合計しますよ!」 

 

……という内容の数式です。

長い数式なので、一見難しそうですが、中身は至ってシンプルです。

アスタリスク(*)は、条件つなぐための接着剤みたいなものだと考えてください(笑)

 

実際のExcelはこちら↓

f:id:relax-words:20180914003144p:plain

 こちらもしっかりとI4セルに4,800円(1,200円×4個)と計算できてますね!

 あとはI11セルまでコピー&ペーストで完成です。

 

※なお、複数条件のカウント・集計はCOUNTIFS関数・SUMIFS関数でも可能です。しかし、上記の関数は

①条件が増えるほどカッコの中身が複雑になりすぎ、第三者がわかりにくい点、②SUMPRODUCT関数ならば1つの関数をマスターするだけで事足りる点、

という2点から私はSUMPRODUCT関数をおすすめしています。

 

すぐにコピーで完了したい方へ

以下の数式部分を入れ替えるだけで使用できちゃいます! 

時間のない方はどうぞ。

  1. =SUMPRODUCT((B4:B20=F4)*(C4:C20=G4))
  2. =SUMPRODUCT(($B$4:$B$20=F4)*($C$4:$C$20=G4))
  3. =SUMPRODUCT(($B$4:$B$20=F4)*($C$4:$C$20=G4)*($D$4:$D$20))
  4. =SUMPRODUCT((B4:B20=F4)*(C4:C20=G4)*(D4:D20))

 

まとめ

 

以上、SUMPRODUCT関数のおすすめの使い方についての紹介でした。

 

最初のうちはパッと見では数式の意味も分かりにくいかもしれません。けれど、活用できるようになれば数値を扱う数式では個人的に最強のExcel関数だと思っています!

慣れないうちは上記の数式をそのまま入力して、とにかく意味を理解できるようにしてください。複雑そうに見えて、シンプルな情報を入力しているだけです!

 

この記事を読んだあなたがSUMPRODUCT関数を使いこなして、より良い日常を過ごせることを祈っています。

 

・補足(おすすめの参考書)

SUMPRODUCT関数だけでなく、Excel全般を勉強したいということで、もし何か参考になる書籍を探している方がいらっしゃるなら、個人的に「Excel最強の教科書(完全版)」(SBクリエイティブ)がおすすめです。  

Excelの書籍って非常に多いですよね?正直、自分が理解しやすいものでしたらどれでも問題はないかと思いますが、結局どれを選べばよいのか迷いがちです。

上記は私が実際何冊か購入した中で、一番わかりやすく、使用していて便利だと感じたものです。

関数の網羅性はもちろんですが、図や写真が多く、またExcelの細かい便利機能についても多く載っていました。一冊でExcelのほとんどのケースに対応可能だと思います。(欠点を強いてあげるなら、マクロについての記述が足りない点です。)

もしよろしければ、お時間があるときに見てみてください。

 

関連記事 

www.relax-words.com

 

www.relax-words.com

 

 

www.relax-words.com

 

*1:

「$B$4」←”$”の付け方

「絶対参照」といって。「F4」キーを押せば付けられます。

Excelは数式をコピーして他のセルに貼り付けると自動的に数式内の参照セルも移動してくれます。...が、範囲を固定したいときは逆効果になってしまうケースがあるんですよね。そういうときに使用するものが「絶対参照」(”$”)です。

数式を固定したいときに使用するものですね。