部屋の予約表を氏名の性のみ参照

質問

いつもお世話になります
windows7 エクセル2010　です。

ご指導を仰ぎたいのは、

１　シート「１０月」の７の行のように氏名の「性」を参照したい。
　　※ただし　シート「入力」には記入されていません。
　　　　　　これはまだ関数が分からないので。
　　どういう数式にすればいいか教えていただけませんか。

９の行のように「記入」した時はラップしたとき赤の背景色

現在は下記のような関数が
=IF($A8="","",IF(SUMPRODUCT((入力!$D$2:$D$100=$A8)*(入力!$G$2:$G$100<=B$5)*
(入力!$H$2:$H$100>B$5))>0,IF(SUMPRODUCT((入力!$D$2:$D$100=$A8)*
(入力!$G$2:$G$100<=B$5)*(入力!$H$2:$H$100>B$5))>1,"●","○"),""))
条件付き書式で
○が青　●が赤　です

回答No.1

kagakusuki · ベストアンサー

まず、シート「１０月」のA7以下に、各部屋の部屋番号を入力して下さい。
　次に、シート「１０月」のA2セルに次の関数を入力して下さい。

=SUBSTITUTE(REPLACE(CELL("filename",A2),1,FIND("]",CELL("filename",A2)),),"月",)+0

　次に、シート「１０月」のB5セルに次の関数を入力して下さい。

=IF(ISNUMBER(($A$1&"/"&$A$2&"/"&COLUMNS($B:B))+0),($A$1&"/"&$A$2&"/"&COLUMNS($B:B))+0,"")

　次に、シート「１０月」のB5セルをコピーして、シート「１０月」のB6セルに貼り付けて下さい。
　次に、シート「１０月」のB5セルの書式設定の表示形式を、[ユーザー定義]の

d

として下さい。
　次に、シート「１０月」のB6セルの書式設定の表示形式を、[ユーザー定義]の

aaa

として下さい。
　次に、シート「１０月」のB７セルに次の関数を入力して下さい。

=IF(COUNTIFS(入力!$D:$D,$A7,入力!$G:$G,"<="&B$5,入力!$H:$H,">="&B$5,入力!$G:$G,">0",入力!$H:$H,"<9999/12/31")=1,INDEX(入力!$E:$E,SUMPRODUCT(ROW(入力!$E$1:INDEX(入力!$E:$E,MATCH(9E+99,入力!$A:$A)))*(入力!$D$1:INDEX(入力!$D:$D,MATCH(9E+99,入力!$A:$A))=$A7)*(入力!$G$1:INDEX(入力!$G:$G,MATCH(9E+99,入力!$A:$A))<=B$5)*(入力!$H$1:INDEX(入力!$H:$H,MATCH(9E+99,入力!$A:$A))>=B$5))),IF(COUNTIFS(入力!$D:$D,$A7,入力!$G:$G,"<="&B$5,入力!$H:$H,">="&B$5,入力!$G:$G,">0",入力!$H:$H,"<9999/12/31")>1,"重複",""))

　次に、以下の操作を行って下さい。
シート「１０月」のB７セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[指定の値を含むセルだけを書式設定]をクリック
　　↓
現れた「次のセルのみを書式設定」欄の左端の欄をクリック
　　↓
現れた選択肢の中にある「セルの値」をクリック
　　↓
「次のセルのみを書式設定」欄の左から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しくない]をクリック
　　↓
「次のセルのみを書式設定」欄の左から3番目の欄に

=""

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた「背景色」のサンプルの中にある緑色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に

=COUNTIFS(入力!$D:$D,$A7,入力!$G:$G,"<="&B$5,入力!$H:$H,">="&B$5,入力!$G:$G,">0",入力!$H:$H,"<9999/12/31")>1

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　次に、シート「１０月」のB5～B７の範囲をコピーして、シート「１０月」のC5～AF７の範囲に貼り付けて下さい。
　次に、シート「１０月」のB7～AF７の範囲をコピーして、同じ列の8行目以下に貼り付けて下さい。
　次に、「１０月」のA1セルに西暦年を表す正の整数値を入力して下さい。

　次に、シート「１０月」のコピーシートを１１枚複製して、そのシート名を各月名（1月～１２月）に変更して下さい。

　以上です。

お礼

今ご回答いただいたのをセットして確認させていただきました。
ご丁寧なご指導は大変ご苦労されたと思ってすごく感謝しています。
本当に重ね重ねありがとうございました。
すごく助かりました。