ÎÊÌâÒÑ¿ªÆô
(ÆÕͨÎÊÌâ)
• VlOOKupPlusV2.12¼ÓÔغóÎÞ·¨ÏÔʾ 2018-08-15
• VlOOKup plusµÄ×÷ÕßÈçºÎÁªÏµ 2016-01-15
• Ò»¸ö¼òµ¥µÄVlOOKupÎÊÌ⣨ÕÒ³öºóÒ»ÁÐÖв»ÔÙÇ°Ò»ÁеıàºÅ£© 2015-07-14
• ÈçºÎ¸ù¾Ý¾Î³¶ÈVlOOKup³öÓëËûÏà²îÔÚ30Ã×Ö®ÄÚµÄÕ¾µã 2014-06-23
• VlOOKupÓ÷¨¼°Æä×÷ÓÃÊÇʲô 2012-04-02
• ¹ØÓÚVlOOKupµÄÒ»¸öСÎÊÌâ 2010-08-24
• excel2007µÄVlOOKupº¯ÊýµÄ²Ù×÷ 2009-08-13
• VlOOKup plusµÄ×÷ÕßÈçºÎÁªÏµ 2016-01-15
• Ò»¸ö¼òµ¥µÄVlOOKupÎÊÌ⣨ÕÒ³öºóÒ»ÁÐÖв»ÔÙÇ°Ò»ÁеıàºÅ£© 2015-07-14
• ÈçºÎ¸ù¾Ý¾Î³¶ÈVlOOKup³öÓëËûÏà²îÔÚ30Ã×Ö®ÄÚµÄÕ¾µã 2014-06-23
• VlOOKupÓ÷¨¼°Æä×÷ÓÃÊÇʲô 2012-04-02
• ¹ØÓÚVlOOKupµÄÒ»¸öСÎÊÌâ 2010-08-24
• excel2007µÄVlOOKupº¯ÊýµÄ²Ù×÷ 2009-08-13
ÎÊÌâ´ð°¸
( 9 )
excel°ïÖú˵Ã÷¾ÍÓÐÏê½â£¡
»Ø´ðÕߣº
ÈÎÎÒ·ÉÏè2008
»Ø´ðʱ¼ä£º2012-04-27 19:36
9 10
ÈÃÅԱߵÄͬʽÌÏ£¡ºÜ¼òµ¥µÄ£¬Ðֵܣ¬ÊµÔÚ²»ºÃ±íÊö£¡
»Ø´ðÕߣº
YX2011
»Ø´ðʱ¼ä£º2012-04-27 21:09
9 9
VLOOKUPº¯Êý
ÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×éÖиÃÊýÖµËùÔÚÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£
ÕâÀïËù˵µÄ¡°Êý×顱£¬¿ÉÒÔÀí½âΪ±í¸ñÖеÄÒ»¸öÇøÓò¡£Êý×éµÄÁÐÐòºÅ£ºÊý×éµÄ¡°Ê×ÁС±£¬¾ÍÊÇÕâ¸öÇøÓòµÄµÚÒ»×ÝÁУ¬´ËÁÐÓÒ±ßÒÀ´ÎΪµÚ2ÁС¢3ÁС¡¡£¼Ù¶¨Ä³Êý×éÇøÓòΪB2:E10£¬ÄÇô£¬B2:B10ΪµÚ1ÁС¢C2:C10ΪµÚ2ÁС¡¡£
Óï·¨£º
VLOOKUP£¨²éÕÒÖµ£¬ÇøÓò£¬ÁÐÐòºÅ£¬Âß¼Öµ£©
¡°²éÕÒÖµ¡±£ºÎªÐèÒªÔÚÊý×éµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÒýÓûòÎÄ×Ö·û´®¡£
¡°ÇøÓò¡±£ºÊý×éËùÔÚµÄÇøÓò£¬Èç¡°B2:E10¡±£¬Ò²¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓã¬ÀýÈçÊý¾Ý¿â»òÊý¾ÝÇåµ¥¡£
¡°ÁÐÐòºÅ¡±£º¼´Ï£ÍûÇøÓò£¨Êý×飩Öдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ£¬Îª1ʱ£¬·µ»ØµÚÒ»ÁÐÖеÄÊýÖµ£¬Îª2ʱ£¬·µ»ØµÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ£»ÈôÁÐÐòºÅСÓÚ1£¬º¯ÊýVLOOKUP ·µ»Ø´íÎóÖµ #VALUE!£»Èç¹û´óÓÚÇøÓòµÄÁÐÊý£¬º¯ÊýVLOOKUP·µ»Ø´íÎóÖµ #REF!¡£
¡°Âß¼Öµ¡±£ºÎªTRUE»òFALSE¡£ËüÖ¸Ã÷º¯Êý VLOOKUP ·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪ TRUE »òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚ¡°²éÕÒÖµ¡±µÄ×î´óÊýÖµ£»Èç¹û¡°Âß¼Öµ¡±ÎªFALSE£¬º¯Êý VLOOKUP ½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ #N/A¡£Èç¹û¡°²éÕÒÖµ¡±ÎªÎı¾Ê±£¬¡°Âß¼Öµ¡±Ò»°ãӦΪ FALSE ¡£ÁíÍ⣺
¡¤Èç¹û¡°²éÕÒÖµ¡±Ð¡ÓÚ¡°ÇøÓò¡±µÚÒ»ÁÐÖеÄ×îСÊýÖµ£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
¡¤Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½¡°²éÕÒÖµ¡± ÇÒ¡°Âß¼Öµ¡±Îª FALSE£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
ÏÂÃæ¾ÙÀý˵Ã÷VLOOKUPº¯ÊýµÄʹÓ÷½·¨¡£
¼ÙÉèÔÚSheet1Öдæ·ÅСÂó¡¢Ë®µ¾¡¢ÓñÃס¢»¨ÉúµÈÈô¸ÉÅ©²úÆ·µÄÏúÊÛµ¥¼Û£º
A B
1 Å©²úÆ·Ãû³Æ µ¥¼Û
2 СÂó 0.56
3 Ë®µ¾ 0.48
4 ÓñÃ× 0.39
5 »¨Éú 0.51
¡¡¡¡¡¡¡¡¡¡¡¡¡
100 ´ó¶¹ 0.45
Sheet2ΪÏúÊÛÇåµ¥£¬Ã¿´ÎÌîдµÄÇåµ¥ÄÚÈݲ»¾¡Ïàͬ£ºÒªÇóÔÚSheet2ÖÐÊäÈëÅ©²úÆ·Ãû³Æ¡¢ÊýÁ¿ºó£¬¸ù¾ÝSheet1µÄÊý¾Ý£¬×Ô¶¯Éú³Éµ¥¼ÛºÍÏúÊ۶ÉèϱíΪSheet2£º
A B C D
1 Å©²úÆ·Ãû³Æ ÊýÁ¿ µ¥¼Û ½ð¶î
2 Ë®µ¾ 1000 0.48 480
3 ÓñÃ× 2000 0.39 780
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡
ÔÚD2µ¥Ôª¸ñÀïÊäÈ빫ʽ£º
=C2*B2 £»
ÔÚC2µ¥Ôª¸ñÀïÊäÈ빫ʽ£º
=VLOOKUP£¨A2£¬Sheet1!A2:B100£¬2£¬FALSE£© ¡£
ÈçÓÃÓïÑÔÀ´±íÊö£¬¾ÍÊÇ£ºÔÚSheet1±íA2:B100ÇøÓòµÄµÚÒ»ÁвéÕÒSheet2±íµ¥Ôª¸ñA2µÄÖµ£¬²éµ½ºó£¬·µ»ØÕâÒ»ÐеÚ2ÁеÄÖµ¡£
ÕâÑù£¬µ±Sheet2±íA2µ¥Ôª¸ñÀïÊäÈëµÄÃû³Æ¸Ä±äºó£¬C2ÀïµÄµ¥¼Û¾Í»á×Ô¶¯¸ú×ű仯¡£µ±È»£¬ÈçSheet1Öеĵ¥¼ÛÖµ·¢Éú±ä»¯£¬Sheet2ÖÐÏàÓ¦µÄÊýÖµÒ²»á¸ú×ű仯¡£
ÆäËûµ¥Ôª¸ñµÄ¹«Ê½£¬¿É²ÉÓÃÌî³äµÄ°ì·¨Ð´Èë¡£
VLOOKUPº¯ÊýʹÓÃ×¢ÒâÊÂÏî
˵µ½VLOOKUPº¯Êý£¬ÏàÐÅ´ó¼Ò¶¼»áʹÓ㬶øÇÒ¶¼Ê¹ÓõúÜÊìÁ·ÁË¡£²»¹ý£¬Óм¸¸öϸ½ÚÎÊÌ⣬´ó¼ÒÔÚʹÓÃʱ»¹ÊÇÁôÐÄһϵĺá£
Ò»£®VLOOKUPµÄÓï·¨
VLOOKUPº¯ÊýµÄÍêÕûÓï·¨ÊÇÕâÑùµÄ£º
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
1£®À¨ºÅÀïÓÐËĸö²ÎÊý£¬ÊDZØÐèµÄ¡£×îºóÒ»¸ö²ÎÊýrange_lookupÊǸöÂß¼Öµ£¬ÎÒÃdz£³£ÊäÈëÒ»¸ö0×Ö£¬»òÕßFalse;ÆäʵҲ¿ÉÒÔÊäÈëÒ»¸ö1×Ö£¬»òÕßtrue¡£Á½ÕßÓÐʲôÇø±ðÄØ£¿Ç°Õß±íʾµÄÊÇÍêÕûÑ°ÕÒ£¬ÕÒ²»µ½¾Í´«»Ø´íÎóÖµ££N/A£»ºóÕßÏÈÊÇÕÒһģһÑùµÄ£¬ÕÒ²»µ½ÔÙÈ¥ÕҺܽӽüµÄÖµ£¬»¹ÕÒ²»µ½Ò²Ö»ºÃ´«»Ø´íÎóÖµ££N/A¡£Õâ¶ÔÎÒÃÇÆäʵҲûÓÐʲôʵ¼ÊÒâÒ壬ֻÊÇÂú×ãºÃÆæ¶øÒÑ£¬ÓÐÐËȤµÄÅóÓÑ¿ÉÒÔÈ¥ÌåÑéÌåÑé¡£
2£®Lookup_valueÊÇÒ»¸öºÜÖØÒªµÄ²ÎÊý£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÎÄ×Ö×Ö·û´®¡¢»ò²ÎÕÕµØÖ·¡£ÎÒÃdz£³£ÓõÄÊDzÎÕÕµØÖ·¡£ÓÃÕâ¸ö²ÎÊýʱ£¬ÓÐÁ½µãÒªÌرðÌáÐÑ£º
A£©²ÎÕÕµØÖ·µÄµ¥Ôª¸ñ¸ñʽÀà±ðÓëÈ¥ËÑÑ°µÄµ¥Ôª¸ñ¸ñʽµÄÀà±ðÒªÒ»Ö£¬·ñÔòµÄ»°ÓÐʱÃ÷Ã÷¿´µ½ÓÐ×ÊÁÏ£¬¾ÍÊÇ×¥²»¹ýÀ´¡£ÌرðÊDzÎÕÕµØÖ·µÄÖµÊÇÊý×Öʱ£¬×îΪÃ÷ÏÔ£¬ÈôËÑÑ°µÄµ¥Ôª¸ñ¸ñʽÀà±ðΪÎÄ×Ö£¬ËäÈ»¿´ÆðÀ´¶¼ÊÇ123£¬µ«ÊǾÍÊÇ×¥²»³ö¶«Î÷À´µÄ¡£
¶øÇÒ¸ñʽÀà±ðÔÚδÊäÈëÊý¾Ýʱ¾ÍÒªÏÈÈ·¶¨ºÃ£¬Èç¹ûÊý¾Ý¶¼ÊäÈë½øÈ¥ÁË£¬·¢ÏÖ¸ñʽ²»·û£¬ÒÑΪʱÒÑÍí£¬Èô»¹ÏëÈ¥×¥£¬ÔòÐèÖØÐÂÊäÈë¡£
B£©µÚ¶þµãÌáÐѵģ¬ÊÇʹÓÃʱһ¸ö·½±ãʵÓõÄС¼¼ÇÉ£¬ÏàÐŲ»ÉÙÈËÔç¾ÍÖªµÀÁ˵ġ£ÎÒÃÇÔÚʹÓòÎÕÕµØַʱ£¬ÓÐʱÐèÒª½«lookup_valueµÄÖµ¹Ì¶¨ÔÚÒ»¸ö¸ñ×ÓÄÚ£¬¶øÓÖҪʹÓÃÏÂÀ·½Ê½£¨»ò¸´ÖÆ£©½«º¯ÊýÌí¼Óµ½Ðµĵ¥Ôª¸ñÖÐÈ¥£¬ÕâÀï¾ÍÒªÓõ½¡°$¡±Õâ¸ö·ûºÅÁË£¬ÕâÊÇÒ»¸öÆð¹Ì¶¨×÷ÓõķûºÅ¡£±ÈÈç˵ÎÒʼÖÕÏëÒÔD5¸ñʽÀ´×¥Êý¾Ý£¬Ôò¿ÉÒÔ°ÑD5Ū³ÉÕâÑù£º$D$5£¬Ôò²»ÂÛÄãÈçºÎÀ¡¢¸´ÖÆ£¬º¯ÊýʼÖÕ¶¼»áÒÔD5µÄÖµÀ´×¥Êý¾Ý¡£
3£®Table_arrayÊÇËÑÑ°µÄ·¶Î§£¬col_index_numÊÇ·¶Î§ÄÚµÄÀ¸Êý¡£Col_index_num ²»ÄÜСÓÚ1£¬ÆäʵµÈÓÚ1ҲûÓÐʲôʵ¼ÊÓõġ£Èç¹û³öÏÖÒ»¸öÕâÑùµÄ´íÎóµÄÖµ#REF!£¬Ôò¿ÉÄÜÊÇcol_index_numµÄÖµ³¬¹ý·¶Î§µÄ×Ü×Ö¶ÎÊý¡£
¶þ£®VLOOKUPµÄ´íÎóÖµ´¦Àí¡£
ÎÒÃǶ¼ÖªµÀ£¬Èç¹ûÕÒ²»µ½Êý¾Ý£¬º¯Êý×ܻᴫ»ØÒ»¸öÕâÑùµÄ´íÎóÖµ#N/A£¬Õâ´íÎóÖµÆäʵҲºÜÓÐÓõġ£±È·½Ëµ£¬Èç¹ûÎÒÃÇÏëÕâÑùÀ´×÷´¦Àí£ºÈç¹ûÕÒµ½µÄ»°£¬¾Í´«»ØÏàÓ¦µÄÖµ£¬Èç¹ûÕÒ²»µ½µÄ»°£¬ÎÒ¾Í×Ô¶¯É趨ËüµÄÖµµÈÓÚ0£¬ÄǺ¯Êý¾Í¿ÉÒÔд³ÉÕâÑù£º
=if(iserror(vlookup(1,2,3,0))=true,0,vlookup(1,2,3,0))
Õâ¾ä»°µÄÒâ˼ÊÇÕâÑùµÄ£ºÈç¹ûVLOOKUPº¯Êý·µ»ØµÄÖµÊǸö´íÎóÖµµÄ»°£¨ÕÒ²»µ½Êý¾Ý£©£¬¾ÍµÈÓÚ0£¬·ñÔò£¬¾ÍµÈÓÚVLOOKUPº¯Êý·µ»ØµÄÖµ£¨¼´ÕÒµ½µÄÏàÓ¦µÄÖµ£©¡£
ÕâÀïÃæÓÖÓÃÁËÁ½¸öº¯Êý¡£
µÚÒ»¸öÊÇiserrorº¯Êý¡£ËüµÄÓï·¨ÊÇiserror(value)£¬¼´ÅжÏÀ¨ºÅÄÚµÄÖµÊÇ·ñΪ´íÎóÖµ£¬Èç¹ûÊÇ£¬¾ÍµÈÓÚtrue£¬²»ÊÇ£¬¾ÍµÈÓÚfalse¡£
µÚ¶þ¸öÊÇifº¯Êý£¬ÕâÒ²ÊÇÒ»¸ö³£Óõĺ¯ÊýµÄ£¬ºóÃæÓлú»áÔÙ¸ú´ó¼ÒÏêϸ½²½â¡£ËüµÄÓï·¨ÊÇif(Ìõ¼þÅжÏʽ£¬½á¹û1£¬½á¹û2)¡£Èç¹ûÌõ¼þÅжÏʽÊǶԵģ¬¾ÍÖ´Ðнá¹û1£¬·ñÔò¾ÍÖ´Ðнá¹û2¡£¾Ù¸öÀý×Ó£º=if(D2=¡±¡±,¡±¿ÕµÄ¡±,¡±Óж«Î÷¡±)£¬Òâ˼ÊÇÈçD2Õâ¸ö¸ñ×ÓÀïÊÇ¿ÕµÄÖµ£¬¾ÍÏÔʾÎÄ×Ö¡°¿ÕµÄ¡±£¬·ñÔò£¬¾ÍÏÔʾ¡°Óж«Î÷¡±¡££¨¿´ÆðÀ´¼òµ¥°É£¿Æäʵ±à³ÌÐò£¬Ò²¾ÍÊÇÕâÑù×ÓÅжÏÀ´ÅжÏÈ¥µÄ¡££©
Èý£®º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±íµµ°¸µÄ´¦Àí¡£
Ò»°ãÀ´Ëµ£¬º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±í£¬Èç¹ûÓÖÊÇÔÚ±ðµÄµµ°¸ÀïץȡÊý¾ÝµÄ»°£¬µµ°¸ÍùÍùÊDZȽϴóµÄ¡£ÓÈÆäÊǵ±ÄãʹÓõĵµ°¸±¾Éí¾ÍºÜ´óµÄʱºò£¬ÄÇÿ´Î¿ªÆôºÍ´æÅ̶¼ÊǺÜÊÜÉ˵ÄÊÂÇé¡£
ÓÐûÓа취°ÑÎļþѹËõһϣ¬¼Ó¿ì¿ªÆôºÍ´æÅ̵ÄËÙ¶ÈÄØ¡£ÕâÀïÌṩһ¸öССµÄ¾Ñé¡£
ÔÚ¹¤×÷±íÀµã»÷¹¤¾ß¡ª¡ªÑ¡Ï¡ª¼ÆË㣬°ÑÉÏÃæµÄ¸üÐÂÔ¶³Ì²ÎÕպʹ¢´æÍⲿÁ¬½áµÄ¹´È¥µô£¬ÔÙ±£´æµµ°¸£¬Ôò»á¼ÓËÙ²»ÉÙ£¬²»ÐÅÄã¿ÉÒÔÊÔÊÔ¡£
ÏÂÃæÏêϸµÄ˵һÏÂËüµÄÔÀí¡£
1£®º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±í£¬Ã¿´ÎÔÚ±£´æµµ°¸Ê±£¬»áͬʱ±£´æÒ»·ÝÆäÍⲿÁ¬½áµÄµµ°¸¡£ÕâÑù¼´Ê¹ÔÚµ¥¶À´ò¿ªÕâ¸ö¹¤×÷±íʱ£¬VLOOKUPº¯ÊýÒ»Ñù¿ÉÒÔץȡµ½ÊýÖµ¡£
2£®ÔÚ¹¤×÷±í´ò¿ªÊ±£¬Î¢Èí»áÌáʾÄ㣬ÊÇ·ñÒª¸üÐÂÔ¶³Ì²ÎÕÕ¡£Òâ˼ÊÇ˵£¬ÄãÒª²»ÒªÁ¬½Ó×îеÄÍⲿµµ°¸£¬ºÃÈÃÄãµÄVLOOKUPº¯Êý×¥µ½×îеÄÖµ¡£Èç¹ûÄãÓÐ×ã¹»µÄÄÍÐÄ£¬²»·ÁÊÔÊÔ¡£
3£®Á˽⵽Õâµã£¬ÎÒÃÇÓ¦¸ÃÖªµÀ£¬Ã¿´Îµ¥¶À´ò¿ªº¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±íʱ£¬ÀïÃæץȡÍⲿµµ°¸µÄÊýÖµ£¬Ö»ÊÇÉÏ´ÎÎÒÃÇ´æÅÌʱ±£´æµÄÖµ¡£ÈôÒªÁ¬½á×îеÄÖµ£¬±ØÐëÒª°ÑÍⲿµµ°¸Í¬Ê±´ò¿ª¡£
VLOOKUPº¯ÊýÎÒËùÁ˽âµÄ£¬Ò²Ö»ÊÇÕâЩ£¬´ó¼ÒÓÐʲôºÃµÄ¾Ñé»òÓÐʲôÒÉÎÊ£¬»¶Ó´ó¼ÒÌá³ö£¬Ò»Æð̽ÌÖ¡£
ÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×éÖиÃÊýÖµËùÔÚÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£
ÕâÀïËù˵µÄ¡°Êý×顱£¬¿ÉÒÔÀí½âΪ±í¸ñÖеÄÒ»¸öÇøÓò¡£Êý×éµÄÁÐÐòºÅ£ºÊý×éµÄ¡°Ê×ÁС±£¬¾ÍÊÇÕâ¸öÇøÓòµÄµÚÒ»×ÝÁУ¬´ËÁÐÓÒ±ßÒÀ´ÎΪµÚ2ÁС¢3ÁС¡¡£¼Ù¶¨Ä³Êý×éÇøÓòΪB2:E10£¬ÄÇô£¬B2:B10ΪµÚ1ÁС¢C2:C10ΪµÚ2ÁС¡¡£
Óï·¨£º
VLOOKUP£¨²éÕÒÖµ£¬ÇøÓò£¬ÁÐÐòºÅ£¬Âß¼Öµ£©
¡°²éÕÒÖµ¡±£ºÎªÐèÒªÔÚÊý×éµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÒýÓûòÎÄ×Ö·û´®¡£
¡°ÇøÓò¡±£ºÊý×éËùÔÚµÄÇøÓò£¬Èç¡°B2:E10¡±£¬Ò²¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓã¬ÀýÈçÊý¾Ý¿â»òÊý¾ÝÇåµ¥¡£
¡°ÁÐÐòºÅ¡±£º¼´Ï£ÍûÇøÓò£¨Êý×飩Öдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ£¬Îª1ʱ£¬·µ»ØµÚÒ»ÁÐÖеÄÊýÖµ£¬Îª2ʱ£¬·µ»ØµÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ£»ÈôÁÐÐòºÅСÓÚ1£¬º¯ÊýVLOOKUP ·µ»Ø´íÎóÖµ #VALUE!£»Èç¹û´óÓÚÇøÓòµÄÁÐÊý£¬º¯ÊýVLOOKUP·µ»Ø´íÎóÖµ #REF!¡£
¡°Âß¼Öµ¡±£ºÎªTRUE»òFALSE¡£ËüÖ¸Ã÷º¯Êý VLOOKUP ·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪ TRUE »òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚ¡°²éÕÒÖµ¡±µÄ×î´óÊýÖµ£»Èç¹û¡°Âß¼Öµ¡±ÎªFALSE£¬º¯Êý VLOOKUP ½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ #N/A¡£Èç¹û¡°²éÕÒÖµ¡±ÎªÎı¾Ê±£¬¡°Âß¼Öµ¡±Ò»°ãӦΪ FALSE ¡£ÁíÍ⣺
¡¤Èç¹û¡°²éÕÒÖµ¡±Ð¡ÓÚ¡°ÇøÓò¡±µÚÒ»ÁÐÖеÄ×îСÊýÖµ£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
¡¤Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½¡°²éÕÒÖµ¡± ÇÒ¡°Âß¼Öµ¡±Îª FALSE£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
ÏÂÃæ¾ÙÀý˵Ã÷VLOOKUPº¯ÊýµÄʹÓ÷½·¨¡£
¼ÙÉèÔÚSheet1Öдæ·ÅСÂó¡¢Ë®µ¾¡¢ÓñÃס¢»¨ÉúµÈÈô¸ÉÅ©²úÆ·µÄÏúÊÛµ¥¼Û£º
A B
1 Å©²úÆ·Ãû³Æ µ¥¼Û
2 СÂó 0.56
3 Ë®µ¾ 0.48
4 ÓñÃ× 0.39
5 »¨Éú 0.51
¡¡¡¡¡¡¡¡¡¡¡¡¡
100 ´ó¶¹ 0.45
Sheet2ΪÏúÊÛÇåµ¥£¬Ã¿´ÎÌîдµÄÇåµ¥ÄÚÈݲ»¾¡Ïàͬ£ºÒªÇóÔÚSheet2ÖÐÊäÈëÅ©²úÆ·Ãû³Æ¡¢ÊýÁ¿ºó£¬¸ù¾ÝSheet1µÄÊý¾Ý£¬×Ô¶¯Éú³Éµ¥¼ÛºÍÏúÊ۶ÉèϱíΪSheet2£º
A B C D
1 Å©²úÆ·Ãû³Æ ÊýÁ¿ µ¥¼Û ½ð¶î
2 Ë®µ¾ 1000 0.48 480
3 ÓñÃ× 2000 0.39 780
¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡
ÔÚD2µ¥Ôª¸ñÀïÊäÈ빫ʽ£º
=C2*B2 £»
ÔÚC2µ¥Ôª¸ñÀïÊäÈ빫ʽ£º
=VLOOKUP£¨A2£¬Sheet1!A2:B100£¬2£¬FALSE£© ¡£
ÈçÓÃÓïÑÔÀ´±íÊö£¬¾ÍÊÇ£ºÔÚSheet1±íA2:B100ÇøÓòµÄµÚÒ»ÁвéÕÒSheet2±íµ¥Ôª¸ñA2µÄÖµ£¬²éµ½ºó£¬·µ»ØÕâÒ»ÐеÚ2ÁеÄÖµ¡£
ÕâÑù£¬µ±Sheet2±íA2µ¥Ôª¸ñÀïÊäÈëµÄÃû³Æ¸Ä±äºó£¬C2ÀïµÄµ¥¼Û¾Í»á×Ô¶¯¸ú×ű仯¡£µ±È»£¬ÈçSheet1Öеĵ¥¼ÛÖµ·¢Éú±ä»¯£¬Sheet2ÖÐÏàÓ¦µÄÊýÖµÒ²»á¸ú×ű仯¡£
ÆäËûµ¥Ôª¸ñµÄ¹«Ê½£¬¿É²ÉÓÃÌî³äµÄ°ì·¨Ð´Èë¡£
VLOOKUPº¯ÊýʹÓÃ×¢ÒâÊÂÏî
˵µ½VLOOKUPº¯Êý£¬ÏàÐÅ´ó¼Ò¶¼»áʹÓ㬶øÇÒ¶¼Ê¹ÓõúÜÊìÁ·ÁË¡£²»¹ý£¬Óм¸¸öϸ½ÚÎÊÌ⣬´ó¼ÒÔÚʹÓÃʱ»¹ÊÇÁôÐÄһϵĺá£
Ò»£®VLOOKUPµÄÓï·¨
VLOOKUPº¯ÊýµÄÍêÕûÓï·¨ÊÇÕâÑùµÄ£º
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
1£®À¨ºÅÀïÓÐËĸö²ÎÊý£¬ÊDZØÐèµÄ¡£×îºóÒ»¸ö²ÎÊýrange_lookupÊǸöÂß¼Öµ£¬ÎÒÃdz£³£ÊäÈëÒ»¸ö0×Ö£¬»òÕßFalse;ÆäʵҲ¿ÉÒÔÊäÈëÒ»¸ö1×Ö£¬»òÕßtrue¡£Á½ÕßÓÐʲôÇø±ðÄØ£¿Ç°Õß±íʾµÄÊÇÍêÕûÑ°ÕÒ£¬ÕÒ²»µ½¾Í´«»Ø´íÎóÖµ££N/A£»ºóÕßÏÈÊÇÕÒһģһÑùµÄ£¬ÕÒ²»µ½ÔÙÈ¥ÕҺܽӽüµÄÖµ£¬»¹ÕÒ²»µ½Ò²Ö»ºÃ´«»Ø´íÎóÖµ££N/A¡£Õâ¶ÔÎÒÃÇÆäʵҲûÓÐʲôʵ¼ÊÒâÒ壬ֻÊÇÂú×ãºÃÆæ¶øÒÑ£¬ÓÐÐËȤµÄÅóÓÑ¿ÉÒÔÈ¥ÌåÑéÌåÑé¡£
2£®Lookup_valueÊÇÒ»¸öºÜÖØÒªµÄ²ÎÊý£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÎÄ×Ö×Ö·û´®¡¢»ò²ÎÕÕµØÖ·¡£ÎÒÃdz£³£ÓõÄÊDzÎÕÕµØÖ·¡£ÓÃÕâ¸ö²ÎÊýʱ£¬ÓÐÁ½µãÒªÌرðÌáÐÑ£º
A£©²ÎÕÕµØÖ·µÄµ¥Ôª¸ñ¸ñʽÀà±ðÓëÈ¥ËÑÑ°µÄµ¥Ôª¸ñ¸ñʽµÄÀà±ðÒªÒ»Ö£¬·ñÔòµÄ»°ÓÐʱÃ÷Ã÷¿´µ½ÓÐ×ÊÁÏ£¬¾ÍÊÇ×¥²»¹ýÀ´¡£ÌرðÊDzÎÕÕµØÖ·µÄÖµÊÇÊý×Öʱ£¬×îΪÃ÷ÏÔ£¬ÈôËÑÑ°µÄµ¥Ôª¸ñ¸ñʽÀà±ðΪÎÄ×Ö£¬ËäÈ»¿´ÆðÀ´¶¼ÊÇ123£¬µ«ÊǾÍÊÇ×¥²»³ö¶«Î÷À´µÄ¡£
¶øÇÒ¸ñʽÀà±ðÔÚδÊäÈëÊý¾Ýʱ¾ÍÒªÏÈÈ·¶¨ºÃ£¬Èç¹ûÊý¾Ý¶¼ÊäÈë½øÈ¥ÁË£¬·¢ÏÖ¸ñʽ²»·û£¬ÒÑΪʱÒÑÍí£¬Èô»¹ÏëÈ¥×¥£¬ÔòÐèÖØÐÂÊäÈë¡£
B£©µÚ¶þµãÌáÐѵģ¬ÊÇʹÓÃʱһ¸ö·½±ãʵÓõÄС¼¼ÇÉ£¬ÏàÐŲ»ÉÙÈËÔç¾ÍÖªµÀÁ˵ġ£ÎÒÃÇÔÚʹÓòÎÕÕµØַʱ£¬ÓÐʱÐèÒª½«lookup_valueµÄÖµ¹Ì¶¨ÔÚÒ»¸ö¸ñ×ÓÄÚ£¬¶øÓÖҪʹÓÃÏÂÀ·½Ê½£¨»ò¸´ÖÆ£©½«º¯ÊýÌí¼Óµ½Ðµĵ¥Ôª¸ñÖÐÈ¥£¬ÕâÀï¾ÍÒªÓõ½¡°$¡±Õâ¸ö·ûºÅÁË£¬ÕâÊÇÒ»¸öÆð¹Ì¶¨×÷ÓõķûºÅ¡£±ÈÈç˵ÎÒʼÖÕÏëÒÔD5¸ñʽÀ´×¥Êý¾Ý£¬Ôò¿ÉÒÔ°ÑD5Ū³ÉÕâÑù£º$D$5£¬Ôò²»ÂÛÄãÈçºÎÀ¡¢¸´ÖÆ£¬º¯ÊýʼÖÕ¶¼»áÒÔD5µÄÖµÀ´×¥Êý¾Ý¡£
3£®Table_arrayÊÇËÑÑ°µÄ·¶Î§£¬col_index_numÊÇ·¶Î§ÄÚµÄÀ¸Êý¡£Col_index_num ²»ÄÜСÓÚ1£¬ÆäʵµÈÓÚ1ҲûÓÐʲôʵ¼ÊÓõġ£Èç¹û³öÏÖÒ»¸öÕâÑùµÄ´íÎóµÄÖµ#REF!£¬Ôò¿ÉÄÜÊÇcol_index_numµÄÖµ³¬¹ý·¶Î§µÄ×Ü×Ö¶ÎÊý¡£
¶þ£®VLOOKUPµÄ´íÎóÖµ´¦Àí¡£
ÎÒÃǶ¼ÖªµÀ£¬Èç¹ûÕÒ²»µ½Êý¾Ý£¬º¯Êý×ܻᴫ»ØÒ»¸öÕâÑùµÄ´íÎóÖµ#N/A£¬Õâ´íÎóÖµÆäʵҲºÜÓÐÓõġ£±È·½Ëµ£¬Èç¹ûÎÒÃÇÏëÕâÑùÀ´×÷´¦Àí£ºÈç¹ûÕÒµ½µÄ»°£¬¾Í´«»ØÏàÓ¦µÄÖµ£¬Èç¹ûÕÒ²»µ½µÄ»°£¬ÎÒ¾Í×Ô¶¯É趨ËüµÄÖµµÈÓÚ0£¬ÄǺ¯Êý¾Í¿ÉÒÔд³ÉÕâÑù£º
=if(iserror(vlookup(1,2,3,0))=true,0,vlookup(1,2,3,0))
Õâ¾ä»°µÄÒâ˼ÊÇÕâÑùµÄ£ºÈç¹ûVLOOKUPº¯Êý·µ»ØµÄÖµÊǸö´íÎóÖµµÄ»°£¨ÕÒ²»µ½Êý¾Ý£©£¬¾ÍµÈÓÚ0£¬·ñÔò£¬¾ÍµÈÓÚVLOOKUPº¯Êý·µ»ØµÄÖµ£¨¼´ÕÒµ½µÄÏàÓ¦µÄÖµ£©¡£
ÕâÀïÃæÓÖÓÃÁËÁ½¸öº¯Êý¡£
µÚÒ»¸öÊÇiserrorº¯Êý¡£ËüµÄÓï·¨ÊÇiserror(value)£¬¼´ÅжÏÀ¨ºÅÄÚµÄÖµÊÇ·ñΪ´íÎóÖµ£¬Èç¹ûÊÇ£¬¾ÍµÈÓÚtrue£¬²»ÊÇ£¬¾ÍµÈÓÚfalse¡£
µÚ¶þ¸öÊÇifº¯Êý£¬ÕâÒ²ÊÇÒ»¸ö³£Óõĺ¯ÊýµÄ£¬ºóÃæÓлú»áÔÙ¸ú´ó¼ÒÏêϸ½²½â¡£ËüµÄÓï·¨ÊÇif(Ìõ¼þÅжÏʽ£¬½á¹û1£¬½á¹û2)¡£Èç¹ûÌõ¼þÅжÏʽÊǶԵģ¬¾ÍÖ´Ðнá¹û1£¬·ñÔò¾ÍÖ´Ðнá¹û2¡£¾Ù¸öÀý×Ó£º=if(D2=¡±¡±,¡±¿ÕµÄ¡±,¡±Óж«Î÷¡±)£¬Òâ˼ÊÇÈçD2Õâ¸ö¸ñ×ÓÀïÊÇ¿ÕµÄÖµ£¬¾ÍÏÔʾÎÄ×Ö¡°¿ÕµÄ¡±£¬·ñÔò£¬¾ÍÏÔʾ¡°Óж«Î÷¡±¡££¨¿´ÆðÀ´¼òµ¥°É£¿Æäʵ±à³ÌÐò£¬Ò²¾ÍÊÇÕâÑù×ÓÅжÏÀ´ÅжÏÈ¥µÄ¡££©
Èý£®º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±íµµ°¸µÄ´¦Àí¡£
Ò»°ãÀ´Ëµ£¬º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±í£¬Èç¹ûÓÖÊÇÔÚ±ðµÄµµ°¸ÀïץȡÊý¾ÝµÄ»°£¬µµ°¸ÍùÍùÊDZȽϴóµÄ¡£ÓÈÆäÊǵ±ÄãʹÓõĵµ°¸±¾Éí¾ÍºÜ´óµÄʱºò£¬ÄÇÿ´Î¿ªÆôºÍ´æÅ̶¼ÊǺÜÊÜÉ˵ÄÊÂÇé¡£
ÓÐûÓа취°ÑÎļþѹËõһϣ¬¼Ó¿ì¿ªÆôºÍ´æÅ̵ÄËÙ¶ÈÄØ¡£ÕâÀïÌṩһ¸öССµÄ¾Ñé¡£
ÔÚ¹¤×÷±íÀµã»÷¹¤¾ß¡ª¡ªÑ¡Ï¡ª¼ÆË㣬°ÑÉÏÃæµÄ¸üÐÂÔ¶³Ì²ÎÕպʹ¢´æÍⲿÁ¬½áµÄ¹´È¥µô£¬ÔÙ±£´æµµ°¸£¬Ôò»á¼ÓËÙ²»ÉÙ£¬²»ÐÅÄã¿ÉÒÔÊÔÊÔ¡£
ÏÂÃæÏêϸµÄ˵һÏÂËüµÄÔÀí¡£
1£®º¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±í£¬Ã¿´ÎÔÚ±£´æµµ°¸Ê±£¬»áͬʱ±£´æÒ»·ÝÆäÍⲿÁ¬½áµÄµµ°¸¡£ÕâÑù¼´Ê¹ÔÚµ¥¶À´ò¿ªÕâ¸ö¹¤×÷±íʱ£¬VLOOKUPº¯ÊýÒ»Ñù¿ÉÒÔץȡµ½ÊýÖµ¡£
2£®ÔÚ¹¤×÷±í´ò¿ªÊ±£¬Î¢Èí»áÌáʾÄ㣬ÊÇ·ñÒª¸üÐÂÔ¶³Ì²ÎÕÕ¡£Òâ˼ÊÇ˵£¬ÄãÒª²»ÒªÁ¬½Ó×îеÄÍⲿµµ°¸£¬ºÃÈÃÄãµÄVLOOKUPº¯Êý×¥µ½×îеÄÖµ¡£Èç¹ûÄãÓÐ×ã¹»µÄÄÍÐÄ£¬²»·ÁÊÔÊÔ¡£
3£®Á˽⵽Õâµã£¬ÎÒÃÇÓ¦¸ÃÖªµÀ£¬Ã¿´Îµ¥¶À´ò¿ªº¬ÓÐVLOOKUPº¯ÊýµÄ¹¤×÷±íʱ£¬ÀïÃæץȡÍⲿµµ°¸µÄÊýÖµ£¬Ö»ÊÇÉÏ´ÎÎÒÃÇ´æÅÌʱ±£´æµÄÖµ¡£ÈôÒªÁ¬½á×îеÄÖµ£¬±ØÐëÒª°ÑÍⲿµµ°¸Í¬Ê±´ò¿ª¡£
VLOOKUPº¯ÊýÎÒËùÁ˽âµÄ£¬Ò²Ö»ÊÇÕâЩ£¬´ó¼ÒÓÐʲôºÃµÄ¾Ñé»òÓÐʲôÒÉÎÊ£¬»¶Ó´ó¼ÒÌá³ö£¬Ò»Æð̽ÌÖ¡£
»Ø´ðÕߣº
zhangshiqin
»Ø´ðʱ¼ä£º2012-04-27 21:56
16 12
Â¥ÉϵÄÕâô¸´ÔÓ£¬1¥˵µÄÓеÀÀí£»
´ò¿ªexcel£¬°´F1½øÈë°ïÖúÎļþ£¬ÊäÈëvlookup£¬ÀïÃæ¾ÍÓÐÏêϸ˵Ã÷£¬»¹ÓеäÐÍÀý×Ó£»ÆäËûº¯ÊýÒ²¿ÉÒÔ°´´Ë·½·¨Ñ§Ï°¡£ÕâÈí¼þ×öµÃºÃ£¬°ïÖúÎļþÇ¿´óÒ²ÊÇÒ»¸öÖØÒªÔÒò¡£
´ò¿ªexcel£¬°´F1½øÈë°ïÖúÎļþ£¬ÊäÈëvlookup£¬ÀïÃæ¾ÍÓÐÏêϸ˵Ã÷£¬»¹ÓеäÐÍÀý×Ó£»ÆäËûº¯ÊýÒ²¿ÉÒÔ°´´Ë·½·¨Ñ§Ï°¡£ÕâÈí¼þ×öµÃºÃ£¬°ïÖúÎļþÇ¿´óÒ²ÊÇÒ»¸öÖØÒªÔÒò¡£
»Ø´ðÕߣº
exloding99
»Ø´ðʱ¼ä£º2012-04-28 09:47
16 12
http://v.youku.com/v_show/id_XMjYzNTAwNDQ4.htmlÕâÓиöÊÓƵ£¬¿´ÏÂ
»Ø´ðÕߣº
gujunduxiu
»Ø´ðʱ¼ä£º2012-04-28 10:44
14 6
À´Ñ§Ï°£¡~
»Ø´ðÕߣº
Michealwu
»Ø´ðʱ¼ä£º2012-04-29 01:02
13 14
VLOOKUPʹÓÃhttp://wenku.baidu.com/view/a183a90790c69ec3d5bb7545.html
¾ÙÀý£ºÓÃ;£ºÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×鵱ǰÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£µ±±È½ÏֵλÓÚÊý¾Ý±íÊ×ÁÐʱ£¬¿ÉÒÔʹÓú¯ÊýVLOOKUP´úÌ溯ÊýHLOOKUP¡£
Óï·¨£ºVLOOKUP(lookup_value£¬table_array£¬col_index_num£¬range_lookup)
²ÎÊý£ºLookup_valueΪÐèÒªÔÚÊý¾Ý±íµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÒýÓûòÎÄ×Ö´®¡£Table_arrayΪÐèÒªÔÚÆäÖвéÕÒÊý¾ÝµÄÊý¾Ý±í£¬¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓá£Col_index_numΪtable_arrayÖдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ¡£Col_index_numΪ1ʱ£¬·µ»Øtable_arrayµÚÒ»ÁÐÖеÄÊýÖµ£»col_index_numΪ2£¬·µ»Øtable_arrayµÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ¡£Range_lookupΪһÂß¼Öµ£¬Ö¸Ã÷º¯ÊýVLOOKUP·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪTRUE»òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚlookup_valueµÄ×î´óÊýÖµ£»Èç¹ûrange_valueΪFALSE£¬º¯ÊýVLOOKUP½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ#N/A¡£
ʵÀý£ºÈç¹ûA1=23¡¢A2=45¡¢A3=50¡¢A4=65£¬Ôò¹«Ê½¡°=VLOOKUP(50£¬A1:A4£¬1£¬TRUE)¡±·µ»Ø50¡£
Ï£ÍûÄܶÔÄãÓÐÓá£
¾ÙÀý£ºÓÃ;£ºÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×鵱ǰÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£µ±±È½ÏֵλÓÚÊý¾Ý±íÊ×ÁÐʱ£¬¿ÉÒÔʹÓú¯ÊýVLOOKUP´úÌ溯ÊýHLOOKUP¡£
Óï·¨£ºVLOOKUP(lookup_value£¬table_array£¬col_index_num£¬range_lookup)
²ÎÊý£ºLookup_valueΪÐèÒªÔÚÊý¾Ý±íµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ£¬Ëü¿ÉÒÔÊÇÊýÖµ¡¢ÒýÓûòÎÄ×Ö´®¡£Table_arrayΪÐèÒªÔÚÆäÖвéÕÒÊý¾ÝµÄÊý¾Ý±í£¬¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓá£Col_index_numΪtable_arrayÖдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ¡£Col_index_numΪ1ʱ£¬·µ»Øtable_arrayµÚÒ»ÁÐÖеÄÊýÖµ£»col_index_numΪ2£¬·µ»Øtable_arrayµÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ¡£Range_lookupΪһÂß¼Öµ£¬Ö¸Ã÷º¯ÊýVLOOKUP·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪTRUE»òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚlookup_valueµÄ×î´óÊýÖµ£»Èç¹ûrange_valueΪFALSE£¬º¯ÊýVLOOKUP½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ#N/A¡£
ʵÀý£ºÈç¹ûA1=23¡¢A2=45¡¢A3=50¡¢A4=65£¬Ôò¹«Ê½¡°=VLOOKUP(50£¬A1:A4£¬1£¬TRUE)¡±·µ»Ø50¡£
Ï£ÍûÄܶÔÄãÓÐÓá£
»Ø´ðÕߣº
xhy1331
»Ø´ðʱ¼ä£º2012-04-29 11:58
15 8
Õâ¸öº¯ÊýÊǸÉÍø¸ñ×î»ù±¾Óõ½µÄ£¬È·ÊµºÜÇ¿´ó£¡£¡
»Ø´ðÕߣº
ľһÐÄ
»Ø´ðʱ¼ä£º2012-04-29 15:38
16 13
1¡¢ÔÚµÚÒ»¸öÔ±¹¤¶ÔÓ¦µÄµ¥Ôª¸ñÖÐÊäÈë¡°£½VLOOKUP(¡±
2¡¢ÓÃÊó±êÑ¡ÖÐÔ±¹¤ÐÕÃûËùÔÚµ¥Ôª¸ñºóÊäÈëÓ¢ÎÄ¡°£¬¡± Èç¡°£½VLOOKUP(C4,¡±
3¡¢ÓÃÊó±êÑ¡Öг¬²úÊý¾Ý·¶Î§ºóÊäÈëÓ¢ÎÄ¡°£¬¡± Èç¡°£½VLOOKUP(C4,Sheet3!A1:B12,¡±
4¡¢°Ñµ¥Ôª¸ñÒýÓøÄΪ¾ø¶ÔÒýÓ㬡°£½VLOOKUP(C4,Sheet3!$A$1:$B$12,¡±
5¡¢¼ÌÐøÊäÈëÒª·µ»ØµÄÔ±¹¤½±½ðËùÔÚÁУ¨Ïà¶ÔÓÚÒýÓ÷¶Î§µÄÁÐÊý²»ÊDZíÖеÄÕæʵÁÐÊý£©È磬¡°£½VLOOKUP(C4,Sheet3!$A$1:$B$12,2£©¡±
6¡¢¼ÓÉÏÁíÒ»°ëÀ¨ºÅºó£¬»Ø³µ¡£
7¡¢Ñ¡ÖиղÅÄǸöµ¥Ôª¸ñ£¬Óá°Ìî³ä±ú¡±ÏòÏÂ×Ô¶¯Ìî³ä¡£
»Ø´ðÕߣº
nrmq
»Ø´ðʱ¼ä£º2012-04-29 20:47
10 8
ÈȵãÎÊÌâ
¸ü¶à¾«²Ê
ÁªÏµÎÒÃÇ - ÎÊͨÐÅר¼Ò | Powered by MSCBSC Òƶ¯Í¨ÐÅÍø © 2006 - |