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

[µo°Ý] ¨ú¥X©T©w®É¶¡¶¡¹j¤º²Å¦X±ø¥óªº¸ê®Æ

[µo°Ý] ¨ú¥X©T©w®É¶¡¶¡¹j¤º²Å¦X±ø¥óªº¸ê®Æ

¤j®a¦n¡A½Ð±Ð¤@­Ó°ÝÃD¡G

°²³]§Úªºªº¼Æ¾Ú¸ê®Æ¬O¨C¬í¤@µ§¡A¥B³sÄò¦¬¶°¤@¾ã¤Ñ¡C
¥Ø¼Ð¡G¿z¿ï¥X¨C10¤ÀÄÁ¸ê®Æ¤¤ªº³Ì§C­Èªº«e¦Ê¤À¤§¤G¤Q¡C

§Úªº·Qªk¬O¥ý±N¸ê®Æ¤À¦¨10¤ÀÄÁ¤@²Õ¡AµM«á¦A§ä¥X¨C²Õªº³Ì§C­È«e20¢H
¦]¬°§Ú¤£·|¼gVBA¡A©Ò¥H´N¥h§ä¤º«Ø¨ç¼Æ«÷´ê¦pªþ¥ó¡C
noisetest.zip (306.68 KB)

»¡©ú¦p¤U¡G
1. ­ì©l¼Æ¾Ú¥u¦³¨âÄæ¦ì¡ÐTIME©MVALUE¡]³£¬O¼ÒÀÀªº¤£¬O¯u¹ê¼Æ¾Ú¡^
2. ¥ÎQuotient()¨ç¼Æ±N¸ê®Æ¤À¦¨600¬í¤@²Õ¡]10¤ÀÄÁ¡^
3. ¥ÎPercentile()¨ç¼Æ§ä¥X«e20¢H¡F©Î¥ý±N¸ê®Æ±Æ§Ç¦A¨ú¥X«e20¢H¸ê®Æ¡C
4. ¥Î¼Ï¯Ã¤ÀªR©Î¶i¶¥¿z¿ï¤è¦¡¨ú¥X±ý¨D±o¤§¸ê®Æ¡C
½Ð¤j®aÀ°¦£½T»{¤@¤U§@ªk¬O§_¥¿½T¡H·PÁ¡C

¥t¥~¡A³oºØ§@ªkªº«e´£¬O¨C¬í¤@µ§ªº¸ê®Æ¨S¦³­«ÂФ]¨S¦³¯Ê¤Ö¡A¤~¯à©T©w¨C600µ§¤À¦¨¤@²Õ¡C
¦ý¹ê»Ú¦¬¸ê®Æ®É¤@©w¤£·|³o»ò²z·Q¡C¡C¡C¨º¨ç¼Æ­n«ç»ò­×§ï¤~¯à³B²z³oºØª¬ªp©O¡H
ÁÙ¦³¡A­n¨D±o¸ê®Æªº«e¦Ê¤À¤§´Xªº¼Æ¾Ú¬O¥Îpercentile()³o­Ó¨ç¼Æ¶Ü¡H
¡]§Úexcel¬O2013ª©¡A¥Îpercentile()µ²ªG©M¥Î¥Îpercentile.exe()­Ó¦³®t¶Z­C¡C¡^

ª½±µ¨ú600µ§¬°¤@²Õ¤ñ¸û²³æ
=IF(B6<PERCENTILE(OFFSET($B$6,INT((ROW()-6)/600),,600),20%),"Y","N")

¥H¨C10¤ÀÄÁ¤º¸ê®Æ¬°¤@²Õ¡A²Õ¼Æ¬ù600¤ñ¤W¤U¡A¤½¦¡¤¤»Ý§ä¥X½d³òªº°_¨´¥¼¸m
=IF(B6<PERCENTILE(INDIRECT("B"&MATCH(FLOOR(A6+1/86400,10/1440),A:A)&":B"&MATCH(CEILING(A6+1/86400,10/1440),A:A)-1),20%),"Y","N")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 1# rosebud

­Y¦³­«ÂЫh°Ï¶¡µ§¼Æ·|¶W¹L600µ§
¥H600¬í¤À²Õ¨ú±o¦P°Ï¶¡ªº¼Æ­È­pºâ¨ä¦Ê¤À¤ñ¬O§_¦b0.2
G6°}¦C¤½¦¡
=IF(PERCENTRANK.INC(IF(INT((ROUND(MOD($A$6:$A$6005,1)*86400,0))/600)=INT((ROUND(MOD(A6,1)*86400,0))/600),$B$6:$B$6005,""),B6,2)<0.2,"Y","N")
¦V¤U½Æ»s
³o¼Ëºâ¥X¨Ó«h¬O00:00:00~00:09:59¬°°Ï¶¡
³Ì®e©ö¬Ý¥X»P§Aªº¤è¦¡¤£¦P¦b³Ì«á2µ§¸ê®Æ¥i¬Ý¥X¨Ó
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¨â¦ìª©¥D«üÂI¡ã

§Ú±N­ì©l¤u§@ªí½Æ»s¤@¥÷¡]test2¡^¦A§â¨â¦ìª©¥D¼gªº¤½¦¡¤À§O¶ñ¤JHÄæ©MIÄæ¡A¨Ã¥B§â¸ê®Æ¶q±q6000µ§¬å¨ì3000µ§¥ª¥k¡C
¬°´ú¸Õ­Y¸ê®Æ¦³¯Êº|©Î­«½Æªºª¬ªp¤U¨â¤½¦¡ªº¤ÏÀ³¡A¬G·N§R°£3µ§¼Æ¾Ú¥H¤Î²K¥[2µ§­«½Æªº¼Æ¾Ú¡Aµ²ªG¦pªþ¥ó©Ò¥Ü¡A¨â¤½¦¡ªºµ²ªG¨Ã¤£¥þµM¤@¼Ë¡C noisetest.zip (755.72 KB)
¬O¤£¬O¦³»ò²Ó¸`§Ú¨Sª`·N¨ì©O¡H
ML890ª©¥Dªº¤½¦¡¬O¡A§ä¥X½d³òªº°_¥½¡A¦A§PÂ_¸Ó°Ï¶¡ªº¸ê®Æ¬O§_ÄÝ©ó«e20¢H¡F
Hsiehª©¥Dªº¤½¦¡¬O¡A§ä¥X00:00:00~00:09:59°Ï¶¡¡A¦A§PÂ_¸Ó°Ï¶¡ªº¸ê®Æ¬O§_ÄÝ©ó«e20¢H
§Ú¬O§_¦³»~¸Ñ©O¡H

TOP

¦^´_ 4# rosebud


    ¤½¦¡­×§ï¤@¤U¡A©M§AFÄ檺¤@¼Ë
=IF(B6<PERCENTILE(INDIRECT("B"&MATCH(FLOOR(A6+1/864000,10/1440),A:A)&":B"&MATCH(CEILING(A6+1/864000,10/1440)-1/864000,A:A)),20%),"Y","N")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦³·N¨£¸ß°Ý®É¡A½Ð«ö·N¨£¤U¤èªº¦^ÂСA³o¼Ë¤~¯à¦¬¨ì³qª¾¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089


    ¦nªº~ÁÂÁª©¥D¼ö¤ß«ü¾É^^

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD