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

[µo°Ý] EXCELªº¤½¦¡ ¤£ª¾À³«ç¼g

[µo°Ý] EXCELªº¤½¦¡ ¤£ª¾À³«ç¼g

½Ð°Ý¤@¤U ¤£ª¾ EXCEL¦pªG·Q¨Ï¥Î¹³¬O¢ä¢Ð¢Ï¤¤ªºEND(xldown) ¥\¯àªº¡@¤½¦¡À³­n«ç¼g©O¡H¡H

¦^´_ 1# icestormer


    play.gif
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh


  ·PÁ¶Wª©^^

TOP

¥»©«³Ì«á¥Ñ icestormer ©ó 2012-7-31 14:25 ½s¿è

¦^´_ 3# icestormer


   ¶Wª©§A¦n ¨º­Ó¥Ñ¤U¦V¤W§äªº ¦³¦¨¥\¤F¦ý ¥Ñ¤W¦V¤U³£¤@ª½¥X¿ù,¨Sªk¦¨¥\©O
§Ú§Ë¤F´X¤Ñ(¦b°Ý¤§«e¤]¦³¸ÕµÛ§Ë¬Ý¬Ý)¹ê¦b¬O§Ë¤£¥X¨Ó..
§Ú¥D­n¬O·Q­n°µ³o­ÓªF¦è§Úªþ¤WEXCELÀɦn¤F EXCEL´ú¸Õ.rar (17.86 KB)
¦]§Ú·Q¤£¥X¦³¤°»ò¤½¦¡©Îªk¤l¥i¥H¤@¦¸´Nºâ¥X¯u¹êªº¥­§¡»ù¦ì,¥u¦n³z¹L«e¤G¦¸¥­§¡­Èªº¨Ó±Æ°£¤@¨Ç¤£¦X²zªº
EXCEL¸Ìªºªí¦³¥D­n¦³¤G­Ó¦ý¸ê®Æªø«×¬O·|Åܰʪº

D1=AVERAGE(C188:C330) ¥ý¥Î³o¤½¦¡ºâ²Ä¤@¦¸ªº¥­§¡­È
D2=AVERAGE(C188:C286)¦A®Ú¾Ú¤WÀYªº[71.66] ,±NC188:C330¤§¤¤¿ï¾Ü ¤ñ71.66ÁÙ°ªªº¼Æ­È ºâ¥­§¡­È(³o³¡¥÷¦n¹³±o¥Î¤W§ä¨ì²Å¦Xªº¼Æ­È«á¶Ç¦^¸Ó¦ì§},ªº¼Ë¤l)
D3=³Ì«á¦A¨Ì²Ä¤G¦¸¥­§¡ªº­È[79.14] ±qC188:C248 ºâ¥X²Ä¤T¦¸¥­§¡

¤£ª¾§Ú·Q³o¼Ë°µEXCEL ¬O§_¥i¥H°µ¨ì?? ¦A¦¸³Â·Ð¤F

TOP

¦^´_ 3# icestormer

¤W¤@¤å¦³ÂI°ÝÃD ½Ð¥H³o¤å¬°·Ç ÁÂ

   ¶Wª©§A¦n ¨º­Ó¥Ñ¤U¦V¤W§äªº ¦³¦¨¥\¤F¦ý ¥Ñ¤W¦V¤U³£¤@ª½¥X¿ù,¨Sªk¦¨¥\©O
§Ú§Ë¤F´X¤Ñ(¦b°Ý¤§«e¤]¦³¸ÕµÛ§Ë¬Ý¬Ý)¹ê¦b¬O§Ë¤£¥X¨Ó..
§Ú¥D­n¬O·Q­n°µ³o­ÓªF¦è§Úªþ¤WEXCELÀɦn¤F EXCEL´ú¸Õ.rar (17.86 KB)
¦]§Ú·Q¤£¥X¦³¤°»ò¤½¦¡©Îªk¤l¥i¥H¤@¦¸´Nºâ¥X¯u¹êªº¥­§¡»ù¦ì,¥u¦n³z¹L«e¤G¦¸¥­§¡­Èªº¨Ó±Æ°£¤@¨Ç¤£¦X²zªº
EXCEL¸Ìªºªí¦³¥D­n¦³¤G­Ó¦ý¸ê®Æªø«×¬O·|Åܰʪº

