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

¦p¦ó¥Î¤½¦¡¡A²¤¹LªÅ¥Õ¦æ¡A½Æ»s¸ê¸ê¨ì¥k°¼

¦p¦ó¥Î¤½¦¡¡A²¤¹LªÅ¥Õ¦æ¡A½Æ»s¸ê¸ê¨ì¥k°¼


¥ª°¼¬O¨t²Î¶×¥Xªº¸ê®Æ¡AùØ­±¤t¬ï¤F«Ü¦hªÅ¥Õ

§Ú§Æ±æ¥Î¡y¤½¦¡¡z¡A²¤ªÅ¥Õ¦æ¡A±N¸ê®Æ¾ã²z¦b¥k°¼


½Ð«ü±Ð¡A¦p¦ó¥Î¤½¦¡°µ¨ì³o¼Ëªº®ÄªG¡H¡]¤£­n¥¨¶°¡AÁÂÁ¡^
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-12-30 20:03 ½s¿è

1©ó RANGE("H1") ¿é¤J¦p¤U¹Ï¤@¤½¦¡
2µM«á©óRANGE("H1") °õ¦æ °}¦C(SHIFT+CTRL+ENTER)
3µM«áRANGE("H1") ´å¼Ð©¹¤U°ÕCOPY°}¦C²[¼Æ
4.OK
¹Ï¤@
=IF(ROW($A1)>COUNTA($A$1:$A$15),"",INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15)),ROW($A1))))
ù

TOP

¦^´_ 2# rouber590324


  ¤j¤jÁÂÁ¡A§Ú¸Õ¹L¡A¥i¥H¡A¦ý¬O¤£°÷§¹¥þ¡C

¤@¡B§Ú±N«e­±ªº IF(ROW($A1)>COUNTA($A$1A$15),"", ®³±¼¡A¦]¬°¤U©Ô¥i¥H±±¨î¡A¤£·|©Ô¹LÀY

¤G¡B±N½d³ò§ï¦¨ INDEX(A$1:A$500,SMALL(IF(A$1:A$500<>"",ROW(A$1:A$500)),ROW(A1)))

¤T¡B¦b¤U©Ôªº¹Lµ{¤¤·|¦b«á­±²£¥Í¤@°ï #NUM! ¡A§Ú¨Óµo²{¬Osmall ³y¦¨ªº¡A

¦pªG¹ê»Ú¥u¦³ 100¦æ¡A¦b²Ä101¦æ¤§«á´N·|Åܦ¨ #NUM!

©Ò¥H³Ì«á­×§ï¦¨

if(ISERROR(SMALL(IF(A$1:A$500<>"",ROW(A$1:A$500)),ROW(A1))),"",INDEX(A$1:A$500,SMALL(IF(A$1:A$500<>"",ROW(A$1:A$500)),ROW(A1))))

²{¦b¤@¤Á¥¿±`¡A°£¤F½Æ¨î¤½¦¡¡A¤U©Ô¹ê¦b¬O«ÜÃø±±¨î¡C

ÁÂÁÂ
¤½¶}¸gÅç¡A³Ð³y´¼¼z¡]§Ú¤S¤£¾a³o¹L¬¡¡^

TOP

¦^´_ 3# eigen

¦b¤U©Ôªº¹Lµ{¤¤·|¦b«á­±²£¥Í¤@°ï #NUM! ¡A§Ú¨Óµo²{¬Osmall ³y¦¨ªº¡A
>>¥i¥H¥[¤W IFFERROR( INDEX(....) ,"")¸Õ¸Õ¬Ý

¥t¤@ºØ¤èªk§R°£ªÅ¥Õ¦C
¿ï¨ú½d³ò>±`¥Î>´M§ä»P¿ï¨ú>¯S®í¥Ø¼Ð>ªÅ®æ>¥kÁä>§R°£
¿ï¨ú½d³ò>F5>¯S®í¥Ø¼Ð>ªÅ®æ>¥kÁä>§R°£

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD