ÎÊÌâÒÑ¿ªÆô
(ÆÕͨÎÊÌâ)
VLOOKUP²Ù×÷Á÷³Ì˻᣿
ÌáÎÊÕß: ybx563822950 ÌáÎÊʱ¼ä: 2009-05-19
• VlOOKupPlusV2.12¼ÓÔغóÎÞ·¨ÏÔʾ 2018-08-15
• VlOOKup plusµÄ×÷ÕßÈçºÎÁªÏµ 2016-01-15
• Ò»¸ö¼òµ¥µÄVlOOKupÎÊÌ⣨ÕÒ³öºóÒ»ÁÐÖв»ÔÙÇ°Ò»ÁеıàºÅ£© 2015-07-14
• ÈçºÎ¸ù¾Ý¾Î³¶ÈVlOOKup³öÓëËûÏà²îÔÚ30Ã×Ö®ÄÚµÄÕ¾µã 2014-06-23
• VlOOKup 2012-04-27
• 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-27
• VlOOKupÓ÷¨¼°Æä×÷ÓÃÊÇʲô 2012-04-02
• ¹ØÓÚVlOOKupµÄÒ»¸öСÎÊÌâ 2010-08-24
• excel2007µÄVlOOKupº¯ÊýµÄ²Ù×÷ 2009-08-13
ÎÊÌâ´ð°¸
( 5 )
ºÃÏóÕâ¸öÎÊÌâÓ¦¸ÃÊÇÔÚEXCELÂÛ̳ÎÊ£¬±È½ÏºÃ!!
VLOOKUP
ÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×鵱ǰÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£µ±±È½ÏֵλÓÚÊý¾Ý±íÊ×ÁÐʱ£¬¿ÉÒÔʹÓú¯Êý VLOOKUP ´úÌ溯Êý HLOOKUP¡£
ÔÚ VLOOKUP ÖÐµÄ V ´ú±í´¹Ö±¡£
Óï·¨
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value ΪÐèÒªÔÚÊý×éµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ¡£Lookup_value ¿ÉÒÔΪÊýÖµ¡¢ÒýÓûòÎı¾×Ö·û´®¡£
Table_array ΪÐèÒªÔÚÆäÖвéÕÒÊý¾ÝµÄÊý¾Ý±í¡£¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓã¬ÀýÈçÊý¾Ý¿â»òÁÐ±í¡£
Èç¹û range_lookup Ϊ TRUE£¬Ôò table_array µÄµÚÒ»ÁÐÖеÄÊýÖµ±ØÐë°´ÉýÐòÅÅÁУº¡¡¢-2¡¢-1¡¢0¡¢1¡¢2¡¢¡¡¢-Z¡¢FALSE¡¢TRUE£»·ñÔò£¬º¯Êý VLOOKUP ²»ÄÜ·µ»ØÕýÈ·µÄÊýÖµ¡£Èç¹û range_lookup Ϊ FALSE£¬table_array ²»±Ø½øÐÐÅÅÐò¡£
ͨ¹ýÔÚ¡°Êý¾Ý¡±²Ëµ¥Öеġ°ÅÅÐò¡±ÖÐÑ¡Ôñ¡°ÉýÐò¡±£¬¿É½«ÊýÖµ°´ÉýÐòÅÅÁС£
Table_array µÄµÚÒ»ÁÐÖеÄÊýÖµ¿ÉÒÔΪÎı¾¡¢Êý×Ö»òÂß¼Öµ¡£
Îı¾²»Çø·Ö´óСд¡£
Col_index_num Ϊ table_array Öдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ¡£Col_index_num Ϊ 1 ʱ£¬·µ»Ø table_array µÚÒ»ÁÐÖеÄÊýÖµ£»col_index_num Ϊ 2£¬·µ»Ø table_array µÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ¡£Èç¹û col_index_num СÓÚ 1£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµÖµ #VALUE!£»Èç¹û col_index_num ´óÓÚ table_array µÄÁÐÊý£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #REF!¡£
Range_lookup ΪһÂß¼Öµ£¬Ö¸Ã÷º¯Êý VLOOKUP ·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪ TRUE »òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚ lookup_value µÄ×î´óÊýÖµ£»Èç¹û range_value Ϊ FALSE£¬º¯Êý VLOOKUP ½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ #N/A¡£
˵Ã÷
Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½ lookup_value£¬ÇÒ range_lookup Ϊ TRUE£¬ÔòʹÓÃСÓÚµÈÓÚ lookup_value µÄ×î´óÖµ¡£
Èç¹û lookup_value СÓÚ table_array µÚÒ»ÁÐÖеÄ×îСÊýÖµ£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½ lookup_value ÇÒ range_lookup Ϊ FALSE£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
ʾÀý
Èç¹ûÄú½«Ê¾Àý¸´ÖƵ½¿Õ°×¹¤×÷±íÖУ¬¿ÉÄÜ»á¸üÒ×ÓÚÀí½â¸ÃʾÀý¡£
²Ù×÷·½·¨
´´½¨¿Õ°×¹¤×÷²¾»ò¹¤×÷±í¡£
ÇëÔÚ¡°°ïÖú¡±Ö÷ÌâÖÐѡȡʾÀý¡£²»ÒªÑ¡È¡ÐлòÁбêÌâ¡£
´Ó°ïÖúÖÐѡȡʾÀý¡£
°´ Ctrl+C¡£
ÔÚ¹¤×÷±íÖУ¬Ñ¡Öе¥Ôª¸ñ A1£¬ÔÙ°´ Ctrl+V¡£
ÈôÒªÔڲ鿴½á¹ûºÍ²é¿´·µ»Ø½á¹ûµÄ¹«Ê½Ö®¼äÇл»£¬Çë°´ Ctrl+`£¨ÖØÒô·û£©£¬»òÔÚ¡°¹¤¾ß¡±²Ëµ¥ÉÏ£¬Ö¸Ïò¡°¹«Ê½ÉóºË¡±£¬ÔÙµ¥»÷¡°¹«Ê½ÉóºËģʽ¡±¡£
¸ÃʾÀýʹÓà 1 ¸ö´óÆøѹµÄ¿ÕÆøÖµ¡£
ÃÜ¶È Õ³¶È ζÈ
.457 3.55 500
.525 3.25 400
.616 2.93 300
.675 2.75 250
.746 2.57 200
.835 2.38 150
.946 2.17 100
1.09 1.95 50
1.29 1.71 0
¹«Ê½ ˵Ã÷£¨½á¹û£©
=VLOOKUP(1,A2:C10,2) ÔÚ A ÁÐÖвéÕÒ 1£¬²¢´ÓÏàͬÐÐµÄ B ÁÐÖзµ»ØÖµ (2.17)
=VLOOKUP(1,A2:C10,3,TRUE) ÔÚ A ÁÐÖвéÕÒ 1£¬²¢´ÓÏàͬÐÐµÄ C ÁÐÖзµ»ØÖµ (100)
=VLOOKUP(.7,A2:C10,3,FALSE) ÔÚ A ÁÐÖвéÕÒ 0.746¡£ÒòΪ A ÁÐÖÐûÓо«È·µØÆ¥Å䣬ËùÒÔ·µ»ØÁËÒ»¸ö´íÎóÖµ (#N/A)
=VLOOKUP(0.1,A2:C10,2,TRUE) ÔÚ A ÁÐÖвéÕÒ 0.1¡£ÒòΪ 0.1 СÓÚ A ÁеÄ×îСֵ£¬ËùÒÔ·µ»ØÁËÒ»¸ö´íÎóÖµ (#N/A)
=VLOOKUP(2,A2:C10,2,TRUE) ÔÚ A ÁÐÖвéÕÒ 2£¬²¢´ÓÏàͬÐÐµÄ B ÁÐÖзµ»ØÖµ (1.71)
¾ßÌå¿Éµ½excel°ïÖúÀÊäÈëvlookup,ºÜÏêϸ
VLOOKUP
ÔÚ±í¸ñ»òÊýÖµÊý×éµÄÊ×ÁвéÕÒÖ¸¶¨µÄÊýÖµ£¬²¢ÓÉ´Ë·µ»Ø±í¸ñ»òÊý×鵱ǰÐÐÖÐÖ¸¶¨Á䦵ÄÊýÖµ¡£µ±±È½ÏֵλÓÚÊý¾Ý±íÊ×ÁÐʱ£¬¿ÉÒÔʹÓú¯Êý VLOOKUP ´úÌ溯Êý HLOOKUP¡£
ÔÚ VLOOKUP ÖÐµÄ V ´ú±í´¹Ö±¡£
Óï·¨
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value ΪÐèÒªÔÚÊý×éµÚÒ»ÁÐÖвéÕÒµÄÊýÖµ¡£Lookup_value ¿ÉÒÔΪÊýÖµ¡¢ÒýÓûòÎı¾×Ö·û´®¡£
Table_array ΪÐèÒªÔÚÆäÖвéÕÒÊý¾ÝµÄÊý¾Ý±í¡£¿ÉÒÔʹÓöÔÇøÓò»òÇøÓòÃû³ÆµÄÒýÓã¬ÀýÈçÊý¾Ý¿â»òÁÐ±í¡£
Èç¹û range_lookup Ϊ TRUE£¬Ôò table_array µÄµÚÒ»ÁÐÖеÄÊýÖµ±ØÐë°´ÉýÐòÅÅÁУº¡¡¢-2¡¢-1¡¢0¡¢1¡¢2¡¢¡¡¢-Z¡¢FALSE¡¢TRUE£»·ñÔò£¬º¯Êý VLOOKUP ²»ÄÜ·µ»ØÕýÈ·µÄÊýÖµ¡£Èç¹û range_lookup Ϊ FALSE£¬table_array ²»±Ø½øÐÐÅÅÐò¡£
ͨ¹ýÔÚ¡°Êý¾Ý¡±²Ëµ¥Öеġ°ÅÅÐò¡±ÖÐÑ¡Ôñ¡°ÉýÐò¡±£¬¿É½«ÊýÖµ°´ÉýÐòÅÅÁС£
Table_array µÄµÚÒ»ÁÐÖеÄÊýÖµ¿ÉÒÔΪÎı¾¡¢Êý×Ö»òÂß¼Öµ¡£
Îı¾²»Çø·Ö´óСд¡£
Col_index_num Ϊ table_array Öдý·µ»ØµÄÆ¥ÅäÖµµÄÁÐÐòºÅ¡£Col_index_num Ϊ 1 ʱ£¬·µ»Ø table_array µÚÒ»ÁÐÖеÄÊýÖµ£»col_index_num Ϊ 2£¬·µ»Ø table_array µÚ¶þÁÐÖеÄÊýÖµ£¬ÒÔ´ËÀàÍÆ¡£Èç¹û col_index_num СÓÚ 1£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµÖµ #VALUE!£»Èç¹û col_index_num ´óÓÚ table_array µÄÁÐÊý£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #REF!¡£
Range_lookup ΪһÂß¼Öµ£¬Ö¸Ã÷º¯Êý VLOOKUP ·µ»ØʱÊǾ«È·Æ¥Å仹ÊǽüËÆÆ¥Åä¡£Èç¹ûΪ TRUE »òÊ¡ÂÔ£¬Ôò·µ»Ø½üËÆÆ¥ÅäÖµ£¬Ò²¾ÍÊÇ˵£¬Èç¹ûÕÒ²»µ½¾«È·Æ¥ÅäÖµ£¬Ôò·µ»ØСÓÚ lookup_value µÄ×î´óÊýÖµ£»Èç¹û range_value Ϊ FALSE£¬º¯Êý VLOOKUP ½«·µ»Ø¾«È·Æ¥ÅäÖµ¡£Èç¹ûÕÒ²»µ½£¬Ôò·µ»Ø´íÎóÖµ #N/A¡£
˵Ã÷
Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½ lookup_value£¬ÇÒ range_lookup Ϊ TRUE£¬ÔòʹÓÃСÓÚµÈÓÚ lookup_value µÄ×î´óÖµ¡£
Èç¹û lookup_value СÓÚ table_array µÚÒ»ÁÐÖеÄ×îСÊýÖµ£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
Èç¹ûº¯Êý VLOOKUP ÕÒ²»µ½ lookup_value ÇÒ range_lookup Ϊ FALSE£¬º¯Êý VLOOKUP ·µ»Ø´íÎóÖµ #N/A¡£
ʾÀý
Èç¹ûÄú½«Ê¾Àý¸´ÖƵ½¿Õ°×¹¤×÷±íÖУ¬¿ÉÄÜ»á¸üÒ×ÓÚÀí½â¸ÃʾÀý¡£
²Ù×÷·½·¨
´´½¨¿Õ°×¹¤×÷²¾»ò¹¤×÷±í¡£
ÇëÔÚ¡°°ïÖú¡±Ö÷ÌâÖÐѡȡʾÀý¡£²»ÒªÑ¡È¡ÐлòÁбêÌâ¡£
´Ó°ïÖúÖÐѡȡʾÀý¡£
°´ Ctrl+C¡£
ÔÚ¹¤×÷±íÖУ¬Ñ¡Öе¥Ôª¸ñ A1£¬ÔÙ°´ Ctrl+V¡£
ÈôÒªÔڲ鿴½á¹ûºÍ²é¿´·µ»Ø½á¹ûµÄ¹«Ê½Ö®¼äÇл»£¬Çë°´ Ctrl+`£¨ÖØÒô·û£©£¬»òÔÚ¡°¹¤¾ß¡±²Ëµ¥ÉÏ£¬Ö¸Ïò¡°¹«Ê½ÉóºË¡±£¬ÔÙµ¥»÷¡°¹«Ê½ÉóºËģʽ¡±¡£
¸ÃʾÀýʹÓà 1 ¸ö´óÆøѹµÄ¿ÕÆøÖµ¡£
ÃÜ¶È Õ³¶È ζÈ
.457 3.55 500
.525 3.25 400
.616 2.93 300
.675 2.75 250
.746 2.57 200
.835 2.38 150
.946 2.17 100
1.09 1.95 50
1.29 1.71 0
¹«Ê½ ˵Ã÷£¨½á¹û£©
=VLOOKUP(1,A2:C10,2) ÔÚ A ÁÐÖвéÕÒ 1£¬²¢´ÓÏàͬÐÐµÄ B ÁÐÖзµ»ØÖµ (2.17)
=VLOOKUP(1,A2:C10,3,TRUE) ÔÚ A ÁÐÖвéÕÒ 1£¬²¢´ÓÏàͬÐÐµÄ C ÁÐÖзµ»ØÖµ (100)
=VLOOKUP(.7,A2:C10,3,FALSE) ÔÚ A ÁÐÖвéÕÒ 0.746¡£ÒòΪ A ÁÐÖÐûÓо«È·µØÆ¥Å䣬ËùÒÔ·µ»ØÁËÒ»¸ö´íÎóÖµ (#N/A)
=VLOOKUP(0.1,A2:C10,2,TRUE) ÔÚ A ÁÐÖвéÕÒ 0.1¡£ÒòΪ 0.1 СÓÚ A ÁеÄ×îСֵ£¬ËùÒÔ·µ»ØÁËÒ»¸ö´íÎóÖµ (#N/A)
=VLOOKUP(2,A2:C10,2,TRUE) ÔÚ A ÁÐÖвéÕÒ 2£¬²¢´ÓÏàͬÐÐµÄ B ÁÐÖзµ»ØÖµ (1.71)
¾ßÌå¿Éµ½excel°ïÖúÀÊäÈëvlookup,ºÜÏêϸ
»Ø´ðÕߣº
ÏèÓÎÀË×Ó
»Ø´ðʱ¼ä£º2009-05-19 15:02
12 14
¿´Ò»ÏÂexcelµÄ°ïÖú¾Í¿É½â¾öÒÓ~~
»Ø´ðÕߣº
lamchunhuii
»Ø´ðʱ¼ä£º2009-05-19 15:12
12 13
´ð£º·¢¸öÊÓƵÁ´½Ó¸øÄã¿´¿´£¬Ï£ÍûÓÐÓÃ
VlookupÓ÷¨
ÊÓƵ: º¯Èë°àµÚ5½²:Ìõ¼þ²éѯVLOOKUP
[flash]http://player.youku.com/player.php/sid/XNjUzODA1NTY=/v.swf[/flash]
VlookupÓ÷¨
ÊÓƵ: º¯Èë°àµÚ5½²:Ìõ¼þ²éѯVLOOKUP
[flash]http://player.youku.com/player.php/sid/XNjUzODA1NTY=/v.swf[/flash]
»Ø´ðÕߣº
wangyuan072
»Ø´ðʱ¼ä£º2009-05-19 16:17
13 15
ÀÁµÄ1B£¬±ÉÊÓÄã
»Ø´ðÕߣº
horizon668
»Ø´ðʱ¼ä£º2009-05-24 13:05
12 14
ºÇºÇ VLOOKUP ²Ù×÷ÖÐ ×îÖ÷ÒªµÄÒ»µã¾ÍÊǼÇס ÔÚµÚ3ÐÐÌîдʱ Êý¶ÔÄã´Ó²éÕÒÁе½Ä¿±êÁÐÊǵڼ¸ÁУ¬±ÈÈç5 ¾ÍÌî5 Õâ¸öÊÇȷȷʵʵµÄÊýÖµ ²»ÒªÌîΪÆäËûµÄ¾ÍOKÁË
»Ø´ðÕߣº
wweinano
»Ø´ðʱ¼ä£º2009-05-31 15:32
11 8
ÈȵãÎÊÌâ
¸ü¶à¾«²Ê
ÁªÏµÎÒÃÇ - ÎÊͨÐÅר¼Ò | Powered by MSCBSC Òƶ¯Í¨ÐÅÍø © 2006 - |