回答編集履歴

2

追記

2021/03/08 11:07

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -21,3 +21,93 @@
21
21
 
22
22
 
23
23
  ただし、Excelのバージョンが古すぎるとSUMIFSが使えないかもしれないです。
24
+
25
+
26
+
27
+
28
+
29
+ ---
30
+
31
+ <追記>
32
+
33
+ ピボットテーブルを作るアプローチを試してみました。
34
+
35
+
36
+
37
+ ```VBA
38
+
39
+ Sub MakePivot()
40
+
41
+
42
+
43
+ Dim wb As Workbook
44
+
45
+ Set wb = Workbooks.Open(Environ("userprofile") & "\Documents\data.csv")
46
+
47
+
48
+
49
+ Dim ws As Worksheet
50
+
51
+ Set ws = wb.Worksheets("data")
52
+
53
+
54
+
55
+ Dim lo As ListObject
56
+
57
+ Set lo = ws.ListObjects.Add(xlSrcRange, ws.UsedRange.Resize(, ws.UsedRange.Columns.Count + 1), , xlYes)
58
+
59
+
60
+
61
+ With lo.ListColumns(lo.ListColumns.Count)
62
+
63
+ .Range(1) = "差異時間"
64
+
65
+ .DataBodyRange.NumberFormatLocal = "[h]:mm"
66
+
67
+ .DataBodyRange.Formula2R1C1 = "=[@実働時間数]-[@普通労働時間]"
68
+
69
+ End With
70
+
71
+
72
+
73
+ wb.PivotCaches.Create(xlDatabase, ws.UsedRange).CreatePivotTable ws.Cells(1, lo.ListColumns.Count + 2)
74
+
75
+
76
+
77
+ Dim pt As PivotTable
78
+
79
+ Set pt = ws.PivotTables(1)
80
+
81
+ With pt.PivotFields("週番号")
82
+
83
+ .Orientation = xlRowField
84
+
85
+ .Position = 1
86
+
87
+ End With
88
+
89
+ With pt.PivotFields("社員番号")
90
+
91
+ .Orientation = xlRowField
92
+
93
+ .Position = 2
94
+
95
+ End With
96
+
97
+ With pt
98
+
99
+ .AddDataField .PivotFields("実働時間数"), "合計 / 実働時間数", xlSum
100
+
101
+ .AddDataField .PivotFields("普通労働時間"), "合計 / 普通労働時間", xlSum
102
+
103
+ .AddDataField .PivotFields("差異時間"), "合計 / 差異時間", xlSum
104
+
105
+ .DataBodyRange.NumberFormatLocal = "[h]:mm"
106
+
107
+ End With
108
+
109
+
110
+
111
+ End Sub
112
+
113
+ ```

1

修正

2021/03/08 11:06

投稿

jinoji
jinoji

スコア4592

test CHANGED
@@ -10,7 +10,7 @@
10
10
 
11
11
  jdTime = WorksheetFunction.SumIfs(Columns(5), Columns(1), shainNo, Columns(7), WeekNo)
12
12
 
13
- ftTime = WorksheetFunction.SumIfs(Columns(5), Columns(1), shainNo, Columns(7), WeekNo)
13
+ ftTime = WorksheetFunction.SumIfs(Columns(6), Columns(1), shainNo, Columns(7), WeekNo)
14
14
 
15
15
  saiTime = jdTime - ftTime
16
16