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

質問

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

回答No.1

nishi6 · ベストアンサー

Ａ　　　　　　Ｂ　　　　　　Ｃ　　　　　Ｄ　　　　　　Ｅ
１　作業開始時間　作業終了時間　　普通時間　　普通時間外　　深夜時間外
２　　　入力　　　　　入力　　　　　式１　　　　　式２　　　　　式３
３

と表があるとします。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で見事できました。さっそく使い業務にいかしたと思います。ありがとうございました。

回答No.2

imogasi

苦労して１日考えました。理解していただけるか心配ですが、取り急ぎ一応載せます。テストも充分でないので、理解して・テストして、自己責任で使ってください、責任は取れません。業務用ソフトなので簡単には出来ません。頑張ってください。
（１）始業時刻はＡ列、終業時刻はＢ列に入力する。
８：３０や５：３０のように入力する。真夜中２時の場合も２：００と
入力してください。
始業時刻＞０、終業時刻＞０のチェックはしていません。
後記の変換後時刻を考えた後で、始業時刻＜終業時刻はチェックしてない。
朝５時を跨いだ勤務時間の入力は、次ぎの日（次行に記入）してもらう約束とすること。別の勤務日の扱いにする。
昼の休息時間との関係で、１２時台の終業時刻も１２時を越える部分は１時以降に繰り越さず、休憩時間としている
（各社取り扱いが違うでしょう。問題ありの点）。本来入れさせない方が簡単になる。１２時３０分まで続けて働いて帰った人は、１：３０と記入可とするとか。
（２）朝５時が１日のスタートなので（私のやり方では、そう捕らえるので）朝５時を０時に変換する。丁度５時間時差のある国へ行ったと考える。（私の工夫）
（３）したがって始業時刻を変換した時刻をＣ列に、終業時刻を変換した時刻をＤ列に入れる。変換は後記の関数式で行う。
（４）１日を、（Ａ）早朝就業、（Ｂ）通常就業、（Ｃ）時間外就業、（Ｄ）深夜就業と名付け、４つに区分する。
ＡはＥ列、ＢはＦ列、ＣはＧ列、ＤはＨ列に出す。
（５）実際の時間外手当てでは（Ａ）と（Ｃ）は同じ扱いだと思うが、計算と考える容易さのため、一旦別に分けている。あとで足すことになる。
（６）（Ｂ）の時間帯の場合、昼の１時間休憩時間を差し引くべきだが、これも計算の容易さのため、一旦は含めて計算している。後に別途Ｉ列を差し引く。
（７）時刻は８：３０のように、：を使って入力する。（標準の時刻の入力方法）
（８）Ａ，Ｂ，Ｃ，Ｄ列の時間計算は、セルにＩＦ関数等で式を入れると、非常に複雑で長い式になりすぎるので、別途関数を作った。（私の工夫）
（９）その関数名は、ＡはＲＧ１、ＢはＲＧ２、ＣはＲＧ３、ＤはＲＧ４で、引数は２つで、Ｃ列とＤ列の値である。例=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:30 18:30 2:30 13:30 1:00 9:00 1:00 0:00 1:00
1:30 3:30 20:30 22:30 0:00 0:00 0:00 2:00 0:00
3:30 4:00 22:30 23:00 0:00 0:00 0:00 0:30 0:00
4:00 4:30 23:00 23:30 0:00 0:00 0:00 0:30 0:00
5:00 5:00 0:00 0:00 0:00 0:00 0:00 0:00 0:00
6:30 8:30 1:30 3:30 2:00 0:00 0:00 0:00 0:00
9:30 18:30 4:30 13:30 0:00 8:00 1:00 0:00 1:00
10:30 17:30 5:30 12:30 0:00 7:00 0:00 0:00 1:00
23:30 24:00 18:30 19:00 0:00 0:00 0:00 0:30 0:00
7:00 8:20 2:00 3:20 1:20 0:00 0:00 0:00 0:00
8:00 8:20 3:00 3:20 0:20 0:00 0:00 0:00 0:00
（１６）表のセルの書式は「時刻」を選ぶこと。すると上
表のように３：００のように、表示されます。
（１７）テストデータはエクセルで、始業時間・就業時間を共に１０分おきに作り、結果を入念にチェックされることをお勧めします。２４＊６＊２４＊６＝２０７３６行で
不可能ではないでしょう。