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

¤jµ§¸ê®Æ´«¦æ³B²z»P¼Æ¦r¤å¦r¤À¹j°ÝÃD

¤jµ§¸ê®Æ´«¦æ³B²z»P¼Æ¦r¤å¦r¤À¹j°ÝÃD

¦U¦ì«e½ú¦n,

¤p§Ì¥Ø«e¹J¨ì¤@­Ó°ÝÃD,

ªþ¤WÀɮ׳sµ²:
ÂIÀ»¤U¸ü

ÀÉ®×»¡©ú¦p¤U:
AÄ檺¸ê®Æ®æ¦¡¬°¥Ü·N,¹ê»Ú¤W¦³«D±`¦hµ§,
¸ê®Æ®æ¦¡¬°¤å¦r+¼Æ¦r
(¤å¦r¥þ³¡¥´"¤å¦r"¶È¬°¥Ü·N¤å¦r³¡¥÷¤§¥Î,¸ê®Æªº¤å¦r¤º®e¦U¤£¬Û¦P,¤£¬O»¡¸ê®Æ¯uªº³£¬O¥H"¤å¦r"¶}ÀY¥[¼Æ¦r)
,¤å¦r©M¼Æ¦rªºªø«×§¡¤£©T©w.
¤å¦r+¼Æ¦rµø¬°¤@µ§¸ê®Æ,
¤£¦Pµ§¸ê®Æ¤§¶¡¥HªÅ®æ©Î´«¦æ¹j¶},¤ñ¦p»¡¤Tµ§¸ê®Æ,
¨º´N¬O¤å¦r+¼Æ¦r ¤å¦r+¼Æ¦r ¤å¦r+¼Æ¦r,
ªÅ®æ¤]¦³¥i¯à¬O´«¦æ.
§Æ±æ¹F¨ìªº®ÄªG¬O§âAÄæ©Ò¦³ªº¸ê®Æªº¤å¦r³¡¥÷»P¼Æ¦r³¡¥÷°Ï¹j¶}¨Ó,
©ñ¦bÀx¦s®æ¤º.

¤å¦r©ñ¦bFÄæ,

¼Æ¦rªº³¡¥÷,

­Y¼Æ¦r¶}ÀY¤£¬O1,«h©ñ¦bGÄæ,¨Ã¥B¦b¼Æ¦r¶}ÀY¥[¤WG1Àx¦s®æ¤º¶ñ¤Jªº¼Æ¦r.

G1¥Ñ¨Ï¥ÎªÌ¤â°Ê¿é¤J,­Y¬°ªÅ¥Õ,«h¼Æ¦r¶}ÀY¤£¥[¥ô¦ó¼Æ¦r,ª½±µ©ñ¦bGÄæ.

­Y¼Æ¦r¶}ÀY¬°1,«hª½±µ±N¼Æ¦r¤£°µ¥ô¦ó­×§ï©ñ¦bIÄæ.


¥Ø«eªº³o­ÓÀÉ®×¥i¥H¹F¨ìÃþ¦ü®ÄªG,

¦ý¦³¥H¤U¯ÊÂI:

1.µLªk§R°£­«½Æ¸¹½X.§Æ±æ¥i¥H¦³¤ñ¹ï¨Ã§R°£­«½Æ¸¹½Xªº®ÄªG.

2.¹Bºâ¹LºC.¦]¬°±Ä¥Î¥¨¶°»P¤½¦¡¦X¨Öªº¤è¦¡,
§âAÄ椣¦Pµ§¸ê®Æ°µ´«¦æ³B²z¨Ã¶K¦ÜEÄ檺µ{¦¡½X¬O¦bºô¸ô¤W¸ß°Ý°ª¤â®É±o¨ìªºµª®×,
§Ú¥u¬OÂà¶K¹L¨Ó¥Î¦Ó¤w,³o³¡¥÷¨ä¹ê¦b¹B§@¤W¨S¦³°ÝÃD.

±N¼Æ¦r»P¤å¦r¤À¶},«h¬O§Ú±qºô¸ô¤W¬d§ä¨ìªº°ª¤â©Ò¼gªº¤½¦¡,
§â¥¦®M¹L¨Ó¨Ï¥Î,

¦ý¬O§Ú¥u§â¤½¦¡¤U©Ô¤F¤­¦Ê¦hµ§,
¥ú¬O³o¼Ë¤l¹Bºâ´N«Üªá®É¶¡,©Ò¥H³o­ÓÀÉ®×µLªk³B²z¤j¶qªº¸ê®Æ,

¤£ª¾¬O§_¦³«e½úÄ@·N«üÂI¸Ó¦p¦ó§ï¦¨¥þ³¡¥¨¶°ªºª©¥»,
´£°ª¥¦ªº¹Bºâ³t«×¨ÃÅý¥¦¥i³B²z¤j¶qªº¸ê®Æ,

¤Q¤À·PÁÂ.

  1. Sub Test()
  2. Dim A, B, T$, Brr(1 To 6000, 1 To 4), xD, N&, u, v
  3. [F2:I6000].ClearContents
  4. Set xD = CreateObject("Scripting.Dictionary")
  5. For Each A In Range([A2], [A65536].End(xlUp)).Value
  6. For Each B In Split(Replace(A, Chr(10), " "), " ")
  7.     For i = 1 To Len(B) + 1
  8.         If Not IsNumeric(Right("x" & B, i)) Then v = i - 1: Exit For
  9.     Next
  10.     If v = 0 Then GoTo 101
  11.     T = Right(B, v): If xD(T) > 0 Then GoTo 101
  12.     u = 4: If Left(T, 1) = "1" Then u = 2: T = [G1] & T
  13.     N = N + 1: Brr(N, 1) = Left(B, Len(B) - v): Brr(N, u) = T
  14.     xD(T) = 1: v = 0
  15. 101: Next
  16. Next
  17. If N > 0 Then [F2].Resize(N, 4) = Brr
  18. End Sub
½Æ»s¥N½X
Xl0000047.rar (14.61 KB)

TOP

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


«D±`·PÁ²a´£³¡ªL«e½ú¼ö¤ßµL¨p´£¨Ñµ{¦¡½X,

¥i¯à¬O¤p§Ì´y­z°ÝÃD®Éªí¹F¤£²M·¡³y¦¨»~·|,¤£¦n·N«ä,

§Æ±æªº®ÄªG¬O­Y¼Æ¦r¶}ÀY¤£¬O1¤~¥[¤WG1Àx¦s®æ¤ºªº¼Æ¦r,

¥H«á¤p§Ì·|¾¨¶q±N°ÝÃD¦A°µ§ó¨ãÅ骺´y­z,½Ð«e½ú¨£½Ì.


¤p§Ì¾Õ¦Û±N²Ä¤Q¤G¦æIf Left(T, 1) = "1"§ï¬°<>"1"¤§«á,

½T¹ê¹F¨ì´Á±æ®ÄªG,³t«×¯uªº¤ñ­ì¤å§ÚªþªºÀɮק֤Ӧh¤F,

¦Ó¥B¸ò¥ý«eµ{¦¡¬Û¤ñ,

«e½ú©Ò´£¨Ñªºµ{¦¡½XÁÙ¥[¤W¤F§R°£­«½Æ¼Æ¦r¤Î§P§O¤å¦r¦³µL»P¼Æ¦r¬Û³sµ¥±j¤j¥\¯à,¥i¤j´TÁYµu¸ê®Æªº¤ñ¹ï»P¾ã²z®É¶¡,

¦A¦¸·PÁ«e½ú.


¨ä¹ê¤p§Ì¦b²Ä¤K¦æ¤§«á´N¤w¸g§¹¥þµLªk²z¸Ñ¤F,
­Y¤è«Kªº¸Ü¬O§_«_¬N½Ð±Ð«e½ú,

¦³Ãö²Ä¤K¦æµ{¦¡½Xªº³¡¥÷,
If Not IsNumeric(Right("x" & B, i)) Then v = i - 1: Exit For

³o­ÓRight("x" & B, i)ªº³¡¥÷,
¬°¦ó¥i¥H¨Ï¥Î"x" & B,¤p§Ì¤£¤Ó²z¸Ñ³o­Ó"x"ªº¥Îªk,
¤£ª¾¬O§_¯à°÷«_¬N³Â·Ð«e½ú¤£§[«üÂI°g¬z,¤Q¤À·PÁÂ.

TOP

¦^´_ 3# greetingsfromtw


±q¤å¦r¦ê¥k¤è¶}©l¨ú¼Æ¦r, ·í¹J«D¼Æ¦r®É, ¨ú±o«D¼Æ¦rªº¦ì¸m¦A´î¤@, §Y¬°¼Æ¦rªø«×,
­Y¸Ó¤å¦r¦ê¬°¡e¯Â¼Æ¦r¡f©Î¡eªÅ®æ¡f¡A·|µo¥Í¿ù»~¡A¦b¤å¦r¦ê«e±j¨î¥[¤@­Ó"x"©Î¥ô¤@¤å¦r¡A´N¥i¥HÁקK¤F¡I

TOP

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

¯uªº¤Q¤À·PÁ²a´£³¡ªL«e½úªº¼ö¤ß«üÂI.

¤p§Ì¬O§_¤è«K¦A¶i¤@¨B¸ß°Ý,
Ãö©ó²Ä11¦æ¦Ü²Ä14¦æ,

¤p§Ì¦³¸ÕµÛ¥hºô¤W·j¯Á¬ÛÃö¸ê®Æ,
¨ä¤¤Ãö©ó«_¸¹ªº³¡¥÷,

¦b½×¾Â¤W¤w¦³«e½ú°µ¸Ô²Óªº¸Ñ»¡:
http://forum.twbts.com/viewthrea ... ighlight=%B0%7D%A6C

¦ýdictionary objectªº³¡¥÷,
¤p§Ì¤£¤Ó²z¸ÑxD(T) > 0³oºØ¼gªk,
¦]ºô¸ô¤W¬d¨ìªº·|»Ý­n¥Îitem©Î¬Oitems¤èªk.
¥t²Ä12¦æ,
¤p§Ì¤]¤£²z¸Ñ¦ó¥H±N Left(T, 1) = "1"
§ï¬°Left(T, 1) <> "1"¤§«á,
´N¥i¥H±NÅã¥Üµ²ªG¹ï½Õ,¦]¬°§Ú¨Ã¨S¦³¥h§ó°Êuªº­È.

¨ä¥¦³¡¥÷,¤p§Ì­W«ä¨}¤[,§ä´M¸ê®Æ«á¤´¤£±o¨ä¸Ñ,


¤p§Ì¤æÁx,

¬O§_¯à°÷«_¬N½Ð¨D«e½ú²¤¬°¸ÑÄÀ

²Ä11¦æ¦Ü²Ä14¦æªº·N¸q,

ÁÙ±æ«e½ú¯à°÷¤£§[½ç±Ð,¤Q¤À·PÁÂ.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-10-10 10:24 ½s¿è

¦^´_ 5# greetingsfromtw


T = Right(B, v)  >>> v¬°¼Æ¦r¦ì¼Æ, ¨ú¥X¥k¤è¦r¤¸¼Æ
If xD(T) > 0 Then GoTo 101  >>> xD(Key) ¥i¥HÀË¥X¨äItem­È, ­Y¬O·sªºKey, ¨ä­È¬°0©Î"", §_«h¬°ÂÂKey, ²¤¹L¤£­p

u = 4 >>> ¼Æ¦r[¹w³]]©ñ¦b i Äæ, §Y°}¦C²Ä 4 Äæ
If Left(T, 1) <> "1" Then u = 2: T = [G1] & T >>> ·í­º¦r¤¸¤£¬°1, §ï©ñ¤_²Ä2Äæ, ¼Æ¦r«e¥[«ü©w¤å¦r

N = N + 1 >>> ¨ú¥X¸ê®Æ²Ö­p¼Æ
Brr(N, 1) = Left(B, Len(B) - v) >>> ¤å¦r¦êÁ`ªø«× - ¼Æ¦rªø«× = ¤å¦rªø«×, ¥Ñ¥ª§ì¨ú
Brr(N, u) = T

xD(T) = 1 >>> ·sªºKey, ¤@«ß«a¤W1¬°¨äItem­È, ¤U¦¸´N¥i¥H°µ¬°¤ñ¹ï¥Î (³o¬O¦r¨åÀÉ¥t¤@¥Îªk)
v = 0 >>> ±N v Âk0, ¨Ñ¤U¤@´`Àô­«·s§PÂ_½á­È

TOP

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


¯uªº«D±`·PÁ­㴣³¡ªL«e½úÄ@·Nªá®É¶¡¤@¦æ¤@¦æ«üÂI¤p§Ì³o¼Ë¤£ªø¶iªº·s¤â,
¤p§Ì¤£³Ó·P¿E.

¯à¥[¤J³o­Ó½×¾Â¹ê¦b¬O¤Ó¦n¤F,¤@©w§V¤O¾Ç²ß.

¦A¦¸·PÁ­㴣³¡ªL«e½úªºµL¨p¨ó§U,¯uªº«ÜÁÂÁ±z.

TOP

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


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

°õ¦æ«e:


°õ¦æµ²ªG:



Sub Test()
Dim A, B, T$, Brr(1 To 6000, 1 To 4), xD, N&, u, v
'¡ô«Å§iÅܼÆ
[F2:I6000].ClearContents
'¡ôµ²ªGÄæÀx¦s®æ ²M°£¤º®e
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxDÅܼƬO ¦r¨å
For Each A In Range([A2], [A65536].End(xlUp)).Value
'¡ô³]³v¶µ°j°é!¥OA¬OAÄæ¸ÌªºÀx¦s®æ­È
For Each B In Split(Replace(A, Chr(10), " "), " ")
'¡ô³]³v¶µ°j°é!¥OBÅܼƬO AÅܼƳQ¤À³Î¦¨¤@ºû°}¦Cªº¤@­Ó°}¦C­È
    For i = 1 To Len(B) + 1
    '¡ô³]¶¶°j°é
        If Not IsNumeric(Right("x" & B, i)) Then v = i - 1: Exit For
        '¡ô±q¦r¦êªº¥k©¹¥ª°¼§ä«D¼Æ¦rªº¦r¤¸¦ì¸mi,¥OvÅܼƬO¦r¤¸¦ì¸m-1
    Next
    If v = 0 Then GoTo 101
    '¡ô¦pªG«D¼Æ¦rªº¦r¤¸¬O¦r¦êªº³Ì«á¤@­Ó¦r!´N¸õ¨ì101¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
    T = Right(B, v): If xD(T) > 0 Then GoTo 101
    '¡ô¥OTÅܼƬOBÅܼƥk°¼ªº³sÄò¼Æ¦r,
    '¡ô¦pªGTÅܼƬdxD¦r¨å±oitem­È ¤j©ó0 !
    '´N¸õ¨ì101¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ(Âo¼Æ¦rªº­«½Æ)

    u = 4: If Left(T, 1) = "1" Then u = 2: T = [G1] & T
    '¡ô¥OuÅܼƬO 4(¹w³]),
    '¦pªGTÅܼƲÄ1­Ó¦r¬O1,¥OuÅܼƬO 2,¥B¥OTÅܼƫe­±²K¥[[G1]Àx¦s®æ­È

    N = N + 1: Brr(N, 1) = Left(B, Len(B) - v): Brr(N, u) = T
    '¡ô¥ONÅܼƲ֥[ 1,¥O¥ª°¼¤å¦r¼g¤JBrr°}¦C²Ä1Äæ,
    '¥O¥k°¼¤å¦r¨ÌN/uÅܼƼg¤JBrr°}¦C

    xD(T) = 1: v = 0
    '¡ô¥O¥HTÅܼƷíkey,item¬O1,¯Ç¤JxD¦r¨å¸Ì(µ¹«á­±°j°éÂo¼Æ¦rªº­«½Æ)
101: Next
Next
If N > 0 Then [F2].Resize(N, 4) = Brr
'¡ô¦pªG¦³¸ê®Æ!´N¥OBrr°}¦C­È±a¤J±q[F2]¶}©lªººë½T½d³òÀx¦s®æ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD