エクセル２００７で時間帯勤務状況の分かるシフトを作っています（１日１シ

質問

エクセル２００７で時間帯勤務状況の分かるシフトを作っています（１日１シート）
現在5分単位で稼働人数の分かる以下のようなシフトを作っています。
A２～２０　氏名
B２～２０　出勤時間
C２～２０　退勤時間
E１～FE１　8:00~21:00までの５分刻みの時刻
E２～FE２　=IF(AND($B2<=E$1,$C2>E$1),"■","")　の関数を利用し勤務時間に■を表示させる

この表に休憩時間を追加したいと考えております
休憩時間は１０パターンあり
パターン１：10:00~10:15,12:00~13:00,15:00~15:15
パターン２：10:15~10:30,12:30~13:30,15:15~15:30
このように、一日の内に３回の休憩時間があります。
この休憩時間を上記のシフト内のDにプルダウンで選択させ
該当する時間に”□”を表示させることは可能でしょうか？
また、不可能な場合他に良い方法はありますでしょうか
時間別に稼働人数を出したいので、塗りつぶし等関数でカウントができないものは考えておりません

お手数ですが、お力をお貸しください。

回答No.2

KURUMITO · ベストアンサー

時間の表示は同じでもシリアル数では合わない場合もありますので、なかなか難しいですね。
次のようにして、時間と分をプラスして分で統一して比較することでやっと解決できました。
表はご提案のとおりとしてパターンについてはその番号をＡ２５から下行に１から２０までＡ４４セルまでに入力し、例えば１のパターンではＡ２５セルに１、Ｂ２５セルに10:00,Ｃ２５セルに10:15,Ｄ２５セルに12:00,Ｅ２５セルに13:00,Ｆ２５セルに15:00,Ｇ２５セルに15:15のように入力するとします。
そこで、Ｄ２セルから下方にはパターンの番号、例えばＤ２セルに１と入力します。
Ｅ２セルには次の式を入力してＦＥ２セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNT($B2:$D2)<>3,"",IF(OR(AND(HOUR(E$1)*60+MINUTE(E$1)>=HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),2))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),2)),HOUR(E$1)*60+MINUTE(E$1)<HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),3))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),3))), AND(HOUR(E$1)*60+MINUTE(E$1)>=HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),4))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),4)),HOUR(E$1)*60+MINUTE(E$1)<HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),5))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),5))),AND(HOUR(E$1)*60+MINUTE(E$1)>=HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),6))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),6)),HOUR(E$1)*60+MINUTE(E$1)<HOUR(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),7))*60+MINUTE(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),7)))),"□",IF(AND($B2<=E$1,$C2>E$1),"■","")))

回答No.3

KURUMITO

回答Ｎｏ2です。ネットのつながりが悪く別の答えを用意していましたが提示できませんでした。
次の式をＥ2セルいは入力するのがよいでしょう。

=IF(COUNT($B2:$D2)<>3,"",IF(OR(AND(ROUND(E$1,5)>=ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),2),5),ROUND(E$1,5)<ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),3),5)),AND(ROUND(E$1,5)>=ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),4),5),ROUND(E$1,5)<ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),5),5)),AND(ROUND(E$1,5)>=ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),6),5),ROUND(E$1,5)<ROUND(INDEX($A$25:$G$45,MATCH($D2,$A$25:$A$45,0),7),5)) ),"□",IF(AND(ROUND($B2,5)<=ROUND(E$1,6),ROUND($C2,5)>ROUND(E$1,5)),"■","")))

回答No.1

MackyNo1

現在のシートをそのまま生かすなら、E2セルに以下の式を入力して右方向および下方向にオートフィルしてください（パターン２の場合にD列のセルに「２」と入力した場合）。

=IF(AND($B2<=E$1,$C2>E$1),IF(OR(AND(E$1<"10:15"+"0:15"*($D2=2),E$1>="10:00"+"0:15"*($D2=2)),AND(E$1<"13:00"+"0:15"*($D2=2),E$1>="12:00"+"0:30"*($D2=2)),AND(E$1<"15:15"+"0:15"*($D2=2),E$1>="15:00"+"0:15"*($D2=2))),"□","■"),"")