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

[µo°Ý] §ä¥X­«Âиê®Æ

ARRAY ³B²z¸ê®Æ
RANGE-UNION¶ñ¦â
Xl0000207.rar (52.52 KB)

TOP

¦^´_ 8# ML089

ML089®v¥S¡A
¯uªºÁÂÁ§AÀ°¦£

TOP

¦^´_ 9# °a¤ªºµ
   
°a¤ªºµ®v¥S¡A
·PÁ§A¦Ê¦£¤¤À°¦£¡AÁÂÁ§A

TOP

¦^´_ 10# samwang


   
Samsung ®v¥S¡A
¸U¤À·PÁ§AÀ°¦£¡AÁÂÁÂ

TOP

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


  
­ã´£³¡ªL®v¥S¡A
¯uªº¤Q¤À·P¿E§AªºÀ°¦£¡AÁÂÁ§A

TOP

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æ«e:


°õ¦æµ²ªG:



Sub TEST_A01()
Dim Arr, xD, i&, T$, T1$, T2$, SR, S, xR As Range, xU As Range
'¡ô«Å§iÅܼÆ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO ¦r¨å
With Range([J1], [A65536].End(3))
'¡ô¥H¤U¬OÃö©ó¥»ªíA~JÄæÀx¦s®æªºµ{§Ç
     .EntireRow.Interior.ColorIndex = xlNone
     '¡ô¥O¸Ó°Ï°ì¥þ¦C©³¦â¬OµL¦â
     .Offset(1, 7).ClearContents
     '¡ô¥O¸Ó°Ï°ì©¹¤U°¾²¾1¦C,©¹¥k7Äæ°Ï°ìÀx¦s®æ²M°£¤º®e
     [H1:J1] = Array("­«ÂЦì¸m", "­«ÂЦ¸¼Æ", "¹ïÀ³³õ¦WºÙ")
     '¡ô¥O[H1:J1]Àx¦s®æ¼g¤J¦C¼ÐÃD
     Arr = .Cells
     '¡ô¥OArrÅܼƬO ¤Gºû°}¦C,¥H¸Ó°Ï°ìÀx¦s®æ­È±a¤J°}¦C¤¤
End With
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    T = Arr(i, 4): T2 = Arr(i, 6)
    '¡ô¥O¦r¦êÅܼƸˤJ°}¦C­È
    xD(T) = Trim(xD(T) & " " & i)
    '¡ô¥OTÅܼƷíkey,item¬O ¦Û¨­³s±µªÅ¥Õ¦r¤¸,¦A³s±µiÅܼÆ,©Ò²Õ¦¨ªº·s¦r¦ê
    If T2 <> "" Then xD(T & "/y") = T2
    '¡ô¦pªGT2ÅܼƤ£¬OªÅ¦r¤¸!´N¥OTÅܼƳs±µ"/y"²Õ¦¨ªº·s¦r¦ê·íkey,
    'item¬OT2ÅܼÆ,¯Ç¤JxD¦r¨å¤¤

Next i
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    SR = Split(xD(Arr(i, 4) & ""), " ")
    '¡ô¥OSRÅܼƬO¤@ºû°}¦C:¥H°}¦C²Ä4Äæ­È´£¨úxD¦r¨åitem,
    '¦A¥HªÅ¥Õ¦r¤¸¤À³Î¦¨¬°¤@ºû°}¦C

    If UBound(SR) <= 0 Then GoTo i01
    '¡ô¦pªGSR°}¦C³Ì«á¤@­Ó¯Á¤Þ¸¹<=0,´N¸õ¨ì¼Ð¥Üi0¦ì¸mÄ~Äò°õ¦æ
    T1 = "": T2 = "": Set xR = Range("D" & i)
    '¡ô¥OT1,T2ÅܼƬO ªÅ¦r¤¸,¥OxRÅܼƬO DÄæi¦CÀx¦s®æ
    For Each S In SR
    '¡ô³]³v¶µ°j°é!¥OSÅܼƬOSR°}¦C­È¤§¤@
        If Val(S) <> i Then
        '¡ô¦pªGSÅܼÆÂà¼Æ­È«á »PiÅܼƤ£¦P
           T1 = T1 & "," & "D" & S
           '¡ô¥OT1ÅܼƬO ¦Û¨­³s±µ³r¸¹,¦A³s±µ"D",³Ì«á³s±µSÅܼƦ¨·s¦r¦ê
           T2 = T2 & "," & Arr(S, 1)
           '¡ô¥OT2ÅܼƬO ¦Û¨­³s±µ³r¸¹,¦A³s±µSÅܼƦC²Ä1ÄæArr°}¦C­È
        End If
    Next S
    Arr(i, 6) = xD(Arr(i, 4) & "/y")
    '¡ô¥O°j°é¦C²Ä6ÄæArr°}¦C­È¬O °j°é¦C²Ä6ÄæArr°}¦C­È³s±µ"/y"¦¨ªº·s¦r¦ê,¬d
    '¬dxD¦r¨å¦^¶Çªºitem­È

    Arr(i, 8) = Mid(T1, 2)
    '¡ô¥O°j°é¦C²Ä8ÄæArr°}¦C­È¬O T1Åܼƨú²Ä2¦r¥H«áªº¥þ³¡¦r¦ê
    Arr(i, 9) = UBound(SR) + 1
    '¡ô¥O°j°é¦C²Ä9ÄæArr°}¦C­È¬O SR°}¦C³Ì¤j¯Á¤Þ¸¹+1
    Arr(i, 10) = Mid(T2, 2)
    '¡ô¥O°j°é¦C²Ä10ÄæArr°}¦C­È¬O T2Åܼƨú²Ä2¦r¥H«áªº¥þ³¡¦r¦ê
    If xU Is Nothing Then Set xU = xR Else Set xU = Union(xU, xR)
    '¡ô¦pªGxUÅܼƬOªÅªº,´N¥OxUÅܼƬOxRÅܼÆ,§_«h´N±NxRÅܼƯǤJxUÀx¦s®æ¶°¸Ì
i01: Next i
[a1].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
'¡ô¥OArr°}¦C±q[A1]¶}©l¼g¤J½d³òÀx¦s®æ¤¤
If Not xU Is Nothing Then xU.EntireRow.Interior.ColorIndex = 6
'¡ô¦pªGxUÅܼƤ£¬OªÅªº,´N¥O¸ÓxUÀx¦s®æ¶°©Ò¦bªº¦C¾ã¦C©³¦â¬°¶À¦â
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD