¦U¦ì«e½ú¦n,
¤p§Ì³Ìªñ¦b½m²ß®É¹J¨ì¤@Ó°ÝÃD,
ªþ¤WÀÉ®×¥H¨Ñ«e½ú°Ñ¦Ò:
20161115_(¦r¨åª«¥ó)³æ®æ¦h¦æÀx¦s®æ¤À¹j¬°¦h¦æ³æ®æÀx¦s®æ°ÝÃD.zip (9.86 KB)
±N°ÝÃD¥H¤å¦r²z¦p¤U:
°²³]¦³¨âª½Äæ¸ê®Æ,
AÄ欰¤@¯ë¤å¦r,BÄ檺¨CÓÀx¦s®æ§¡¦³¦hµ§¸ê®Æ,¨Cµ§¸ê®Æ¦³´«¦æ,
¥Ü·N¦p¤U:
BÄæ
111
222
333
¥H¤W¤Tµ§¼Æ¦r§¡¦b¦P¤@ÓÀx¦s®æ¤¤.
§Æ±æ¥i¥H±NBÄ檺¨Cµ§¸ê®Æ¤À¹j¦Ü³æ¤@Àx¦s®æ,¶K¦ÜFÄæ,
¨Ã±NAÄ檺¸ê®Æ¨Ì¾ÚBÄ檺¸ê®Æ¥h¶K¤W,
©Ó¤W¨Ò,
°²³]¤W¨Ò¤§BÄæ¹ïÀ³¤§AÄæÀx¦s®æ¬°"AAA",
«h´Á±æµ²ªG¦p¤U:
AÄæ BÄæ
AAA 111
AAA 222
AAA 333
¤p§Ì«_¬Nɥν׾«e½ú¥H¤Îºô¤W¨ä¥LªB¤Íªºµ{¦¡½X¶i¦æקï,
ק蠟µ{¦¡½X¦p¤U:- Sub test()
- '¦¹¬°°Ñ¦Ò½×¾Â«e½ú¤Îºô¤W¨ä¥LªB¤Í©Ò´£¨Ñ¤§µ{¦¡½X¶i¦æקï,
- '«D§Úì³Ð
- '½×¾Âºô§};http://forum.twbts.com/thread-18600-1-1.html
- Dim Arr, Brr(1 To 65536, 1 To 1), myD, C, UC
- 'Ãö³¬¿Ã¹õ§ó·s
- Application.ScreenUpdating = False
- Set myD = CreateObject("scripting.dictionary")
- Arr = Range("a2:c" & Cells(Rows.Count, "a").End(3).Row).Value
- '²M°£µ²ªGÄ檺¸ê®Æ
- Range("e2", Cells(Rows.Count, "f")).ClearContents
- For Each A In Range("b2:b" & Cells(Rows.Count, "b").End(3).Row).Value
- For Each B In Split(A, Chr(10))
-
- '±NBÄæ¦h¦æÀx¦s®æ¤ºªºÈ¤À³Î¨Ã¥N¤J°}¦C,
- '¨Ï¥Î¦r¨åª«¥ó±N«½ÆȧR°£
- If myD(B) = 1 Then GoTo 101
- N = N + 1
- Brr(N, 1) = B
- myD(B) = 1
- 101: Next B
- Next A
- [f2].Resize(N, 1) = Brr
- '"---"µê½u½d³ò¤ºªºµ{¦¡½X¦bBÄæ¦h¦æÀx¦s®æ¦³«½Æ¸ê®Æ©ÎªÅ¦æ®É·|¥X²{°ÝÃD
- '-------------------------------------------------------
- 'pºâBÄæ¦h¦æÀx¦s®æ¤ºªºÈªºÁ`¼Æ¶q¨ÃÅã¥Ü©óCÄæ
- For i = 1 To UBound(Arr)
- C = Split(Arr(i, 2), Chr(10))
- UC = UBound(C) + 1
- Arr(i, 3) = UC
- Next i
- [a2].Resize(UBound(Arr), 3) = Arr
- '±NAÄ檺¸ê®Æ¨ÌCÄ檺¦¸¼Æ¶K¦ÜEÄæ
- For j = 2 To UBound(Arr) + 1
- Cells(j, 1).Copy _
- Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Resize(Cells(j, 3), 1)
- Next j
- '-------------------------------------------------------
- 'CÄ檺¼Æ¦r¨Ï¥ÎªÌ¥Î¤£¨ì,¬G¦b³Ì«á²M°£CÄæ
- Columns(3).ClearContents
- '¶}±Ò¿Ã¹õ§ó·s
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X ¥H¤Wµ{¦¡½XY¹J¨ìBÄæ¸ê®Æ¦³ªÅ¦æ©Î¬O¦³«½Æ¤§±¡§Î®É«h·|¥X²{°ÝÃD,
°ÝÃD±¡§Î·Ð½Ð°Ñ¦ÒªþÀÉ©Ò¥Ü.
¤p§ÌW«ä³\¤[,ÁÙ¬OµLªk¸Ñ¨M,¤æÁx¤W¨Ó¨D§U,
ÁÙ±æ«e½ú¤£§[«üÂI°g¬z,¤Q¤À·PÁ |