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

[µo°Ý] ½Ð±Ð¤ÀÄæ±Æ¦C:¦³µL§ó§Öªº¼gªk

[µo°Ý] ½Ð±Ð¤ÀÄæ±Æ¦C:¦³µL§ó§Öªº¼gªk

·Q½Ð°Ý¦U¦ì¦p¦óÅý¤U­±ªº¥¨¶°¶]±o§ó§Ö(²Ä¤G­Ó³¡¤À)
°²³]§Ú¦bAÄæ,BÄæ¦U¦³50000¦C¦ÛµM¼Æ,
CÄæ=AÄæ+BÄæ,
DÄæ=ºØÃþ

CÄæ§Ú¹Á¸Õ¤F¤U­±¤TºØ¼gªk
¤À§O¬O:
1.ª½±µ¥Îcell¬Û¥[
2.¥Î¤u§@ªí¨ç¼Æ
3.¥Î¦r¨å
¤èªk1¯Ó¶O2¬í
¤èªk2¯Ó¶O3¬í,
¥Î¦r¨å«h¬O0¬í
²@µLºÃ°Ýªº¥Î¦r¨å³Ì§Ö

¥N½X¦p¤U
  1. Public Sub ¨âÄæ¬Û¥[1()
  2. Start = Timer    '­p®É¶}©l

  3. With ActiveSheet
  4.     For i = 1 To 50000
  5.         .Cells(i, 3) = .Cells(i, 1) + .Cells(i, 2)
  6.     Next
  7. End With

  8. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô
  9. ===================
  10. Public Sub ¨âÄæ¬Û¥[2()
  11. Start = Timer    '­p®É¶}©l

  12. With ActiveSheet
  13.     For i = 1 To 50000
  14.         .Cells(i, 3) = Application.WorksheetFunction.Sum(.Cells(i, 1), .Cells(i, 2))
  15.     Next
  16. End With

  17. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô Sub
  18. End Sub
  19. ===============
  20. Public Sub ¨âÄæ¬Û¥[3()
  21. Start = Timer    '­p®É¶}©l
  22. Dim d As Object
  23. Set d = CreateObject("Scripting.Dictionary")

  24. With ActiveSheet
  25.     For i = 1 To 50000
  26.         d(i) = Cells(i, 1) + Cells(i, 2)
  27.     Next
  28.     Range("C1:C50000") = Application.Transpose(d.items)
  29. End With

  30. Set d = Nothing

  31. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô Sub
  32. End Sub
½Æ»s¥N½X
===============
¤Á¤J¥¿ÃD:

DÄæ¬OºØÃþ,§Ú·Q­nÅýCÄ檺­È¨Ì·Ó¦UºØÃþ±Æ¦b¹ïÀ³ªºÄæ
Ä´¦p²Ä1,3¦C¬OºØÃþA,²Ä2,5¦C¬OºØÃþB,²Ä4¦C¬OºØÃþC
§Ú¥ý¥Î¦r¨å²¾°£­«½Æ«á,¦b²Ä50001¦CªºE,F,GÄæ¦C¥X¤T­ÓºØÃþ
µM«á¨C¤@¦C´N·|¨Ì·Ó50001¦C¦C¥Xªº¼Æ¦r,§âCÄ檺­È¨ÌºØÃþ±Æ¦b¸ÓÄæ
Ãþ¦ü¼Ï¯Ã¤ÀªR¨º¼Ë

§ÚªººÃ°Ý¬O,µL½×§Ú¦p¦ó¹Á¸Õ,´N¬O°µ¤£¥XAÄæ+BÄ樺ºØ³t«×(0¬í)
¤@¼Ë³£¬O50000¦C,¥Ø«e³t«×¶È¯à¹F3¬í
1.¥Îloop+ifŪ¥X¨C¦C¹ïÀ³ªºÄ渹
2.¥Î¦r¨å§â¨C¤@¦CÀ³¦b­þ¤@ÄæŪ¶i¥h,¦AŪ¥X¨Ó
3.¦P2,¤£¹LŪ¥X¨Óªº¤è¦¡´«¦¨offset

µ²ªG¬O¤èªk2=3,ªá¶O3¬í,¤èªk1ªá¶O6¬í
§Ú¤£À´ªº¦a¤è¬O¬°¤°»ò¥Î¦r¨å¬Û¥[50000¦C¥i¥H0¬í¹F¦¨
¦ý¬O¤ÀÄæ±Æ§Ç50000¦C´N·|¯Ó®É¸û¤[
¦³¤H¥i¥H«üÂI°g¬z¶Ü
ÁÂÁÂ

======================
  1. Public Sub ¤ÀÄæ´ú¸Õ1()
  2. Start = Timer        '­p®É¶}©l
  3. Dim d As Object
  4. Set d = CreateObject("scripting.dictionary")
  5. With ActiveSheet
  6.     arr1 = .Range("D1:D50000")   '§Q¥Î¦r¨å¯S©Ê²¾°£­«½Æ
  7.     For i = 1 To 50000
  8.         d(arr1(i, 1)) = 1
  9.     Next
  10.    
  11.     For i = 1 To 6
  12.         .Range("E50001").Resize(1, d.Count) = d.keys    '©ó²Ä50001¦C¦C¥X©Ò¦³ºØÃþ
  13.     Next
  14.    
  15.     For i = 1 To 50000
  16.             For j = 1 To 6
  17.                 If .Cells(i, 4) = .Cells(50001, 4 + j) Then
  18.                 k = j + 4                                'Ū¥X¨C¦CÀ³¸ÓÂ\¦b­þ¤@Äæ
  19.                 End If
  20.             Next j
  21.         .Cells(i, k) = .Cells(i, 3)   '¨Ì§Ç«öÄ渹¦C¥X¹ïÀ³­È
  22.     Next i
  23.    
  24. Set d = Nothing
  25. Set arr1 = Nothing
  26. End With

  27. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô
  28.    
  29. End Sub
  30. ======================
  31. Public Sub ¤ÀÄæ´ú¸Õ2()
  32. Start = Timer          '­p®É¶}©l

  33. Dim d1 As Object
  34. Dim d2 As Object
  35. Set d1 = CreateObject("scripting.dictionary")
  36. Set d2 = CreateObject("scripting.dictionary")

  37. With ActiveSheet

  38.     arr1 = .Range("D1:D50000")     '§Q¥Î¦r¨å¯S©Ê²¾°£­«½Æ
  39.     For i = 1 To 50000
  40.         d1(arr1(i, 1)) = 1
  41.     Next
  42.    
  43.     For i = 1 To 6
  44.         .Range("E50001").Resize(1, d1.Count) = d1.keys   '©ó²Ä50001¦C¦C¥X©Ò¦³ºØÃþ
  45.     Next
  46.    
  47.     For i = 1 To 6
  48.         d2(.Range(Chr(68 + i) & "50001").Value) = i + 4  'Ū¥X¨C¦CÀ³¸ÓÂ\¦b­þ¤@Äæ
  49.     Next
  50.    
  51.     For i = 1 To 50000
  52.         .Cells(i, d2(.Range("D" & i).Value)) = .Cells(i, 3)   '¨Ì§Ç«öÄ渹¦C¥X¹ïÀ³­È
  53.     Next i
  54.    

  55. Set d1 = Nothing
  56. Set d2 = Nothing
  57. Set arr1 = Nothing

  58. End With

  59. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô
  60.    
  61. End Sub
  62. =================
  63. Public Sub ¤ÀÄæ´ú¸Õ3()
  64. Start = Timer    '­p®É¶}©l

  65. Dim d1 As Object
  66. Dim d2 As Object
  67. Set d1 = CreateObject("scripting.dictionary")
  68. Set d2 = CreateObject("scripting.dictionary")

  69. With ActiveSheet
  70.     arr1 = .Range("D1:D50000")   '§Q¥Î¦r¨å¯S©Ê²¾°£­«½Æ
  71.     For i = 1 To 50000
  72.         d1(arr1(i, 1)) = 1
  73.     Next
  74.    
  75.     For i = 1 To 6
  76.         .Range("E50001").Resize(1, d1.Count) = d1.keys   '©ó²Ä50001¦C¦C¥X©Ò¦³ºØÃþ
  77.     Next
  78.    
  79.     For i = 1 To 6
  80.         d2(.Range(Chr(68 + i) & "50001").Value) = i + 1   'Ū¥X¨C¦CÀ³¸ÓÂ\¦b­þ¤@Äæ
  81.     Next
  82.    
  83.     For i = 1 To 50000
  84.         .Cells(i, 3).Offset(0, d2(.Range("D" & i).Value)) = .Cells(i, 3)   '¨Ì§Ç«öÄ渹¦C¥X¹ïÀ³­È
  85.     Next i
  86.    

  87. Set d1 = Nothing
  88. Set d2 = Nothing
  89. Set arr1 = Nothing

  90. End With

  91. MsgBox "Completion in  " & Format((Timer - Start) / 86400, "hh:mm:ss")   '­p®Éµ²§ô
  92.    
  93. End Sub
½Æ»s¥N½X
=================
ªþ¥ó¤@ª½¶Ç¤£¤W¥h..
§Ú©ñ¦b§Úªºftp...
http://iamjo.myweb.hinet.net/¤ÀÄæ±Æ¦C.xls
MPEG

¥»©«³Ì«á¥Ñ bobomi ©ó 2015-4-5 07:18 ½s¿è

¤§©Ò¥H¼É§Ö¤£¬O¦b©ó¦r¨å
¦Ó¬O¦b©ó§A¥Î¤F "1¦¸©Ê¶ñ¥Rªº¤è¦¡"  Range("C1:C50000") = Application.Transpose(d.items)

¸ÑªkÃþ¦ü¦p¤U

    Col3 = [C1 : C50000].Value
    col4 = [D1 : D50000].Value
    Dim ar(1 To 50000, 1 To 6)
    For i = 1 To 50000
       ' .Cells(i, 3).Offset(0, d2(.Range("D" & i).Value)) = .Cells(i, 3)   '¨Ì§Ç«öÄ渹¦C¥X¹ïÀ³­È
        ar(i, d2(col4(i, 1))) = Col3(i, 1) '¨Ì§Ç«öÄ渹¦C¥X¹ïÀ³­È
    Next i
   
   .Cells(1, 9).Resize(50000, 6) = ar ' ---> 1¦¸©Ê¶ñ¥R

TOP

¦^´_ 2# bobomi

ªGµM¤@¬í´N§¹¦¨
¨ü±Ð¤F

¤ÀÄæ´ú¸Õ.jpg (104.82 KB)

1’

¤ÀÄæ´ú¸Õ.jpg

MPEG

TOP

¤§©Ò¥H¼É§Ö¤£¬O¦b©ó¦r¨å
¦Ó¬O¦b©ó§A¥Î¤F "1¦¸©Ê¶ñ¥Rªº¤è¦¡"  Range("C1:C50000") = Application.Transpose ...
bobomi µoªí©ó 2015-4-5 07:17



¦A½Ð±Ð¤U,¦pªGºØÃþ¼Æ¤£¬O©T©w­È©O
°}¦C¤j¤p¦n¹³µLªk¥ÎÅܼƨM©w
§Ú¹Á¸Õ§ï¼g¦¨ Dim ar(1 to 50000,1 to d1.count)
·|¥X²{¡u¥²¶·¬O±`¼Æ¹Bºâ¦¡¡vªº¿ù»~°T®§
¥i§_¦b«üÂI¤@¤G
ÁÂÁÂ
MPEG

TOP

ReDim ar(1 to 50000,1 to d1.count)

TOP

¦^´_ 5# bobomi

¨ü±Ð,ÁÂÁÂ
¤@ª½¥H¬°Redim¥u¯à§ïÅÜ°}¦C¤j¤p
°ÝÃD¸Ñ¨M¤F,
¤£¹LÁÙ¬O¤£À´¬°¤°»òredim¥i¥H¥HÅܼƫŧi°}¦C¤j¤p,
dim«o¤£¦æ©O?
MPEG

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD