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

[µo°Ý] ½Ö¯àÀ°³o´X¥G­«½Æ°Ê§@ªºVBA½G¨­ ÁÂÁÂ

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-8-30 19:50 ½s¿è

¦^´_ 60# ML089


¥þ°ì°}¦C½T¹ê¸û§Ö¬Ù¸ê·½!

MMULT¦bXP+OFFICE2000¤U, ³Ì¦h¥i¥Î¨ì5643¦C, ¦A¦h´N#VALUE,
¤£ª¾¨ä¥Lª©¥»¥i¥Î¨ì¦h¤Ö¦C?


¡Ä¡Äµn¿ý®É³£¤Ä¿ï¤F¡e¦Û°Êµn¿ý¡f¡A¨C¦¸¶i¨Ó½×¾ÂÁÙ­n¦A¿é¤J±b¸¹±K½X¡A¯u¤£¤è«K¡I
¡@

TOP

¦^´_ 61# ­ã´£³¡ªL


MMULT ¦b2007ª©
1. ´ú¸Õ 1000000X2 ¤Î 2X1000000 ³£¥i¥H¶]
2. ´ú¸Õ 4000X4000¥H¤W´N·|¥X²{¤½¦¡¸ê·½¤£¨¬ªº°T®§
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 61# ­ã´£³¡ªL

¡Ä¡Äµn¿ý®É³£¤Ä¿ï¤F¡e¦Û°Êµn¿ý¡f¡A¨C¦¸¶i¨Ó½×¾ÂÁÙ­n¦A¿é¤J±b¸¹±K½X¡A¯u¤£¤è«K¡I

§Úªº·|¦Û°Êµn¿ý(WIN10+CHROME)
¦ý¤@¬q®É¶¡(¦n¹³¤@­Ó¬P´Á§a)´N·|­n¨D­«·sµn¿ý¡A­Y§A¤£±`¤W¨Ó´N·|·Pı¨C¦¸µn¿ý³£­n­«·s¿é¤J±b¸¹±K½X¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-30 23:00 ½s¿è

¦^´_ 47# ­ã´£³¡ªL

½Ð°Ý¦³¨S¦³¿ìªkÅý§Ú³oÀÉ®× (°£¤F©î³æ¥~)
¯à°÷©ó¹Bºâ§¹«á ´N±N¬Y­Ó¥\¯àÃö±¼ ¦nÅý¥¦§â¸ê·½Åý¥X¨Ó
¤]´N¬O»¡¦b°õ¦æ¦P¼Ë¨ºÀÉ ¦ý¤£»Ý­n¥¦¦b§Y®É¹Bºâ
¦]¬°²Î­p§¹«á´N·Óªí¾Þ½Ò¤F ¥u¬OÁÙ·|»Ý­n¥Î¨ì§Oªº³¡¤À
¦³³Ò±z¤F ÁÂÁÂ
§Ú²{¦b¬O"¨ç¼Æ+vba" double check ¤]¤ñ¸û©ñ¤ß

TOP

¦^´_ 62# ML089


«¢! ÁÙ¬O·sª©¦³®Æ~~
ÁÂÁ¡I

¤§«e³£¥Î©_¼¯ª¾ÃÑ+¡A²{¦bª©­±¤@§ï¤w­±¥Ø¬Ò«D¡A´XµLexcel±M¥Î®ÄªG¡I
excel-home«D±`±M·~¡A¦ý¥xÆW¤Ö¤H¥Î¡A¦³®É¦ê¦êªù¤l¡A
¥xÆW¤ñ¸û±M¤_excel½×¾Âªº¡A¦ü¥G¥u³Ñ³o¸Ì¡A¦Ó¦U¦ìªO¥D¥\¤O¤]´X¥iÀ°¦£¤j³¡¥÷°ÝÃD¡A©Ò¥H¤]¤Ö¤W¨Ó¡]¤W¨Ó¤]¬O¬Ý¬Ý¦Ó¤w¡^¡I

TOP

¦^´_ 64# lcctno


¥»¨Ó¬ï¿Ç¤l¡]¤½¦¡¡^¶ûºC¡A§ï¦¨¸È¤l¡]vba¡^¸û§Ö¨Ç¡A
²{¦b·Q¿Ç¤l¦A¬ï·f¸È¤l¡A°ÝÃD¤S¦^¨ì­ìÂI¡A
¤½¦¡½d³ò²Ä¤@®æ¯d¤½¦¡¡A¨ä¤U½Æ»s«á¦A¶K¦¨­È¡]¥H¢´¢´¼Ó½d¨ÒÀɬ°·Ç¡^¡G
Sub §ó·s¤½¦¡()
With [µ²ªG1!B4:B2562]
¡@¡@¡@[µ²ªG1!B3].Copy .Cells
¡@¡@¡@.Value = .Value
End With
End Sub

³o¼Ë¥u¦³²Ä¤@®æ¤½¦¡·|¼vÅT¹B§@¡ã¡ã

©Î¦p¤U¡G
Sub §ó·s¤½¦¡2()
With [µ²ªG1!B3:B2562]
¡@¡@.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=¸ê®Æ¦C_³Ì°ª»ù)*(A3>=¸ê®Æ¦C_³Ì§C»ù)))"
¡@¡@.Value = .Value
End With
End Sub

