【VBA】週番号が同じ日付のデータを抜き出し、計算を行う。

Question

お世話になります。

現在業務用のVBAファイルを作成しておりまして、全社員の毎日の勤怠データがシステムから１つのCSVファイルとして
下図のような形で出力されるようになっています。

Ａ[1]　　 Ｂ[2]　　　Ｃ[3]　　　　　Ｄ[4]　　Ｅ[5]　　　　　Ｆ[6]　　　 　Ｇ[7]
社員番号　氏名　　　　年月日　　　　曜日　　　実働時間数　　普通労働時間　週番号
1　　　　テスト社員　 20210315　　(月)　　　７：００　　　６：００　　　12
1　　　　テスト社員 　20210316　　(火)　　　８：００　　　６：００　　　12
1　　　　テスト社員 　20210317　　(水)　　　８：００　　　８：００　　　12
1　　　　テスト社員 　20210318　　(木)　　　８：００　　　８：００　　　12
1　　　　テスト社員 　20210319　　(金)　　　８：００　　　８：００　　　12
1　　　　テスト社員 　20210320　　(土)　　　８：００　　　８：００　　　12

この中で
社員番号が同じ　かつ　週番号が同じ　かつ　週の実働時間数の合計が40時間を超えている週の
実働時間数と普通労働時間数の差異分の時間の合計を出したいと考えています。
図の中ですと月曜日に1時間分の差異が出て火曜日に2時間分の差異が出ているため、
「３：００」という値を出したいと考えています。

ＶＢＡ初心者でインターネットから色々調べてみたのですがこのような応用の仕方が見つからず困っております。
お手数おかけいたしますがご助力いただければ幸いです。

Answer 1

多分こんな雰囲気でやったら出来ると思います。

    Dim shainNo, WeekNo, jdTime, ftTime, saiTime

    jdTime = WorksheetFunction.SumIfs(Columns(5), Columns(1), shainNo, Columns(7), WeekNo)
    ftTime = WorksheetFunction.SumIfs(Columns(6), Columns(1), shainNo, Columns(7), WeekNo)
    saiTime = jdTime - ftTime

ただし、Excelのバージョンが古すぎるとSUMIFSが使えないかもしれないです。

---

＜追記＞
ピボットテーブルを作るアプローチを試してみました。

Sub MakePivot()
    
    Dim wb As Workbook
    Set wb = Workbooks.Open(Environ("userprofile") & "\Documents\data.csv")
    
    Dim ws As Worksheet
    Set ws = wb.Worksheets("data")
    
    Dim lo As ListObject
    Set lo = ws.ListObjects.Add(xlSrcRange, ws.UsedRange.Resize(, ws.UsedRange.Columns.Count + 1), , xlYes)
    
    With lo.ListColumns(lo.ListColumns.Count)
        .Range(1) = "差異時間"
        .DataBodyRange.NumberFormatLocal = "[h]:mm"
        .DataBodyRange.Formula2R1C1 = "=[@実働時間数]-[@普通労働時間]"
    End With
    
    wb.PivotCaches.Create(xlDatabase, ws.UsedRange).CreatePivotTable ws.Cells(1, lo.ListColumns.Count + 2)
    
    Dim pt As PivotTable
    Set pt = ws.PivotTables(1)
    With pt.PivotFields("週番号")
        .Orientation = xlRowField
        .Position = 1
    End With
    With pt.PivotFields("社員番号")
        .Orientation = xlRowField
        .Position = 2
    End With
    With pt
        .AddDataField .PivotFields("実働時間数"), "合計 / 実働時間数", xlSum
        .AddDataField .PivotFields("普通労働時間"), "合計 / 普通労働時間", xlSum
        .AddDataField .PivotFields("差異時間"), "合計 / 差異時間", xlSum
        .DataBodyRange.NumberFormatLocal = "[h]:mm"
    End With

End Sub

Comments

[JaguarSugar]

ご回答いただきありがとうございます！
SUMIFS関数を使うことは考え付いておりませんでした。
いただいた回答を元に再度考えてみます。

[jinoji]

要件次第ですけど、VBAにこだわらず、社員一覧にSUMIFS関数を仕込んだ算出用Excelファイルを用意しておいて、CSVを参照させる、なんて手もあるかもしれません。