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

[µo°Ý] §ä¥X ¬Û¦Pªº¸ê®Æ

[µo°Ý] §ä¥X ¬Û¦Pªº¸ê®Æ

¥»©«³Ì«á¥Ñ john2006168 ©ó 2010-6-21 23:35 ½s¿è

1.½Ð°Ý¦pªGsheet1¦³¨Ç¸ê®Æ¸òsheet2¤ñ¸û,¬Û¦Pªº¦b«ü©wªºsheet "john" show ¥X¨Ó.
¥Îvba«ç»ò¼g??
2.¦pªG¤£¬Û¦Pªº,¦b«ü©wªºsheet "john" show ¥X¨Ó.
¥Îvba¤S«ç»ò¼g
TEST.zip (1.66 KB)

ÁÂÁ¦U¦ì«e½ú´£¨Ñ³o»ò¦hª¾ÃѦb½×¾Â¤W
¤µ¤Ñ«á¾Ç½m²ß¨ì­nª`·N°õ¦æ®Ä¯à!
¤ß±oµù¸Ñ¦p¤U!½Ð¦U¦ì«e½ú«ü¥¿¨Ã«ü¾É!ÁÂÁÂ!
Option Explicit
Sub TEST_2()
Dim i&, x&, Y, Z, Arr, Brr, Crr, T
'¡ô«Å§iÅܼÆ
T = Timer
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OY,Z¦U¬O¦r¨å
For x = 1 To 2
'¡ô³]¥~¶¶°j°é§â¨âªí¸ê®Æ ¥ÎZ¦r¨å¾ã²z ¬°¤£­«½Æ¨Ã¦U±NZ¦r¨åÂà¸m¬°°}¦C
',¦A¸Ë¤J¦r¨å¦¨¬°Y(3), Y(4)

   Y(x) = Sheets(x).Range("A1:A" & Sheets(x).[A65536].End(3).Row)
   Y(x) = Application.Transpose(Y(x))
   '¡ôºÉ¶q¤£¥ÎÂà¸mªº¤è¦¡³B²z¸ê®Æ!¤@¨â¦¸ÁÙ¦n!¦h¦¸¯Ó®É!
   Crr = Y(x)
   '¡ô»Ý­n¥ÎCrr±N¦r¨å¸Ìªº°}¦C²±¸Ë¥X¨Ó°õ¦æ¤ñ¸û§Ö
   For i = 1 To UBound(Crr)
      Z(Crr(i)) = ""
   Next
   Y(x + 2) = Application.Transpose(Z.KEYS)
   '¡ôºÉ¶q¤£¥ÎÂà¸mªº¤è¦¡³B²z¸ê®Æ!¤@¨â¦¸ÁÙ¦n!¦h¦¸¯Ó®É!
   Z.RemoveAll
Next
For x = 3 To 4
'¡ô³]¥~¶¶°j°é§â¨â°}¦C¸ê®Æ¤ÀÃþ¨Ã²Õ¦¨¦r¦ê
   Crr = Y(x)
   '¡ô»Ý­n¥ÎCrr±N¦r¨å¸Ìªº°}¦C²±¸Ë¥X¨Ó°õ¦æ¤ñ¸û§Ö
   For i = 2 To UBound(Crr)
      If Z(Crr(i, 1)) = "" Then
         Arr = Arr & Crr(i, 1) & "|"
         Z(Crr(i, 1)) = Z(Crr(i, 1)) + 1
         Else
            Brr = Brr & Crr(i, 1) & "|"
            Arr = Replace(Arr, Crr(i, 1) & "|", "")
      End If
   Next
Next
Brr = Application.Transpose(Split(Brr, "|"))
Arr = Application.Transpose(Split(Arr, "|"))
'¡ô±NArr,Brr¦r¦ê ¥Î"|" ²Å¸¹©î¸Ñ¬°¤@ºû°}¦C,¨ÃÂà¸m¬°µ²ªG
'¦]¬°Arr,Brr«Å§i¨S¦³«ü©w¬O¤°»òÃþ«¬¸ê®Æ!©Ò¥H¥i¥HÅÜ´«Ãþ«¬!

With Sheets(3)
   .[I1].Resize(, 2) = Array("¬Û¦P", "¤£¦P")
   .[I2].Resize(UBound(Brr), 1) = Brr
   .[J2].Resize(UBound(Arr), 1) = Arr
End With
Set Y = Nothing
Set Z = Nothing
Set Arr = Nothing
Set Brr = Nothing
Set Crr = Nothing
MsgBox Timer - T & "’"
End Sub

TOP

¦^´_ 4# Andy2483
¦U¦ì«e½ú¦n:
1.ª½±µ¦b¦r¨å¸Ì¸Ì­±ªº°}¦C­È¤Þ¥Î©Î½s¿è«Ü¯Ó®É¶¡
2.¤Ï¦Ó§â°}¦C´£¨ú¥X¨Ó°µ°}¦C­È¤Þ¥Î©Î½s¿è¤ñ¸û§Ö
3.¸ê®Æ¤Ö®t²§¤£¤j!§ï5000µ§¸ê®Æ´N®t«Ü¦h¤F!
4.Application.Transpose()Âà¸mªº¤è¦¡¤ñ¸ûºC
TEST_20221018_20_4.zip (69.19 KB)

Hsieh«e½úªº¤è¦¡¤ñ¸û§Ö:


¤W¤@¼Óªº¤è¦¡¶WºC:


§ï¨}¤@¤U,µy¦n:

TOP

ÁÂÁ«e½ú­Ì
¤µ¤Ñ½m²ß°}¦C»P¦r¨å

Option Explicit
Sub TEST()
Dim i&, x&, Y, Z
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
For x = 1 To 2
   Y(x) = Sheets(x).Range("A1:A" & Sheets(x).[A65536].End(3).Row)
   Y(x) = Application.Transpose(Y(x))
   For i = 1 To UBound(Y(x))
      Z(Y(x)(i)) = ""
   Next
   Y(x + 2) = Application.Transpose(Z.KEYS)
   Z.RemoveAll
Next
For x = 3 To 4
   For i = 2 To UBound(Y(x))
      If Z(Y(x)(i, 1)) = "" Then
         Y(5) = Y(5) & Y(x)(i, 1) & "|"
         Z(Y(x)(i, 1)) = Z(Y(x)(i, 1)) + 1
         Else
            Y(6) = Y(6) & Y(x)(i, 1) & "|"
            Y(5) = Replace(Y(5), Y(x)(i, 1) & "|", "")
      End If
   Next
Next
Y(6) = Application.Transpose(Split(Y(6), "|"))
Y(5) = Application.Transpose(Split(Y(5), "|"))
Workbooks.Add
[A1].Resize(, 2) = Array("¬Û¦P", "¤£¦P")
[A2].Resize(UBound(Y(6)), 1) = Y(6)
[B2].Resize(UBound(Y(5)), 1) = Y(5)
End Sub

TOP

¦^´_ 1# john2006168
  1. Sub Ex()
  2. Dim Ar(), Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. With Sheet1
  7. For Each a In .Range(.[A2], .[A65536].End(xlUp))
  8.    d(a.Value) = a.Value
  9.    d2(a.Value) = a.Value
  10. Next
  11. End With
  12. With Sheet2
  13. For Each a In .Range(.[A2], .[A65536].End(xlUp))
  14.    d1(a.Value) = a.Value
  15.    d2(a.Value) = a.Value
  16. Next
  17. End With
  18. ReDim Preserve Ar(s): Ar(s) = "¬Û¦PName": s = s + 1
  19. ReDim Preserve Ay(k): Ay(k) = "¤£¦PName": k = k + 1
  20. For Each ky In d2.keys
  21.   If d.exists(ky) = True And d1.exists(ky) = True Then
  22.   ReDim Preserve Ar(s) '¬Û¦P
  23.   Ar(s) = ky
  24.   s = s + 1
  25.   Else
  26.   ReDim Preserve Ay(k) '¤£¦P
  27.   Ay(k) = ky
  28.   k = k + 1
  29.   End If
  30. Next
  31. With Sheet3
  32. .Cells = ""
  33. .[A1].Resize(s, 1) = Application.Transpose(Ar)
  34. .[B1].Resize(k, 1) = Application.Transpose(Ay)
  35. End With
  36. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# john2006168
John2006168: ¦bSheet1¤¤¤]¦³¬Û¦Pªº¸ê®Æ ½Ð°Ý ¬O³æ¤@ªº(¤£)¬Û¦P¸ê®Æ ÁÙ¬O¦hµ§ªº(¤£)¬Û¦P¸ê®Æ
½Ðªþ¤W§t¬Û¦P¤Î¤£¬Û¦P¸ê®Æ½d¨Ò

TOP

        ÀR«ä¦Û¦b : ¦Û¤v®`¦Û¤v¡A²ö¹L©ó¶ÃµoµÊ®ð¡C
ªð¦^¦Cªí ¤W¤@¥DÃD