条件で合計を分ける？方法を教えていただけますか？

質問

どなたかお分かりになる方、教えていただけますでしょうか？
下記添付画像のようなエクセル表があります。

この表から、テストの点数の総合計が50点以上で、かつ科目「英」と「数」の合計も50点以上の人は「Yes」、そうでない人物は「No」ということが、すぐに分かるような表を作りたいのです。

例えば、Aさんはこれまで4つのテストを受けており、そのテスト全ての合計点数が80点で、かつ「英」と「数」の総合計70点なので、「Yes」
Bさんは、これまでテスト２つで総合計が50点、「英」「数」の総合計も50点なので「Yes」、
Cさんは３つのテストで総合計は60点だけれど、「英」「数」の合計が30点なので「No」、
Dさんは１つのテストで総合計50点だけれど、「理」以外のテストを受けてないので「英」「数」総合計０点なので「No」

色々考えたのですが、私の知識では全く思いつかず。。。
お分かりになる方がいらっしゃいましたら、ご教授いただけないでしょうか。

どうぞよろしくお願い致します。

回答No.6

tom04

こんばんは！
すでに回答は出ていますので、
参考程度で目を通してみてください。

名前が重複して出ていますので
余計なお世話かもしれませんが、
作業用の列を使って、重複している名前は一つにするようにしていました。

↓の画像の作業用の列、E2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れ、オートフィルで下へコピーします。
（結果の欄の数式が100行まで対応できるようにしていますので、それくらいコピーしても構いません）

結果のG2セルに
=IF(COUNT($E$2:$E$100)<ROW(A1),"",INDEX($A$2:$A$100,SMALL($E$2:$E$100,ROW(A1))))

H2セルに
=IF(G2="","",IF(AND(SUMIF($A$2:$A$100,G2,$D$2:$D$100)>=50,SUMPRODUCT(($A$2:$A$100=G2)*($B$2:$B$100="英"),$D$2:$D$100)+SUMPRODUCT(($A$2:$A$100=G2)*($B$2:$B$100="数"),$D$2:$D$100)>=50),"Yes","No"))

という数式を入れ、G2・H2セルを範囲指定し、H2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、長々と書きましたが参考になれば幸いです。m(__)m

回答No.5

tetumaru_1

A B C D E F G H I
1 名前 科目 検索 テスト 点数 名前 合計 英数 判定
2 A 英 A英 第１回 30 A 80 70 YES
3 A 数 A数 第１回 20 B 50 50 YES
4 A 理 A理 第１回 10 C 60 30 NO
5 A 数 A数 第２回 20 D 50 0 NO
6 B 英 B英 第１回 20
7 B 数 B数 第２回 30
8 C 英 C英 第２回 10
9 C 数 C数 第２回 20
10 C 理 C理 第２回 30
11 D 理 D理 第１回 50
A2のセル=CONCATENATE(A2,B2)
G2のセル=SUMIF(A2:A11,F2,E2:E11)
H2のセル=SUMIF(C2:C11,F2&"英",E2:E11)+SUMIF(C2:C11,F2&"数",E2:E11)
I2のセル=IF(AND(H2>=50,G2>=50),"YES","NO")
必要行数だけ下へコピーしてください。
この方が見やすいと思います。

回答No.4

MackyNo1

＞「テストの点数の総合計が50点以上で、かつ科目「英」と「数」の合計も50点以上」

よくよく考えたら、「英」と「数」の合計が50点以上なら総合計も必ず50点以上になりますね。

例示した条件が誤りで、総合計の条件が「50点」以上でない場合は、適宜数字を変更してください。

もし、ご質問の趣旨がそのような条件でない場合には、もう少し具体的にYesの条件を例示してください。

回答No.3

MackyNo1

＞この表から、テストの点数の総合計が50点以上で、かつ科目「英」と「数」の合計も50点以上の人は「Yes」、

名前が「A」の人の場合以下の式で結果を表示できます。

=IF(AND(SUMPRODUCT((A2:A100="A")*C2:C100)>=50,SUMPRODUCT((A2:A100="A")*(B2:B100={"英","数"})*C2:C100)>=50）,"Yes","No")

回答No.2

mame9999

＃１です

画像の内容が間違っていたので、修正します

回答No.1

mame9999

まずピボットテーブルを作り並びをかえます
そのピボットテーブルを書式と値のコピーをします

その後ＩＦ関数をつかって判定します

サンプルを載せておきます

この投稿のマルチメディアは削除されているためご覧いただけません。