SUMPRODUCT関数 『複数条件』に当てはまらない個数

質問

いつもお世話になっております。

【データ】
　　　A　　 　B 　　 　C　　　D
2　 　1 　　 　4 　　 　 4
3　 　2 　　　 2 　　　 2
4　 　3　　　　5　　　　 1
・　　　・　　　・　　　　　・　　　
・
・
・

というように１から５までの値が入っています。

　　　　　　　　　　A　　　　　　B　　　　　　C
パターン1　　　A>=4　　　　B>=4　　　　C>=4
パターン2　　　A<2 　　　　　　　　　　　　C>=4
パターン3　　　　　　　　　　　　　　　　　　C<3
パターン4　　　　　　　　　　　　　　　　　3=<C<4

『パターン１～4に当てはまらない』データのD列の合計を出したいのですが、
どのようにしたら出るのかわかりません。

ちなみに、上記のパターンは、AかつBかつCという条件になっています。
例えば、パターン１は、「Aが4以上かつBが4以上かつCが4以上」という意味です。

説明の仕方がわかりにくいと思いますが、どうぞ宜しくお願い致します。

回答No.6

mshr1962 · ベストアンサー

優先順に除外の条件整理すると
パターン1' C>=4（パターン3,4の除外）[絶対条件]
パターン2' A>=2（パターン2の除外）[C>=4が確定の絶対条件]
パターン3' A<4,B>=4（パターン1の除外の1）[C>=4,A>=2が確定のB>=4の場合の条件]
パターン4' A>=4,B<4（パターン1の除外の1）[C>=4,A>=2が確定のB<4の場合の条件]
となるので
パターン1'Andパターン2'And(パターン3'orパターン4')[パターン3',4'に重複範囲はなし]のDの合計なら
=SUMPRODUCT((C>=4)*(A>=2)*((A<4)*(B>=4)+(A>=4)*(B<4)),D)

お礼

ご回答、ありがとうございます。

>((A<4)*(B>=4)+(A>=4)*(B<4)),

パターン1'～パターン2' はわかったのですが、
こうやって書くのですね。。勉強になります。
これから試してみます。

また、知人から
『他の列にパターンのマークをつけて、どこにもマークがないものを集計したりとすればよいのではないですか』と助言を頂きました。
こちらも試したいと思います。

丁寧に回答していただき、とてもよく分かりました。
本当にありがとうございました。

回答No.5

imogasi

質問の書き方が稚拙。
＞当てはまらない
なら当てはまるほうを書く必要なく、直接当てはまるほうを書いたら（考えたら）。
NOT関数を使う手もあるかも知らない（未確認）が、凝らなくて良いと思う。
＞AかつBかつCという条件になっています
AND条件であればSUMPRODUCT関数では
=SUMPRODUCT((A列条件表現)*(B列条件表現)*(C列条件表現))
と＊を使うのは知っているのかどうか。知っているなら、今までやったパターンでも書いて質問したら。
質問する前にSUMPRODUCT関数の解説など読み、自分で試行してみるべきである。
ーー
パターン１では
A列条件表現
４以下　　（A1:A100<4)
B列条件表現
４以下　　（B1:B100<4)
C列条件表現
４以下　　（C1:C100<4)
これでデータが小数点数もあるらしいが、上記式で正しいか（該当するか）やってみて。
--
条件が無い列文がしに部分の（）をそっくり省略すればよい。
（パターン２，３，４など）

お礼

ご回答、ありがとうございます。

質問の書き方が稚拙で、申し訳ありません。。（イヤみで書いてるわけではありませんよ（笑））

>なら当てはまるほうを書く必要なく、直接当てはまるほうを書いたら（考えたら）。

考えたら一直線のところがありまして、
パターン１～４の場合のD列の合計をだしてグラフを作成していました。
そして、「ではパターン１～４に当てはまらないものは」というように
集計が進んで、頭の中が『当てはまらない・・・当てはまらない』となってしまったのです。

>＊を使うのは知っているのかどうか。知っているなら、今までやったパターンでも書いて質問したら。

知っています。こちらでは、SUMPRODUCT関数について色々教えていただき、パターン１についてもSUMPRODUCT関数を使って計算しました。今、調べましたら、imogasiさんにも先日お世話になっていました！ありがとうございます！
紙に書きながら色々考えてから質問しましたが、自分が考えている範囲でも書けばよかったです。稚拙な質問となってしまいました。申し訳ありません。

>パターン１では、A列条件表現・・・

こちらは、Ａ、Ｂ、Ｃは４以下ではない場合もあるのです。。それがまたごちゃごちゃしてしまって。

今回もご回答、ありがとうございました。
これからの質問の仕方にも気をつけたいと思います。

回答No.4

noname#176215

それだけ条件がはっきりしているのですから 条件を書き出して DSUM
関数で集計すればいいように思います。

お礼

ご回答ありがとうございます。

>それだけ条件がはっきりしているのですから

そうですね。。
実際はもっと列が多いので、頭の中がごちゃごちゃしてしまいました。
考えてみます。

ありがとうございました。

回答No.3

Cupper

条件が成り立つ場合のSUMPRODUCT関数を書くことができるのであれば
（全てのパターン数）－（条件の成り立つパターン数）
でOK

条件部分は
　(A>=4)*(B>=4)*(C>=4) + (A<2)*(C>=4) + (C<3) + (C>=3)*(C<4)
で良いと思います

ブール(Boolean)代数を覚えると、上記のような引き算をしなくてもSUMPRODUCT関数だけで条件を設定することができます。
また、複雑な条件も簡素にすることができるかもしれません。
※例えば、パターン４は C=3 にすることができます
（見た目に分かりやすくなることもありますが、数式から初期の条件を読み取れない場合もあります）
これを機会にブール代数を覚えてみてはいかがでしょう。

※SUMPRODUCT関数の記述方法についてはExcelのヘルプや解説サイト、解説書籍などを参照してください

お礼

ご回答、ありがとうございます。

申し訳ありません。私の説明不足でした。
値は、小数も入るのです。

>ブール(Boolean)代数を覚えると、上記のような引き算をしなくても>SUMPRODUCT関数だけで条件を設定することができます。

プール代数、覚えておきます。時間があるときに挑みたいと思います。
ありがとうございました。

回答No.2

noname#79209

#1です。
合っていませんね。

回答No.1

noname#79209

データは整数のみですよね？
だとすれば、パターン４は「C=3」しかないですよね？
つまり、C<=3ならパターン3,4に当てはまってしまいます。
従って、パターン１～４に当てはまらないのは、
少なくともC>=4です。よって
AND(C>=4,OR(A<=3,B<=3))
では？
合ってるかな？

補足

ご回答、ありがとうございます。

>データは整数のみですよね？

申し訳ありません。私の説明不足でした。
小数もあるんです！！！

パターン１～４に当てはまらないデータとして、

　　　A　　 　B 　　 　C　　　D
2　 3.67 　　3.5　 　 4.9

などがあります。
並べ替えをして手作業で調べました・・・・。
関数を使ってどうにか調べられないかと悩んでいます。。

ありがとうございました。