½Ð°Ý¦³¼Æµ§¤£¦P­Èªº¦Ò¸Õ¤À¼Æ¡I¦p¦ó³]©w¨ç¼Æ§ä¥X³Ì±µªñ50¤Àªº¤H¦W©O¡I

ªü¦t­ô

Book1.rar (1.49 KB)

¤U¸ü¦¸¼Æ: 24

2010-9-27 23:26

½Ð°Ý¦³¼Æµ§¤£¦P­Èªº¦Ò¸Õ¤À¼Æ¡I¦p¦ó³]©w¨ç¼Æ§ä¥X³Ì±µªñ50¤Àªº¤H¦W©O¡I½Ð¦U¦ì¤j¤jÀ°¦£¡IÁÂÁÂ

oobird

¦^´_ 1# ªü¦t­ô


    ©w¸q¦WºÙ¡Ga=MAX(IF(Sheet1!$B$1:$B$11<=50,Sheet1!$B$1:$B$11))
b=MIN(IF(Sheet1!$B$1:$B$11>=50,Sheet1!$B$1:$B$11))
ab=IF(ABS(50-a)<ABS(50-b),a,b)
Àx¦s®æ¿é¤J¤½¦¡¡G=INDIRECT("a"&MATCH(ab,$B$1:$B$11,0))

toomellowhaw

=INDEX(A1:A11,MATCH(MIN((B1:B11-50)^2),(B1:B11-50)^2,))
°}¦C¤½¦¡

Hsieh

ªñ¦ü­È.rar (1.86 KB)

¤U¸ü¦¸¼Æ: 4

2010-9-28 09:54

°}¦C¤½¦¡=IF(ROW(A1)>y,"",INDEX($A$1:$A$11,SMALL(IF(($B$1:$B$11=x+50)+($B$1:$B$11=50-x),ROW($B$1:$B$11)),ROW(A1))))

¤U¸ü (74.02 KB)

2010-9-28 10:02

joviwang

{=INDEX(A1:A11,MATCH(SMALL(ABS(50-B1:B11),1),ABS(50-B1:B11),))}

ªü¦t­ô

·PÁ¦U¦ì¤j¤jÀ°¦£¡IÁÂÁ¡I

ANGELA

=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)

toomellowhaw

¦^´_ 7# ANGELA


=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)
¹ï¤£°_¡A¥i¥H³Â·Ð±z¸Ñ»¡¨ç¼Æ¸Ì«e¨â¶µ°Ñ¼Æ¶Ü¡H

ANGELA

=LOOKUP(2,1/((MIN((50-B1:B11)^2))=(50-B1:B11)^2),A1:A11)
²Ä¤@­Ó°Ñ¼Æ2,¥i¥H¬O¨ä¥L¼Æ¦r,¥Î2 ¬O²ßºD¦Ó¤w,²Ä¤G­Ó°Ñ¼ÆB1¨ìB11=min((50-B1:B11)^2)·|±o¨ìTRUE,§_«h±o¨ìFALSE,¥Î1¥h°£·|±o¨ì1©Î#DIV/0!,LOOKUPªº¯S©Ê¬O¤ñ¹ï¤£¨ì®É·|ªð¦^³Ì«áªº¼Æ¦r,¤]´N¬O1ªº¦ì¸m.

Hsieh

¦^´_ 8# toomellowhaw


    ¥HÄæ¦ì¤À¸Ñ¤½¦¡(¦p¹Ï)

¤U¸ü (13.02 KB)

2010-9-30 14:27