Excel2000の関数式をご教示下さい。

質問

初心者です。よろしくお願いします。
具体的に、以下のような結果のデータがあります。

A B C D E F G
1 日付 曜日 休日 出勤 退勤 時間1 時間2
2 2/2 月 1 08:00 17:00 8.00 0.00
3 2/3 火 1 08:00 18:45 8.00 1.75
4 2/4 水 0 00:00 00:00 0.00 0.00
5 2/5 木 1 10:00 19:00 8.00 0.00
6 2/6 金 1 10:30 20:00 8.00 0.50
7 2/7 土 1 10:00 15:00 4.00 0.00
.
.
以下の様な条件でF列とG列に労働時間を求める関数をご教示下さい。(VBAでもよいですが、不慣れです)

・休日表示(C列)　0＝休日,1＝稼働日 数値表示
・出勤時間(D列) 時刻表示
・退勤時間(E列) 時刻表示　
・時間1(F列)　1日の労働時間が8hrに達するまでは、ここに時間を求める。(例：1時間45分の場合は1.75と数値表示)
・時間2(F列)　1日の労働時間が8hrを超えた場合は、ここに時間を求める。(例：1時間30分の場合は1.50と数値表示)
・12:00～13:00までは休憩の為、カウントしない

以上よろしくお願い致します。

回答No.5

maruru01 · ベストアンサー

No.1です。

No.3の方が指摘されている、24:00以降も対応した数式です。
F2（定時）に、

=ROUND(MIN(E2+(D2>E2)-D2-(D2<="12:00"*1)*(E2+(D2>E2)>="12:45"*1)*"0:45","8:00")*24,2)

G2（総残業時間）に、

=ROUND(MAX(E2+(D2>E2)-D2-(D2<="12:00"*1)*(E2+(D2>E2)>="12:45"*1)*"0:45"-"8:00",0)*24,2)

H2（早出）に、

=ROUND(MIN(MIN(E2+(D2>E2),"8:00")-MIN(D2,"8:00"),G2*"1:00")*24,2)

I2（深夜）に、

=ROUND(MIN(MAX(E2+(D2>E2),"22:00")-MAX(D2,"22:00"),G2*"1:00")*24,2)

です。
セルの表示形式は全て、数値などで。
意図と違う場合は補足して下さい。

お礼

重ね重ねのご教示感謝致します。
今、いろいろとやっているのですけれど、出来そうな匂いがして、とてもウキウキしているところです。
（何分にも初心者なので、なかなか理解出来なくて．．）

補足

質問させて下さい。
G列に入力したかったのは、総残業時間ではなく、早出と深夜を抜いた通常の残業時間でした。
これは、行を一行追加し、G列を総残業時間、H列を残業時間、I列を早出時間、J列を深夜時間として、H=G-(I+J)としましたが、よろしいのでしょうか？
もう少しスマートなやり方なんぞあるのでしょうか？

