|
楼主 |
发表于 2019-3-7 09:56
|
显示全部楼层
本帖最后由 lgzxmlg 于 2019-3-7 10:23 编辑
我想分开判断,但后面代码不会。我参考他人的学者改吧。我改成这样,还是有偏差,不知错在哪。
Sub test()
Dim r%, i%
Dim arr, brr
Dim rq1 As Date
Dim rq2 As Date
With Worksheets("sheet1")
r = .Cells(.Rows.Count, 1).End(xlUp).Row
rq1 = Int(Application.Min(.Range("a2:a" & r)))
rq2 = Int(Application.Max(.Range("a2:a" & r)))
ts = Application.Ceiling(rq2 - rq1 + 1, 5)
ReDim brr(1 To ts / 5, 1 To 5)
For i = 1 To UBound(brr)
brr(i, 1) = rq1 + (i - 1) * 5
brr(i, 2) = brr(i, 1) + 4
Next
arr = .Range("a2:g" & r)
For i = 1 To UBound(arr)
For j = 1 To UBound(brr)
If arr(i, 1) >= brr(j, 1) And arr(i, 1) <= brr(j, 2) And arr(i, 3) > 0 Then
brr(j, 3) = brr(j, 3) + arr(i, 3)
brr(j, 4) = brr(j, 4) + arr(i, 7)
brr(j, 5) = brr(j, 5) + 1
If arr(i, 1) >= brr(j, 1) And arr(i, 1) <= brr(j, 2) And arr(i, 7) > 0 Then
brr(j, 3) = brr(j, 3) + arr(i, 3)
brr(j, 4) = brr(j, 4) + arr(i, 7)
brr(j, 5) = brr(j, 5) + 1
End If
End If
Next
Next
End With
For i = 1 To UBound(brr)
If Len(brr(i, 5)) <> 0 And brr(i, 5) <> 0 Then
brr(i, 3) = Round(brr(i, 3) / brr(i, 5), 2)
brr(i, 4) = Round(brr(i, 4) / brr(i, 5), 2)
End If
Next
With Worksheets("sheet2")
.UsedRange.Offset(1, 0).ClearContents
.Range("a2").Resize(UBound(brr), UBound(brr, 2) - 1) = brr
End With
End Sub
|
|