続関数誕生日を入力別のセルに色がつく、変更▢がつく

質問

現在の表をUPしました。
問題が出ましたので回答のほう宜しくお願い致します。
さて問題ですが、画像の赤線で囲いました、説明します。問題は◎連動と?連動のところが両方ついていることです。次に式を示します。
◎、=IF(C24<>"",IF(SUMPRODUCT(($B$4:$S$6=C24)*1),"◎",""))
?、=IF(SUMPRODUCT((SUBSTITUTE(C24,RIGHT(C24,2),"")=SUBSTITUTE($B$4:$S$6,RIGHT($B$4:$S$6,2),""))*1,($B$5:$S$7=C25)*1),"□","")
C24 、112はH4にあります、ですのでC26に◎がつきます、この時C27、□がついてしまいます。
計算上は間違いはありません。
□の計算式に「同じ誕生日は省く」、「対象にしない設定」というのを付けることはできませんでしょうか？

回答No.1

bunjii · ベストアンサー

>□の計算式に「同じ誕生日は省く」、「対象にしない設定」というのを付けることはできませんでしょうか？
1つのセルで"◎">"□">"○">"△"の順で条件分岐する数式が次のようになります。
B26=IF(B24<>"",IF(SUMPRODUCT(($B$4:$Q$6=B24)*1),"◎",IF(SUMPRODUCT((SUBSTITUTE(B24,RIGHT(B24,2),"")=SUBSTITUTE($B$4:$Q$6,RIGHT($B$4:$Q$6,2),""))*1,($B$5:$Q$7=B25)*1),"□",IF(SUMPRODUCT((RIGHT(B24,2)=RIGHT($B$4:$Q$6,2))*1),"○",IF(SUMPRODUCT(($B$5:$Q$7=B25)*1),"△","")))),"")
あなたが考えているテーブルが度々変更され、自分で数式を組み替えられないのであれば無駄な考えをやめたら如何でしょうか？
◎連動と言う表現自体が私の思考に合いません。
"◎"の結果を得られる数式は次のようになります。
B26=IF(SUMPRODUCT(($B$4:$Q$6=B24)*1),"◎","")
但し、B24セルが未入力のときのチェックは別途追加する必要があります。
"□"の結果を得られる数式は次のようになります。
B27=IF(SUMPRODUCT((SUBSTITUTE(B24,RIGHT(B24,2),"")=SUBSTITUTE($B$4:$Q$6,RIGHT($B$4:$Q$6,2),""))*1,($B$5:$Q$7=B25)*1),"□","")
この2つを組み合わせると目的の結果になると思います。

お礼

長々とありがとうございました。さようなら