もう1つ、
8:00に出勤して12:45に退勤すると、労働時間が4.0時間になりますが、(昼はカウントされないのでこれでOKなのですが)
8:00に出勤して12:30に帰ると、労働時間が4.5時間になってしまいます。、(昼の30分がカウントされているようなのです）
これはどこがいけないのでしょうか？
長々と申し訳ありません。

回答No.7

maruru01

>これは、行を一行追加し、G列を総残業時間、H列を残業時間、I列を早出時間、
>J列を深夜時間として、H=G-(I+J)としましたが、よろしいのでしょうか？
いいと思います。
ただし、数式中のセルの参照先に注意して下さい。
（数式入力後に1列挿入すると、後ろの列の参照がずれます。）

>これはどこがいけないのでしょうか？
いけないとかではなく、考え方の問題です。
私としては、出勤・退勤時間が休憩時間中になることはないという考え方で数式を作成しました。
つまり、12:30に退勤しても、それは12:00退勤として扱う（12:00と入力する）べきだと判断しました。
あえて、休憩時間中に退勤時間を指定する必要があるのでしょうか？

お礼

なるほど、おっしゃる通りですね。
参考になります。
ありがとうございました。

補足

皆様、本当にお世話になりました。
私も、勉強して早く皆様のレベルになるように努力します。
又、別の場で初歩的な質問をすると思いますが、その節も何卒よろしくお願い致します。

回答No.6

mshr1962

#2,#4のmshr1962です。24時間以降の対応ですが
時間の表示書式を[h]:mmにすれば今の式のままでも可能です。
例　翌日午前3時⇒h:mmの場合 1 3:00⇒[h]:mmの場合 27:00
の様な対応になります。

お礼

何度も本当にありがとうございます。
大変参考になりました。
早く活かせるように、技術を身に付けたいと思います。

回答No.4

mshr1962

#2のmshr1962です。
>この場合は式内で「13:00」の箇所を「12:45」と直すだけでよろしいのでしょうか？
私の式の場合は
13/24を12.75/24に変更になります。(45分は3/4時間)
>8:00以前の作業時間は早出出勤としてH列に表示させたいです。
H2=IF(G2<>0,IF(D2<1/8,1/8-D2,0)*24,0)
>22:00以降の作業時間は深夜出勤としてI列に表示させたいです。
I2=IF(G2<>0,IF(E2>5/6,E2-5/6,0)*24,0)

但し、1日8時間までは通常通り定時間扱いがあるため、
残業時間のG列の扱いが微妙です。
早出出勤・深夜出勤を除く場合は別途、残業時間の列が
必要と思われます。

お礼

何度ももうしわけありません。感謝致します。
これから作りこみます。

回答No.3

noname#148473

かなり複雑な処理になりますね。作業用のセルを１つ使った方法を考えてみます。他の方の回答では、退勤時間が0:00以降になった場合に対応できないようなので、そうした場合も考慮してあります。

F2=IF(H2>8,8,H2)
G2=IF(H2>8,H2-8,0)
H2=(E2-D2-IF(AND(D2<=1/2,E2>=13/24),1/24,0)+(E2<5/24*1))*C2*24

のように入力後、F2:H2を下のほうへコピーしてください。

お礼

ありがとうございます。
今、一生懸命作業をしております。
No1の方の補足で書きましたが、私の誤りと追加のお願いがあります。
申し訳ありませんが、お時間がありましたら、こちらもよろしくお願い致します。

回答No.2

mshr1962

F2=MIN(E2-D2-IF(AND(D2<=1/2,E2>=13/24),1/24,0),1/3)*24
G2=IF(F2=8,E2-D2-IF(AND(D2<=1/2,E2>=13/24),1/24,0),0)*24

もっといい方法があるかもしれませんが．．．

お礼

ありがとうございました。
とても助かりました。

回答No.1

maruru01

こんにちは。maruru01です。

F2に、

=ROUND(MIN(E2-D2-(D2<="12:00"*1)*(E2>="13:00"*1)*"1:00","8:00")*24,2)

と入力して、下の行へコピーします。
また、G2に、

=ROUND(MAX(E2-D2-(D2<="12:00"*1)*(E2>="13:00"*1)*"1:00"-"8:00",0)*24,2)

と入力して、下の行へコピーします。

なお、F・G列とも、セルの表示形式は「数値」の小数点以下2桁や、
ユーザー定義で、「0.00」にしておいて下さい。

お礼

ありがとうございます。
（表が見にくくて申し訳ありませんでした。）
とても助かりました。
今、頑張ってやっています。
そこで、もうひとつわがままなお願いを補足させて下さい。
よろしくお願いします。

補足

1件、私の説明に誤りがありました。
カウントされない昼休みは「12:00～13:00」ではなく「12:00～12:45」でした。
この場合は式内で「13:00」の箇所を「12:45」と直すだけでよろしいのでしょうか？

実は、もう１つ（２つ）ご教示のお願いが発生しました。

・8:00以前の作業時間は早出出勤としてH列に表示させたいです。
・22:00以降の作業時間は深夜出勤としてI列に表示させたいです。
（残業時間をより細分化させたい／但し、1日8時間までは通常通り定時間扱い）
こんな事が可能でしょうか？何度も申し訳ありません。