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

[µo°Ý] ¨D«ü©wÄæ¦ìªº¦¸¤p­È¤Î¦¸¤j­È(¤£¥]§t0)

[µo°Ý] ¨D«ü©wÄæ¦ìªº¦¸¤p­È¤Î¦¸¤j­È(¤£¥]§t0)

¤j¤j­Ì,
¤Uªí­n¨D¦¸¤p­È¤Î¦¸¤j­È(³£¤£¥]§t0)
ÄæC2~C24¦³¥H¤U¼Æ­È,§Ú·Q¦bD2¿é¤J¤½¦¡,¨D«ü©wÄæ¦ìªº¦¸¤p­È?E2¿é¤J¤½¦¡¨D«ü©wÄæ¦ìªº¦¸¤j­È? ½Ð°Ý¦³¤°»ò¤½¦¡¥i¥H¨D±oµª®×?  TKS.

¦^´_ 1# PJChen

¦b§A¨D²³¼Æ¨º©«¤¤¾Ç¨ìªº¤èªk
http://forum.twbts.com/viewthrea ... amp;page=6#pid52283

D2Àx¦s®æ=SMALL(C:C,COUNTIF(C:C,"<"&C2))¤U©Ô
E2Àx¦s®æ=LARGE(C:C,COUNTIF(C:C,">"&C2))¤U©Ô

0©M#NUM!ªº°ÝÃD¹ï§A¨Ó»¡À³¸Ó¤£Ãø¡A´N½Ð§A¦Û¤v³B²zÅo~

TOP

¦^´_ 2# Bodhidharma

·PÁ±zªº´£¥Ü.
§ï¦¨¥H¤U¥i¸Ñ¨M#NUM!°ÝÃD,¤£¹L¦¸¤p­È0¤£¥X²{ªº°ÝÃD´N¸Õ¤£¥X¨Ó¤F!!

D2=IFERROR(SMALL(C:C,COUNTIF(C:C,"<"&C2)),"")

E2=IFERROR(LARGE(C:C,COUNTIF(C:C,">"&C2)),"")

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-4-25 11:35 ½s¿è

¦^´_ 3# PJChen

¸Õ¸Õ¬Ý ¦¸¤j¤p.rar (10.78 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

·PÁ¤j¤j.
«Üok.

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-4-25 19:33 ½s¿è

­É4¼ÓÀÉ®×´ú¸Õ

°}¦C¤½¦¡

¦¸¤p¡A´N¬O¤p©ó¸Ó¼Æªº³Ì¤j­È¡A¥~¥[TEXT¥h0­È
=MAX(IF($A$1:$A$222<A1,$A$1:$A$222))
=TEXT(MAX(IF($A$1:$A$222<A1,$A$1:$A$222)),"0;;")

¦¸¤j¡A´N¬O¤j©ó¸Ó¼Æªº³Ì¤p­È¡A¥~¥[TEXT¥h0­È
=MIN(IF($A$1:$A$222>A1,$A$1:$A$222))
=TEXT(MIN(IF($A$1:$A$222>A1,$A$1:$A$222)),"0;;")

¦]TEXT¦^¶Ç«á¬°¤å¦r¡A«áÄò¨Ï¥Î»Ýª`·N¦AÂର¼Æ­È
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

[ª©¥DºÞ²z¯d¨¥]
  • ¤p»x(2013-4-25 20:15): ¨Ï¥Î"¥N½X"¼ÐÅÒ,³o¼Ë¬O¤£¬O²M·¡¦h¤F

­É4¼ÓÀÉ®×´ú¸Õ

°}¦C¤½¦¡

¦¸¤p¡A´N¬O¤p©ó¸Ó¼Æªº³Ì¤j­È¡A¥~¥[TEXT¥h0­È
  1. =MAX(IF($A$1A$222<A1,$A$1A$222))
  2. =TEXT(MAX(IF($A$1A$222<A1,$A$1A$222)),"0;;")
½Æ»s¥N½X
¦¸¤j¡A´N¬O¤j©ó¸Ó¼Æªº³Ì¤p­È¡A¥~¥[TEXT¥h0­È
  1. =MIN(IF($A$1A$222>A1,$A$1A$222))
  2. =TEXT(MIN(IF($A$1A$222>A1,$A$1A$222)),"0;;")
½Æ»s¥N½X
¦]TEXT¦^¶Ç«á¬°¤å¦r¡A«áÄò¨Ï¥Î»Ýª`·N¦AÂର¼Æ­È
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# Hsieh

¶Wª©
¥i§_±N ¸T¥Îªí±¡ ³]¬°¤º©w

6¼Ó±Ä¥Î²³æ¼Ò¦¡¤U µoªí¦^ÂÐ ´N·|¦³ªí±¡
7¼Ó±Ä¥Î°ª¯Å¼Ò¦¡¤U µoªí¦^ÂÐ ±N ¸T¥Îªí±¡ ¤Ä¿ï(¥»´N¤w¸g¤º©w¤Ä¿ï)
¥H«e§Ú³£¬O¥Î6¼Ó¤è¦¡´N¤£·|¥X²{"ªí±¡"³o¼Ë¡A¤£ª¾³o´X¤Ñ³£Åܦ¨³o¼Ë¯u¤£¤è«K¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089


    ³o¤£¬O¨t²Î°ÝÃD,³o¬Oµo¨¥®É·|­ûÀ³ª`·Nªº­ì«h
µ{¦¡½XÀ³¸Ó¨Ï¥Î"¥N½X"¥]¬A°_¨Ó,³o¼Ëµ{¦¡½X¤º®e´N¤£·|¥X¿ù,¬Ýªº¤H¤]²M·¡,
©Ò¥H½Ð¦U¦ìµo§Gµ{¦¡½X®É¦h¥[ª`·N
2013-4-25 ¤U¤È 08-11-20.png
­É¥Î§A#7ªºµo¤å,¨Ï¥Î"¥N½X"¼ÐÅÒµo¤å,¬Ý°_¨Ó¬O¤£¬O²M·¡¦h¤F?
¤ß¦w¡A¥­¦w   
µoªí¥ô¦óµL·N¸qªº·PÁ©ÎÄé¤ô¤å³¹¤@«ß¬å,¬Ã±¤±b¸¹,½Ð¸Ô¾\ª©³W!!

TOP

¦^´_ 3# PJChen
  1. =IFERROR(1/(1/SMALL(A:A,COUNTIF(A:A,"<"&A1))),"")
  2. =IFERROR(1/(1/LARGE(A:A,COUNTIF(A:A,">"&A1))),"")
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD