[EXCEL] 集計入門　クロス集計、どうやる？ ピボットテーブルか？ COUNTIFSか？SUMPRODUCT関数か？　その３　SUMPRODUCT編

▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索

前回、前々回と集計入門ということで、クロス集計について書きました。

[EXCEL] 集計入門　クロス集計、どうやる？ ピボットテーブルか？ COUNTIFSか？SUMPRODUCT関数か？
その１　ピボットテーブル編（非推奨）
その２　COUNTIFS編（おすすめ
その続きです。

【まとめ】
・SUMPRODOCT関数は記述がわかりやすい。
・ただし、条件が１つだけの場合は計算できない。
・COUNTIFS関数か、SUMPRODOCT関数かは、状況によるが、
条件が１つだけ：COUNTIFS関数（COUNTIF関数ではない）
条件が１つだけだが、複数になるかもしれない場合：COUNTIFS関数
条件が初めから複数とわかっている場合：SUMPRODUCT関数
といえる。

【説明】
今回は、SUMPRODOCT関数を使ったクロス集計です。

下図のデータを・・・

下のように集計します。

前回はCOUNTIFS関数を使ってやりましたが、今回は、SUMPRODCUT関数を使ってみます。

ただ、SUMPRODOCT関数は、ちょっとわかりづらい（私には）。
なので、ここは、Copilotに聞いちゃいましょう（と言っても、ブラウザを立ち上げたら、Bingが開くので、そこに　SUMPRODUCT　と入れて検索しただけですが）。

これだけだとよくわからないので、実際に表を作ってみると・・・

一気に「積」を出す、ということですね。

ただし、今回の「クロス集計」では、この例では使えません。
引き続き、Copilotの答えを見ていくと・・・

今回のクロス集計に少しヒントになりそうな記述がありました。
C1:C3="Yes"　の部分です。

実際に表に数式を入れていきます。

左上のセルに以下を入れます。
=SUMPRODUCT(($C:$C=$F5))

=SUMPRODUCT(($C:$C=$F5))の式の
$C:$C=$F5　は、C列（通勤方法）がＦ５（徒歩）のもの、です。
コピーのことを考えて、C列は「絶対参照」に、「F5」は、列番号だけ「絶対参照」にしておきます。行番号は、下にコピーした際、検索条件も下にずれる「相対参照」にしています。

ただし、この式だけでは、何も出てきません。
SUMPRODUCT関数は、そもそも、「掛け合わせ」をする関数なので、条件が一つだけだと、結果が出ません。

掛け合わせる条件を追加します。

「通勤時間」が「0分以上」は、「D:D>=0」です。
ただし、数式を直接数式に入れるのは「御法度」なので、「0」が入っているセルを入れます。
D:D>=G2

更に、コピーすることを考えて、
検索対象となる「D：D」は、ずれないように「＄Ｄ：＄Ｄ」にします（Ｆ４キー１回押下）。
検索条件となる「G2」は、コピーしても、縦（行）はずれず、横（列）だけずれるように、「行」には「＄」を付け（F4キーを２回押下）、「絶対参照」にします。

結果、$D:$D>=G$2　となります。

これを、追加します。

=SUMPRODUCT(($C:$C=$F5)*($D:$D>=G$2))

注意が必要なのは、
①「掛け合わせ」なので、条件は、掛け算で使う「＊」（アスタリスク）で繋ぐ
② 条件はシンプルに等式や不等式で繋ぐ
③ それぞれの条件をカッコで括る
です。

なお、「$D:$D>=G$2」は「所要時間（D列）が 0分以上」ですが、今回の場合は「$D:$D>G$2」（０分より大きい）でも、実質同じです（通勤時間が０分の人はいないので）。
ただし、この数式をコピーすることを考え、「○分以上」としておきます（そうしないと、他のセルでも「○分以上」が「○分より大きい」になってしまうため）。

条件を追加すると・・・

結果は「３」と出ました。
「通勤方法」が「徒歩」で、「所要時間」が「0分」以上の人数です。

「参照元のトレース」をしてみると・・・

C列に加え、D列、そして「０」が条件に加わっていることががわかります。


上の表では「0分」以上の人を数えているので、これに、
「20分未満」という条件を追加します。
これは「D:D<20」ですが、「検索対象」のD列を絶対参照にし、検索条件は「20」の代わりにセル番地を入れ、かつ、行は「絶対参照」にして、
「$D:$D<G$4」とします。

追加すると・・・
=SUMPRODUCT(($C:$C=$F5)*($D:$D>=G$2)*($D:$D<G$4))

人数は「２」となりました。

「参照元のトレース」をしてみると・・・

分かりづらいと思いますが、「20」にも「・」が付いていて、参照していることがわかります。

これで、「通勤方法」が「徒歩」で、かつ、「所要時間」が「０分以上」かつ「20分未満」の人の数が出ました。

後はこの数式をコピーするだけです。

これで、他のセルにも数式が入りました。
数式をコピーすれば、一つ一つのセルに計算式を入れる必要はありません。これは集計の基本です。

ただし、合計が「14」となっていて、全体の数と異なります。
これは、「80分以上」の欄にも「　分未満」（「　分」が空欄となっている下のセル）の条件が掛かっているからです。
この「　分未満」の条件は削ります。

一番上のセルの数式
=SUMPRODUCT(($C:$C=$F5)`($D:$D>=K$2)`($D:$D<K$4))
を
=SUMPRODUCT(($C:$C=$F5)`($D:$D>=K$2))
に修正し、下にコピーします。

これで、個々のセルの合計が、全体の件数と同じになり、個々のセルの結果が正しいことがわかりました。
「数式はコピーしたうえで、必要の応じて修正する」、これも集計の基本です。

COUNTIFS関数か、SUMPRODUCT関数か？

以上で、SUMPRODUCT関数を使ったクロス集計の説明は終わりです。
繰り返しになりますが、SUMPRODOCT関数は、
「掛け合わせ」が前提なので、「複数の条件の場合」でないと、使えない、ということです。
また、前回示したCOUNTIFS関数とは、記述方法が異なります。

COUNTIFS　と　SUMPRODUCT　を比べてみます。
（わかりづらくなるので「＄」は取っておきます。）

=COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)
=SUMPRODUCT((C:C=F5)*(D:D>=G2)(D:D<G4))

COUNTIFS　は、検索対象と検索条件を「, 」で区切って並べていきます。
SUMPRODUCT　は、検索対象と検索条件をカッコの中に一緒に入れて、「*」で繋いでいきます。

また、「以上」「未満」といった検索条件を
COUNTIFS　は、「"」で括って、かつ、セル番地と「&」で繋げますが、
SUMPRODUCT　は、「"」も「＆」も不要で、セル番地と繋げます。

COUNTIFS　　　D:D,">="&G2
SUMPRODUCT　D:D>=G2

これ、どちらがわかりやすいでしょうか？
記述が楽なのは、SUMPRODUCTでしょう。
「"」も「＆」もいりませんから。
しかも「*」で条件をかけ合わせていくのは、わかりやすいと思います。
というか、COUNTIFS　も同じように「"」も「＆」も省略できればいいのにな、って思います。
COUNTIFS の複数条件設定で躓くのは、多分、ここでしょうから。
（私も、久しぶりに使うと「"」や「＆」を忘れてエラーを出してしまいまう。）

というわけで、「複数条件の掛け合わせ」には、SUMPRODUCT関数を使いましょう、と言いたいところですが・・・
SUMPRODUCT関数の最大の弱点は、「掛け合わせないといけない」ということ。
つまり、条件が１つでは答えがでない、ということです。
その点、COUNTIFS関数は、条件が１つでも、複数でも答えが出ます（だから、単一条件しか使えないCOUNTIF関数を使う必要はありません）。

また、COUNTIF関数より、SUMPRODUCT関数の方が認知度が低い（知っている人が少ない）と思われるので、他の人（後任）にとっては、COUNTIFS関数の方が判りやすいかもしれません（ただし、数式の記述はSUMPRODUCT関数の方が判りやすいともいえるので、なんともいえません）。

結論は出ませんが、少なくとも
条件が１つだけの場合は、COUNTIFS関数（COUNTIF関数ではない）、
条件が１つだけだが、複数になるかもしれない場合も、COUNTIFS関数
条件が初めから複数とわかっているのなら、SUMPRODUCT関数、
と言えるでしょう。
いかがでしょうか？

なお、
「クロス集計なら最初から SUMPRODUCT関数」と割り切ってもいいでしょうが、
同一シート上で、単一条件と複数条件の集計表がある場合（あるいは、単一条件から、条件が追加されていく可能性がある場合）、COUNTIFS関数で統一した方が、（後任も）わかりやすいかもしれません。

以上、クロス集計について、でした。
「クロス集計」とは２つの項目を掛け合わせる（クロス）する集計ですが、今回のものは、実際「３つ」の条件を掛け合わせているので、「入門」にしては、ちょっと複雑だったかもしれません。
でも、実務では、よくあること。
このくらいのものができないと、実務では役に立たない、とも言えます。
「クロス集計」は、実務のキモの一つですので、今後も、取り上げていきたいと思います。

以上、参考になれば幸いです。
もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです！

そうそう、「通勤時間」の平均とデータバーの説明を忘れていました。
それは次回以降に。