平均残業時間を計算するVBAプログラム

はじめに

Excel VBAでグループ別の平均残業時間を計算するプログラムを作成しました

元データ

元データは以下の画像です

実装したソースコード

Sub overtime()
    
    Dim hour As Long
    Dim count As Long
    Dim array1(3) As Variant
    Dim start_num As Long
    Dim end_num As Long
    Dim sh1, sh2 As Worksheet
    Dim start_data As Long
    Dim end_data As Long
    Dim data_count As Long
    Dim sum As Double
    Dim avg As Double
    
    
    start_num = 3
    Set sh1 = Worksheets("データ")
    Set sh2 = Worksheets("list")
    end_num = sh2.Cells(Rows.count, 2).End(xlUp).Row
    
    Dim i As Integer
    Dim j As Integer
    j = 0
    For i = start_num To end_num
        Dim data1 As String
        data1 = sh2.Cells(i, 2)
        array1(j) = CVar(data1)
        j = j + 1
    Next
    
    start_data = 4
    end_data = sh1.Cells(Rows.count, 2).End(xlUp).Row
    data_count = 0
    sum = 0
    avg = 0
    Dim k As Long
    Dim l As Long
    l = 4
    Dim m As Long
    
    For m = 0 To 2
        Dim group_name As String
        Dim data_groupname As String
        Dim data_timehour As Double
        group_name = CStr(array1(m))
        For k = start_data To end_data
            data_groupname = sh1.Cells(k, 3)
            data_timehour = sh1.Cells(k, 4)
            If data_groupname = group_name Then
                sum = sum + data_timehour
                data_count = data_count + 1
            End If
        Next
        sh1.Cells(l, 12) = group_name
        avg = sum / data_count
        sh1.Cells(l, 13) = avg
        avg = 0
        sum = 0
        data_count = 0
        l = l + 1
        
    Next
    
End Sub

実行した結果

以下の画像です

最後に

中小企業なら、各部署別の平均残業時間を計測できます。是非ご活用ください。