Excel SUMPRODUCT関数とSUMIFS関数 縦横混合の集計を比較してみる

Excel 一歩先へ

スポンサーリンク

はじめに

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

前回の記事で、SUMPRODUCT関数を使って、複数条件を指定した足し算をする例を扱った。

しかしSUMPRODUCT関数って小難しいから、そんなに使いたくはなく、同じ複数条件で足し算するならSUMIFS関数を使いたいところだ。

では複数条件の足し算なら全てSUMIFS関数でいけるかというと、意外にそうはならない場面がある。

SUMIFS関数で縦横混合の集計はできない

ここからは、以前のSUMIFS式の記事で取り上げた内容とかなり重複があるけど、SUMIFS関数で思い通りにならない例を述べる。

思い通りにはならないんだけどそれでもSUMIFS関数で解決する方法をさっさと読みたいなら、こちらへどうぞ。

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

上図の問題は、

  • 都道府県(A列)が和歌山県
  • 性別(B列)が男

という条件に、横軸(D2:I2セル)の「月＝2月」条件も付け加えて、M3セルに数値合計を求めたいという例だ。

これのサンプルExcelファイルはこちら。
こういう風に、月が横軸にあって、縦も横も集計条件として考慮しないといけないケースには、私なんかは実務でたまに出くわす。

この複数条件集計を、無理矢理にSUMIFS関数で解決しようとすると、

=SUMIFS($D$3:$I$20,$A$3:$A$20,$K3,$B$3:$B$20,$L3,$D$2:$I$2,$M2)

というような感じになるだろう。

合計したい数値が集まったセル範囲$D$3:$I$20を先頭に持ってきて、後は条件式を順番に設定した感じ。
でもこの式では、エラーになってしまう。

合計する数値範囲が$D$3:$I$20と縦横に入り混じったのが悪かったのか？
ではその数値範囲を、縦方向の広がりは無くして$D$3:$I$3に変更し、式を次のようにしてみるとどうか。

=SUMIFS($D$3:$I$3,$A$3:$A$3,$K3,$B$3:$B$3,$L3,$D$2:$I$2,$M2)

しかしこれでもエラーになってしまう。

SUMIFS関数は、縦なら縦、横なら横に、条件範囲と数値範囲が統一されて並んでいる場合しか集計できないのだ。

ちなみに今回、横方向まで条件指定をしなくて良くて、最初から2月の数値を集計すれば良いだけなら、そのSUMIFS式は次のようになる。

=SUMIFS($E$3:$E$20,$A$3:$A$20,$K3,$B$3:$B$20,$L3)

SUMPRODUCT関数で縦横混合の集計を

SUMIFS関数では、数値や集計条件が縦横に混合していると、集計できないことが分かった。

これが、SUMPRODUCT関数でなら普通に可能だ。その式をズバリ書くと、次のようになる。

=SUMPRODUCT(($A$3:$A$20=$K3)*($B$3:$B$20=$L3)*($D$2:$I$2=M$2)*($D$3:$I$20))

SUMIFS関数は元々がSUMIF関数の拡張ということで、複数条件の集計に特化した関数なわけ(なら縦横混合の集計にも対応してほしいのだが)だけど、SUMPRODUCT関数は別にそういう性質ではない。

掛け算と足し算との合わせ技をするのが大元の性質といってもよく、その気になればかなり幅広い用途に使える。
そして今回の例のように、縦横が入り混じった集計でも自在に集計できる。

というわけでSUMPRODUCT関数はSUMIFS関数より強力な複数条件集計ができるのだが、いかんせん重くなりやすい。
数万行のデータを集計するとかの際、SUMIFS関数ならさほど重くならないが、SUMPRODUCT関数をたくさん使っていると重くなってしまう。

そして数式中に括弧()が多くなりやすくて複雑だし、SUMIFS関数はカンマ「,」で区切っていくだけなのが書きやすい。

だから、繰り返し述べるが、可能な限りSUMPRODUCT関数は使わずに済ませ、SUMIFS関数などのお手軽な関数で済ませたいものだ。

それでも縦横混合の集計をSUMIFS関数で行うには

それでは今回の例のような、数値や集計条件が縦横に混合している場合の集計を、なんとかSUMIFS関数でやる方法を考えてみる。

といってもSUMIFS関数のみ単独で実施するのは困難なので(できるのかな？)、ちょっとINDEX関数・MATCH関数なんてものを補助に使ってみる。

上図ではM1セルに、MATCH関数を使って

=MATCH($M$2,$D$2:$I$2,0)

という式を入れてある。

M2セル「2月」に完全一致(式の最後で0を指定したもの)するものを、D2:I2のセル範囲から検索して、その位置である「2」(左から2番め)が答えとして返されている。

そしてこのM1セルの結果を利用して、M3セルに

=SUMIFS(INDEX($D:$I,0,$M$1),$A:$A,$K3,$B:$B,$L3)

というように書くと、正しく集計できる。

このINDEX関数は、特にMATCHと組み合わせて使うと強力だけど、慣れるのにかなり苦労するであろう関数の一つだ。

要するに今回の

INDEX($D:$I,0,$M$1)
という式の場合は、3つ書いてある引数について順に述べると
  1. $D:$Iのセル範囲の中で
  2. 縦方向は問わないので、引数は0
  3. 横方向は、M1セルの、MATCH関数で求めた2。

というようになる。

結果として、セル範囲$D:$Iの中で、横方向2番めにあるE列(=2月の列)が、SUMIFS関数で合計する数値の範囲となるというわけ。

このINDEX・MATCHの合わせ技は随所で活躍するが、何度も根気良く使っていかないと身に着かないだろう。

今回はM1セルを補助セルとして使ったが、これを使わず最初からまともにM3セルに式を全部書くなら

=SUMIFS(INDEX($D:$I,0,MATCH($M$2,$D$2:$I$2,0)),$A:$A,$K3,$B:$B,$L3)

というようになる。

まあこれは、私の目から見ても長ったらしくてややこしいので、こういうときは作業セル・補助セルは積極使用していく方が良いと思う。

縦横混合の集計を、SUMIFS関数だけでなくINDEX・MATCHなんて関数まで組み合わせてやってみたが、SUMPRODUCT関数単独のほうが使いやすいと思うかもしれないね。

というか、こういう縦横混合の集計などという作業が必要にならないように、大元の資料を組み立てることを心がけた方が良いかとも思う。

私がこんな縦横混合集計なんかをやらされるのは、他の人からいきなり作りのよろしくないExcelファイルを渡された場合がほとんどであり、自分で一からExcelファイルを作り込む場合はSUMIFS関数で集計できるように構築する。

というか、Excelの表は縦長を基調に作り込むのが本来のあり方であり、年月とかが横に広く並ぶのは本来は好ましくないから、まずはそういった感覚を養うことが大事だろうね。

スポンサーリンク