Excelの式が上手く行きません。

質問

色々調べてみたんですが、上手く行かないので質問させてください。

　　　　　　　　1日目　　　　　　　　　　　　2日目
セルの列「C15」「C16」「C17」　　「C18」「C19」「C20」　
田中さん　「 」「A」「A」　　　　　　　　　「 」「A」「A」
鈴木さん　「 」「A」「A」　　　　　　　　　「 」「 」「 」

こんな感じでセルに入力したものを、次のシートで自動的にリストが作成されるように式を作っています。
1つのセル内に、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る。
(2)鈴木さんのみが「 」「A」「A」のとき、鈴木さんの名前が出る。
(3)田中さん・鈴木さんともに「 」「A」「A」のときは、田中さんと鈴木さんの名前が並んで表示される。
という3つの条件をすべて満たせる式を作りたいのですが、どうしても上手く行きません。

方法I：2つの式の間に「&」を入力した場合。
=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="",Sheet1!R4C17="A"),Sheet1!R4C4&IF(AND(Sheet1!R5C15="",Sheet1!R5C16="",Sheet1!R5C17="A"),Sheet1!R5C4,))

これだと、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る⇒○
(2)鈴木さんのみが「 」「A」「A」のとき、なにも表示されない⇒×
(3)田中さん・鈴木さんともに「 」「A」「A」のときに、田中さんと鈴木さんの名前が並んで表示される⇒○
となって、(2)が上手くいきません。

方法II：2つの式の間に「&」、1つ目の式の最後に「,""」(該当しない場合は空白を表示)を入力した場合。
=IF(AND(Sheet1!R4C15="",Sheet1!R4C16="A",Sheet1!R4C17="A"),Sheet1!R4C4,""&IF(AND(Sheet1!R5C15="",Sheet1!R5C16="A",Sheet1!R5C17="A"),Sheet1!R5C4,))

これだと、
(1)田中さんのみが「 」「A」「A」のとき、田中さんの名前が出る⇒○
(2)鈴木さんのみが「 」「A」「A」のとき、鈴木さんの名前が出る⇒○
(3)田中さん・鈴木さんさんともに「 」「A」「A」のときに、Bさんの名前が出ない。⇒×
となって、(3)が上手くいきません。

(1)・(2)・(3)を全てクリアする方法はありませんでしょうか・・・。
知識がないもので、もしかしたら式自体がもっといい方法があるのかもしれませんが・・・。

最終的には人数を増やして、
他のセルでは「A」「 」「 」や別のローマ字「 」「 」「B」の条件に当てはまる名前を表示させて、
自動でリスト化できるようにしたいと考えていますが、これは式の問題が解決すれば応用でいけると思います。

本当は別のセルに「１」と入れれば1日目（C15・16・17）を参照して、「2」と入れれば2日目（C18・19・20）を参照するように作りたかったのですが、これは方法がわからないので諦めています。

困っています。どなたかわかる方いらっしゃいましたら教えてください・・・。

回答No.2

ionatsu

作業用シートを作ると全て解決するように思います。
[sheet1]
これはあなたのメインシートと同じですが、参照したい表が途中にあってややこしいので、全て左上にくっつけて考えています。。（/はセルの区切り、:は行名とセルとの区別を示します）
Ａ:氏名/(3つ横に結合して)1/(3つ横に結合して)2
Ｂ:田中/ /A/A/ /A/A
Ｃ:鈴木/ /A/a/ / /
[sheet2]
作業用シートです。以下のように作ってみて下さい。（同様とします）

Ａ:=concatenate(A3,A4)/=concatenate(B3,B4)
Ｂ:1/2
Ｃ=IF(AND(COUNTIF(Sheet1!C2:D2,"A")=2,Sheet1!B2=""),Sheet1!A2,"")/=IF(AND(COUNTIF(Sheet1!F2:G2,"A")=2,Sheet1!E2=""),Sheet1!A2,"")
Ｄ:=IF(AND(COUNTIF(Sheet1!C3:D3,"A")=2,Sheet1!B3=""),Sheet1!A3,"")/=IF(AND(COUNTIF(Sheet1!F3:G3,"A")=2,Sheet1!E3=""),Sheet1!A3,"")

ちなみに、ここまで完成させて、以下のように見えていれば成功です。
Ａ:田中鈴木/田中
Ｂ:1/2
Ｃ:田中/田中
Ｄ:鈴木/

[sheet3]
あなたの言うところの、結果を表示させたい別シートです。以下のように入力して下さい。

Ａ:1
Ｂ:=INDEX(Sheet2!1:1,1,A1)

入力したらこのように結果がでます。

Ａ:1
Ｂ:田中鈴木

ちなみに、このA1セルが、あなたのいう「１と入れれば１日目を参照」です。2に変えればおわかりいただけると思います。以上です。

お礼

回答ありがとうございますm(_ _)m

(COUNTIF(Sheet1!C3:D3,"A")=2,Sheet1!B3="")この方がスマートですね。
日付を変える部分も指摘いただきありがとうございます！