2つ条件である文字の値を参照したい ～2

質問

いつもお世話になります
Excel 2010 win7です

入室　退出の管理ですが下記のアドレスで
質問させていますが再度投稿しました。
http://okwave.jp/qa/q7255506.html

例
1　リストによる選択
B4:B20(野原/高原)
E5:E20(入/退)

2　手動入力
C4:D22(氏名)
F4:F22(日付　例　02/01)

例:
　　　　　 　入室日　退出日(F列)
　N101 田中　勝　02/01　　02/06
　N101 北　和夫　02/06　　02/10

シート(フォマットは同じ)数は各月の枚数です

　ご指導を仰ぎたいのは(図参照)、関数での式を
図は2月6日を表示しています

例えば
1 入所先居室が空室の時
B4(ユニット/高原)C4(居室/N101)D4(氏名/北　和夫)E4(入退/入)F4(退所日/02/10)を入力すれば、
本来はそのまま下記に入力される数式
B25(居室/N101)を参照しながら C25(入退別/入)D25(氏名/北　和夫)

2 入所先居室が入室中の時
　※この場合は　「北　和夫」
B4(ユニット/高原)C4(居室/N101)D4(氏名) E4(入)を入力すれば、
上記の1でいいのですが

※当日まで(この場合で言うと田中　勝/0206退)入室者が居るので「北　和夫」一旦
C35 D35に待機させたい
　翌日の2月7日には待機からC25/D25に入れたい

誠に恐れ入りますがどんな数式を適用する必要なのか分からず
丸投げ的で申し訳ありませんがなんとかお力添えいただけないでしょうか

回答No.1

CC_T

関数一発では処理できないと思います。作業用セルを設定してみましょう。

(1)作業用シート
・行に人、列に部屋を割り振った一覧形式とする
　→縦列で見ると、その部屋を使う（使った）人が分かるようになる。
　表内の各セルには関数today()とvlookup()を使用して、
　その部屋の利用者であり、かつ
　・退室日が当日以前なら「退」、
　・入室日が未来日付なら「待」
　・入室日が当日以前かつ退室日が未来日付なら「入」
　を表示するようにIF文の判定式を入力しておく。
　こんな↓感じ
　 　　　部屋１　部屋２　部屋３　・・・
　人１　　入
　人２　　　　　　　待
　人３　　　　　　　入
　人４　　　　　　　退
　人５　　退
　　・
　　・

(2)表示用シート
　居室欄／待機欄には、vlookupなりで作業用シートの該当部屋の列から
　「入」と「待」の行を検索し、対象者や日付を表示する。
　こんな↓感じ
　　　　　　現入室者　　　　　　　　　　利用予定待機者
　部屋１　人１　〇/〇～〇/△
　部屋２　人３　〇/□～〇/◇　　　　人２　☆/▽～☆/◎
　部屋３　なし　
　　・
　　・
といったところでしょうかね・・・。

ただ、ダブルブッキングの判定ができないとか、二人以上の待機者が要るケースに
対応できないなど問題点も幾つか考えれらますので、処理として適切とは言い難いかと思います・・・。

～～～
いっそのこと処理をマクロ化した方が間違いも無くて良いと思います。
マクロはちょっと・・・と敷居高に感じられるかもしれませんが、別に噛みつかれる訳でもなし。
恐れるほどとっつき難いものでもないですから、ぼちぼち練習問題などで経験積んでみてはどうでしょうか。。
一応、基本構想だけ書いておきます。

　(1)現状表示・予約入力用シート
　(2)宿帳シート
　(3)部屋割シート
の３シートに分けて処理するのが入退室の履歴も残せて良いかと。

１）表示シート
　・各部屋を縦軸にとり、入室状況と待機者ID＆氏名などを表示する欄を設ける。
　・フォームのボタンなどで「情報更新」ボタンを作成して、下記内容のマクロを登録
　　部屋割りシートの情報をチェック＆更新する
　　　・現入室者について、当日以前の退室予定であれば、消去
　　　・待機者トップの入室日が当日以降の日付ならば現入室者欄に移動し、当該者は待機者リストから消去
　　　・待機者リストを入室予定日順に並び替え
　・氏名、部屋、入室予定、退室予定を登録するための欄を作る。
　　フォームのボタンなどで「ブッキング確認／登録」ボタンを作成して、下記内容のマクロを登録。
　　・入力欄のデータと部屋割りシートのデータに照らし合わせてブッキングがないかチェックする。
　　　（当該の部屋の現入室者＆待機者の入退室日と入力欄の値を比較する。）
　　・ブッキングなければ、ID自動採番して（ID最終行＋１）宿帳シートに登録するとともに、
　　　部屋割りシートの待機リストに入力データ内容を追加する。　　　
　　・「情報更新」マクロの実行
　・「予約内容呼び出し」「変更登録」ボタンを作成し、下記機能のマクロを作成して登録
　　・「予約内容呼び出し」は、IDを入力して対象データを表示する
　　・「変更登録」は入力内容でブッキングが無いか確認して、問題なければ
　　　部屋割りシートの旧データを削除し。待機リストに入力データ内容を追加する。　　　
　　・部屋割りシートの待機リストを入室予定日順に並び替え
　　・「情報更新」マクロの実行
　各処理はユーザーフォーム化したりイベントマクロ登録すると「それっぽく」なってきますね。
２）宿帳シート
　予約/入退室の履歴を残すためのシート。ID、氏名、部屋、入室予定、退室予定、のデータの一覧
３）部屋割りシート
　各部屋別の現入室者ID、退室予定、待機者ID等を管理するためのシート。