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

[µo°Ý] ¦p¦ó¥[¸ê®Æ

¦^´_ 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
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD