Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

¸ê®Æªí:

¤U¸ü (91.5 KB)

2023-4-14 14:19

µ²ªGªí °õ¦æ«e:

¤U¸ü (34.35 KB)

2023-4-14 14:20

°õ¦æµ²ªG:

¤U¸ü (52.7 KB)

2023-4-14 14:20

Option Explicit
Sub TEST()
Dim Brr, Y, R&, i&, j&, T$, T0$
Dim xR As Range, Sh1 As Worksheet, Sh2 As Worksheet
Set Y = CreateObject("Scripting.Dictionary")
Set Sh1 = Sheets("¤u§@ªí1"): Set Sh2 = Sheets("Output")
Set xR = Sh1.[A1].CurrentRegion: Brr = xR
For i = 2 To UBound(Brr)
   T = Trim(Brr(i, 2))
   If T = "" Then GoTo i01
   If T Like "C### [A-Z]*" Then
      R = R + 1
      T0 = Split(T, " ")(0)
      Y(T0 & "|r") = R: Y(T0 & "|n") = 1: Y(T0 & "|tt") = Brr(i, 3)
      For j = 1 To 5: Brr(R, j) = Brr(i, j): Next
      GoTo i01
   End If
   Y(T & "|n") = Y(T & "|n") + 1
   Y(T & "|tt") = Y(T & "|tt") + Brr(i, 3)
   Brr(Y(T & "|r"), 3) = Round(Y(T & "|tt") / Y(T & "|n"), 2)
   Brr(Y(T & "|r"), 4) = Brr(Y(T & "|r"), 4) + Brr(i, 4)
   Brr(Y(T & "|r"), 5) = Brr(Y(T & "|r"), 5) + Brr(i, 5)
   
i01:
Next
[5:65536].Clear
Sh2.[A5].Resize(R, 5) = Brr
Set Y = Nothing: Set xR = Nothing: Erase Brr
Set Sh1 = Nothing: Set Sh2 = Nothing
End Sub