|
请各位帮忙修改下周汇总代码,现在使用的代码是每7天汇总成一周,数据是求的平均值现在的需求需要将平均值采用求合(累加)的形式进行汇总并算出达成率!
Sub demo()
Application.ScreenUpdating = False
md = Array(0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
Rows.EntireRow.Hidden = False
a = [a1].CurrentRegion
For i = UBound(a) To 2 Step -1
If InStr(1, a(i, 1), "M", 1) <> 0 Then 'If Mid(a(i, 1), 2, 1) = "M" Then
Range(i & ":" & i).Delete
End If
Next
a = [a1].CurrentRegion
col = UBound(a, 2)
ReDim s(1 To col)
ReDim av(1 To col)
For i = 2 To UBound(a)
d = Day(a(i, 1)): m = Month(a(i, 1))
If d = 1 Then w = 0
For j = 2 To col
If d Mod 7 = 1 Then s(j) = 0
s(j) = s(j) + a(i, j)
Next
If d Mod 7 = 0 Then
r = r + 1: w = w + 1
Cells(i + r, 1).EntireRow.Insert
Cells(i + r, 1) = m & "M" & w & "W"
For j = 2 To col
If d = 7 Then av(j) = 0
Cells(i + r, j) = s(j) / 7: av(j) = av(j) + s(j) / 7
Next
Range(Cells(i + r - 7, 1), Cells(i + r - IIf(i + md(m) - d <= UBound(a), 0, 1), 1)).EntireRow.Hidden = True
End If
If d = md(m) Then
r = r + 1
Cells(i + r, 1).EntireRow.Insert
Cells(i + r, 1) = m & "M"
For j = 2 To col
Cells(i + r, j) = (av(j) + IIf(d <> 28, s(j), 0)) / (w + d Mod 7)
Next
Range(Cells(i + r - (d - 1) Mod 7 - 1, 1), Cells(i + r - 1, 1)).EntireRow.Hidden = True
End If
Next
Application.ScreenUpdating = True
End Sub
- Sub demo()
- Application.ScreenUpdating = False
- md = Array(0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
- Rows.EntireRow.Hidden = False
- a = [a1].CurrentRegion
- For i = UBound(a) To 2 Step -1
- If InStr(1, a(i, 1), "M", 1) <> 0 Then 'If Mid(a(i, 1), 2, 1) = "M" Then
- Range(i & ":" & i).Delete
- End If
- Next
- n = 1
- a = [a1].CurrentRegion
- col = UBound(a, 2)
- ReDim s(1 To col)
- ReDim av(1 To col)
- For i = 2 To UBound(a)
- d = Day(a(i, 1)): m = Month(a(i, 1))
- If d = 1 Then w = 0
- For j = 2 To col
- If d Mod 7 = 1 Then s(j) = 0
- s(j) = s(j) + a(i, j)
- Next
- If d Mod 7 = 0 Then
- r = r + 1: w = w + 1
- Cells(i + r, 1).EntireRow.Insert
- Cells(i + r, 1) = m & "M" & w & "W"
- For j = 2 To col - 1
- If j = col Then n = 7
- If d = 7 Then av(j) = 0
- Cells(i + r, j) = s(j) / n
- Next
- Cells(i + r, j) = Cells(i + r, j - 1) / Cells(i + r, j - 2)
- Range(Cells(i + r - 7, 1), Cells(i + r - IIf(i + md(m) - d <= UBound(a), 0, 1), 1)).EntireRow.Hidden = True
- End If
- If d = md(m) Then
- r = r + 1
- Cells(i + r, 1).EntireRow.Insert
- Cells(i + r, 1) = m & "M"
- For j = 2 To col
- Cells(i + r, j) = (av(j) + IIf(d <> 28, s(j), 0)) / (w + d Mod 7)
- Next
- Range(Cells(i + r - (d - 1) Mod 7 - 1, 1), Cells(i + r - 1, 1)).EntireRow.Hidden = True
- End If
- Next
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|