如下代码第二段循环计算平均值的能不能并到第一个循环内一起计算?
因为第一个循环是求和,且条件和第二段还不一样。
第二段内也有两种条件。
没别的意思,只是想多学习一下这种相对复杂一点的多条件汇总的最简单且最有效的写法。
我的写法是分开了的,所以循环的次数也会增加。
我相信肯定是有办法的,只是我这个菜鸟不会写!!!!
- Sub 求和()
- Dim arr, brr, crr, i&, j&, n&, s, k1, y&, t, t1
- Dim d As Object
- Dim k As Date
- Set d = CreateObject("scripting.dictionary")
- Range("c17:aj36").ClearContents
- k = [d1]
- k1 = [a17]
- arr = Range("a17:aj36")
- For i = 1 To UBound(arr)
- If i > "10" Then k1 = [a27]
- s = k & "|" & k1 & "|" & arr(i, 2)
- If Len(s) Then d(s) = i
- Next
- k1 = [a17]
- brr = Worksheets("生产数据").Range("A1").CurrentRegion.Resize(, 38)
- t = Array(arr, 8, 5, 7, 9, 17, 19, 26, 24, 25, 27, 34, 21)
- t1 = Array(brr, 11, 14, 15, 16, 26, 27, 13, 19, 20, 21, 28, 33)
- ReDim crr(1 To UBound(arr), 1 To 36)
- For i = 5 To UBound(brr)
- y = d(brr(i, 1) & "|" & brr(i, 6) & "|" & brr(i, 2))
- If y Then
- For j = 1 To UBound(t)
- crr(y, 2) = crr(y, 2) + 1
- arr(y, t(j)) = arr(y, t(j)) + Val(brr(i, t1(j)))
- Next j
- End If
- Next i
- For i = 1 To UBound(arr)
- If i > "10" Then k1 = [a27]
- For j = 5 To UBound(brr)
- If brr(j, 1) = k And brr(j, 6) = k1 And brr(j, 2) = arr(i, 2) And brr(j, 11) <> "" Then
- If brr(j, 1) = k And brr(j, 6) = k1 And brr(j, 2) = arr(i, 2) And brr(j, 13) <> "" Then
- arr(i, 23) = arr(i, 23) + (brr(j, 9) - brr(j, 25)) / 60
- Else
- End If
- arr(i, 4) = arr(i, 4) + (brr(j, 9) - brr(j, 24)) / 60
- End If
- Next j
- Next i
- k1 = [a17]
- For i = 1 To UBound(arr)
- For j = 3 To 36
- If arr(i, j) = "0" Then
- arr(i, j) = ""
- End If
- Next
- Next
- Range("a17:aj36").Value = arr
- Set d = Nothing
- End Sub
复制代码
生产数据.rar
(952.25 KB, 下载次数: 7)
|