作業時間をエクセルで計算させたい

作業時間をエクセルで計算させたいのですが、よろしくお願いします。
条件は
１．作業時間　０時～２４時
２．普通作業時間帯　８時３０分～１７時２０分（昼休み１時間で７時間５０分）
３．普通時間外作業時間帯　５時～８時３０分及び１７時２０分～２２時
４．深夜時間外作業時間帯　０時～５時及び２２時～２４時
５．昼休みは１２時～１３時
６．時間は分単位
です。この条件で
作業開始時間　　作業終了時間　普通時間　普通時間外　深夜時間外
　　　Ａ１　　　　　　　　　Ｂ１　　　　　Ｃ１　　　　　Ｄ１　　　　　Ｅ１
という項目を設け、作業開始時間Ａ１と作業終了時間Ｂ１に時間を入力し、
普通時間Ｃ１・普通時間外Ｄ１・深夜時間外Ｅ１のセルに計算させたいのですが
よろしくお願いします。作業ですのであらゆる時間帯があり、作業終了時間も決まってません。できれば、マクロでなく関数で計算させたいのですがよろしくお願いします。　　

No.1 ベストアンサー 回答者： nishi6 回答日時： 2003/02/02 20:58

　　　　　Ａ　　　　　　Ｂ　　　　　　Ｃ　　　　　Ｄ　　　　　　Ｅ

１　作業開始時間　作業終了時間　　普通時間　　普通時間外　　深夜時間外
２　　　入力　　　　　入力　　　　　式１　　　　　式２　　　　　式３
３

と表があるとします。C2、D2、E2に式１、式２、式３を入力します。後はコピーしてください。

式１=　IF( MIN("12:00",B2)>MAX( "8:30",A2) ,(MIN("12:00",B2)-MAX( "8:30",A2)),0)
　　　+IF((MIN("17:20",B2)>MAX("13:00",A2)),(MIN("17:20",B2)-MAX("13:00",A2)),0)

式２=　IF( MIN( "8:30",B2)>MAX( "5:00",A2) ,(MIN( "8:30",B2)-MAX( "5:00",A2)),0)
　　　+IF((MIN("22:00",B2)>MAX("17:20",A2)),(MIN("22:00",B2)-MAX("17:20",A2)),0)

式３=　IF( MIN( "5:00",B2)>MAX( "0:00",A2) ,(MIN( "5:00",B2)-MAX( "0:00",A2)),0)
　　　+IF((MIN("24:00",B2)>MAX("22:00",A2)),(MIN("24:00",B2)-MAX("22:00",A2)),0)

作業終了時間が24:00を超える場合は想定していません。
作業開始時間～24:00と0:00～作業終了時間に分けて入力します。
また、24時間超の勤務時間は対応していません。（対応すれば、式がどんどん複雑になっていきます）

上式は、下のユーザー定義関数（面倒な期待値計算等を計算する時に使う方法です。）を考えた後、作り出した式です。
ある意味、調和が取れていてきれいではあります。ただ、ユーザー定義関数の方が可読性、保守性はいい様に思いました。
関数をお望みのようなので作ってみました。ご参考に。

＜下の関数の使い方＞
　=KinmuJikan(勤務開始時刻セル、勤務終了時刻セル、計算区分)
・・・・・・・・・計算区分　1:普通、2:普通時間外、3:深夜
のように使うユーザー定義関数です。

Function KinmuJikan(srtTM As Date, endTM As Date, Kinmu As Integer)
　　Dim tmTable As Variant　　'作業時間帯のテーブル
　　Dim tmKbn As Variant　　　'作業時間帯の区分(0:休み、1:普通、2:普通時間外、3:深夜)
　　tmTable = Array("0:00", "5:00", "8:30", "12:00", "13:00", "17:20", "22:00", "24:00")
　　tmKbn = Array(0, 3, 2, 1, 0, 1, 2, 3)

　　Dim srtKbn As Integer　　'勤務開始の区分
　　Dim endKbn As Integer　　'勤務終了の区分
　　Dim t As Integer　　　　　'カウンタ
　　'どの区分から勤務開始し、どれで終わったか調べる
　　For t = LBound(tmTable) + 1 To UBound(tmTable)
　　　　If tmTable(t - 1) < srtTM And srtTM <= tmTable(t) Then srtKbn = t
　　　　If tmTable(t - 1) < endTM And endTM <= tmTable(t) Then endKbn = t
　　Next

　　Dim srtTime As Date　　　'作業時間帯での計算開始時刻
　　Dim endTime As Date　　　'作業時間帯での計算終了時刻
　　KinmuJikan = 0
　　For t = srtKbn To endKbn
　　　　srtTime = Application.Max(tmTable(t - 1), srtTM)
　　　　endTime = Application.Min(tmTable(t), endTM)

　　　　'勤務時間を合算する
　　　　KinmuJikan = KinmuJikan - (endTime - srtTime) * (tmKbn(t) = Kinmu)
　　Next
End Function

この回答へのお礼

急な出張で、返事が今日になってしまいすみませんでした。上記の式1.2.3で見事できました。さっそく使い業務にいかしたと思います。ありがとうございました。

お礼日時：2003/02/09 14:55

No.2 回答者： imogasi 回答日時： 2003/02/04 09:45

苦労して１日考えました。

理解していただけるか心配ですが、取り急ぎ一応載せます。テストも充分でないので、理解して・テストして、自己責任で使ってください、責任は取れません。業務用ソフトなので簡単には出来ません。頑張ってください。
（１）始業時刻はＡ列、終業時刻はＢ列に入力する。
８：３０や５：３０のように入力する。真夜中２時の場合も２：００と
入力してください。
始業時刻＞０、終業時刻＞０のチェックはしていません。
後記の変換後時刻を考えた後で、始業時刻＜終業時刻はチェックしてない。
朝５時を跨いだ勤務時間の入力は、次ぎの日（次行に記入）してもらう約束とすること。別の勤務日の扱いにする。
昼の休息時間との関係で、１２時台の終業時刻も１２時を越える部分は１時以降に繰り越さず、休憩時間としている
（各社取り扱いが違うでしょう。問題ありの点）。本来入れさせない方が簡単になる。１２時３０分まで続けて働いて帰った人は、１：３０と記入可とするとか。
（２）朝５時が１日のスタートなので（私のやり方では、そう捕らえるので）朝５時を０時に変換する。丁度５時間時差のある国へ行ったと考える。（私の工夫）
（３）したがって始業時刻を変換した時刻をＣ列に、終業時刻を変換した時刻をＤ列に入れる。変換は後記の関数式で行う。
（４）１日を、（Ａ）早朝就業、（Ｂ）通常就業、（Ｃ）時間外就業、（Ｄ）深夜就業と名付け、４つに区分する。
ＡはＥ列、ＢはＦ列、ＣはＧ列、ＤはＨ列に出す。
（５）実際の時間外手当てでは（Ａ）と（Ｃ）は同じ扱いだと思うが、計算と考える容易さのため、一旦別に分けている。あとで足すことになる。
（６）（Ｂ）の時間帯の場合、昼の１時間休憩時間を差し引くべきだが、これも計算の容易さのため、一旦は含めて計算している。後に別途Ｉ列を差し引く。
（７）時刻は８：３０のように、：を使って入力する。（標準の時刻の入力方法）
（８）Ａ，Ｂ，Ｃ，Ｄ列の時間計算は、セルにＩＦ関数等で式を入れると、非常に複雑で長い式になりすぎるので、別途関数を作った。（私の工夫）
（９）その関数名は、ＡはＲＧ１、ＢはＲＧ２、ＣはＲＧ３、ＤはＲＧ４で、引数は２つで、Ｃ列とＤ列の値である。例=RG1(C2,D2)など。
（１０）（３）の就業時間の変換（Ｃ列）は
　　　=IF(A2-5/24>0,A2-5/24,A2+19/24)
　　　（３）の終業時間の変換（Ｄ列）は
　　　=IF(B2-5/24>0,B2-5/24,B2+19/24)
で、ご覧のように勿論形は同じである。
（１１）（９）のＲＧ１～ＲＧ４と言う関数の内容は下記の通り。ＶＢＥの標準モジュールにコピーし貼りつけること。
Function RG1(s, e)
Select Case s
Case Is < 3.5 / 24
If e < 3.5 / 24 Then
RG1 = e - s
Else
RG1 = 3.5 / 24 - s
End If
Case Else
RG1 = 0
End Select
End Function
'----------------
Function RG2(s, e)
Select Case s
Case Is < 3.5 / 24
If e < 3.5 / 24 Then
RG2 = 0
ElseIf e < 12.5 / 24 Then
RG2 = e - 3.5 / 24
Else
RG2 = 12.5 / 24 - 3.5 / 24
End If
Case Is < 12.5 / 24
If e < 12.5 / 24 Then
RG2 = e - s
Else
RG2 = 12.5 / 24 - s
End If
Case Else
RG2 = 0
End Select
End Function
'---------------
Function RG3(s, e)
Select Case s
Case Is < 12.5 / 24
If e < 12.5 / 24 Then
RG3 = 0
ElseIf e < 17# / 24 Then
RG3 = e - 12.5 / 24
Else
RG3 = 17# / 24 - 12.5 / 24
End If
Case Is < 17# / 24
If e < 17# / 24 Then
RG3 = e - s
Else
RG3 = 17# / 24 - s
End If
Case Else
RG3 = 0
End Select
End Function
'--------------
Function RG4(s, e)
Select Case s
Case Is < 17# / 24
If e < 17# / 24 Then
RG4 = 0
Else
RG4 = e - 17# / 24
End If
Case Else
RG4 = e - s
End Select
End Function
(注）１２．５の０．５は３０分を表す。変換前の１２．５＋５＝１７．
５（すなわち夕５時）を表す。
２４で全て割っているのは、エクセルが時刻は１日＝２４時間を１とする数値として
表して・扱っているからです。
（１２）Ｅ，Ｆ，Ｇ，Ｈの第２行の関数式は下記の通り。第３行目以下は複写して下さい。
Ｅ２は=RG1(C2,D2)
Ｆ２は=RG2(C2,D2)
Ｇ２は=RG3(C2,D2)
Ｈ２は=RG4(C2,D2)
Ｉ列は=KYU(C2,D2)
（１３）昼休憩時間を計算する関数式は（Ｉ列）
Function kyu(s, e)
Select Case s
Case Is < 7# / 24
If e < 7# / 24 Then
kyu = 0
ElseIf e > 8 / 24 Then
kyu = 1 / 24
Else
kyu = e - 7 / 24
End If
Case Else
kyu = 0
End Select
End Function
（１４）あとはＥ＋Ｇを出し、Ｂから昼休憩Ｉを引き、Ｅ＋Ｇ、Ｈには自社の時間外料率を掛けて、Ｂには時給者には時給額をかけて計算してください。
ここでは省略します。ここでいう表は計算表・ワークシート（裏方表）なので別シートにした方が良いかもしれない。
（１５）データサンプル（Ａ列からＩ列。ＯＫＷＥＢで強制改行される
と思いますので、修正して見て下さい。）
実際就業実際終了換算就業換算終業早朝就業通常就業時間外就業深夜就業昼休憩
7:3018:302:3013:301:009:001:000:001:00
1:303:3020:3022:300:000:000:002:000:00
3:304:0022:3023:000:000:000:000:300:00
4:004:3023:0023:300:000:000:000:300:00
5:005:000:000:000:000:000:000:000:00
6:308:301:303:302:000:000:000:000:00
9:3018:304:3013:300:008:001:000:001:00
10:3017:305:3012:300:007:000:000:001:00
23:3024:0018:3019:000:000:000:000:300:00
7:008:202:003:201:200:000:000:000:00
8:008:203:003:200:200:000:000:000:00
（１６）表のセルの書式は「時刻」を選ぶこと。すると上
表のように３：００のように、表示されます。
（１７）テストデータはエクセルで、始業時間・就業時間を共に１０分おきに作り、結果を入念にチェックされることをお勧めします。２４＊６＊２４＊６＝２０７３６行で
不可能ではないでしょう。