記入のデーターを別のシートに

質問

いつもお世話になります。
WIN7 EXCELL2010 です。
「記入」のデーターを日毎のシートに振り分けたいです。

参照図で説明させていただきます。
ご指導賜りたいのは　2点あって
その1
シート「記入」から　各シートに振り分ける数式をおねがいいたします。
その2
シート「記入」から　20140714　20140715　20140721　にデーターを振り分けるのですが、
2の行はどなたとも重複していません。
3と4の行は同じ部屋で「入所日　と　退所日」　7/20　が重複しています。
退所日の「田中」はそのまま「102」に　「伊藤」は一旦「待機」に入れ、「2010721」のシートでは102に戻したい。
特にその2については可能でしょうか。
何かいい方法が　その1　その2　あればご指導いただけませんでしょうか。

　　　　　　　　　　※20140716～20140720　は意図的に省いています。

日毎のシートには、
A1 文字で入力
E1 A1の文字を　“yyyy/mm/dd”　に
　　=DATE(INT(A1/10000),MOD(INT(A1/100),100),MOD(A1,100))
F2 =IF(OR(B1<>B2,B2=""),"",IF(D2>MAX(INDEX(D:D,MATCH(B2,B:B,0)):INDEX(E:E,MATCH(B2,B:B,0)+COUNTIF(B$1:B1,B2)-1)),"","重複or待機"))
マクロ　A1の文字をシート名に
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target.Range("A1").Value
End Sub

誠に恐れ入りますが何卒よろしくお願いいたします。

回答No.2

MackyNo1 · ベストアンサー

No1の回答の数式に参照セルの誤りがありましたので以下のように訂正してください。

B3セル
=INDEX(記入!C:C,MIN(INDEX(((記入!$B$2:$B$20<>$A3)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),)))&""

C3セルに以下の式を入力し右方向に1つ、下方向にオートフィルしてください(セルの表示形式を日助にする）。

=IF(B3="","",INDEX(記入!D:D,MIN(INDEX(((記入!$B$2:$B$20<>$A3)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),))))

待機のB9セルには以下の式を入力して下方向にオートフィルしてください（F列に「重複or待機」と入力されている日付範囲の行を取得しています）

=INDEX(記入!C:C,SMALL(INDEX(((記入!$F$2:$F$20<>"重複or待機")+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),),ROW(1:1)))&""

日付欄のC9セルは以下の式を入力し右方向に1つ、下方向にオートフィルしてください。

=IF(B9="","",INDEX(記入!D:D,SMALL(INDEX(((記入!$F$2:$F$20<>"重複or待機")+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),),ROW(1:1))))

お礼

早いご指導を受けながらお礼が遅くなり誠に申し訳ありません。
試させていただきうまくできました。
感謝　感謝です。

ありがとうございました。

回答No.1

MackyNo1

いまひとつシートのレイアウトがわからないところがありますが、単純にE1セルに集計したい日付が入力されていて、A2セル以下に部屋番号が入力されているなら、該当する日付で部屋番号に対応する一番上の行のデータを抽出したいなら以下の式を入力して下方向にオートフィルします。

B2セル(名前）
=INDEX(記入!C:C,MIN(INDEX(((記入!$B$2:$B$20<>$A2)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),)))&""

C2セルには以下の式を入力し右方向および下方向にオートフィルします。
=IF(B9="","",INDEX(記入!D:D,MIN(INDEX(((記入!$B$2:$B$20<>$A2)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),))))

ただし上記の数式は、表示データ数やデータ範囲が多くなるとシートの動きが重くなるので、E1セルに日付を入力するシートを1枚用意して、E1セルの値を変更する（リストで日付をドロップダウンリストから選択させる）ようにするとよいと思います。

＃今回は回答しませんでしたが、待機の部分には日付範囲に入ったデータは（上に表示しているものを除いて）すべて表示するということでよいのでしょうか？

補足

早速のご回答有難うございます。
早速試めさせていただきました。

御指導の数式を入れてみましたところ「氏名」私なりの変更でうまくいきました。　他方「入所日　退所日」うまく表示されません。

その1
B2セル(名前）
=INDEX(記入!C:C,MIN(INDEX(((記入!$B$2:$B$20<>$A2)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),)))&""
下記のように変更させていただい処、「氏名」は表示されました。
B3セル(名前）
=INDEX(記入!C:C,MIN(INDEX(((記入!$B$2:$B$20<>$A3)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),)))&""

その2
C2セルには以下の式を入力し右方向および下方向にオートフィルします。
=IF(B9="","",INDEX(記入!D:D,MIN(INDEX(((記入!$B$2:$B$20<>$A2)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),))))

その1と同様に変更して試しましたが反映されませんでした。
C3セルには以下の式を入力し右方向および下方向にオートフィルします。
=IF(B9="","",INDEX(記入!D:D,MIN(INDEX(((記入!$B$2:$B$20<>$A3)+(記入!$D$2:$D$20>$E$1)+(記入!$E$2:$E$20<$E$1))*10000+ROW($2:$20),))))

ご質問の
＃今回は回答しませんでしたが、待機の部分には日付範囲に入ったデータは（上に表示しているものを除いて）すべて表示するということでよいのでしょうか？

3と4の行は同じ部屋で「入所日　と　退所日」　7/20　が重複しています。
退所日の「田中」はそのまま「102」に　「伊藤」は一旦「待機」に入れ、「2010721」のシートでは102に戻したい。
特に　退所する人と入所する人が時間は違っても同日に重なり少なからずあります。
誠に恐れ入りますが再度ご指導いただけませんでしょうか。
よろしくお願いいたします。