Option Explicit
Sub TEST()
Dim Brr, Crr, Y, i&, j&, T1$, T2$, T3%
Dim Sh1 As Worksheet, Sh2 As Worksheet
Set Y = CreateObject("Scripting.Dictionary")
Set Sh1 = Sheets("原始資料"): Set Sh2 = Sheets("總表")
Brr = Range(Sh1.[C2], Sh1.Cells(Rows.Count, "A").End(3))
Sh2.[B4:R14].ClearContents: Crr = Sh2.[A3:R14]
For i = 1 To UBound(Brr)
T1 = Brr(i, 1): T2 = Format(Brr(i, 2), "M月"): T3 = Brr(i, 3)
Y(T1 & "|" & T2) = Y(T1 & "|" & T2) + T3
Next
For i = 2 To UBound(Crr) - 1
For j = 2 To 13: Crr(i, j) = 0 + Y(Crr(i, 1) & "|" & Crr(1, j)): Next
Next
With Sh2
.[A3:R14] = Crr
.[N4:N13] = "=SUM(B4:M4)"
.[O4:O13] = "=SUM(B4:D4)"
.[P4:P13] = "=SUM(E4:G4)"
.[Q4:Q13] = "=SUM(H4:J4)"
.[R4:R13] = "=SUM(K4:M4)"
.[B14:R14] = "=SUM(B4:B13)"
End With
Set Y = Nothing: Set Sh1 = Nothing: Set Sh2 = Nothing: Erase Brr, Crr
End Sub
發表於 2023-4-26 16:45
| 