°õ¦æ¤¤¶·µ¥«Ý¨Ç®É¶¡¡]¦³¦p·í¾÷¡^¡A§¹¦¨«á§Y¤£·|¼vÅT¨ä¥¦¾Þ§@¡I
¡@

TOP

¦^´_ 66# ­ã´£³¡ªL
³ø§i°ª¤â
°õ¦æ¤¤¶·µ¥«Ý¨Ç®É¶¡¡]¦³¦p·í¾÷¡^
°õ¦æ³o¶µ¥Î¤£¨ì4¬í «Ü­È±o
¦ý¬O¦³¤@­Ó°ÝÃD Àx¦sÂ÷¶}«á ¦A¶}±Ò µo²{­Y§ïÅܲέp¤é´Á¤§°Ï¶¡ ´N¤£·|§ïÅÜ¼Æ¾Ú ¥B¥´¶}®É¨Ã¨S¦³©úÅ㪺¹Bºâ°Ê°µ ³o¼Ë¨ºÀÉ®×´N¬O¥u¯à¤@¦¸©Ê¨Ï¥Î(°£«D¬O¤£·Q§ïÅܲέp°Ï¶¡ ©Î¿é¤J¨ä¥LªÑ²¼ªºdata ¦³¨S¦³¿ìªk¥[¤@­Ó¶}Ãö ÀH®É¥iÀH©Ê¦Ó¥Î
«Ü³øºp¸ûºC¦^ÂÐ ¦]¦b°µ´ú¸Õ¨º°õ¦æÀÉ (¥Ñ©ó¬Ý¤£À´ ¥u¯à¹ê°µ¬Ý²{¶H) ©Ò¥Hªá¤F¤£¤Ö®É¶¡

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=¸ê®Æ¦C_³Ì°ª»ù)*(A3>=¸ê®Æ¦C_³Ì§C»ù)))"  
½Ð³Â·Ð§i¶D§Ú¦³³o¦æ¬O¤°»ò¥Î³~?

ÁÂÁ±zªºÀ°¦£

TOP

¦^´_ 67# lcctno


¦ý¬O¦³¤@­Ó°ÝÃD Àx¦sÂ÷¶}«á ¦A¶}±Ò µo²{­Y§ïÅܲέp¤é´Á¤§°Ï¶¡ ´N¤£·|§ïÅÜ¼Æ¾Ú ¥B¥´¶}®É¨Ã¨S¦³©úÅ㪺¹Bºâ°Ê°µ
³o¼Ë¨ºÀÉ®×´N¬O¥u¯à¤@¦¸©Ê¨Ï¥Î(°£«D¬O¤£·Q§ïÅܲέp°Ï¶¡ ©Î¿é¤J¨ä¥LªÑ²¼ªºdata ¦³¨S¦³¿ìªk¥[¤@­Ó¶}Ãö ÀH®É¥iÀH©Ê¦Ó¥Î

·Q¨Ó·½¸ê®ÆÅÜ°Ê¡A¤½¦¡¦Û°ÊÅÜ°Ê¡A¨º´N¬O¡e±`¾n¤½¦¡¡f¡A¤]¬O¦]³o­Ó¦ÓÅý¾Þ§@ÀÉ®×ê¤â¡A
©Ò¥H¡A¸ê®ÆÅÜ°Ê¡A¥u¯à´£¨Ñ¡e¥HVBA¨ê·s¡f¤è¦¡±o¨ì³Ì·sµ²ªG¡A¡A¦]¦¹¡A¨âºØVBA¥u¬O¥Î¨ÓÅçÃÒ¨âµ{µ²ªG¬O§_¤@­P¡H
­Y¸ê®Æ¤£¦h¡A´N«O«ù¤½¦¡±`ºA¡A³sVBA³£¥i¬Ù¥h¤F¡I
­Y¸ê®Æ«Ü¦h¤S­n«O«ù¤½¦¡¡A®¤µL¤èªkÅýÀɮפ£¥d¡]Ãö±¼¦Û°Ê­«ºâ¡A»P¨Ï¥ÎVBA¦P¼Ë®ÄªG¡A¦ý·|³y¦¨¨ä¥¦¤½¦¡µLªk¹Bºâ¡A¤£¬O¦n¤èªk¡^¡A

.Formula = "=IF(A3="""","""",SUMPRODUCT((A3<=¸ê®Æ¦C_³Ì°ª»ù)*(A3>=¸ê®Æ¦C_³Ì§C»ù)))"  
³o¤£¬OªþÀɤ¤ªº¤½¦¡¶Ü¡H¥u¬O§Q¥ÎVBA¥h¸m¤J¡I¤]¥i¥Î¡e¿ý»s¡f¨ú±oFormulaR1C1ªº¤½¦¡¡I

¦³¨Ç°ÝÃD»Ý­n²Ö¿n¸gÅç¤~À´¡A¤@®ÉµLªk»¡¤Ó¸Ô²Ó¡A½ÐºCºC°Ñ°u¡ã¡ã
¡@

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD