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

vlookup §ä¨ì2­Ó¤@¼Ë±ø¥ó¸ê®Æ §ì¨ú ¦^¶Ç°ÝÃD

vlookup §ä¨ì2­Ó¤@¼Ë±ø¥ó¸ê®Æ §ì¨ú ¦^¶Ç°ÝÃD

VLOOKUP   ¥Ø«e§Ú¥h§ä 1A                       1A                1A
                                                                            YA                XD

¥i¬O¥u·|¦^¶ÇYA   ¦³¿ìªk2­Óª½³£¦^¶Ç¶Ü?

^^

¦^´_ 1# iwiniwin
¤£ª¾±zªº¸ê®Æ¦p¦ó¤À¥¬¡A¥i§_¤W¶Ç½d¨ÒÀɨѰѡH
Ex. ¨Ï¥ÎINDEX, SMALL, IF²Õ¦X¦¨°}¦C¤½¦¡¡A¥i¹ï¬Û¦PÃþ§O¶µ¥Ø¾ã¦X¨ä¸ê®Æ¡C

TOP

¥»©«³Ì«á¥Ñ iwiniwin ©ó 2014-4-8 17:53 ½s¿è

123.rar (8.94 KB)





¦p¹Ï¤ù¤¤  §Ú¥h§ä1A1  ·|Åã¥ÜA  ¦ý¬O  §Ú§Æ±æ³s¥t¤@­Ó1A1  ¤@°_Åã¥Ü
Åܦ¨¦³  A  A99   ¦pªG¦³3­Ó¥H¤W­È ³£¥i¥H¨Ï¥Î§ì¨ú


ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2014-4-9 09:50 ½s¿è

VLOOKUP¡DÅý¢ÐÄæ³æ®æ¦s©ñ¤@­Ó¥H¤Wµ²ªG­È¡]¥HªÅ®æ¤À¹j¡^
¢°¡D»²§UÄæ¡D¢Ô¢°¡G
  1. =E1&IF(ISNA(MATCH(D1,D2:D$999,)),""," "&VLOOKUP(D1,D2:F$999,3,))
½Æ»s¥N½X
¡@©Î¡G
  1. =E1&LOOKUP("龥",IF({1,0},""," "&VLOOKUP(D1,D2:F$999,3,)))
½Æ»s¥N½X
¡@
¡@
¢±¡D¬d¸ßÄæ¡D¢Ð¢°¡G
¡@=IF(A1="","",VLOOKUP(A1,D:F,3,))

TOP

·PÁ¦^µª   ½Ð°Ý §Ú²{¦b1A1  ¥i¯à¤£¬O©ñ¦b²Ä¤@®æ ¤]³\´¡¤J¦b¤¤¶¡  §Ú¬Ý¦¨¬O¼gªk¬O Á׶}«e­±ªº­È¤£¥h
VLOOKUP  ¦³¿ìªk¥þ³¡ VLOOKUP ¦b¥h§ì¨ú ²Ä¤@­Ó§ä¨ì ©M ¦a2­Ó§ä¨ìªº­È¶Ü

TOP

¦^´_ 5# iwiniwin


   
°Ñ¦ÒÀÉ¡G
http://www.funp.net/942352
¡@
¤£¥[»²§UÄæ¡G°}¦C¤½¦¡¡D¥k©Ô¡A©Ò¦³²Å¦XªÌ¥H³æ®æÅã¥Ü
=INDEX($K:$K,SMALL(IF($J$1:$J$25=$A1,ROW($J$1:$J$25),4^8),COLUMN(A$1)))&""
©Î¡G
=INDEX($K:$K,SMALL(IF($J$1:$J$25=$A1,ROW($J$1:$J$25),4^8),COLUMN(A$1)))&IF(C1="",,"/"&C1)
¡@
VLOOKUP ¥u¯à§ì¨ì²Ä¢°­Ó¡AµLªk§ì¥X¥þ³¡¡ã¡ã

TOP

¬Ý§¹¼Ó¤W¼gªºªF¦è¡@¡@¥u¦³¢±­Ó¦r¨ØªA¡@¡@«Ü¦h³æ¿W¨ç¼Æ³£·|¡@¡@¦ý¬O¦X¦b¤@°_´N¬O¼g¤£¥X¨Ó¡×¡@¡×

TOP

¥»©«³Ì«á¥Ñ iwiniwin ©ó 2014-4-10 14:28 ½s¿è

½Ð°Ý¤@¤U³ÌÃöÁ䪺¦a¤è   VLOOKUP  3  Ãö©ó³o­Ó§ì¨ú  3³o­Ó§Ú·d¤£²M·¡¦ì¯«»ò³o­Ó¥i¥H¥Î3
§Ú¼gªº¸Ü §Ú·|¥Î §ì¨ú 2  ³o¼Ë¥u·|²£¥Í2­Ó¼Æ­È

§ì¨ú3³o­Ó¬OªÅªº  ÁÙ¨S²£¥Íªº¸ê®Æ  ¥i¬O³o¼Ë°µ  «o¥i¥H§ì¨ú¨ì©Ò¦³¸ê®Æ

TOP

=INDEX($KK,SMALL(IF($J$1J$25=$A1,ROW($J$1J$25),4^8),COLUMN(A$1)))&""
½Ð°Ý¤@¤U 4^8  ³o­Ó­È¬O«ç»ò¨Óªº
^^

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2014-4-11 10:30 ½s¿è

¦^´_ 9# iwiniwin


   
4^8 = 65536¡@¡Ö¡@OFFICE 2003 ³Ì¤j¡]³Ì«á¡^¦C¸¹¡Aªí¥Ü±zªº¸ê®Æ¥Î¤£¨ì³o¤@¦C¡]§¹¥þªÅ¥Õ¡^¡AÅý¤£²Å¦X±ø¥óªÌ¨ú¥Î³o¤@¦C¡]ªÅ¥Õ¡^¡A
¾Ç²ßEXCEL¡A­«ÂI¦b¡e¬Ý¡f¦Ó¤£¬O¡e°Ý¡f¡A¦]¬°°ÝÃD¥i¯à§O¤H¤w°Ý¹L¤d¦Ê¦¸¡A½×¾Â·j¤@¤U´N¦³¤F¡A³o¬O½×¾Âªº¥D­n¥Øªº¡A
¯u§ä¤£¨ì¦A°Ý§Y¥i¡ã¡ã

¥H¤U¦³§ó¦h©«¤l¤Î¸ê®Æ¥i¬Ý¡G
http://club.excelhome.net/forum-3-1.html
http://tw.knowledge.yahoo.com/dir/dir?sid=396540031

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