ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦P¤@µo²¼¸¹½X¦C¥X©Ò¦³­q³æ½s¸¹(¦C¦b¦P¤@Àx¦s®æ)

¦hÄ榡:
Sub test_02()
Dim Arr, Brr, xD, i&, T$, T2$, R&, C%, Cx%, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 200)
For i = 2 To UBound(Arr)
    T = Arr(i, 1): T2 = Arr(i, 2)
    If T = "" Or T2 = "" Then GoTo 99
    R = xD(T):  C = xD(T & "/c")
    If R = 0 Then N = N + 1: R = N + 1: xD(T) = R: Brr(R, 1) = Arr(i, 1)
    C = C + 1: xD(T & "/c") = C: Brr(R, C + 1) = T2
    If C > Cx Then Cx = C: Brr(1, Cx + 1) = "­q³æ(" & Cx & ")"
99: Next i
Brr(1, 1) = "µo²¼¸¹½X"
Range("g1").Resize(N + 1, Cx + 1) = Brr
End Sub

TOP

¦^´_ 11# ­ã´£³¡ªL


    ÁÂÁ«e½ú
¤@¦~¦h¤F,²{¦b¤~¤j·§¬ÝÀ´
°õ¦æµ²ªG:
µ²ªG.jpg
2023-1-5 16:45



Option Explicit
Sub test_02()
Dim i&, N&, R&, T$, T2$, C%, Cx%, Arr, Brr, xD
'¡ô«Å§iÅܼÆ(i,N,R)¬Oªø¾ã¼ÆÅܼÆ,(T,T2)¬O¦r¦êÅܼÆ,(C,Cx)¬Oµu¾ã¼ÆÅܼÆ,
'¨ä¥¦¬O³q¥Î«¬ÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD¬O ¦r¨å
Arr = Range([a1], [b65536].End(3))
'¡ô¥OArr¬O¤Gºû°}¦C!¥H[A1]¨ìBÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ,³o½d³òÀx¦s®æ­È±a¤J
ReDim Brr(1 To UBound(Arr), 1 To 200)
'¡ô«Å§iBrr°}¦C½d³ò!Áa¦V±q1¨ìArr°}¦C³Ì¤j¯Á¤Þ¦C¸¹,¾î¦V±q1¨ì200
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q2¨ì Arr°}¦C³Ì¤j¯Á¤Þ¦C¸¹
    T = Arr(i, 1)
    '¡ô¥OT³o¦r¦êÅܼƬO i°j°é¦C1ÄæArr°}¦C­È
    T2 = Arr(i, 2)
    '¡ô¥OT2³o¦r¦êÅܼƬO i°j°é¦C2ÄæArr°}¦C­È
    If T = "" Or T2 = "" Then GoTo 99
    '¡ô¦pªGT¦r¦êÅܼƬO ªÅ¦r¤¸ ©Î  ¦pªGT2¦r¦êÅܼƬO ªÅ¦r¤¸,´N¸õ¨ì99¦ì¸mÄ~Äò°õ¦æ
    R = xD(T)
    '¡ô¥OR³oªø¾ã¼ÆÅܼƬO ¥HT¦r¦êÅܼƬdxD¦r¨å¦^¶Çªºitem­È  (PS:­Y¬d¤£¨ì!Rªì©l­È¬O 0)
    C = xD(T & "/c")
    '¡ô¥OC³oµu¾ã¼ÆÅܼƬO ¥HT¦r¦êÅܼƳs±µ"/c"ªº·s¦r¦ê,¬dxD¦r¨å¦^¶Çªºitem­È
    '(PS:­Y¬d¤£¨ì!Cªì©l­È¬O 0)
    If R = 0 Then
    '¡ô¦pªGRÅܼƬO 0 ??
       N = N + 1
       '¡ô¥ON³oªø¾ã¼ÆÅܼƬO ¦Û¨­­È +1  (PS:Nªì©l­È¬O 0)
       R = N + 1
       '¡ô¥ORÅܼƬO NÅÜ¼Æ +1
       xD(T) = R
       '¡ô¥O¥HTÅܼƷíkey,item¬O RÅܼÆ,©ñ¦^¦r¨å
       Brr(R, 1) = Arr(i, 1)
       '¡ô¥OÅܼƦC1ÄæBrr°}¦C­È¬O i°j°é¦C1ÄæArr°}¦C­È
    End If
    C = C + 1
    '¡ô¥OCÅܼƬO ¦Û¨­­È +1
    xD(T & "/c") = C
    '¡ô¥O¥HTÅܼƳs±µ"/c"ªº·s¦r¦ê·íkey,item¬O CÅܼÆ,©ñ¤J¦r¨å
    Brr(R, C + 1) = T2
    '¡ô¥ORÅܼƦC(C1ÅܼÆ+1)ÄæBrr°}¦C­È¬O T2¦r¦êÅܼÆ
    If C > Cx Then Cx = C: Brr(1, Cx + 1) = "­q³æ(" & Cx & ")"
    '¡ô¦pªGCÅÜ¼Æ > Cx³oµu¾ã¼ÆÅܼÆ,´N¥OCxÅܼƬO CÅܼÆ,
    '1¦C(CxÅܼÆ+1)ÄæBrr°}¦C­È¬O "­q³æ(" ³s±µ CxÅÜ¼Æ ¦A³s±µ ")" ²Õ¦¨ªº·s¦r¦ê

99: Next i
Brr(1, 1) = "µo²¼¸¹½X"
Range("g1").Resize(N + 1, Cx + 1) = Brr
'[G1]Àx¦s®æÂX®i¦V¤U(NÅܼÆ+1)¦C,¦V¥kÂX®i(CxÅܼÆ+1)Äæ,³o½d³òÀx¦s®æ­È¥HBrr°}¦C­È±a¤J
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : §ïÅܦۤv¬O¦Û±Ï¡A¼vÅT§O¤H¬O±Ï¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD