ExcelのSUMPRODUCT関数(2)複数条件を指定した足し算

Excel 一歩先へ

スポンサーリンク

はじめに

前回は、ExcelのSUMPRODUCT関数について、足し算と掛け算の複合技というべき基本機能を取り上げた。
本番は今回で、複数条件を指定した足し算をする機能について述べる。

まずSUMIFS関数で集計

まず、今回の例題で使用するExcelファイルを、こちらに保存しています。

上図の表で、H2:I2セル及びH3:I3セルに示した条件で金額(E列)を集計する数式といえば、まず最初にSUMIFS式を思い浮かべられるようでいてほしい。

そのためにK2セルに入れたSUMIFS数式は

=SUMIFS($E$2:$E$20,$A$2:$A$20,$H2,$B$2:$B$20,$I2)

となる。

以前の記事でも取り上げているので、あまり詳述はしないが、背景色の色分けだけはしておいた。

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

SUMIF関数は集計のための条件を1つしか指定できないが、今回は都道府県・性別という2つの条件を指定するため、SUMIFS関数でないといけないわけだ。

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

SUMPRODUCT関数で集計

さて、このSUMIFS関数は、Excel2003までは存在しない関数だった。
だから、複数条件を指定した集計をするためには、どうしても代わりが必要で、そのためにSUMPRODUCT関数はめちゃくちゃ使いまくった。

上記のSUMIFS関数による集計を、SUMPRODUCT関数でやると、このようになる。

=SUMPRODUCT(($A$2:$A$20=$H2)*($B$2:$B$20=$I2)*($E$2:$E$20))

なんかもう、本当にややこしい。
括弧()文字が幾つもあるのはともかく、「=」やら「*」の記号が何度も出てくるのが、普通の関数では有り得ないことだ。

言葉で書いてまとめると、

=SUMPRODUCT((条件の範囲01=条件指定01)*(条件の範囲02=条件指定02)*(合計する数値範囲))

てな感じになるんだけど、もうこれを覚えるというか、毎回見ながら型にはめて書くくらいじゃないと、慣れない人にはとても使っていけないだろう。
私も単純に、型にはめて書いてばかりだ。

なんでこんな書き方をするのか、理屈を無理矢理に述べてみる。

SUMPRODUCT関数の理屈

=SUMPRODUCT(($A$2:$A$20=$H2)*($B$2:$B$20=$I2)*($E$2:$E$20))

再掲した上の式で、背景色が黄色の文字と緑色の文字だけに注目する。

これは前回取り上げたように、A列・B列・E列の掛け算どうしの結果を足し算するって計算を、まとめて書いたものだ。

ではそこに、なぜ「=」文字とかが付いてくるのか。
条件指定の1個め
$A$2:$A$20=$H2
の部分は、「$A$2:$A$20セルの中で、$H2セルの条件と合致するもの」を一挙に検索しているわけだ。

そしてここがポイントなのだが、その検索の結果として、$H2セルの条件と

  • 合致したものは1
  • 合致しなかったものは0

という結果を入手しているのだ。

もう少し固い言い方をすると、合致するとTRUE、合致しなかったらFALSEという結果を得ている。実際にExcelの中では、TRUEは1、FALSEは0と数値として等しいってことになってるんだけどね。

だから、合致した1と合致してない0を、掛け合わせ＆足し合わせをしていくことで、結果として複数条件に合致したものの合計が求められるということだ。
強引に締めたが、これで分かるだろうか？

なんでSUMPRODUCT関数ばっかり、こんな風に括弧()文字の中に「=」が登場したりする妙な書き方をするかって、SUMPRODUCT関数は配列関数ってやつの一種だから。

配列関数というのは、Excelの中でもかなり特殊でマニアックなもので、私も理解を放棄している代物。
ちなみに本来の配列関数は、最後にCtrl+Shift+Enterキーを押すという特殊な操作が必要なんだけど、SUMPRODUCT関数はそこまでは必要ない。

配列なんて言葉が出てきてるけど、関数の中で括弧で囲まれた

$A$2:$A$20
$B$2:$B$20
$E$2:$E$20
とかが、配列というやつを構成しているというわけ。

SUMPRODUCT関数だけでもややこしいので、配列とか配列関数なんていうのについては、これ以上は深入りしない。

SUMPRODUCT関数で「+」を使う

役立てられる場面は少ないと思うが、オマケで一応書いておく。

上図のL4セルで、
都道府県=愛媛県 または 年齢=34
の金額を合計するという、いわゆるOR条件による計算をしている。
(該当するセルの背景色をピンク色にしてある)

その数式が

=SUMPRODUCT((($A$2:$A$20=$H4)+($D$2:$D$20=$J4))*($E$2:$E$20))

となっていて、「+」記号がOR条件の役割をしているというわけだ。

$A$2:$A$20=$H4
$D$2:$D$20=$J4
の2条件を足し合わせているわけね。

ただこんな技は、忘れて問題ない。

上図のL5セルで
都道府県=茨城県 または 性別=女
の条件で集計しようとして、「+」記号を使ったSUMPRODUCT式を使っている。

条件に該当するセルの背景色をピンク色にしているが、それらを足しても6,500にしかならない。

しかし20行目の1,500円は、「都道府県=茨城県」「性別=女」の条件を両方とも満たしているので、これが二重に足されてしまい6,500+1,500=8,000という結果がL5セルに出ていることになる。

先程の
都道府県=愛媛県 または 年齢=34
条件は、こういう重複が起きないケースをわざと選んだ。

こういう風に、よほど上手く使わないと集計ミスを起こすので、SUMPRODUCT関数で「+」を組み込むのはやめておいた方が良いだろう。

SUMPRODUCT関数の使い方については、次回くらいまでもう少し続けて書きたいと思う。

スポンサーリンク