D1=AVERAGE(C186:C279) ¥ý¥Î³o¤½¦¡ºâ²Ä¤@¦¸ªº¥­§¡­È
D2=AVERAGE(C188:C286)¦A®Ú¾Ú¤WÀYªº[6.89] ,±NC186:C235¤§¤¤¿ï¾Ü ¤ñ6.89ÁÙ°ªªº¼Æ­È ºâ¥­§¡­È(³o³¡¥÷¦n¹³±o¥Î¤W§ä¨ì²Å¦Xªº¼Æ­È«á¶Ç¦^¸Ó¦ì§},ªº¼Ë¤l)
D3=³Ì«á¦A¨Ì²Ä¤G¦¸¥­§¡ªº­È[6.81] ±qC186:C108 ºâ¥X²Ä¤T¦¸¥­§¡

¤£ª¾§Ú·Q³o¼Ë°µEXCEL ¬O§_¥i¥H°µ¨ì?? ¦A¦¸³Â·Ð¤F

TOP

EXCELªº´X¤½¦¡¹ê¦b§Ë¤£¥X¨Ó

¹ï¤£°_ ¤£ª¾«ç¤@¦^¨Æ ¤@ª½»¡§Ú¤£¯à­×§ï.. ¤S³Q§ÚPOªº¶Ã¤F §Ú´N­«PO¤@­Ó

   ¶Wª©§A¦n ¨º­Ó¥Ñ¤U¦V¤W§äªº ¦³¦¨¥\¤F¦ý ¥Ñ¤W¦V¤U³£¤@ª½¥X¿ù,¨Sªk¦¨¥\©O
§Ú§Ë¤F´X¤Ñ(¦b°Ý¤§«e¤]¦³¸ÕµÛ§Ë¬Ý¬Ý)¹ê¦b¬O§Ë¤£¥X¨Ó..
§Ú¥D­n¬O·Q­n°µ³o­ÓªF¦è§Úªþ¤WEXCELÀɦn¤F EXCEL´ú¸Õ.rar (13.75 KB)
¦]§Ú·Q¤£¥X¦³¤°»ò¤½¦¡©Îªk¤l¥i¥H¤@¦¸´Nºâ¥X¯u¹êªº¥­§¡»ù¦ì,¥u¦n³z¹L«e¤G¦¸¥­§¡­Èªº¨Ó±Æ°£¤@¨Ç¤£¦X²zªº
EXCEL¸Ìªºªí¦³¥D­n¦³¤G­Ó¦ý¸ê®Æªø«×¬O·|Åܰʪº

D1=AVERAGE(C5:C170) ¥ý¥Î³o¤½¦¡ºâ²Ä¤@¦¸ªº¥­§¡­È(³o¸Ì¬O60,360,54)
D2=®Ú¾Ú¤WÀYªº[60,360,54] ,±NC5:C170¤§¤¤¿ï¾Ü ¤ñ60,360.54ÁÙ§Cªº¼Æ­È ,ºâ¥­§¡­È(³o³¡¥÷¦n¹³±o¥Î¤W§ä¨ì²Å¦Xªº¼Æ­È«á¶Ç¦^¸Ó¦ì§},ªº¼Ë¤l)
D3=³Ì«á¦A¨Ì²Ä¤G¦¸¥­§¡ªº­È[120.3] ±qC5:C170§ä¨ì¤ñ120.3ÁÙ§Cªº­È ºâ¥X²Ä¤T¦¸¥­§¡

¨Ì¤WÀYªº»Ý¨D ²Ä¤@¨B±o¯à¦Û°Êºâ¥X¥Ñ¤W¦Ü¤Uªº¸ê®Æ¦³´Xµ§(¤§«á¥Î¦b½d³ò¤§¥Î)
¦A¨Ó «h¬O ³sÄò¤G¦¸«ö ¥­§¡­È ¦b©T©w½d³ò¤º(C5:C170)¤¤ §ä¨ì¤ñ¥­§¡­È§Cªº¼Æ­È¥ÑC5:¨ì¸Ó¼Æ­È¬°½d³ò ­«­p¤@¦¸¥­§¡­È

¤Ñ§r ¬Ý°_¨Ó¦n³Â·Ð ÁÙ¬O¶Wª©¦³§ó²³æ¦n¥Îªº¤èªk ¥i¥H¤@¨Bºâ¥X¨Ó©O?
(¦]¦³ªº»ù®æ«Ü°ª¦ý¼Æ¶q«Ü§C ¦pªG¤£§â¥¦±Æ°£,»ù®æ±N·|ÄY­« ¥¢¯u)

TOP

¦^´_ 5# icestormer
D1=AVERAGE(C186:C279) ¥ý¥Î³o¤½¦¡ºâ²Ä¤@¦¸ªº¥­§¡­È
D2=AVERAGE(C188:C286)¦A®Ú¾Ú¤WÀYªº[6.89] ,±NC186:C235¤§¤¤¿ï¾Ü ¤ñ6.89ÁÙ°ªªº¼Æ­È ºâ¥­§¡­È(³o³¡¥÷¦n¹³±o¥Î¤W§ä¨ì²Å¦Xªº¼Æ­È«á¶Ç¦^¸Ó¦ì§},ªº¼Ë¤l)
D3=³Ì«á¦A¨Ì²Ä¤G¦¸¥­§¡ªº­È[6.81] ±qC186:C108 ºâ¥X²Ä¤T¦¸¥­§¡

¬Ý¤£À´§A¨C¦¸¥­§¡ªº½d³ò¦³¬Æ»ò³W«h?
´NµLªk±oª¾»P§Aªºµo°Ý¦³¦óÃöÁp?
¦Ü©ó§Axldownªº¤½¦¡
À³¸Ó¬O°}¦C¤½¦¡=INDEX($A$2:$A$171,MATCH(TRUE,$A$2:$A$171="",0)-1,)
¦Ó¤£¬O=INDEX($A$2:$A$171,MATCH(TURE,$A$2:$A$171="",0)-1,)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


    EXCEL´ú¸Õ.rar (13.75 KB)   ¥Î³o­Óªþ¥ó »¡¤@¤U¦n¤F ¤§«e¦³ÂI°ÝÃD..

¤@¶}©l ¥ý±qC5:C170 ³o¬O¥Ø«e²Ä¤@­Óªíªº¸ê®Æªø«×(³o»Ý­n¥ý±qC5¦V¤U§ä¨ì³Ì«á¤@µ§(C170)¨Ã¶Ç¦^¸Ó¦ì§}($C$170) ·|±o¥X 60,360.54
¦A¨Ó ¦A§Q¥Î¨º­Ó60,360.54¥h¤ñ¹ï½d³ò C5:C170¤§¶¡ ¦³¨º¤@­Ó¼Æ­È¤ñ¥¦°ªªº(ªí¤¤¬OC170®æ¤ñ60,360.54°ª),¦A§â­nºâ¥­§¡­Èªº½d³ò±qC5:170>>C5:169 ±o¥X 120.30
¦A­«´_¤@¦¸ §â 120.30 ¦A¥h¤ñ¹ï ½d³òC5:C170¤§¶¡ ¦³¨º¤@­Ó¼Æ­È¤ñ¥¦°ªªº(ªí¤¤¬OC158®æ¤ñ120.3ÁÙ°ª),ºâ¥­§¡­Èªº½d³ò±qC5:C170>>>C5:C158 ³Ì«á±o¥X65.15

1:­n¯à¦Û°Ê«ö¸ê®ÆªíC5:ºâ¦Ü³Ì«á¤@µ§ªº ¥­§¡­È
2:­n¯à¤ñ¹ï¥X¤ñ¥­§¡­È°ªªº¼Æ­È¦V¤W¤@®æ ªº½d³ò,¨ÌC5¨ì¨º¤@®æ¬°½d³òºâ¥X¥­§¡­È.


§Ú¦Û¤v¦³¸ÕªÌ¥ÎMATCH ¬O¥i¥HÅã¥Ü²Å¦Xªº¸ê®Æ¦b½d³ò¤¤ªº²Ä´X®æ¦ý¨SªkÅã¥Ü¦ì§}(¨Ò:C20) ³o¬O¤£¬O­n°t¦XADDRESS,?¤S­n«ç¼gªk©O??

TOP

¦^´_ 8# icestormer


   
¨º­Ó¤½¦¡§Ú¥Î½Æ»s¨Ã¶K¤W¥hEXCEL ¤W¦ýÓýÅã¥Ü(#N/A) ¤£ª¾¬O¨º¥X¿ù¤F??

TOP

¦^´_ 9# icestormer

¤½¦¡¿ù»~¬O¦]¬°¥²¶·¨Ï¥Î°}¦C¤½¦¡¤~¯à¦¨¥\(Ctrl+Shift+Enter)

¸Õ¸Õ¬Ý
¥ý«Ø¥ß¤@­Ó¦WºÙrng
°Ñ·Ó¨ì¤½¦¡=OFFSET(§ó·s!$C$5,,,MATCH(TRUE,§ó·s!$A$5:$A$65536="",0)-1,)

D1=AVERAGE(rng)
D2=SUMPRODUCT((rng<D1)*rng)/(COUNT(rng)-SUMPRODUCT((rng>=D1)*1))
D3=SUMPRODUCT((rng<D2)*rng)/(COUNT(rng)-SUMPRODUCT((rng>=D2)*1))   
EXCEL´ú¸Õ.zip (16 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD