- ©«¤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# Hsieh
ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß¨ìJOIN() Àx¦s®æÈ»Ýn¸g¹L¨â¦¸Âà¸m,ÅýÅܦ¨¤@ºû°}¦C,¤~¯à³s±µ¦¨·s¦r¦ê
¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü±Ð
Option Explicit
Sub Ex()
Dim A As Range, d, d1, mystr$
'¡ô«Å§iÅܼÆ:A¬O Àx¦s®æÅܼÆ,(d,d1)¬O³q¥Î«¬ÅܼÆ,mystr¬O¦r¦êÅܼÆ
Set d = CreateObject("Scripting.Dictionary")
'¡ô¥Od³o³q¥ÎÅܼƬO ¦r¨å
Set d1 = CreateObject("Scripting.Dictionary")
'¡ô¥Od1³o³q¥ÎÅܼƬO ¦r¨å
For Each A In Range([A1], [A1].End(xlDown))
'¡ô³]³v¶µ°j°é!¥OA³oÀx¦s®æÅܼƬO ½d³òÀx¦s®æ¸Ìªº¤@®æ,
'½d³òÀx¦s®æ:¥»ªí[A1]¨ì [A]©¹¤U§ä³Ì«á¤@Ó¦³¤º®eÀx¦s®æ,³o½d³òÀx¦s®æ
mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 4))), ",")
'¡ô¥Omystr³o¦r¦êÅܼƬO AÅܼÆ(Àx¦s®æ)¦V¥kÂX®i4ÄæÀx¦s®æ¸g2¦¸Âà¸m«á,
'¥H ","³s±µ¦¨ªº·s¦r¦ê
d(mystr) = d(mystr) + A.Offset(, 4).Value
'¡ô¥O¥HmystrÅܼƬ°key,item¬O item¦Û¨ + AÅܼƦV¥k°¾²¾4®æÀx¦s®æ(EÄæ)È,
'¯Ç¤Jd¦r¨å
d1(mystr) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 2).Value, _
A.Offset(, 3).Value, d(mystr))
'¡ô¥O¥HmystrÅܼƬ°key,item¬O °}¦C(AÅܼÆ(§t)¥k°¼4ÓÀx¦s®æÈ,
'¥HmystrÅܼƬdd¦r¨åªºitemÈ)
Next
[H:L] = ""
'¡ô¥O[H:L]Àx¦s®æȬO ªÅ¦r¤¸
[H1].Resize(d1.Count, 5) = Application.Transpose(Application.Transpose(d1.items))
'¡ô¥O[H1]ÂX®i¦V¤Ud1¦r¨åkey¼Æ¦C,ÂX®i¦V¥k5Äæ,³o½d³òÀx¦s®æÈ¥Hd1¦r¨åitemsÂà¸m¨â¦¸±a¤J
End Sub |
|