¤jµ§¸ê®Æ´«¦æ³B²z»P¼Æ¦r¤å¦r¤À¹j°ÝÃD
- ©«¤l
- 45
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 59
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-6
- ³Ì«áµn¿ý
- 2019-6-22
|
¤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ÁÂ. |
|
|
|
|
|
|
- ©«¤l
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-5
|
- Sub Test()
- Dim A, B, T$, Brr(1 To 6000, 1 To 4), xD, N&, u, v
- [F2:I6000].ClearContents
- Set xD = CreateObject("Scripting.Dictionary")
- For Each A In Range([A2], [A65536].End(xlUp)).Value
- For Each B In Split(Replace(A, Chr(10), " "), " ")
- For i = 1 To Len(B) + 1
- If Not IsNumeric(Right("x" & B, i)) Then v = i - 1: Exit For
- Next
- If v = 0 Then GoTo 101
- T = Right(B, v): If xD(T) > 0 Then GoTo 101
- u = 4: If Left(T, 1) = "1" Then u = 2: T = [G1] & T
- N = N + 1: Brr(N, 1) = Left(B, Len(B) - v): Brr(N, u) = T
- xD(T) = 1: v = 0
- 101: Next
- Next
- If N > 0 Then [F2].Resize(N, 4) = Brr
- End Sub
½Æ»s¥N½X
Xl0000047.rar (14.61 KB)
|
|
|
|
|
|
|
- ©«¤l
- 45
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 59
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-6
- ³Ì«áµn¿ý
- 2019-6-22
|
¦^´_ 2# ã´£³¡ªL
«D±`·PÁ²a´£³¡ªL«e½ú¼ö¤ßµL¨p´£¨Ñµ{¦¡½X,
¥i¯à¬O¤p§Ì´yz°ÝÃD®Éªí¹F¤£²M·¡³y¦¨»~·|,¤£¦n·N«ä,
§Æ±æªº®ÄªG¬OY¼Æ¦r¶}ÀY¤£¬O1¤~¥[¤WG1Àx¦s®æ¤ºªº¼Æ¦r,
¥H«á¤p§Ì·|¾¨¶q±N°ÝÃD¦A°µ§ó¨ãÅ骺´yz,½Ð«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ÁÂ. |
|
|
|
|
|
|
- ©«¤l
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-5
|
¦^´_ 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 |
|
|
|
|
|
|
- ©«¤l
- 45
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 59
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-6
- ³Ì«áµn¿ý
- 2019-6-22
|
¦^´_ 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ÁÂ. |
|
|
|
|
|
|
- ©«¤l
- 2831
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 2887
- ÂI¦W
- 0
- §@·~¨t²Î
- ¡e²¤¡f
- ³nÅ骩¥»
- ¡e²¤¡f
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¡e²¤¡f
- µù¥U®É¶¡
- 2013-5-13
- ³Ì«áµn¿ý
- 2024-11-5
|
¥»©«³Ì«á¥Ñ ã´£³¡ª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Â_½áÈ |
|
|
|
|
|
|
- ©«¤l
- 45
- ¥DÃD
- 10
- ºëµØ
- 0
- ¿n¤À
- 59
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2014-2-6
- ³Ì«áµn¿ý
- 2019-6-22
|
¦^´_ 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. |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 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
|
|
|
|
|