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

½Ð±Ð¸ê®Æ·J¾ã°ÝÃD

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-17 13:53 ½s¿è

ÁÂÁÂ Hsieh «e½ú
ÁÂÁÂ n7822123 «e½ú
¥H¤U¤ß±oµù¸Ñ,Àµ½Ð«e½ú­Ì«ü¥¿»P«ü¾É!
Option Explicit
Sub TEST()
Dim Arr, Brr, C&, i&, R&, T, Y, Z, Q
Set Y = CreateObject("Scripting.Dictionary")
Sheets("Sheet5").Cells = ""
'¡ô¥O ¤u§@ªí "Sheet5" ©Ò¦³Àx¦s­Ó³£¬OªÅ¦r¤¸
''''''''''''''''''''''''''''''''''''''''''''''''''''''

With Sheets("Sheet1")
   Set Brr = .[A1].CurrentRegion
  '¡ô¥O Brr¬O [A1]¬Û¾F«DªÅ®æ©Ò¦ê³s°_¨ÓªºÀx¦s®æ,ÂX®i¨ì¤è¥¿°Ï°ìªº³Ì¤p½d³òÀx¦s®æ
   C = .[A1].End(xlToRight).Column
   '¡ô¥OC¬O¦¹ªíªºÄæ¼Æ
   R = .[A1].End(xlDown).Row
   '¡ô¥OR¬O¦¹ªíªº¦C¼Æ
End With
For i = 1 To R
'¡ô³]°j°é§â¤@ºû°}¦C­Ë¤J¦r¨å¸Ì·íitem
   T = Brr(i, 1)
   Q = Brr(i, 2)
   Arr = Brr(i, 1).Resize(, C)
   Arr = Application.Transpose(Application.Transpose(Arr))
   Y(T & "|" & Q) = Arr
   '¡ô¥O¦¹KEYªºITEM¬OArr¤@ºû°}¦C
Next
With Sheet5
   .[A1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Y.items))
   '¡ô§âY¦r¨åªº¤@ºû°}¦CITEM­È±q[A1]¶}©l¶K¤J
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheets("Sheet2")
   Set Brr = .Range(.[D1], .[A1].End(xlDown))
   C = .[A1].End(xlToRight).Column - 2
   R = .[A1].End(xlDown).Row
End With
For Each Z In Y.KEYS
'¡ô³]°j°é§âitemªº¤@ºû°}¦C§ïÅÜ°}¦C¤j¤p
'³o¸Ì«Ü­«­n!
'¦]¬°¦pªG³Ì«áÂà¸m¶K¤W®É!¦r¨åITEMªº¶°¦X¤£¬O¤è¥¿ªº
'´N¨S¿ìªkÂà¸m¶K¤W

   Y(Z) = Array("", "")
Next
For i = 1 To R
'¡ô³]°j°é§â¤@ºû°}¦C­Ë¤J¦r¨å¸Ì·íitem
   T = Brr(i, 1)
   Q = Brr(i, 2)
   Arr = Brr(i, 3).Resize(, C)
   Arr = Application.Transpose(Application.Transpose(Arr))
   If Y.Exists(T & "|" & Q) Then
   '¡ô¦pªG²Õ¦X¦r¦ê¦b¦r¨å¸Ì¦³!
      Y(T & "|" & Q) = Arr
      '¡ô±ø¥ó¦¨¥ß´N¥O¦¹KEYªºITEM¬OArr¤@ºû°}¦C
   End If
Next
With Sheet5
   .[I1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Y.items))
   '¡ô§âY¦r¨åªº¤@ºû°}¦CITEM­È±q[I1]¶}©l¶K¤J
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheets("Sheet3")
   Set Brr = .Range(.[K1], .[A1].End(xlDown))
   C = .[A1].End(xlToRight).Column
   R = .[A1].End(xlDown).Row
End With
For Each Z In Y.KEYS
'¡ô³]°j°é§âitemªº¤@ºû°}¦C§ïÅÜ°}¦C¤j¤p
'³o¸Ì«Ü­«­n!
'¦]¬°¦pªG³Ì«áÂà¸m¶K¤W®É!¦r¨åITEMªº¶°¦X¤£¬O¤è¥¿ªº
'´N¨S¿ìªkÂà¸m¶K¤W

   Y(Z) = Split(",,,,,,,,,,", ",")
Next
For i = 1 To R
'¡ô³]°j°é§â¤@ºû°}¦C­Ë¤J¦r¨å¸Ì·íitem
   T = Brr(i, 1)
   Q = Brr(i, 2)
   Arr = Brr(i, 1).Resize(, C) '
   Arr = Application.Transpose(Application.Transpose(Arr))
   If Y.Exists(T & "|" & Q) Then
   '¡ô¦pªG²Õ¦X¦r¦ê¦b¦r¨å¸Ì¦³!
      Y(T & "|" & Q) = Arr
      '¡ô±ø¥ó¦¨¥ß´N¥O¦¹KEYªºITEM¬OArr¤@ºû°}¦C
   End If
Next
With Sheet5
   .[K1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Y.items))
   '¡ô§âY¦r¨åªº¤@ºû°}¦CITEM­È±q[K1]¶}©l¶K¤J
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''
With Sheets("Sheet4")
   Set Brr = .Range(.[R1], .[A1].End(xlDown))
   C = .[A1].End(xlToRight).Column - 2
   R = .[A1].End(xlDown).Row
End With
For Each Z In Y.KEYS
'¡ô³]°j°é§âitemªº¤@ºû°}¦C§ïÅÜ°}¦C¤j¤p
'³o¸Ì«Ü­«­n!
'¦]¬°¦pªG³Ì«áÂà¸m¶K¤W®É!¦r¨åITEMªº¶°¦X¤£¬O¤è¥¿ªº
'´N¨S¿ìªkÂà¸m¶K¤W

   Y(Z) = Split(",,,,,,,,,,,,,,,", ",")
Next
For i = 1 To R
   T = Brr(i, 1)
   Q = Brr(i, 2)
   Arr = Brr(i, 3).Resize(, C)
   Arr = Application.Transpose(Application.Transpose(Arr))
   If Y.Exists(T & "|" & Q) Then
   '¡ô¦pªG²Õ¦X¦r¦ê¦b¦r¨å¸Ì¦³!
      Y(T & "|" & Q) = Arr
     '¡ô±ø¥ó¦¨¥ß´N¥O¦¹KEYªºITEM¬OArr¤@ºû°}¦C
   End If
Next
With Sheet5
   .[V1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Y.items))
  '¡ô§âY¦r¨åªº¤@ºû°}¦CITEM­È±q[V1]¶}©l¶K¤J
End With
Set Arr = Nothing
Set Brr = Nothing
Set Y = Nothing
End Sub

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-17 14:58 ½s¿è

¦U¦ì«e½ú¦n:
«á¾Çµo²{¤@­Ó«Ü¦³·N«äªº²{¶H
½Ð±Ð¦U¦ì«e½ú³o¬O¤°»òÅÞ¿è?

1.¤£Âà¸m¶K¤J¨S¦³¸ê®Æ
.[V1].Resize(Y.Count, C) = Y.items

2.Âà¸m¤@¦¸!¸ê®Æ¬O¾î©ñ
.[V1].Resize(Y.Count, C) = Application.Transpose(Y.items)

3.Âà¸m¨â¦¸¤~·|¬O§Ú­Ì­nªº¸ê®Æ!
.[V1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Y.items))

4.Âà¸m¤T¦¸¦P2.
.[V1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Application.Transpose(Y.items)))

5.Âà¸m4¦¸¤S¦P3.
.[V1].Resize(Y.Count, C) = Application.Transpose(Application.Transpose(Application.Transpose(Application.Transpose(Y.items))))

¤@¶}©l¥¼Âà¸mªº¸ê®Æ¬O¤°»ò¼Ëªºª¬ºA?
¬°¤°»ò¤£¯àª½±µ¶K¤W´N¦n?ÁÙ­nÂà¸m¨â¦¸?¤~¬O§Ú­Ì­nªº¸ê®Æ?
ÁÂÁ«e½ú­Ì«üÂI!
¤j·§¥u¦³«á¾Ç³oºØ¶Ì¤l¤~·|¥h¸ÕÂà4¦¸ªºµ²ªG!
«¢!

TOP

¦^´_ 12# Andy2483
Andy2483«e½ú¦n¡I
¤p§Ì¤~²¨¾Ç²L¡A­Y¦³¸ÑÄÀ¤£¹ï¦a¤è¦A½Ð¨ä¥L«e½ú«ü¥¿¡A
¬O§_item¬O¤@ºû°}¦C¡A¬°¤ô¥­±Æ¦C¡A¦ý¸Ó½d¨Ò¤¤¹ïÀ³¤£¤î¤@­Óitem¡A
©Ò¥H¥²¶·¨Ï¥Î¨â¦¸ªºÂà¸m(Transpose)°Ê§@¡A¤~·|Åܦ¨¯u¥¿ªº¤Gºû°}¦C¥iª½±µ¼g¤J¤u§@ªí¡A
¸Ó¬q¸ÑÄÀ¤]¬O¨ä¥L«e½ú¯d¤Uªº¨¬¸ñ¡A§Ú¤]ÁÙ¦b°Ñ³z¤¤¡A¦b¦¹µ¹§A°Ñ¦Ò....

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-19 08:11 ½s¿è

¦^´_ 13# shuo1125


    ÁÂÁÂ shuo1125 «e½ú
«Ü¦³¹D²z¼Ú!
" item¬O¤@ºû°}¦C¡A¬°¤ô¥­±Æ¦C¡A¦ý¸Ó½d¨Ò¤¤¹ïÀ³¤£¤î¤@­Óitem¡A"
1.¦pªG item¤@ºû°}¦C¡A¦bªÅ¶¡·§©À¬O¤ô¥­X¶b¤è¦V±Æ¦C
2.½d¨Ò¤¤¹ïÀ³¤£¤î¤@­Óitem,²qitem»PitemÀ³¸Ó¬OªÅ¶¡Z¶b¤è¦V±Æ¦C
3.©Ò¥H¥ýÂà¸m¤@¦¸Åý©Ò¦³¤¸¯À¨Ö¦¨¤@­Ó¤Gºû°}¦C,¨Ã¥B¶X¶b±ÛÂà90«×
4.²Ä¤G¦¸Âà¸m¬O¶Z¶b
5.¦pªG¬O²Ä¤T¦¸Âà¸m¤]¬O¶Z¶b
6.¦pªG¬O²Ä¥|¦¸Âà¸m¦A¬O¶Z¶bÂà¦^¨Ó
7. itemÂà¸m¬°°}¦C§Þ¥©·|¨Ï¥Î¤ñ¸û­«­n!¯u²z¯dµ¹°ª¤â¸Ñµª!
¥ç®v¥ç¤Í ÁÂÁÂ

¥H¤U¬O²q´úªº¥Ü·N¹Ï!½Ð«e½ú­Ì«ü¥¿¨Ã«ü¾É!ÁÂÁÂ!
¨C­ÓÃC¦â¥Nªí¨C­Ó  item¤@ºû°}¦C:


¨Ö¦¨¤@­Ó¤Gºû°}¦C!¶X¶b±ÛÂà90«×:


²Ä¤G¦¸Âà¸m¬O¶Z¶b:


¦pªG¬O²Ä¤T¦¸Âà¸m¤]¬O¶Z¶b


¦pªG¬O²Ä¥|¦¸Âà¸m¦A¬O¶Z¶bÂà¦^¨Ó

TOP

¦^´_ 13# shuo1125

¥H¤U¨âºØ¤è¦¡¦b¸ê®Æ¤Öªº®É­Ô¥Î! ¸ê®Æ¦h´N¥Î§Oªº¤è¦¡¨ú¥N¥¦!
¤Ó¯Ó®É¶¡¤F!

1.¦h¦¸ ´£¨ú°}¦CªºÄæ/¦C:Application.Index()
2.¦h¦¸ °}¦CªºÂà¸m:Application.Transpose()

TOP

¦^´_ 15# Andy2483
Andy2483«e½ú¦n¡I
°l¨D®Ä²v¹ï§Ú¨Ó»¡¸ôÁÙ¤Ó»·...
ÁÂÁ±zªº«ü¾É¡I

TOP

¦^´_ 16# shuo1125
¤À¨É«e½ú¤ß±o
http://forum.twbts.com/viewthrea ... mp;extra=#pid119783
¤W¦C³sµ²¸Ìªº¾Ç²ß¦³´ú¸Õ¨ì
¦pªG­nÂà¸m¦r¨åªºitem¤@ºû°}¦C¦¨¬°¤Gºû°}¦C!¥²¶·­n©Ò¦³item¤@ºû°}¦C§¹¾ã!

Sub TEST()
Workbooks.Add
[A1].Resize(1, 5) = Array("¦X­p", , , , 5000)
End Sub
¤W­z¤@¯ëªº¤@ºû°}¦C¼g¤JÀx¦s®æ¬O¥i¥Hªº!

¦ý¬O©ñ¤J¦r¨å¸Ìªº¤@ºû°}¦C¸Ì¤¤¶¡3­ÓªÅ¤¸¯À«o¬O¤£³Q©Ó»{
¦p¤U:
TT = "Á`­p"
Y(TT) = Array(TT, , , , V)

ÁöµM¨S¦³¿ìªk°õ¦æ!

¦ý¬O¥H¤U¤è¦¡¬O¥i¥Hªº!
TT = "Á`­p,,,," & V
Y(TT) = Split(TT, ",")
'¡ô¥Î","¤À³Î¦r¦ê


¥H¤U¤è¦¡¤]¥i¥H
TT = "Á`­p"
Y(TT) = Array(TT, "", "", "", V)

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD