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

[µo°Ý] ¦p¦ó¥Î¨ç¼Æ¨Ì±ø¥ó¿z¿ï¥X¤£­«½Æªº¸ê®Æ

[µo°Ý] ¦p¦ó¥Î¨ç¼Æ¨Ì±ø¥ó¿z¿ï¥X¤£­«½Æªº¸ê®Æ

¥»©«³Ì«á¥Ñ zheng211016 ©ó 2017-11-26 21:38 ½s¿è

¦pÃD

·Q­n¦³Ãþ¦ü¹Ï¤ù¤W³oºØ¥\¯à
½Ð°Ý¦U¦ì¤j¤j¦³¤°»ò¿ìªk
¨Ì±ø¥ó¿z¿ï¤£­«½Æ¸ê®Æ.rar (6.66 KB)

¦^´_ 1# zheng211016


    °}¦C¤½¦¡¡A¬G§¹¦¨¿é¤J«á¡A½Ð«ö¤U²Õ¦XÁäCTRL+SHIFT+ENTER

TOP

¦^´_ 2# joblyc017

·PÁ °ª¤â¬Û§U

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 4# hcm19522


  ·PÁ¤j¤j ±zªº¤èªk¤]«Ü¦n ·PÁ¥Τ߫ü±Ð

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-8 16:16 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, R&, C%, M&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([C2], [A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~CÄæÀx¦s®æ­È±a¤J
ReDim Crr(1 To UBound(Brr), 1 To 100)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò¦PBrr°}¦C,¾î¦V½d³ò1~100
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 3)
   '¡ô¥OTÅܼƸˤJ²Ä3Äæ°j°é°}¦C­È,¤@¤è­±©w¸q¬°¦r¦ê,¥t¤@¦n³B¬OÁYµu«á¤èµ{¦¡½X
   If Z(T) = "" Then C = C + 1: Z(T) = C: Crr(1, C) = T: Z(T & "|r") = 1
   '¡ô±N­º¦¸¯Ç¤JZ¦r¨åªº ¯¸ÂIkey¨Ì§Ç§G¦bCrr°}¦C²Ä1¦C,
   '¨Ã¥OZ¦r¨å°O¦í ³o¯¸ÂIkey¬O¦bCrr°}¦Cªº­þ­ÓÄæ¦ì,
   'Z(T & "|r") = 1¬O¬°¤F¼ÐÃD¦CÅý¥X1¦C

   R = Z(T & "|r") + 1: Z(T & "|r") = R
   Crr(R, Z(T)) = Brr(i, 1)
   '¡ô¤£ºÞ ¯¸ÂIkey¬O¤£¬O­º¦¸¯Ç¤JZ¦r¨å,³£À³¸Ó±N §å¸¹¼g¤JCrr¹ïÀ³keyªºÄæ¦ì¤U
   If M < R Then M = R
   '¡ô³o¬O­n§P©wCrr°}¦C­n¼g¤JÀx¦s®æ®É,»Ý­n¦h¤Ö¦C¼Æ
Next
[E:IV].ClearContents
'¡ô²M°£µ²ªGÄ椺®e
[E1].Resize(M, C) = Crr
'¡ô±q[E1]¶}©lªº½d³ò¼g¤JCrr°}¦C­È
Set Z = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾Ç©µ¦ù¾Ç²ß²Î­p¦U¯¸ÂIª««~¼Æ¶q¦X­p,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æµ²ªG:



Option Explicit
Sub TEST_1()
Dim Brr, Crr, Z, i&, R&, C%, M&, T$
'¡ô«Å§iÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([C2], [A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~CÄæÀx¦s®æ­È±a¤J
ReDim Crr(1 To 2, 1 To 100)
'¡ô¥OCrrÅܼƬO ¤GºûªÅ°}¦C,Áa¦V½d³ò1~2,¾î¦V½d³ò1~100
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 3)
   '¡ô¥OTÅܼƸˤJ²Ä3Äæ°j°é°}¦C­È
   If Z(T) = "" Then C = C + 1: Z(T) = C: Crr(1, C) = T
   '¡ô±N­º¦¸¯Ç¤JZ¦r¨åªº ¯¸ÂIkey¨Ì§Ç§G¦bCrr°}¦C²Ä1¦C,
   '¨Ã¥OZ¦r¨å°O¦í ³o¯¸ÂIkey¬O¦bCrr°}¦Cªº­þ­ÓÄæ¦ì

   Crr(2, Z(T)) = Crr(2, Z(T)) + Brr(i, 2)
   '¡ô¤£ºÞ ¯¸ÂIkey¬O¤£¬O­º¦¸¯Ç¤JZ¦r¨å,³£À³¸Ó±N¼Æ¶q ¥[¤JCrr¹ïÀ³keyªºÄæ¦ì¤U
Next
[E1:IV2].ClearContents
'¡ô²M°£µ²ªGÄ椺®e
[E1].Resize(2, C) = Crr
'¡ô±q[E1]¶}©lªº½d³ò¼g¤JCrr°}¦C­È
Set Z = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD