µ{¦¡¥X¿ù

mdr0465

¦U¦ì¤j¤j,§A¦n

§Ú°Ñ¦Ò¤F¥t¤@¦ì¾Ç¥Sªºµª®×­×§ï¤F¤º®e©ñ¦b§Úªº¤u§@ªí¨½, ¦ý¤p§Ì¤£¤~¬Y´X­Ó¦ì¸m³£¬O¤£©ú¥Õ«ç¼Ë¥i¥H­×§ï,½Ð¦U¦ì½ç±Ð

mdr0465

¦^´_ 5# Andy2483


    Andy  ¥S,ÁÂÁ§A´£¨Ñ¦³¥Î¸ê®Æµ¹§Ú°Ñ¦Ò, ÁÂÁÂ

Andy2483

¦^´_ 4# mdr0465


    ½Ð°Ñ¦Ò¥H¤UÃìµ²
https://learn.microsoft.com/zh-t ... help/split-function

mdr0465

¦^´_ 3# Andy2483

ÁÂÁÂAndy®v¥S¥X¤â¬Û§U, ¦ý§Ú²Ó¤ß¾Ç²ß·í¤¤ªºµ{¦¡¤¤, ¦³¤@­Ó¤£©úªº¦a¤è,§Æ±æAndy ¥S½ç±Ð

   Sub Test_a1()
Dim Arr, Brr(1 To 10000, 1 To 10), xD, T$, i&, j%, N&, R&
Sheets("Result").UsedRange.ClearContents
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Inventor!x1], [Inventor!a65536].End(3))
For i = 4 To UBound(Arr)
    If Not IsNumeric(Arr(i, 1) & "") Then GoTo i01
    T = Arr(i, 3) & "\" & Arr(i, 4) & "\" & Arr(i, 9) & "\" & Arr(i, 10) & "\" & Arr(i, 11)
    R = xD(T)
    If R = 0 Then
       N = N + 1: xD(T) = N: Brr(N, 1) = N: R = N
       For j = 2 To 10
          Brr(N, j) = Arr(i, Val(Split("//3/4/5/6/8/9/10/11/12", "/")(j)))
       Next
       GoTo i01
    End If
    Brr(R, 5) = Val(Brr(R, 5)) + Val(Arr(i, 6))
    Brr(R, 6) = Val(Brr(R, 6)) + Val(Arr(i, 8))
    Brr(R, 10) = Val(Brr(R, 10)) + Val(Arr(i, 12))
i01: Next i
If N = 0 Then Exit Sub
With [Result!a2:J2].Resize(N)
     .Rows(1).Copy .Cells
     .Value = Brr
     .Columns(2).Resize(, 9).Sort Key1:=.Item(2), Order1:=xlAscending, Header:=xlNo
End With
End Sub


§Ú¦bºô¤W§ä¨ìªºÀ³¥Î¸ê®Æ¬O Split("//3/4/5/6/8/9/10/11/12", "/",j), ¦ý¬°¦óandy ¾Ç¥S·|¥Î¨ì(Split("//3/4/5/6/8/9/10/11/12", "/")(j)
½Ð½ç±Ð,ÁÂÁÂ

Andy2483

¦^´_ 1# mdr0465


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
¥H¤U½m²ß¤è®×½Ð«e½ú°Ñ¦Ò

Option Explicit
Sub Test_a1()
Dim Arr, Brr(1 To 10000, 1 To 10), xD, T$, i&, j%, N&, R&
Sheets("Result").UsedRange.ClearContents
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Inventor!x1], [Inventor!a65536].End(3))
For i = 4 To UBound(Arr)
    If Not IsNumeric(Arr(i, 1) & "") Then GoTo i01
    T = Arr(i, 3) & "\" & Arr(i, 4) & "\" & Arr(i, 9) & "\" & Arr(i, 10) & "\" & Arr(i, 11)
    R = xD(T)
    If R = 0 Then
       N = N + 1: xD(T) = N: Brr(N, 1) = N: R = N
       For j = 2 To 10
          Brr(N, j) = Arr(i, Val(Split("//3/4/5/6/8/9/10/11/12", "/")(j)))
       Next
       GoTo i01
    End If
    Brr(R, 5) = Val(Brr(R, 5)) + Val(Arr(i, 6))
    Brr(R, 6) = Val(Brr(R, 6)) + Val(Arr(i, 8))
    Brr(R, 10) = Val(Brr(R, 10)) + Val(Arr(i, 12))
i01: Next i
If N = 0 Then Exit Sub
With [Result!a2:J2].Resize(N)
     .Rows(1).Copy .Cells
     .Value = Brr
     .Columns(2).Resize(, 9).Sort Key1:=.Item(2), Order1:=xlAscending, Header:=xlNo
End With
End Sub

mdr0465

¸É¥R¦h¤@ÂI¡A§Ú¥Øªº¬O·Q¨C¤@­Ó­q³æ¸¹¡A´Ú¸¹¡Aªø¡AÁï¡A°ª¡AÂkÃþ¦A±N­«¶q©Mcmb¥[Á`¡AÁÂÁÂ