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

[µo°Ý] ¦p¦ó´£°ª¦h±ø¥ó¥[Á`®Ä²v SUMIFS vs SUMPRODUCT vs VBA

¦^´_ 39# ML089

¦n¹³ÁÙ¬O loop §Ö
  1. Sub sonny3_v3() 'VBA split+join
  2.     t1 = Timer
  3.     Dim RowsCnt, m, SubTotalAr() As Double
  4.     Dim DataArea As Variant
  5.     Dim i%, j%
  6.     AllType = " AÃþ|BÃþ|CÃþ|DÃþ|EÃþ|FÃþ|GÃþ|HÃþ|IÃþ|JÃþ"
  7.     ReDim SubTotalAr(0 To 9, 0 To 11)
  8.     Application.ScreenUpdating = False
  9.     With Sheets("­ì©l¸ê®Æ")
  10.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
  11.         DataArea = .Range("A2").Resize(RowsCnt - 1, 3)
  12.         For m = 1 To UBound(DataArea)
  13.             i = UBound(Split(Split(AllType, DataArea(m, 1))(0), "|"))
  14.             j = -1
  15.             Do
  16.                 j = j + 1
  17.             Loop Until Month(DataArea(m, 2)) = (j + 1)
  18.             SubTotalAr(i, j) = SubTotalAr(i, j) + DataArea(m, 3)
  19.         Next m
  20.     End With
  21.    
  22.     With Sheets("Á`ªí")
  23.         .Range("B4").Resize(9 + 1, 12) = SubTotalAr
  24.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
  25.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
  26.         .Range("O4:R13").FormulaR1C1 = "=SUM(RC[-13]:RC[-11])"
  27.     End With
  28.     Application.ScreenUpdating = True
  29.     t2 = Timer
  30.     MsgBox "¯Ó®É" & t2 - t1
  31. End Sub
½Æ»s¥N½X
ss

TOP

¦^´_ 41# sunnyso

§Ú´ú¸Õµ²ªG¤]¬O¤@¼Ë
UBound(Split(Split( ¥Î¤F3­Ó¨ç¼Æ¡ASplit( ³oºØ¤å¦r¨ç¼ÆÀ³¸Ó¤]¤ñ¸ûºC

³o¨Ò¤l¾Ç²ß¨ì¡G
EXCEL ¨ç¼Æ ¤ñ VBA¨ç¼ÆºC«Ü¦h
¤å¦r¨ç¼Æ ¤ñ ¼Æ¦r¨ç¼ÆºC
´î¤Ö°j°é¾A®É¸õ¥X
´î¤Ö¨ç¼Æ¨Ï¥Î¶q
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-7-1 09:56 ½s¿è

¦^´_ 42# ML089

§¹¥þ¦P·N
¥t¥[

VBA¥Î°}¦C¡A´î¤ÖŪ¼gworksheet¦¸¼Æ

Worksheet ´î¤Ö¥Î°}¦C¤½¦¡ ¡]ctrl+shift+Enter)
ss

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-7-1 10:24 ½s¿è
¦^´_  ML089

§¹¥þ¦P·N
¥t¥[

VBA¥Î°}¦C¡A´î¤ÖŪ¼gworksheet¦¸¼Æ

Worksheet ´î¤Ö¥Î°}¦C¤½¦¡ ¡]ctrl+shift+Enter)

sunnyso µoªí©ó 2013-7-1 09:53


¤£­nª[µø»²§UÄæ¡A¦]爲°}¦C¤½¦¡¤ñ»²§UÄæºC

°}¦C¤½¦¡¾A¦X¤p«¬­pºâ¡A¡]©M¾Ç²ß½m¥\¡^
ss

TOP

¦^´_ 43# sunnyso


VBA±N¸ê®Æ¤@¦¸Åª¤J°}¦C¦A°µ­pºâ¤ñ¸û§Ö¡A¤£ª¾¹D°}¦C¤j¤p¦³µL­­¨î¡C
¦pªG¸ê®Æ¶q«Ü¤j¶W¹L°}¦C­­¨î®É¦³µL³B²z¤è¦¡¡C
¥Ø«e§Ú·Q±N9­Ó¤å¦rÀÉ(¨C­ÓÀɮ׬ù3~5¸U¦æ¡A¨C¦æ80¦r)¦P®Éª½±µÅª¤J°}¦C¦A¨Ó³B²z¡A½Ð±Ð³oºØ¤è¦¡¬O§_¥i¦æ¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 45# ML089
­­¨îÀ³¸Ó´N¬O°O¾ÐÅé¤j¤p§a¡A§â¸ê®ÆŪ¨ì°}¦C²z½×¤W´N¬OŪ¨ì°O¾ÐÅ餺¡A¥Îstop¤¤Â_¨ÃÆ[¹î¤u§@ºÞ²z­û>³B²zµ{§Ç>Excel.exe©Ò¦ûªº°O¾ÐÅé¤j¤p¥i¥H±oª¾¡C¦ý°O¾ÐÅé¤S¤À¶¥¼h:§Ö¨ú¡B¹êÅé¡BµêÀÀ¡A¦pªGŪ¤J¸ê®Æ¹L¤j¦Ó¨Ï¥Î¨ìµêÀÀ°O¾ÐÅé(¨ä¹ê´N¬OµwºÐ¤À­¶ÀÉ)¡A³t«×´N¨S­ì¥»¨º»ò§Ö¤F¡C

TOP

¦^´_ 46# stillfish00

ÁÂÁ¦^ÂÐ

¬d¸ß¤U¦Cºô­¶
http://msdn.microsoft.com/zh-tw/library/b388cb5s(v=vs.80).aspx

¸Ì­±»¡¨ì¡A¨C­Ó°}¦C¤§ºû«×ªø«×³£¨ü­­©ó Integer ¸ê®Æ«¬§Oªº³Ì¤j­È¡A¤]´N¬O (2 ^ 31) - 1¡AµM¦Ó¡A°}¦C¤§Á`¤j¤p¤]¦P®É¨ü­­©ó¨t²Î¥i¥Îªº°O¾ÐÅé¡C
©Ò¥H»¡¥i«Å§i¼Æ¶qÀ³¸Ó¤£¬O°ÝÃD¡A¥i¬O¨ü­­©ó¨t²Î¥i¥Îªº°O¾ÐÅé¡C
«áÄò³B²z®É¯uªº¹J¨ì°ÝÃD¦A¨Ó½Ð±Ð¤j®a¡C


------------------------------------------------------------------------
Visual Basic ¤¤ªº°}¦C¤j¤p

Visual Studio 2005 ¨ä¥Lª©¥» ¥»¥DÃD©|¥¼±µ¨üµû¤À - ¬°³o­Ó¥DÃDµû¤À
°}¦C¤j¤p¬°¨ä©Ò¦³ºû«× (Dimension) ªø«×¤§­¼¿n¡C¥¦¥Nªí¥Ø«e¥]§t©ó°}¦C¤¤ªº¤¸¯ÀÁ`¼Æ¡C
¤U¦C½d¨Ò«Å§i¤@­Ó¤Tºû°}¦C¡J
Dim prices(3, 4, 5) As Long
ÅÜ¼Æ prices °}¦Cªº¾ãÅé¤j¤p¬° (3 + 1) x (4 + 1) x (5 + 1) = 120¡C

°}¦C¤j¤p¦Ò¼{¨Æ¶µ
³B²z°}¦C¤j¤p®É¡A½Ðª`·N´XÂI¡J
ºû«×ªø«×
¨C­Óºû«×ªº¯Á¤Þ³£ ¥H 0 ¬°°_ÂI¡A¤]´N¬O¥¦ªº½d³ò¬O¥Ñ 0 ¨ì¥¦ªº¤W­­ (Upper Bound)¡C¦]¦¹¡A«ü©wºû«×ªºªø«×·|¤ñ¸Óºû«×ªº«Å§i¤W­­¦h 1¡C

ªø«×­­¨î
¨C­Ó°}¦C¤§ºû«×ªø«×³£¨ü­­©ó Integer ¸ê®Æ«¬§Oªº³Ì¤j­È¡A¤]´N¬O (2 ^ 31) - 1¡CµM¦Ó¡A°}¦C¤§Á`¤j¤p¤]¦P®É¨ü­­©ó¨t²Î¥i¥Îªº°O¾ÐÅé¡C­Y±z¸Õ¹Ï¹ïÁ`¤j¤p¶W¹L¥i¥Îªº RAM ¤§°}¦C¶i¦æªì©l¤Æ¡ACommon Language Runtime ±NÂY¦^ OutOfMemoryException ¨Ò¥~ª¬ªp¡C

¤j¤p¤Î¤¸¯À¤j¤p
°}¦C¤j¤p»P¨ä¤¸¯Àªº¸ê®Æ«¬§OµLÃö¡C¤j¤p¥Ã»·¬O«ü¤¸¯ÀªºÁ`¼Æ¡A¦Ó¤£¬O¥¦­Ì©óÀx¦sÅ餤©Ò¦ûªº¦ì¤¸²Õ¡C
°O¾ÐÅé®ø¯Ó¶q
¹ï°}¦C¦b°O¾ÐÅ餤ªºÀx¦s¤è¦¡°µ¥ô¦ó°²³]³£¬O¤£¦w¥þªº¡CÀx¦sÅé·|¦]¤£¦P¸ê®Æ¼e«×ªº¥­¥x¦Ó¦³®t²§¡A©Ò¥H¬Û¦P°}¦C©ó 64 ¦ì¤¸¨t²Î¤W©Ò¦û°O¾ÐÅé±N¸û 32 ¦ì¤¸¨t²Î¨Óªº¦h¡C·í±zªì©l¤Æ°}¦C®É¡AÀHµÛ¨t²Î²ÕºA¤£¦P¡ACommon Language Runtime (CLR) ·|«ü¬£Àx¦sÅéºÉ¥i¯à±N¤¸¯À¦s©ñ¦b¤@°_¡A©Î¬O®Ú¾Ú¹êÅéµwÅé¬É­­±N¥¦­Ì¥þ³¡¥[¥H½Õ¾ã¡C¦P®É¡A°}¦C»Ý­n¯Ó¥ÎÀx¦sÅé¥H¨Ñ¨ä±±¨î¸ê°T¨Ï¥Î¡A¦¹®ø¯Ó¶q·|ÀHµÛºû«×¼W¥[¦Ó¼W¥[¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 47# ML089

·PÁÂML089,
ss

TOP

¦^´_ 47# ML089
«D±`¸ÔºÉªº¸ê°T¡AÁÂÁ¡A¤S¾Ç¨ì¤F¤£¤Ö¡C

TOP

¦^´_ 47# ML089
   
³B²z°}¦C¤j¤p®É¡A½Ðª`·N´XÂI¡J
ºû«×ªø«×
¨C­Óºû«×ªº¯Á¤Þ³£ ¥H 0 ¬°°_ÂI¡A¤]´N¬O¥¦ªº½d³ò¬O¥Ñ 0 ¨ì¥¦ªº¤W­­ (Upper Bound)¡C¦]¦¹¡A«ü©wºû«×ªºªø«×·|¤ñ¸Óºû«×ªº«Å§i¤W­­¦h 1¡C
¤]¥i¥H¬O³o¼Ëªº


EX.GIF
2013-7-6 05:49
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD