[[20101221205345]]　『作業時間と休憩時間』（FA）　ページの最後に飛ぶ

『作業時間と休憩時間』（FA）

 いつもお世話になっております。
 また行き詰りましたので皆様のお力をお借りしたくご質問させて頂きます。

 現在作業工数の割出しを検討中です。
 下記のような表があります。
　　  A       B      C       D         E         F         G         H        I　　　　　J　　　　 K　　　  L
 1                                    開始      開始  　　終了  　　終了  
 2 社員番号 作業者 シフト 　作業    　日付      時間　　　日付      時間   作業時間    顧客名　  製品名　　数量
 3 　001 　  AAA　   C　　　111 　 2010/12/21   22:00  2010/12/22　 2:00　　 　　　　　 A社　　　AKSS-1　　100
 4 　015 　  BBB　   A　　　111 　 2010/12/21    8:30  2010/12/21　 17:00 　 　　　　　 B社　　　XKS1111　 10000
　　 
 別のシートで、シフト別のタイムテーブルがあります。

     A   B　　　　C　 　D　　  E     F      G　　　 H　 　 I　　 J     K      L       M     N      O  
 1     シフトA　 開始  終了   時間       シフトB　 開始   終了  時間       シフトC　 開始  終了   時間
 2      朝礼　   8:00　8:15　 0:15     　  作業 　 16:00 20:00  4:00     　  作業 　 20:00  0:00  4:00
 3      掃除　 　8:15  8:30   0:15         休憩　  20:00 21:00  1:00     　  休憩 　  0:00  1:00  1:00
 4      作業   　8:30  12:00  3:30     　  作業 　 21:00  0:00  3:00     　  作業 　  1:00  5:00  4:00
 5      休憩　 　12:00 13:00  1:00     　  残業1H   0:00  1:15  1:15     　  残業1H   5:00  6:15  1:15
 6      作業　 　13:00 15:00  2:00     　  残業2H   1:15  2:15  1:00     　  残業2H   6:15  7:15  1:00
 7      休憩　 　15:00 15:10  0:10     　  残業3H　 2:15  3:15  1:00     　  残業3H   7:15  8:15  1:00
 8      作業　 　15:10 17:00  1:50
 9      休憩　 　17:00 17:30  0:30
 10     残業1H 　17:30 18:15  0:45
 11     残業2H 　18:15 19:15  1:00
 12     残業3H 　19:15 20:00  0:45
 13     掃除   　20:00 20:15  0:15

 やりたいことは、作業開始日、時間、作業終了日、時間を入力するとタイムテーブルから各シフトの休憩時間を差し引いた作業時間をI列に表示したいのです。
 例：表の1行目の場合3:00、2行目の場合7:20

 どうかご教授宜しくお願い致します。

 製作中のエクセル2007(日本語版)
 使用想定、Excel2000、Excel2003、Excel2007(各英語、タイ語版)
 製作中のＯＳ、ＸＰ(日本語版)
 使用想定ＯＳ、ＸＰ(各英語、タイ語版)

 時間の計算で変換誤差（倍精度実数の為）が出るのが怖いので
 シリアル値を分（Minute）に変換して整数計算をしています
 時間の計算が、今一自信が無いので善く検証して見て下さい

 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim i As Long
    Dim lngRow As Long
    Dim vntData As Variant
    Dim vntColumn As Variant
    Dim vntTime As Variant

    'シフト、開始日付、開始時間、終了日付、終了時間の位置を列挙
    vntColumn = Array(3, 5, 6, 7, 8)

    With Target
        If .Count > 1 Then
            Exit Sub
        End If
        If .Row <= 2 Then
            Exit Sub
        End If
        'シフト、開始日付、開始時間、終了日付、終了時間の何れかが入力された場合
        For i = 0 To UBound(vntColumn)
            If .Column = vntColumn(i) Then
                Exit For
            End If
        Next i
        If i > UBound(vntColumn) Then
            Exit Sub
        End If
        '入力行位置を取得
        lngRow = .Row
    End With

    '社員番号、作業者、シフト、開始日付、開始時間、終了日付、終了時間迄を配列として取得
    vntData = Me.Cells(lngRow, "A").Resize(, 8).Value

    '入力されたシフト、開始日付、開始時間、終了日付、終了時間を検査
    For i = 0 To UBound(vntColumn)
        Select Case i
            Case 0 'シフト
                If IsEmpty(vntData(1, vntColumn(i))) Then
                    Exit Sub
                End If
            Case 1, 3 '日付
                If Not IsDate(vntData(1, vntColumn(i))) Then
                    Exit Sub
                End If
            Case Else  '時刻
                If VarType(vntData(1, vntColumn(i))) <> vbDouble Then
                    Exit Sub
                End If
        End Select
    Next i

    '作業時間を分で計算
    vntTime = TimeCalc(vntData, vntColumn)
    '作業時間が有るなら
    If vntTime <> -1 Then
        Application.EnableEvents = False
        With Me.Cells(lngRow, "I")
            'セルの書式を変更
            .NumberFormat = "h:mm"
            '分をシリアル値に変換してI列に代入
            .Value = TimeSerial(vntTime \ 60, vntTime Mod 60, 0)
        End With
        Application.EnableEvents = True
    End If

 End Sub

 Private Function TimeCalc(vntData As Variant, vntColumn As Variant) As Long

    Dim i As Long
    Dim lngRows As Long
    Dim vntTabl As Variant
    Dim lngNumb As Long
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim lngSum As Long

    'タイムテーブルの位置を取得
    lngNumb = (Asc(StrConv(vntData(1, vntColumn(0)), vbNarrow + vbUpperCase)) - 65) * 5 + 2

    '作業開始時間を午前0時からの経過分に変換
    lngStart = ConvMinute(vntData(1, vntColumn(2)))
    '作業終了時間を午前0時からの経過分に変換
    lngEnd = ConvMinute(vntData(1, vntColumn(4)) _
                + vntData(1, vntColumn(3)) - vntData(1, vntColumn(1)))

    'タイムテーブルの先頭セル位置を基準とする（先頭列の列見出しのセル位置）
    With Worksheets("Sheet2").Cells(1, lngNumb)
        '行数の取得
        lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
        If lngRows <= 0 Then
            TimeCalc = -1
            Exit Function
        End If
        'タイムテーブルを配列に取得
        vntTabl = .Offset(1).Resize(lngRows, 4).Value
    End With

    'タイムテーブルに就いて繰り返し
    For i = 1 To lngRows
        If vntTabl(i, 1) = "作業" Or vntTabl(i, 1) Like "残業*" Then
            'タイムテーブルの開始を午前0時からの経過分に変換
            '開始がタイムテーブル開始先頭時刻より小さい場合、
            '翌日とする為、1日を加算
            If vntTabl(i, 2) < vntTabl(1, 2) Then
                vntTabl(i, 2) = vntTabl(i, 2) + 1
            End If
            '経過分に変換
            vntTabl(i, 2) = ConvMinute(vntTabl(i, 2))
            'タイムテーブルの終了を午前0時からの経過分に変換
            If vntTabl(i, 3) < vntTabl(1, 2) Then
                vntTabl(i, 3) = vntTabl(i, 3) + 1
            End If
            '経過分に変換
            vntTabl(i, 3) = ConvMinute(vntTabl(i, 3))
            'タイムテーブルの開始、終了が作業時間内に在るなら
            If lngStart <= vntTabl(i, 3) And vntTabl(i, 2) <= lngEnd Then
                'タイムテーブルの開始が作業開始時刻より前なら
                'タイムテーブルの開始を作業開始時刻に
                If lngStart > vntTabl(i, 2) Then
                    vntTabl(i, 2) = lngStart
                End If
                'タイムテーブルの終了が作業終了時刻より後なら
                'タイムテーブルの終了を作業終了時刻に
                If vntTabl(i, 3) > lngEnd Then
                    vntTabl(i, 3) = lngEnd
                End If
                '作業時間を加算
                lngSum = lngSum + (vntTabl(i, 3) - vntTabl(i, 2))
            End If
        End If
    Next i

    TimeCalc = lngSum

 End Function

 Private Function ConvMinute(vntValue As Variant) As Long

    'シリアル値を午前0時からの経過分に変換
    ConvMinute = Int(vntValue) * 24 * 60 + Hour(vntValue) * 60 + Minute(vntValue)

 End Function

 (Bun)

 後、以下の★印を追加した方が善いかも

            Case Else  '時刻
                If VarType(vntData(1, vntColumn(i))) <> vbDouble Then
                    Exit Sub
                Else '★追加
                    If vntData(1, vntColumn(i)) < 0 Or 1 <= vntData(1, vntColumn(i)) Then '★追加
                        Exit Sub '★追加
                    End If '★追加
                End If
        End Select

 (Bun)

 Bun様

 いつもお世話になっております。ご回答ありがとうございます。
 ご丁寧なコメントを書いて頂きとても勉強になります。

 早速、マクロの方ご確認させて頂きました。
 シフトAは問題ないのですが、シフトBとCの休憩時間が反映されません。
 再度ご教授お願い致します。

 (FA)

 ごめん、タイムテーブルの先頭データを変数に退避するのを忘れていました
 ★印を変更して下さい

 後、計算は、シフト1回分で善いのですよね？
 もし、泊まり込みでシフト2回分、3回分と成る場合が有るならコードは変わります

 Private Function TimeCalc(vntData As Variant, vntColumn As Variant) As Long

    Dim i As Long
    Dim lngRows As Long
    Dim vntTabl As Variant
    Dim lngNumb As Long
    Dim lngStart As Long
    Dim lngEnd As Long
    Dim lngSum As Long
    Dim vntTop As Variant '★追加

    'タイムテーブルの位置を取得
    lngNumb = (Asc(StrConv(vntData(1, vntColumn(0)), vbNarrow + vbUpperCase)) - 65) * 5 + 2

    '作業開始時間を午前0時からの経過分に変換
    lngStart = ConvMinute(vntData(1, vntColumn(2)))
    '作業終了時間を午前0時からの経過分に変換
    lngEnd = ConvMinute(vntData(1, vntColumn(4)) _
                + vntData(1, vntColumn(3)) - vntData(1, vntColumn(1)))

    'タイムテーブルの先頭セル位置を基準とする（先頭列の列見出しのセル位置）
    With Worksheets("Sheet2").Cells(1, lngNumb)
        '行数の取得
        lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
        If lngRows <= 0 Then
            TimeCalc = -1
            Exit Function
        End If
        'タイムテーブルを配列に取得
        vntTabl = .Offset(1).Resize(lngRows, 4).Value
    End With

    '先頭データを変数に退避
    vntTop = vntTabl(1, 2) '★追加
    'タイムテーブルに就いて繰り返し
    For i = 1 To lngRows
        If vntTabl(i, 1) = "作業" Or vntTabl(i, 1) Like "残業*" Then
            'タイムテーブルの開始を午前0時からの経過分に変換
            '開始がタイムテーブル開始先頭時刻より小さい場合、
            '翌日とする為、1日を加算
 '           If vntTabl(i, 2) < vntTabl(1, 2) Then
            If vntTabl(i, 2) < vntTop Then  '★変更
                vntTabl(i, 2) = vntTabl(i, 2) + 1
            End If
            '経過分に変換
            vntTabl(i, 2) = ConvMinute(vntTabl(i, 2))
            'タイムテーブルの終了を午前0時からの経過分に変換
 '           If vntTabl(i, 3) < vntTabl(1, 2) Then
            If vntTabl(i, 3) < vntTop Then '★変更
                vntTabl(i, 3) = vntTabl(i, 3) + 1
            End If
            '経過分に変換
            vntTabl(i, 3) = ConvMinute(vntTabl(i, 3))
            'タイムテーブルの開始、終了が作業時間内に在るなら
            If lngStart <= vntTabl(i, 3) And vntTabl(i, 2) <= lngEnd Then
                'タイムテーブルの開始が作業開始時刻より前なら
                'タイムテーブルの開始を作業開始時刻に
                If lngStart > vntTabl(i, 2) Then
                    vntTabl(i, 2) = lngStart
                End If
                'タイムテーブルの終了が作業終了時刻より後なら
                'タイムテーブルの終了を作業終了時刻に
                If vntTabl(i, 3) > lngEnd Then
                    vntTabl(i, 3) = lngEnd
                End If
                '作業時間を加算
                lngSum = lngSum + (vntTabl(i, 3) - vntTabl(i, 2))
            End If
        End If
    Next i

    TimeCalc = lngSum

 End Function

 (Bun)

 Bun様

 早速のご返答とご教授恐縮です。
 マクロの方、ご確認させて頂きました。
 理想道理のマクロとなりました！ありがとうございます。

 ＞後、計算は、シフト1回分で善いのですよね？
 ＞もし、泊まり込みでシフト2回分、3回分と成る場合が有るならコードは変わります
 上記に関しては可能性がありますが、今の段階ではハッキリとどのような仕様になるか決められないのと
 イレギラーなことなので別管理とする予定です。

 まだ課題を抱えているので、また行き詰ることがあればご教授お願い致します。

 (FA)

コメント返信：

[ 一覧(最新更新順) ]

[[20101221205345]] 『作業時間と休憩時間』（FA） ページの最後に飛ぶ

[[20101221205345]]　『作業時間と休憩時間』（FA）　ページの最後に飛ぶ