Excel2007以降のSUMIFS関数 ―SUMIF以上だがSUMPRODUCT未満―

Excel 一歩先へ

スポンサーリンク

Excelで、条件に合致する数値を合計する関数として、SUMIF関数があって、別の記事で紹介している。

Excel 絶対参照・相対参照 SUMIF関数を題材にして語る

2018年5月29日

この他に、SUMIFS関数というのがあるが、それを使われているだろうか。

正直言って、Sが付くSUMIFS関数は、Sが付かないSUMIFの上位互換的な関数だ。

SUMIFS関数の概要①SUMIFを代用できる例

Excel2003までを知らない人にはピンと来ないだろうが、Excel2003までにはSUMIF関数があってもSUMIFS関数がなかった。

そしてSUMIF関数は集計のための条件を1つしか指定できないため、その解決の要望が多く、SUMIFS関数がExcel2007から新規追加され、集計のための条件を複数追加できるようになった。

上図は、A列~D列の表において、「顧客名=山田」の条件を満たす1月売上の合計をK3セルに求める例だ。
分かりやすくするために山田さんの名前と数字は背景色を青にしている。

計算式は、SUMIF関数を使うなら図にある通り

=SUMIF($A$3:$A$13,$J3,$D$3:$D$13)
で良い。

そしてこれを、SUMIFS関数で書き直すと
=SUMIFS($D$3:$D$13,$A$3:$A$13,$J3)
となる。

パッと見で分かりにくいかも知れないが、合計する範囲である「$D$3:$D$13」が、SUMIF関数では一番後ろなのに対しSUMIFS関数では一番前だ。

正直この例では、SUMIFじゃなくてSUMIFS関数で良い。
SUMIFなんて一切使わず全てSUMIFS関数を使おうというなら、それでも良いだろう。

SUMIFS関数の概要②複数条件で集計する場合

次にSUMIFでは出来ないことをやれる、SUMIFS関数の特長を述べる。

前述の問題について、
「顧客名=山田、商品=バナナ、決済手段=カード」
の3つに条件を増やしてその合計を求めたい。

解答としては上図の通り

=SUMIFS($D$3:$D$13,$A$3:$A$13,$J3,$B$3:$B$13,$K3,$C$3:$C$13,$L3)
となる。

いよいよ分かりにくくなってきた。一応、文字の背景色を分けては見たが、
=SUMIFS(合計する数値範囲,条件の範囲01,条件指定01,条件の範囲02,条件指定02,条件の範囲03,条件指定03)
という構造になっている。
条件指定については、多分いくつでも増やせるので、どんどん分かりにくくなっていく。

SUMIFS関数の注意点

さて、SUMIFS関数についてだが、ぜひとも触れておかなければならない注意点がある。
というかこの注意点、非常に大事なことだと思うのだが、私の知る限りどのサイトとかでもろくに言及されていない。

実はこの関数、条件指定を縦横に組み合わせることはできないのだ。

上図は、E・F列に2月・3月が加わった。
そしてM2セルに「2月売上」と書いたわけだが、これを「1月売上」「3月売上」と書き換えたなら、その月に対応する売上が自動計算されるように式を入れたいのだ。

ここで、

SUMIFS関数なら条件指定を無限にできるのだから、M2に書かれた「2月売上」というのもD2セル~F2セルから条件指定すればええやん

と私は思っていた。しかし

実はそれだと、上図のようにエラーになる。
上図では、M3セルの計算式を

=SUMIFS($D$3:$F$13,$A$3:$A$13,$J3,$B$3:$B$13,$K3,$C$3:$C$13,$L3 ,$D$2:$F$2,M$2)
として、下線の部分を追加している。

すなわち、D2~F2セルの見出しをM2セルで条件指定して、合計範囲をD3~F13セルと3列にまたがらせたわけだけど、これではエラーになるのだ。
なんでエラーになるかって、そういう仕様だからとしか言えない。
縦横を組み合わせられない仕様だからとしか。

SUMPRODUCT関数

では、どうしても上図の問題を関数で解決したい場合、手段はあるのか?
ある。Excel2003から既に存在したSUMPRODUCT関数だ。

正確にはこの関数は、「A1×B1 + A2×B2 + A3×B3・・・」みたいな掛け算と足し算の合わせ技をするような関数なのだけど、SUMIFS関数が存在しないExcel2003で複数条件指定の集計をしたいときに専ら使われていたと言って良いだろう。

上図の問題を、このSUMPRODUCT関数で解決するとなると、

=SUMPRODUCT(($A$3:$A$13=$J3)($B$3:$B$13=$K3)($C$3:$C$13=$L3)($D$2:$F$2=M$2)($D$3:$F$13))

となる。条件式で 「=(イコール) 」と「*(かける)」を使って結びつけていくのが特徴。

この関数について書いていくと、それだけで凄く長くなるので、必要と感じたら個別記事で取り上げます。

スポンサーリンク