エクセルで作成するシフト表について質問です。

質問

現在エクセルでガントチャート式のシフト表を作っているのですが、休憩時間を2回設定できるようにしようとしてどのように計算式を組めばよいのか分からず困っております。

以下、現状組んでいる式です。

=IF(OR(勤務開始時間>time,勤務終了時間<=time),"",IF(OR(休憩開始時間>time,休憩終了時間<=time),1,2))

（例）
　　　　　　　　　　　　　　　（ガントチャート現状）
勤務開始時間 10:00 9 10 11 12 13 14 15 16 17 18 19 時間
勤務終了時間 18:00　　　 －－－休　休－ーーーーーーー
休憩開始時間 12:00
休憩終了時間 13:00
　　　　　　　　　　　　　　　（ガントチャート理想）
休憩開始時間 15:00
休憩終了時間 16:00 9 10 11 12 13 14 15 16 17 18 19 時間
　　　　　　　　　　　　　　　　 －－－休　休ーーー休休ーーー

どのようにすれば2回目の休憩時間を組み込めるでしょうか？
皆様の知恵をご教授ください。
宜しくお願い致します。

回答No.2

OrangeCup150

詳細は分かりませんが、以下のような感じでやってみてください。
ガントチャートの時刻は15分刻み（15分間隔）です。
多分、これを参考にすればいい感じにできると思います。
長いですから先に一読してから試してみてください。

【見出し】
A5=名前
B5=勤務開始時刻
C5=勤務終了時刻
D5=休憩１開始時刻
E5=休憩１終了時刻
F5=休憩２開始時刻
G5=休憩２終了時刻

【レコード】
A6=鈴木太郎
B6=9:00
C6=18:00
D6=12:00
E6=13:00
F6=15:00
G6=15:15

【時刻見出し】
H3=時刻目盛り
H4=7:00
H5==MINUTE(H4)

I4=7:15

以降、H4【=7:00】I4【=7:15】、J4【=7:30】の様に15分間隔で時刻目盛りを21時ぐらいまで入力する。
【注意】オートフィルで行うと鈴木太郎は12時00分から13時15分まで休憩、15時の休憩は15時15分から15時30分の休憩、18時15分まで働くと表示される。
手入力でAF4【13:00】AN4【15:00】AO4【15:15】AZ4【18:00】と時刻を入力すると修正されます。
つまり、オートフィルの時刻と手入力時刻は内部の時刻値がごくわずかに異なるため正しく処理するには手入力が必要です。（簡潔に言えばエクセルの不具合があるので、オートフィルを使わないで手入力してください。あっ！、これは私の使っているエクセルが古いせいかもしれません。新しいエクセルなら大丈夫かもしれません。）

【勤怠予定の表示】
H6==IF(OR(H$4<$B6,H$4>=$C6),"外",IF(OR(AND(H$4>=$D6,H$4<$E6),AND(H$4>=$F6,H$4<$G6)),"休","働"))
【補足】意味的にはこうなります
外：勤務時間外　休：休憩時間　働：労働時間
=IF(OR(時刻目盛り<勤務開始時刻,時刻目盛り>=勤務終了時刻),"外",IF(OR(AND(時刻目盛り>=休憩１開始時刻,時刻目盛り<休憩１終了時刻),AND(休憩２開始時刻<=時刻目盛り,時刻目盛り<休憩２終了時刻)),"休","働"))
【補足２】休憩時間３，４を追加するには以下のようにしてください。
IF(OR(H$4<$B6,H$4>=$C6),"外",IF(OR(AND(休憩時間１),AND(休憩時間２),AND(休憩時間３),AND(休憩時間４)),"休","働"))

【オートフィル処理】
H5【=MINUTE(H4)】をBN5【=MINUTE(BN4)】までオートフィルで処理する。
同様にH6【IF式】をBN6【IF式】までオートフィルで処理する。

【書式設定】
H4は【セルの書式設定】の【表示形式】の【ユーザ定義】で【h】を指定（時のみ表示に指定）
H4とH5は左揃え（左詰め）に設定する。

あと、細かいですが、「時刻」と「時間」を区別して使うようにしてください。
時刻は3時15分などの時点を指すときに用いる。時間は時刻と時刻の間隔を指すときに用いる。たとえば、勤務時間は勤務開始時刻と勤務終了時刻の間隔です。