|
如題:
TXET.rar
(1.66 MB, 下载次数: 14)
因前期格式設定原因,導致現在求歷史平均值有點複雜了
附件內“機種數據庫工作表”需要更新“生產數據工作表”內的17列的歷史平均值
計算方式有寫出來了,但是有點繞,且不是很符合標準寫法,怕數據累計多了后會計算錯誤
機種數據庫工作表A:A F2:N2 為求平均值條件,生產數據工作表內B:B D:D為源數據條件,Q:Q為計算列
有試過用字典先增加KEY或去重(D&B)方式計算,但都卡在了字典計算完后無法拿出來放進機種數據庫工作表對應的位置。
所以想請大哥大姐們看一下,給個案列或者是意見!!!!
下面是我寫的計算代碼,目前計算出來的結果是對的,只是寫法有點取巧。
- Private Sub Worksheet_Activate()
- 'Sub sum()
- Dim arr, brr, srr(), sht As Worksheet
- Dim d As Object
- Set sht = Sheet3
- Set d = CreateObject("scripting.dictionary")
- n = sht.Range("a65536").End(xlUp).Row
- brr = sht.Range("a5:bz" & n)
- If brr(1, 1) = "" Then
- MsgBox "临ゼ块ネ玻计沮"
- Exit Sub
- End If
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- n = Sheet5.Range("a65536").End(xlUp).Row
- Sheet5.Range("f3:n" & n).ClearContents
- arr = Sheet5.Range("a2:n" & n)
- s = 0: k = 0
- For i = 1 To 9
- m = i + 1
- s = m + 9
- t = s + 9
- For j = 1 To UBound(brr)
- tj = "L" & i
- Key = brr(j, 4)
- If brr(j, 2) = tj And brr(j, 17) <> "" Then
- If d.exists(Key) Then
- n = d(Key)
- ReDim Preserve srr(1 To 29, 1 To d.Count)
- srr(m, n) = brr(j, 2)
- srr(s, n) = srr(s, n) + brr(j, 17): srr(t, n) = srr(t, n) + 1
- Else
- k = k + 1
- d(Key) = k
- ReDim Preserve srr(1 To 29, 1 To d.Count)
- srr(1, k) = brr(j, 4): srr(m, k) = brr(j, 2)
- srr(s, k) = srr(s, k) + brr(j, 17): srr(t, k) = srr(t, k) + 1
- End If
- End If
- Next j
- Next i
- For i = 2 To UBound(arr)
- For j = 6 To 14
- m = j - 4
- s = m + 9
- t = s + 9
- For x = 1 To UBound(srr, 2)
- If arr(i, 1) = srr(1, x) Then
- If arr(1, j) = srr(m, x) Then
- arr(i, j) = srr(s, x) / srr(t, x)
- End If
- End If
- Next x
- Next j
- Next i
- Sheet5.Range("a2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
- Application.EnableEvents = True
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|