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

Ãö©ó¼Ò½k¬d§ä ¦h­Ó­È °ÝÃD

Ãö©ó¼Ò½k¬d§ä ¦h­Ó­È °ÝÃD

A1  
A3
A5
J1
J22
J3
Àx¦s®æ¦p¤W   
§Ú¦bBÄæ¥h§âJÀx¦s®æ©Ò¦³¸ê®Æ§ì¥X¨Ó  ¼gªk¦p¤U  ¦ý¬OÀx¦s®æ©¹¤U©Ô ³£¥uªá§ì¨ìJ1  ¦³¿ìªk Åý¤½¦¡©¹¤U©Ô
¥i¥H§ì¨ú¨ì¥þ³¡ªºÀx¦s®æ¶Ü?

=INDEX($A:$A,MATCH("J"&"*",$A:$A,0))

¦^´_ 1# iwiniwin

B1 ¿é¤JÃöÁä¦r
B2=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(FIND($B$1,$A$1:$A$100)),ROW($1:$100),""),ROW(A1))),"") °}¦C¤½¦¡¡A¤U©Ô

TOP

¥»©«³Ì«á¥Ñ JBY ©ó 2015-3-5 23:16 ½s¿è

B2 ¿é¤J°}¦C¤½¦¡¡A¤U©Ô

=T(INDEX(A:A,1/LARGE(IF(ISNUMBER(FIND("J",A$1:A$999)),1/ROW(A$1:A$999),1%),ROW(A1))))

TOP

2F ªº¤½¦¡§Ú¬OµLªk¦¨¥\
3F ·PÁ ¤½¦¡¥i¥H¨Ï¥Î  ¤£¹L¤ñ¸ûÃø²z¸Ñ  1%  ¸ò¨ú1/  ³o³¡¤Àªº·§©À
ÁÙ¦³Ãö©ó­È J3  §Ú¤£½T©w¨ì²Ä´X¦C  ¦³¿ìªk¤u¬O©¹¤U©Ô ³Ì«á¥u§ì¨ìJ3¶Ü?  ¦b¤£½T©w³Ì«á¤@­Ó­È¨ì²Ä´X¦C

TOP

¥»©«³Ì«á¥Ñ JBY ©ó 2015-3-6 14:32 ½s¿è
2F ªº¤½¦¡§Ú¬OµLªk¦¨¥\
3F ·PÁ ¤½¦¡¥i¥H¨Ï¥Î  ¤£¹L¤ñ¸ûÃø²z¸Ñ  1%  ¸ò¨ú1/  ³o³¡¤Àªº·§©À
ÁÙ¦³Ãö©ó­È J3 ...


1] 2F ªº°}¦C¤½¦¡, ¨Ï¥Î INDEX+SMALL ¬O½×¾Â¦hªö¥Îªº¤èªk

{=T(INDEX(A:A,SMALL(IF(ISNUMBER(FIND("J",A$1:A$1000)),ROW(A$1:A$1000),1000),ROW(A1))))}

2] 3F ªº°}¦C¤½¦¡, ¨Ï¥Î INDEX+LARGE ¬O½×¾Â¸û¤Ö¥Îªº¤èªk, §@¬°°Ñ¦Ò¥Î

{=T(INDEX(A:A,1/LARGE(IF(ISNUMBER(FIND("J",A$1:A$1000)),1/ROW(A$1:A$1000),1%),ROW(A1))))}

3] 1% =0.01 §@¬°®ø°£¿ù»~¥Î¡C¥Î F9 À¿À¿¤½¦¡, ´N·|©ú¥Õ¡C

4] ¨ú 1/......³o³¡¤Àªº·§©À :  

¦pªG, ¤½¦¡¨Ï¥Î  :  INDEX(A:A,LARGE(IF(ISNUMBER(FIND¡K¡K....... ªð¦^¼Æ¾Ú¬°­Ë§Ç¡C

©Ò¥H, ¤½¦¡¨Ï¥Î  :  INDEX(A:A,1/LARGE(IF(ISNUMBER(FIND¡K¡K... ªð¦^¼Æ¾Ú¬°顺§Ç¡C

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD