| |
▼chou さん：
↑ と、大見得をきりましたが、実際にどれぐらいかかるか？
以下でお試しください。
H列に所要時間を記載、データ最終行の下のH列に合計と平均を記載します。
Sub Test()
Dim w As Variant
Dim c As Range
Dim st As Double
Dim ed As Double
Dim tot As Double
Dim cnt As Long
With Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 7)
ReDim w(1 To .Rows.Count, 1 To 1)
w(1, 1) = "所要時間"
For Each c In .Columns(1).Offset(1).Resize(.Rows.Count + 1).Cells
If c.Row > 2 Then
If c.Value <> c.Offset(-1).Value Then
w(c.Row - 1, 1) = ed - st
tot = tot + ed - st
cnt = cnt + 1
End If
End If
If c.Row > .Rows.Count Then Exit For
st = c.Offset(, 4).Value2
ed = c.Offset(, 4).Value2
If Not IsEmpty(c.Offset(, 6)) Then ed = c.Offset(, 6).Value2
Next
.Columns(8).Value = w
.Range("G" & .Rows.Count).Offset(1).Value = "合計"
.Range("H" & .Rows.Count).Offset(1).Value = tot
.Range("G" & .Rows.Count).Offset(2).Value = "平均"
.Range("H" & .Rows.Count).Offset(2).Value = tot / cnt
End With
End Sub
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
