Shimesaba-ba blog

大手日系企業で経営管理や経理業務を行い、英語や海外とは無縁な状態から海外で業務を行い、グローバル企業へ転職。英語、経営管理、ビジネスについての経験や学んだことをアウトプットします。

SUMPRODUCT関数の実務的な使い方とは?縦横複数条件の合計集計に効果抜群

エクセルで縦横の条件合致したものを

合計したい場面ってよくあるのではないでしょうか?

地道に目視で探すのも良いですが、

時間がかかってしまいますよね。

 

f:id:Shimesaba-ba:20200920145312p:plain

例えばこんな場面です。

 

下記データベースから

縦が「青森」かつ「野菜」

横が「1Q」

という条件に合致した合計を

求めたいとします。

 

 

f:id:Shimesaba-ba:20200920120318p:plain

実績データベース

 

皆さんならどうやってやりますか?

いくつかやり方があると思いますが

 

重要なことは目的に応じて手段を

使い分けることです。

 

 

 サンプルを無料で置いておきますので

ダウンロードしてみて下さい。

 

ファイルを見ながら参照して頂けると

よりわかりやすいと思います。 

下記がサンプルのエクセルです。

drive.google.com

 

 

 

PIVOTテーブルの活用

 

縦が「青森」かつ「野菜」

横が「1Q」という時

いくつなのか求めたい時など

 

特定の条件を入れて調べたり、

集計されるアウトプットの形に拘りがなければ

PIVOTテーブルが確実です。

 

理由は条件をプルダウンで選ぶだけで

AND条件やOR条件を確実に作ることが

できるからです。

 

今回、答えは42個でした。

サンプルをダウンロードして頂いた方は

"Pivot実績集計"タブをご覧ください。

 

f:id:Shimesaba-ba:20200920102455p:plain

 

このように数を求めることが

目的ならこのやり方が確実です。

 

複数条件の集計を可能にするSUMPRODUCT関数

 

しかしレポートのために

データベースのタブが複数あって、

それらをあわせて一元化して

集計したい場面ありませんか?

 

例えば下記のように

今年の実績と前年の実績が

別テーブルになっていて、

それらを使って条件に合致したものを

集計したい場面です。

 

黄色の月をリストから任意の月を

選択したら自動でデータが置換されます。

 

f:id:Shimesaba-ba:20200920102728p:plain

 

 4月を選択して見ると、

データが自動で置換されました。

f:id:Shimesaba-ba:20200920131251p:plain

 

これを一瞬で自動的に作り上げるには

どうしたらできるのでしょうか?

 

これができるとレポート作成のために

データベースから地道にコピペしたり

目視で確認したりマニュアル作業を

各段に減らすことができます。

 

SUMPRODUCT関数の実務的な使い方

 その時に使うのがSUMPRODUCT関数です。

この関数使い方はパッと見は少し難しいので、

中身を解説します。

 

私はSUMPRODUCT関数を

使えるようになって、

半日程かかっていたレポーティングが

30分で間違えなくできるようになりました。

 

だから今回ご紹介しようと思いました。

集計作業が早くなったことも価値ですが、

 

分析という付加価値を生む作業に

時間をかけることが

できるようになったこと

が良かった点です。

 

SUMPRODUCT関数で何をしているのか?

では関数の中身をまずは

大枠から説明していきます。

 

サンプルをダウンロード頂いた方は

"レポート用"タブをご参照下さい。

 

f:id:Shimesaba-ba:20200920121210p:plain


例として

前年の沖縄の1Qの販売個数47個

のセルの計算式を解説します。

 

f:id:Shimesaba-ba:20200920123430p:plain

 

f:id:Shimesaba-ba:20200920123517p:plain

 

ポイントは

かつ条件の時は*(アスタリスク)

使うこと、

条件を設定し終わったら,(カンマ)を打ち、

その後に合計する範囲を設定する2点です。


しかし、

 

やってみるとわかると思うのですが、

いざ自分で1から計算式を

入れようとすると

間違える可能性大で

私はできませんでした。

 

だからまずは式をパクりましょう。

その上で、自分のファイルにあうように

調整する方が確実です。

 

コチラがその式です。

コピペしてご自身のファイルに

活用してみて下さい。

  

=SUMPRODUCT((データベース前年!$B$6:$B$26=レポート用!$A5)*(データベース前年!$F$5:$M$5=レポート用!$A$2),データベース前年!$F$6:$M$26)

 

サンプルのエクセルを

無料でダウンロードできるので、

 

そのファイルを加工して

使って頂くのも良いと思います。

 

サンプルエクセルを見ている方は

わかると思いますが、

 

縦列だけで複数条件の設定

をすることも可能です。

"東日本"かつ"野菜"といった設定した上で、

さらに横列で"4月"に該当するデータを

合計することもできます。

 

f:id:Shimesaba-ba:20200920132413p:plain

 

色んな条件設定が可能

かつ条件の時には

アスタリスク(*)を使いましたが、

他にも"または"条件も設定できます。

その時はプラス(+)を使えばできます。

 

SUMPRODUCT関数のまとめ

メリット

縦列横行に複数条件を設定して

自在に集計ができます。

 

しかし数字を探すことだけが目的なら

ピボットテーブルがベターです。

なぜなら直感的でわかりやすいからです。

 

しかし単にデータを探すだけでなく、

定型フォーマットに複数タブを使って

レポーティングを作成するには

ピボットテーブルでは対応できません。

 

そこでSUMPRODUCTを使い、

複数タブから条件を設定して

条件に一致した集計作業を一瞬にして

行うことができます。

 

このように目的に応じて

集計する方法を

選択するのが重要です。

デメリット

式がパッと見複雑になってしまうことです。

なのでエクセルに不慣れな人も更新するファイルの場合は難しいかもしれません。

 

自分だけあるいは

エクセルに詳しい数人しか

更新しないのであれば

使う価値が高い関数です。

 

ファイルが重たくなる点も

デメリットになります。

何千何万ものデータがあると

動作が重くなる可能性があります。

 

そういう場合は自動計算をやめて

手動計算にすることをオススメします。

数式タブの計算方法の設定からできます。

f:id:Shimesaba-ba:20200920140019p:plain

10年以上実務でエクセルを使ってきたので、

今後もこういった小技もご紹介したい

と思います。