|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub 更新()
Dim nR1%, Arr(), nR2%, Brr(), nL%, dRq As Date, dRq2 As Date, Crr()
Dim ds As Object, n%, m%, s
Set ds = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1")
nR1 = .Range("c1048576").End(xlUp).Row
Arr = .Range("c1:o" & nR1).Value
End With
With Sheets("Sheet2")
nR2 = .Range("b1048576").End(xlUp).Row
nL = .Range("xfd1").End(xlToLeft).Column
Crr = .Range("b1").Resize(1, nL).Value
If nR2 > 1 Then dRq = .Range("b" & nR2).Value
For i = 2 To nL - 1
ds(Crr(1, i)) = i
Next
ReDim Brr(1 To nR1, 1 To 5)
For i = 2 To nR1
If Arr(i, 1) > dRq Then
If dRq2 <> Arr(i, 1) Then
m = m + 1
Brr(m, 1) = Arr(i, 1)
dRq2 = Arr(i, 1)
End If
s = Arr(i, 9) / IIf(Arr(i, 13) = "", 1, 2)
For j = 12 To 13
If ds.exists(Arr(i, j)) Then
n = ds(Arr(i, j))
Brr(m, n) = Brr(m, n) + s
End If
Next
End If
Next
If m > 0 Then
.Range("b" & nR2 + 1).Resize(m, nL - 1).Value = Brr
End If
End With
End Sub
|
|