エクセルで縦横の条件合致したものを
合計したい場面ってよくあるのではないでしょうか？
地道に目視で探すのも良いですが、
時間がかかってしまいますよね。
例えばこんな場面です。
下記データベースから
縦が「青森」かつ「野菜」
横が「１Q」
という条件に合致した合計を
求めたいとします。
皆さんならどうやってやりますか？
いくつかやり方があると思いますが
重要なことは目的に応じて手段を
使い分けることです。
サンプルを無料で置いておきますので
ダウンロードしてみて下さい。
ファイルを見ながら参照して頂けると
よりわかりやすいと思います。
下記がサンプルのエクセルです。
PIVOTテーブルの活用
縦が「青森」かつ「野菜」
横が「１Q」という時
いくつなのか求めたい時など
特定の条件を入れて調べたり、
集計されるアウトプットの形に拘りがなければ
PIVOTテーブルが確実です。
理由は条件をプルダウンで選ぶだけで
AND条件やOR条件を確実に作ることが
できるからです。
今回、答えは42個でした。
サンプルをダウンロードして頂いた方は
"Pivot実績集計"タブをご覧ください。
このように数を求めることが
目的ならこのやり方が確実です。
複数条件の集計を可能にするSUMPRODUCT関数
しかしレポートのために
データベースのタブが複数あって、
それらをあわせて一元化して
集計したい場面ありませんか？
例えば下記のように
今年の実績と前年の実績が
別テーブルになっていて、
それらを使って条件に合致したものを
集計したい場面です。
黄色の月をリストから任意の月を
選択したら自動でデータが置換されます。
4月を選択して見ると、
データが自動で置換されました。
これを一瞬で自動的に作り上げるには
どうしたらできるのでしょうか？
これができるとレポート作成のために
データベースから地道にコピペしたり
目視で確認したりマニュアル作業を
各段に減らすことができます。
SUMPRODUCT関数の実務的な使い方
その時に使うのがSUMPRODUCT関数です。
この関数使い方はパッと見は少し難しいので、
中身を解説します。
私はSUMPRODUCT関数を
使えるようになって、
半日程かかっていたレポーティングが
30分で間違えなくできるようになりました。
だから今回ご紹介しようと思いました。
集計作業が早くなったことも価値ですが、
分析という付加価値を生む作業に
時間をかけることが
できるようになったこと
が良かった点です。
SUMPRODUCT関数で何をしているのか？
では関数の中身をまずは
大枠から説明していきます。
サンプルをダウンロード頂いた方は
"レポート用"タブをご参照下さい。
例として
前年の沖縄の１Qの販売個数47個
のセルの計算式を解説します。
ポイントは
かつ条件の時は＊（アスタリスク）を
使うこと、
条件を設定し終わったら,(カンマ)を打ち、
その後に合計する範囲を設定する2点です。
しかし、
やってみるとわかると思うのですが、
いざ自分で１から計算式を
入れようとすると
間違える可能性大です。
私はできませんでした。
だからまずは式をパクりましょう。
その上で、自分のファイルにあうように
調整する方が確実です。
コチラがその式です。
コピペしてご自身のファイルに
活用してみて下さい。
=SUMPRODUCT((データベース前年!$B$6:$B$26=レポート用!$A5)*(データベース前年!$F$5:$M$5=レポート用!$A$2),データベース前年!$F$6:$M$26)
サンプルのエクセルを
無料でダウンロードできるので、
そのファイルを加工して
使って頂くのも良いと思います。
サンプルエクセルを見ている方は
わかると思いますが、
縦列だけで複数条件の設定
をすることも可能です。
"東日本"かつ"野菜"といった設定した上で、
さらに横列で"4月"に該当するデータを
合計することもできます。
色んな条件設定が可能
かつ条件の時には
アスタリスク（*）を使いましたが、
他にも"または"条件も設定できます。
その時はプラス(+)を使えばできます。
SUMPRODUCT関数のまとめ
メリット
縦列横行に複数条件を設定して
自在に集計ができます。
しかし数字を探すことだけが目的なら
ピボットテーブルがベターです。
なぜなら直感的でわかりやすいからです。
しかし単にデータを探すだけでなく、
定型フォーマットに複数タブを使って
レポーティングを作成するには
ピボットテーブルでは対応できません。
そこでSUMPRODUCTを使い、
複数タブから条件を設定して
条件に一致した集計作業を一瞬にして
行うことができます。
このように目的に応じて
集計する方法を
選択するのが重要です。
デメリット
式がパッと見複雑になってしまうことです。
なのでエクセルに不慣れな人も更新するファイルの場合は難しいかもしれません。
自分だけあるいは
エクセルに詳しい数人しか
更新しないのであれば
使う価値が高い関数です。
ファイルが重たくなる点も
デメリットになります。
何千何万ものデータがあると
動作が重くなる可能性があります。
そういう場合は自動計算をやめて
手動計算にすることをオススメします。
数式タブの計算方法の設定からできます。
10年以上実務でエクセルを使ってきたので、
今後もこういった小技もご紹介したい
と思います。
