複数の宿泊日の時

質問

いつもお世話になります。
WINDOWS7 EXCELL2010 です。
データーを元に分かり易くするために参照図の「空室状況」の画像のような表現を考えています。

現在はシート「予約一覧」のA列(チェックイン)に日付を入力し1日のみの宿泊は下記の数式で可能です。
ただ複数の宿泊の時のアイデァが分からず困りました。
この複数の宿泊を「空室状況」に対応するにはどうすればいいか御指導いただけませんでしょうか。
宜しくお願いいたします。

参考に
1 シート　予約一覧
A 4の行の青は　=$A4=検索日　シート　検索日=見取り図F1
B 作業列1　F4 =A4&G4 作業列2　G4 =C4
2 シート　見取り図
A 条件付書式 =COUNTIFS(予約一覧の部屋番号,B5,予約一覧の宿泊日,検索日)>0
B　作業列3　I5 =F1&B5
C 顧客名　=IF(ISNA(VLOOKUP(I5,予約一覧!$F$4:$G$17,2,FALSE)),"",VLOOKUP(I5,予約一覧!$F$4:$G$17,2,FALSE))

回答No.3

keithin · ベストアンサー

回答の数式からは、ＩＮしか記入されてない場合の判定部分を除去するだけです。
Ｂ６：
=IFERROR(VLOOKUP(SUMIFS(予約一覧!$A:$A,予約一覧!$E:$E,B5,予約一覧!$B:$B,"<="&$F$1,予約一覧!$C:$C,">"&$F$1),予約一覧!$A:$D,4),"")

まぁ、ヒトが考えた数式（とかマクロとか）を分析したり理解するってのは、エクセルに慣れた人でもやりたく無いウンザリな作業です。
から、教わった数式も自分で考えたり工夫とか無し全部人任せになるのは、しょーがありませんね。
＃そういうのを自分の「仕事」に使って大丈夫なのかってのは、回答者は敢えて触れない部分なんですけどね。

お礼

再指導ありがとうございます。
お気持ち察します。
申し訳ありません。
できるだけ御迷惑掛けないように気を付けます。

回答No.2

tom04

こんにちは！

1日だけの宿泊でも、↓の画像のようにチェックアウトを入力してはどうでしょうか？

そうであれば画像ではSheet2のB6セルに
=INDEX(予約一覧!$C$1:$C$1000,MAX(IF((予約一覧!$A$1:$A$1000<=$C$1)*(予約一覧!$B$1:$B$1000>=$C$1)*(予約一覧!$D$1:$D$1000=B5),ROW($A$1:$A$1000))))&""
これは配列数式になりますので、Ctrl+Shift＋Enterで確定！

B6セルをF6セルまでフィルハンドルでコピー！　→　B6～F6セルが範囲指定されている状態で
セルの四隅のマウスポインタが上下左右の小さな矢印になるところで
Ctrlキーを押しながら2行下へドラッグ　→　同様に2行下へ・・・
という操作を行うと画像のような感じになります。

※　普通に考えればチャックアウトとチェックインが同じ日の場合もあるかと思いますが、
そこら辺は不等号で調整してみてください。m(_ _)m

お礼

御指導ありがとうございました。
何時乍ら済みません。
勝手に変更しまして。

変更させていただいた数式です。
=INDEX(予約一覧!$C$1:$C$1000,MAX(IF((予約一覧!$A$1:$A$1000<$C$1)*(予約一覧!$B$1:$B$1000>$C$1)*(予約一覧!$D$1:$D$1000=B5),ROW($A$1:$A$1000))))&""

御指導いただいた下記の数式を上記に変更させていただきました。
=INDEX(予約一覧!$C$1:$C$1000,MAX(IF((予約一覧!$A$1:$A$1000<=$C$1)*(予約一覧!$B$1:$B$1000>=$C$1)*(予約一覧!$D$1:$D$1000=B5),ROW($A$1:$A$1000))))&""

補足

御指導いただき誠に有難うございます。

宿泊と言う観点からチェックアウトの日でなく宿泊日、と言うことはシート「見取り図」で空室状況はチェックアウトの前日で表したいのですがどうすればよろしいでしょうか。
恐れ入りますが再指導をお願いできないでしょうか。

回答No.1

keithin

添付図：
予約一覧のＡ列には連番を列記する
Ｂ列以降に予約一覧を記入する

見取り図のＢ６：
=IFERROR(VLOOKUP(SUMIFS(予約一覧!$A:$A,予約一覧!$E:$E,B5,予約一覧!$B:$B,"<="&$F$1,予約一覧!$C:$C,">"&$F$1)+SUMIFS(予約一覧!$A:$A,予約一覧!$E:$E,B5,予約一覧!$B:$B,$F$1,予約一覧!$C:$C,""),予約一覧!$A:$D,4),"")

＃ダブルブッキングになっていないことは予約一覧側で検査しておく事。

＃そもそも。
連泊の時だけＯＵＴを記載するんじゃなく、１泊のお客様もＯＵＴの日付として翌日の日付を記入しておけば、数式は半分の長さになります。

補足

早速ご指導を誠に有難うございます。
御指導を反映させていただきました。

その結果上手く思い通りのものにでき感謝感激です。
※　チェックアウトの日付も入力する予定でした。
　　何よりもどうしていいかわからず入力の列だけ作りました。

下記のチェックアウトを入力すれば数式は半分になると言うことですが再指導いただけたらありがたいのですが御願い出来ないでしょうか。

＃そもそも。
連泊の時だけＯＵＴを記載するんじゃなく、１泊のお客様もＯＵＴの日付として翌日の日付を記入しておけば、数式は半分の長さになります。