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

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

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

¦h±ø¥ó¥[Á`®Ä²v SUMIFS³Ì§Ö¡ASUMPRODUCT ¦¸¤§
§Ú¼gªºVBA³ÌºC¡C
¤£ª¾¹D¦U¦ì¤j¤j¯à§_«üÂI¤@¤U¡A¬O§_¦³¿ìªk´£°ªVBAªº®Ä²v¡C

¨â­Ó±ø¥óªº¥[Á` - Sonny.rar (491.08 KB)
ss

¦^´_ 50# GBKEE

ÁÂÁ¸ɥR»¡©ú
°}¦Cªº¯Á¤Þ¤W¤U«ü¼Ð¤]¥i¥H¦Û­q
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

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¼Ëªº


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

TOP

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

TOP

¦^´_ 47# ML089

·PÁÂML089,
ss

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

¦^´_ 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

¦^´_ 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

¥»©«³Ì«á¥Ñ 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

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

¦^´_ 42# ML089

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

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

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

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD