エクセルの関数について教えてください。

質問

エクセルの関数について教えてください。

わからないことがあるので質問します。

画像のような表があって、Ａの列にはチェックのための記号が入力できるように空けています。
イ、ロ、ハ、ニ、ホの5人がいてそれぞれの得意科目と苦手科目がありそれを表にしてます。
イ君とニ君がいるとする(A2とA5に☆印をつける)と、
イ君は国語が得意、ニ君は国語と理科が得意で今、国語と理科は得意な人がいる、というのが一目で分かるように色分けしたいのです。
Ａの列には何も入力していないときはセルの背景は白で、例えば、
・A2に☆と入力すると２の行の○があるセル(この場合C2)の背景を赤にする
・その○があるセルの縦一列(C2からC6まで)をオレンジに塗りつぶす
(☆の数は一個とは限らない)
というような関数を書きたいのですが、
どのように書けばいいのでしょうか？
一つのセルに関数を書いて、それを全部にコピーして完成させたいのですが、
可能でしょうか？

ネットなどで調べて書いてみたのですが、C2のセルの、条件付書式の条件1に、=AND($A2="☆",C2="☆")と入力して、背景を赤に設定し、
それを、C2をコピー→C2からG6を選択→形式を選択して貼り付け、書式にチェックをいれて貼り付ける方法で、どのセルに☆を入れても○があるセルの背景は赤くなったのですが、2番目の『その○があるセルの縦一列をオレンジに塗りつぶす』方法が分かりません。

MATCH関数で、○がある位置を検索して・・・と考えているのですが、その関数であっているのかも分からないですし、条件が複雑のなのでこんがらがっているので教えてください。
よろしくお願いします。

回答No.1

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

あまり広くないセル範囲で設定するとして
C2セルからG6セルまでを選択して
条件付き書式
　条件1 数式が =AND($A2="☆",C2="○")
　条件2 数式が =SUMPRODUCT(($A$2:$A$6="☆")*(C$2:C$6="○"))

人数が多い場合は1行目や最終行などに条件2の数式を入れてあげます。
さらに多い場合は、I列～M列に
I2セル =IF($A2="","",IF(C2="○",1,""))
右へ下へオートフィル
その合計を出して、判断材料にします

お礼

回答していただいてありがとうございます。

SUMPRODUCTという関数があるんですね！
実際はもう少し大きな表でしたが、教えていただいた通りにやってみるとできました。
結構複雑な関数になるだろうなと思っていたのですが、一つでできるのですね！

とても助かりました！ありがとうございました。