Excel2000での勤務時間計算

質問

以前何度か質問をして、その都度解決したのですが、少しパターンが変わっただけでチンプンカンプンになってしまう初心者からの質問です。
よろしくお願いします。

以下のような勤務体系があります。
出勤時間と退勤時間を入力して、条件にあてはまる区分に時間を振り分けたいと思います。

(1)早出：5:00～8:30
(2)午前定時：8:30～12:00
(3)昼休み：12:00～12:45
(4)午後定時：12:45～16:45
(5)時間内残業：16:45～17:30
但し、(1)+(2)+(4)の労働時間が8時間を超える場合は、17:15～17:30は午後休みになる。
(6)通常残業：17:30～10:00
(7)深夜残業：10:00～05:00

(A1セルに)出勤時間入力(例:8:00)
(B1セルに)退勤時間入力(例:18:15)

(C1セルに)早出時間表示(例:0.50)－30分なので0.5時間です。
(D1セルに)定時間(午前＋午後)表示(例:7.50)－12:00～12:45は昼休みなので時間のカウントはしない。

(E1セルに)時間内残業表示(例:0.50)－17:15～17:30の15分は午後休みなのでカウントしない。

(F1セルに)通常残業表示(例:0.75)

(G1セルに)深夜残業表示(例:0.00)

それぞれ割増率が違う為、以上のように各セルに労働時間を区分けして表示させたいです。
何卒ご教示お願い致します。

回答No.2

maruru01 · ベストアンサー

こんにちは。maruru01です。

まず、(6)、(7)の「10:00」は、「22:00」と判断します。
また、出勤時間は必ず5:00以降で、退勤時間は必ず翌日の5:00以前とします。
退勤時間は、0:00以降は、そのまま「4:00」などと入力するものとします。
（「28:00」のように入力しても対応しますが。）

C1=MAX((MIN("8:30",B1+(A1>=B1))-MAX("5:00",A1))*24,0)
D1=MAX((MIN("16:45",B1+(A1>=B1))-MAX("8:30",A1)-(A1<"12:00"*1)*(B1+(A1>=B1)>"12:45"*1)*"0:45")*24,0)
E1=MAX((MIN(IF(C1+D1>=8,"17:15","17:30"),B1+(A1>=B1))-MAX("16:45",A1))*24,0)
F1=MAX((MIN("22:00",B1+(A1>=B1))-MAX("17:30",A1))*24,0)
G1=MAX((MIN("29:00",B1+(A1>=B1))-MAX("22:00",A1))*24,0)

です。
多くの例で確認したわけではないので、いろいろ試してみて下さい。

お礼

ありがとうございます。
maruru01さん、前回の時もお世話になりました。
早速、試してみます。感謝致します。

回答No.4

imogasi

＃３です。＃３では抽象的なことを言ったので、具体的な
ことを考えて見ました。
例えば「早出定時」内の時間を計算するとき
８時３０分との差を出さないといけないと思います。
その８時３０分はどうして関数式内で表すか。
Ａ列に退勤時刻があるとして
（１）A1-"8:30"でその時間が表されます。
または使わないセル（仮にX1セルとして）に8:30と入れ、
=A1-X1とする方法もあります。
（２）もうひとつ、８時３０分より早く終わると、値がマイナスになるので、その場合は０にする。
（３）１２時を越えると３時間３０分で打ちきらないとだめなわけです。この３時間３０分をどう表すか。
"3:30"では旨く行かない。
エクセルの時刻シリアル値は２４時間を１とする「小数」で表しますから、３時間３０分は3.5/24で良いと思います。他に良い表し方があるかもしれませんが。
（４）結局Ｂ１に下記を入れて下に複写してください。
=IF(A1-"8:30"<0,0,IF(A1-"8:30">3.5/24,3.5/24,A1-"8:30"))で旨く行くようです。
（テスト例）「午前定時」範囲内の時間数の計算
Ａ列は退勤時間。
（Ａ列）　　（Ｂ列）
9:30 1:00
7:30 0:00
13:00 3:30
12:30 3:30
12:10 3:30
10:20 1:50
6:30 0:00
8:30 0:00
8:40 0:10
同じことを全「区分」ごとに１列使って計算してみてください。１日総労働時間は、昼休みを除いた和になります。
後は適宜、区分ごとの（割増）乗率％を、従業員時間単価に掛け、時間数を掛ければよい。
但し時間数は時刻シリアル値であるから２４を掛ける必要あり。＝列の時間数＊時給＊２４です。
セルの書式設定は、時刻と数値を適宜使い分ける必要あり。
また出勤時間によるマイナス時間の補正が必要。
９時３０分出勤なら、午前定時から１時間（="9:30"-"8:30")が必要。１１時退勤なら上記の２時間３０分
から、１時間マイナスの１時間３０分が午前定時の時間。
複雑になるがこれが真ともかなと思います。考えてみてください。

お礼

再度のご回答ありがとうございます。
貴方の親切丁寧なご回答に大変感謝致します。
参考にして、勉強したいと思います。

回答No.3

imogasi

私も＃１さんの意見に賛成です。場合分けの複雑な時間外計算は関数では、複雑になりすぎて、ネスト（関数の中の引数が関数になっている）が深くなります。かといってVBAを勧めても、直ぐには習得が難しいでしょう。
１つお勧めは、出きるだけ使う列が多くなるのをいとわず、中間結果を出して、一歩一歩やるより他ないでしょう。例えば「区分」ごとに、勤務者各人に列を設け、該当時間計算式を１つ１つ（一区分ずつ）完成する。「その終了時間－その区分のスタート時間が」マイナスの場合は空白、時間範囲を越える時は、その時間範囲にする式を作る。
まず計算に必要な事項ややり方を文章で表現して、その判らない１つにつき、関数式のテクニックを質問すべきでしょう。
それが出来ないなら、お金に絡む問題である以上、エクセルを使う方式を作るべきでないと思います。

お礼

ありがとうございます。
頑張ります。

回答No.1

snoopy64

式だけでできなくもないですが、かなり長～い、難解な式になると思います。
ですので、このケースはマクロの利用をお勧めします。
以前僕が書いた勤怠表のブックがありますので、そのコードをここに貼り付けてもいいですけど、これもそこそこ長いですし、理解して適宜書き換える必要がありますから、それ相応のスキルが必要かと・・・

お礼

ありがとうございます。
おっしゃる通りマクロの必要性は、ヒシヒシ感じております。（とりあえずＶＢＡ自動記録をして、書き換える程度のスキルしかありません）
勉強します。
将来の為に、貴兄の勤怠表を参照したかったのですが、やっぱり長くて無理ですか？