[µo°Ý] Ãö©óAÄæ¬O§_¥i¥H§PÂ_¬Û¦P§Ç¸¹«á±NBÄæ¦X¨Ö¨Ã«O¯d­ì©l¼Æ¾Ú

killer77107

·Q½Ð°Ý¦U¦ì«e½ú¡A¦³¦bª©¤Wª¦¤å¨Ã¥B¦³¨Ï¥ÎChatGPT¡A¦ý¬Oµ²ªG³£»P¹w´Áªº¬Û®t¬Æ»·
·Q¬Ý¬O§_¦³¨ç¼Æ©Î»yªk¥i¥H¸Ñ¨M³o­Ó°ÝÃD¡A­ì©lÀɮפλyªk½Ð°Ñ¾\ªþ¥ó(½Ð°Ñ¾\¤À­¶-´ú¸Õ°Ï)¡AÁÂÁÂ!
°ÝÃDÂI¦p¤U
AÄæ¬O§_¥i§PÂ_¬Û¦P­È¦V¤U¶ñº¡(½Ð°Ñ¾\ÀɮפÀ­¶-»Ý¨D»¡©ú1)
§PÂ_AÄæ­Y§Ç¸¹¬Û¦P­È±NBÄæ«O¯d­ì­È¦X¨Ö¨Ã¦Û°Ê´«¦C¡AAÄæ­«½Æ­È§R°£¡C(½Ð°Ñ¾\ÀɮפÀ­¶-»Ý¨D»¡©ú2)

³Â·Ð¦U¦ì«e½ú«ü¾É¡A¬Ý¬O§_¦³¾÷·|¥i§ï¶i¥Ø«e¤H¤u³vµ§¥h§@·~ªº¤è¦¡¡A«áÄò­Y§¹¦¨·|±NÀɮפÀ¨É¤W¨Ó¡A·PÁÂ!!

Andy2483

¦^´_ 1# killer77107


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æ«e:

¤U¸ü (131.73 KB)

2023-5-4 09:45

°õ¦æµ²ªG:

¤U¸ü (150.8 KB)

2023-5-4 09:46

Option Explicit
Sub TEST()
Dim Brr, Y, i&, T$, P$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Set xR = [»Ý¨D»¡©ú1!A1].CurrentRegion: Brr = xR
For i = 2 To UBound(Brr)
   P = Brr(i, 1)
   T = Switch((T <> P) * (P <> ""), P, P = "", T)
   If T = "" Then GoTo i01
   Y(T) = Replace(Trim(Y(T) & " " & Brr(i, 2)), " ", vbLf)
i01: Next
[J1] = "=A1": [K1] = "=B1"
[J2].Resize(Y.Count, 1) = Application.Transpose(Y.keys)
[K2].Resize(Y.Count, 1) = Application.Transpose(Y.items)
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,½m²ß¤£¨Ï¥ÎkeysÂà¸mµ²ªG,¦Ó¥Hµ²ªG¸ê®ÆÂл\­ì°}¦C¸ê®Æ,¦A±Nµ²ªG¼g¤JÀx¦s®æ,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST_1()
Dim Brr, Y, i&, R&, T$, P$, xR As Range
Set Y = CreateObject("Scripting.Dictionary")
Set xR = [»Ý¨D»¡©ú1!A1].CurrentRegion: Brr = xR
For i = 2 To UBound(Brr)
   P = Brr(i, 1)
   T = Switch((T <> P) * (P <> ""), P, P = "", T)
   If T = "" Then GoTo i01
   If Y(T) = "" Then
      Y(T) = Y.Count + 1: Brr(Y(T), 1) = T: Brr(Y(T), 2) = Brr(i, 2): GoTo i01
   End If
   R = Y(T): Brr(R, 2) = Replace(Trim(Brr(R, 2) & " " & Brr(i, 2)), " ", vbLf)
i01: Next
[J1].Resize(Y.Count + 1, 2) = Brr
Set Y = Nothing: Set xR = Nothing: Erase Brr
End Sub

­ã´£³¡ªL

¬O·Q­n¨âºØ»Ý¨D??
»Ý¨D1) ¶ñº¡§Ç¸¹??
»Ý¨D2) ¦X¨Ö¬Û¦P§Ç¸¹ªºBÄ椺®e??

­Y¬O­nª½±µ¹F¨ì¦X¨Ö®ÄªG, ­ìªí¬O­þ¤@­Ó???

killer77107

·PÁÂandy2483«e½ú¡A¤U¯Z¦^¥h¬ã¨s
·Ç´£³¡ªL¤j¤j±z¦n¡A¬Oªº ¬O·Q­n¨âºØ»Ý¨D¡A­ìªí¬O¤À­¶»Ý¨D»¡©ú1 AB¨âÄæ¸ê®Æ
§Æ±æ¥i¥H°õ¦æµ²ªGÅܦ¨¤À­¶»Ý¨D»¡©ú2 ¥k°¼ªº®æ¦¡¡A·PÁ«e½ú¤£§[«ü¾É¡AÁÂÁ¡I

­ã´£³¡ªL

¦^´_ 5# killer77107


³o­þ¬O¨âºØ»Ý¨D
¬Oª½±µ±Nªí¤@...§Ç¸¹¤£³sÄò(ªÅ¥Õ--µø¬°¤W¤@­Ó§Ç¸¹¦P²Õ), Åܦ¨¦X¨Öªºªí§a!(¤@­Ó§Ç¸¹¤@¦æ, BÄæ¦X¨Ö+´«¦C)

»Ý¨D1>>ª½±µ²£¥Í¦X¨Ö
Sub ¦X¨Ö_01()
Dim Arr, i&, N&
Arr = Range([a1], [b65536].End(3))
For i = 2 To UBound(Arr)
    If Arr(i, 1) <> "" Then
       N = N + 1
       Arr(N + 1, 1) = Arr(i, 1)
       Arr(N + 1, 2) = Arr(i, 2)
    Else
       Arr(N + 1, 2) = Arr(N + 1, 2) & Chr(10) & Arr(i, 2)
    End If
Next i
[e:f].ClearContents
[e1:f1].Resize(N + 1, 2) = Arr
End Sub

Andy2483

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


    ÁÂÁ«e½ú«ü¾É¤£¥Î¦r¨åªº¤èªk

killer77107

¦^´_ 6# ­ã´£³¡ªL
©êºp ¥i¯à¬O§Ú¹ï»yªkÁÙ¤£°÷¤F¸Ñ
­ì¥ý¥H¬°­n©î¨â¬q¤~¥i¥H°µ¡A¨S·Q¨ì«e½úªº»yªk§¹¥þ¥i¥Hª½±µ¸Ñ¨M§Úªº°ÝÃD
·PÁÂ!! §Ú¦b¦n¦n¤F¸Ñ¤@¤U!!

¦^´_7#Andy2483 ·PÁ«e½ú±z´£¨Ñªº¤è¦¡¡A¤@¨Ö¾Ç²ß ÁÂÁÂ!!

Andy2483

¦^´_ 6# ­ã´£³¡ªL
¦^´_ 8# killer77107


    ÁÂÁ¨â¦ì«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß ­ã´£³¡ªL«e½úªº¤è®×,¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú«ü±Ð

Option Explicit
Sub ¦X¨Ö_01()
Dim Arr, i&, N&
'¡ô«Å§iÅܼÆ
Arr = Range([a1], [b65536].End(3))
'¡ô¥OArr¬O¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J°}¦C¸Ì
For i = 2 To UBound(Arr)
'¡ô³]¶¶°j°é
    If Arr(i, 1) <> "" Then
    '¡ô¦pªG§Ç¸¹¤£¬OªÅ¥Õ?
       N = N + 1
       '¡ô¥ONÅܼƲ֥[1
       Arr(N + 1, 1) = Arr(i, 1)
       '¡ô¥O§Ç¸¹­È±a¤JArr°}¦C²Ä1Äæ«ü©wªº¯Á¤Þ¦C¸¹¦ì¸m
       Arr(N + 1, 2) = Arr(i, 2)
       '¡ô¥O§÷®Æ­È±a¤JArr°}¦C²Ä2Äæ«ü©wªº¯Á¤Þ¦C¸¹¦ì¸m
    Else
    '¡ô§_«h
       Arr(N + 1, 2) = Arr(N + 1, 2) & Chr(10) & Arr(i, 2)
       '¡ô¥O§÷®Æ­È ±a¤JArr°}¦C²Ä2Äæ«ü©wªº¯Á¤Þ¦C¸¹¦ì¸m­È¸õ¦æªº«á­±
    End If
Next i
[e:f].ClearContents
'¡ô¥O²M°£Âµ²ªGÄ椺®e
[e1:f1].Resize(N + 1, 2) = Arr
'¡ô¥O§½³¡ªºArr°}¦C­È±a¤JÀx¦s®æ¤¤
End Sub