エクセル（関数） ド素人にお助け願います！

質問

エクセルド素人です。質問の意味も的確に伝えたれるかわかりませんが、教えて下さい。

アルバイトの方の出勤簿で、勤務時間のうち早朝・通常・遅出・深夜の時間数を計算できる公式が知りたいです。

早朝（5：00～8：00）　通常（８：00～18：00）　遅出（18：00～22：00）　深夜（22：00～翌日5：00）に設定したいです。

例(1)　7：00～9：30の場合は、早朝［１時間］　通常〔1時間半］
例(2)　16：00～20：00の場合は、通常［2時間］　遅出［2時間］
例(3)　19：00～25：30の場合は、遅出［3時間］　深夜［３時間半］
例(4)　21：00～翌日9：00の場合は、遅出［1時間］　深夜［7時間］　早朝［3時間］　通常［1時間］

必ず同じ時間に出勤するということもなく、日をまたいでしまうこともあるので、とてもややこしく、いろんな式を試してみましたが上手く行きません。ド素人の質問でも、私のやりたいことがわかって下さる方、どうかお助けお願いします。

　　A（日付）　B（曜日）　C（出勤時間）　D（終了時間）　E（早朝）　F（通常）　G（遅出）　H（深夜）
１　　６日　　　　　水　　　　　7：00　　　　　　　9：30　　　　　1：00　　　1：30
２　　７日　　　　　木　　　　 16：00　　　　　　20：00　　　　　　　　　　　2：00　　　　2：00
３　　８日　　　　　金　　　　 19：00　　　　　　25：30　　　　　　　　　　　　　　　　　　3：00　　　　3：30
４　　９日　　　　　土　　　　 21：00　　　　　　　9：00　　　　　3：00　　　1：00　　　 1：00　　　　 7：00

C（出勤時間）とD(終了時間）を入力すると、E（早朝）・F（通常）・G（遅出）・H（深夜）を自動で計算することは可能でしょうか？

回答No.5

chie65536（@chie65535）

追記。

８日(金)の例のように、深夜00:00を跨いでの勤務があるので、こういう場合は工夫が必要です。

・方法１：勤務時間を２つに別ける。

勤務時間を19:00～24:00、00:00～01:30(25:00)の２つの範囲に分割して計算します。

すると、時間帯区分は、0：00～24：00の区間だけ考えれば良いので

早朝（5：00～8：00）
通常（８：00～18：00）
遅出（18：00～22：00）
深夜１（22：00～24：00）
深夜２（0：00～5：00）

の５つだけ考えれば良い事になります。

早朝＝早朝（5：00～8：00）と19:00～24:00が重複する時間数＋早朝（5：00～8：00）と00:00～01:30が重複する時間数

通常＝通常（８：00～18：00）と19:00～24:00が重複する時間数＋通常（８：00～18：00）と00:00～01:30が重複する時間数

遅出＝遅出（18：00～22：00）と19:00～24:00が重複する時間数＋遅出（18：00～22：00）と00:00～01:30が重複する時間数

深夜＝深夜１（22：00～24：00）と19:00～24:00が重複する時間数＋深夜１（22：00～24：00）と00:00～01:30が重複する時間数＋深夜２（0：00～5：00）と19:00～24:00が重複する時間数＋深夜２（0：00～5：00）と00:00～01:30が重複する時間数

・方法２：時間帯を２種類用意する。

勤務終了時間が24：00を超えるので、時間帯区分を「２４時を超えてないもの」と「２４時を超えたもの」と２種類用意します。

すると、時間帯区分は

深夜１（0：00～5：00）
早朝１（5：00～8：00）
通常１（８：00～18：00）
遅出１（18：00～22：00）
深夜２（22：00～29：00）
早朝２（29：00～36：00）
通常２（36：00～42：00）
遅出２（42：00～46：00）
深夜３（46：00～48：00）

の９種類になります。

早朝＝早朝１（5：00～8：00）と19:00～25:30が重複する時間数＋早朝２（29：00～36：00）と19:00～25:30が重複する時間数

通常＝通常１（８：00～18：00）と19:00～25:30が重複する時間数＋通常２（36：00～42：00）と19:00～25:30が重複する時間数

遅出＝遅出１（18：00～22：00）と19:00～25:30が重複する時間数＋遅出２（42：00～46：00）と19:00～25:30が重複する時間数

深夜＝深夜１（0：00～5：00）と19:00～25:30が重複する時間数＋深夜２（22：00～29：00）と19:00～25:30が重複する時間数＋深夜３（46：00～48：00）と19:00～25:30が重複する時間数

---

方法１だと退勤時刻が24：00より大きい場合、勤務時間を２つに分割する処理が入るので、式が複雑になります。

方法２だと、退勤時刻が「25：30」とかになっていても、そのまま計算できるので、式が簡単になります。

なので、方法２の方が楽に書ける筈です。

＞IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか？！

基本は

=IF(OR(AとDの関係式,BとCの関係式),0,IF(BとDの関係式,B,D)-IF(AとCの関係式,A,C))+IF(OR(AとD’の関係式,BとC’の関係式),0,IF(BとD’の関係式,B,D’)-IF(AとC’の関係式,A,C’))

です。

A、Bには「出勤時刻、退勤時刻」が入ったセルを書きます。

C、Dには、深夜なら「0：00、5：00」など、１つ目の時間帯区分を書きます。

C'、D'には、深夜なら「22：00、29：00」など、２つ目の時間帯区分を書きます（深夜は３つ目もあるので注意）

「関係式」は、前の回答にある通りの「大小比較をする式」を書きます。

お礼

chie65535様
　昨日からご丁寧な回答ありがとうございます。24：00をまたぐと、とてもややこしくなるので、0：00で一旦区切って考えれば良いというヒントは、大変役に立ちました。基本の式も教えて頂き、ありがとうございました。参考にさせてもらいながら、今から格闘してみたいと思います。

回答No.4

chie65536（@chie65535）

その２です。

回答No.3

chie65536（@chie65535）

添付図が縮小されて字が読みづらいので２つに分割しました。

その１です。

回答No.2

chie65536（@chie65535）

A～B間、C～D間の２つの「区間」があった時、その２つの区間の「重なり具合」は、添付図のようになります。

この時、必ず「A＜B」「C＜D」とします。

AとC、AとD、BとC、BとDのそれぞれの関係と、重なり具合を色分けすると「どの関係を調べれば、どう重なっているかが判る」ようになります。

A～B間を「出勤から退勤まで」、C～D間を「早朝、通常、遅出、深夜１、深夜２」とした時、AとC、AとD、BとC、BとDのそれぞれの関係を調べると「それぞれの重なり具合」が判ります。

なお「深夜」が０時（２４時）を跨ぐと面倒なので、

深夜１＝22:00～24:00
深夜２＝00:00～05:00

として別々に考え、最後に「足し算」します。

添付図で「より後」と「等しい」が同じ色になっているので、その部分は「前でない」と考えます。

「より前」と「等しい」が同じ色になっている部分も「後でない」と考えます。

重なりが「AからD」になっている部分の「重なってる時間数」は「D-A」の引き算で計算できます。

その他の重なりも、同様の引き算で計算できます。

こう見ると「上のなし」「AからDまで」「AからBまで」「CからDまで」「CからBまで」「下のなし」の６種類あると判ります。

６種類ですが「なし」「から」「まで」を別々に考えれば

「なし」または「なし以外」
「Aから」または「Cから」
「Dまで」または「Bまで」

の３つを判断すれば良いと判ります。

『「なし」または「なし以外」』は「AとDの関係」と「BとCの関係」で判断できます。

『「Aから」または「Cから」』は「AとCの関係」で判断できます。

『「Dまで」または「Bまで」』は「BとDの関係」で判断できます。

あとは「上記の通りにIF関数を書くだけ」です。

お礼

chie65535様 ご丁寧に説明して下さったり、画像を添付して頂き、ありがとうございます。 今は携帯からしか見られず、画像が見られなかったので、明日会社のパソコンで確認させてもらいます。 IF関数の入力の仕方も教えていただけると嬉しいのですが、無理でしょうか？！

回答No.1

aokii

一つのセルで色々な条件を付けて計算しようとすると、長い数式になり修正が非常に大変になります。
なるべく平易な分かりやすい数式となるように、各時間帯別に、勤務開始時刻、勤務終了時刻、休憩開始時刻、休憩終了時刻を区切り、通常勤務時間、通常残業時間、深夜残業時間、合計勤務時間を計算しています。

お礼

aokii様　早々の回答ありがとうございます。パソコンに不慣れなもので、アドレスにアクセスさせて頂きましたが、ファイルを開くことが出来ず、確認することが出来ませんでした。申し訳ありません。