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

½Ð°ÝLOOKUP¦³¶i¶¥ª©¶Ü?

½Ð°ÝLOOKUP¦³¶i¶¥ª©¶Ü?

http://naturefruit.myweb.hinet.net/school.xls
¨Ò¦p:¾Ç®Õ=XX°ê¤p  (XX¤£¤@©wªº¦W¤l)

¦]¬°¦³200~300¦C.©Ò¥H§Æ±æ¯à§Ö³t§@·~.§Ú·Q¤F«Ü¤[.
¦bGÄæ¥ÎLOOKUP¼g..¤£¹L¸g±`¥X°ÝÃD..¦p¤U:
G2=LOOKUP(D1,{"*¥®¨à¶é*","*°ê¤p*","*°ê¤¤*","*°ª¤¤*","*¾Ç°|*","*Å@®Õ*","*¤j¾Ç*"},{"1","2","3","4","5","5","5"})
§Æ±æGÄæ¦]¬°DÄæÃöÁä¦rªºÃö«Y,ÅýGÄæ¥X²{12345

µM«á§Ú¦b¨Ì12345±Æ§Ç´N¯à¤F~

¤£¹L§Ú§Æ±æ¯à±N¾Ç°|,Å@®Õ,¤j¾Ç¬Û¦PÃþ«¬ªº©ñ¦b¤@°_.©Ò¥H·|¦³ÂIÃø«×¤F.

¥ý«Ø¥ß¸ê®Æªí®æ
G2=VLOOKUP(RIGHT(D2,2),¸ê®Æªí®æ½d³ò,2,0)
¥®¨à¶é«Ø¥ß¸ê®Æªí®æ®É§ï¥Î"¨à¶é"

¸ê®Æªí®æ¦p¤U
¨à¶é  1
°ê¤p  2
°ê¤¤  3
.  .
.  .
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

G2

=LOOKUP(2,1/FIND({"¥®¨à¶é","°ê¤p","°ê¤¤","°ª¤¤","¾Ç°|","Å@®Õ","¤j¾Ç"},D2),{1,2,3,4,5,5,5})
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¤ñ¸û¼Ð·Ç¥Îªk«Øij¥Îvlookup(..),¦pasimov©Ò¥Ü
¥®¨à¶é´N¥®¨à¶éÀ³¸Ó¤£¥Î§ï¨à¶é§a

¤]´£¨Ñ¤@ºØ¸Õ¸Õ
=--MID("1xx2x3x4x5x5x5x",FIND($D2,"¥®¨à¶é°ê¤p°ê¤¤°ª¤¤¾Ç°|Å@®Õ¤j¾Ç"),1)
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

¤í¯Ê¦Ò¼{ªÅ¥Õ
¸É¤W

G2

=LOOKUP(,-FIND({"","¥®¨à¶é","°ê¤p","°ê¤¤","°ª¤¤","¾Ç°|","Å@®Õ","¤j¾Ç"},D2),{"",1,2,3,4,5,5,5})
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¤j®a³£¦n¥Î¤ß.¦Ó¥B¤]¦n¼F®`.·PÁ¤j®a!!

TOP

¤ñ¸û¼Ð·Ç¥Îªk«Øij¥Îvlookup(..),¦pasimov©Ò¥Ü
¥®¨à¶é´N¥®¨à¶éÀ³¸Ó¤£¥Î§ï¨à¶é§a

¤]´£¨Ñ¤@ºØ¸Õ¸Õ
=--MID ...
gong µoªí©ó 2010-6-4 08:42



¥X²{#VALUE!  ¤£¹L¨ÌµM«Ü·PÁ±zªº¥Î¤ß!

TOP

¥ý«Ø¥ß¸ê®Æªí®æ
G2=VLOOKUP(RIGHT(D2,2),¸ê®Æªí®æ½d³ò,2,0)
¥®¨à¶é«Ø¥ß¸ê®Æªí®æ®É§ï¥Î"¨à¶é"

¸ê®Æªí®æ ...
asimov µoªí©ó 2010-6-4 01:24



¥X²{#REF!  ¤£¹L¨ÌµM«Ü·PÁ±zªº¥Î¤ß.

TOP

§Ú¥´¤£¶}file §A¥i¥H¥ý¥Îvlookup «á¦A¥[¤Wif(iserror)¸Õ¤@¤U..

TOP

¥»©«³Ì«á¥Ñ asimov ©ó 2010-6-5 02:10 ½s¿è
¥X²{#REF!  ¤£¹L¨ÌµM«Ü·PÁ±zªº¥Î¤ß.
vpower µoªí©ó 2010-6-4 17:55



    ¸ê®Æªí®æ­n«Ø¥ß©ó¤£¦PÀx¦s®æ

G2=VLOOKUP(RIGHT(D2,2),$I$1¡G$J$7,2,0)
¾÷¾¹¤H¤T¤j©w«ß-«OÅ@¤HÃþ¡BªA±q©R¥O¡B«OÅ@¦Û¤v
§Úªº¤T¤j©w«ß-¥Í©R¡B¥Í¦s¡B¥Í¬¡

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD