使ç¨lookupå½æ°å§ï¼æ¯hlookupè¿æ¯vlookupï¼åç
§å
¬å¼ä½¿ç¨ã
å®æ´çå
¬å¼æ¯ï¼vlookup(lookup_value, table_array, col_index_num, [range_lookup])ã
lookup_valueï¼æ¯è¦å¨åºåç第ä¸åä¸æç´¢çå¼ãlookup_valueå¯ä»¥æ¯å¼æå¼ç¨ã
table_arrayï¼å
å«æ°æ®çåºåãå¯ä»¥ä½¿ç¨å¯¹åºåï¼ä¾å¦ï¼A2:D8ï¼æåºåå称çå¼ç¨ã
col_index_numï¼table_arrayåæ°ä¸å¿
é¡»è¿åçå¹é
å¼çåå·ãcol_index_numåæ°ä¸º1æ¶ï¼è¿åtable_array第1åä¸çå¼ï¼col_index_num为2æ¶ï¼è¿åtable_array第2åä¸çå¼ï¼ä¾æ¤ç±»æ¨ãå¦æ col_index_num åæ°å°äº1ï¼åvlookup è¿åé误å¼ï¼å¤§äº table_arrayçåæ°ï¼åvlookupè¿åé误å¼ã
range_lookupï¼ä¸ä¸ªé»è¾å¼ï¼æå®å¸ævlookupæ¥æ¾ç²¾ç¡®å¹é
å¼è¿æ¯è¿ä¼¼å¹é
å¼ï¼å¦ærange_lookup为trueæ被çç¥ï¼åè¿å精确å¹é
å¼æè¿ä¼¼å¹é
å¼ãå¦ææ¾ä¸å°ç²¾ç¡®å¹é
å¼ï¼åè¿åå°äºlookup_valueçæ大å¼ï¼å¦å¦ærange_lookup为trueæ被çç¥ï¼åå¿
é¡»æååºæåtable_array第1åä¸çå¼ï¼å¦åvlookupå¯è½æ æ³è¿åæ£ç¡®çå¼ï¼
å¦ærange_lookup为falseæ0ï¼åä¸éè¦å¯¹table_array第1åä¸çå¼è¿è¡æåºãå¦ærange_lookupåæ°ä¸ºfalseæ0ï¼vlookupå°åªæ¥æ¾ç²¾ç¡®å¹é
å¼ãå¦æ table_array ç第ä¸åä¸æ两个ææ´å¤å¼ä¸lookup_valueå¹é
ï¼å使ç¨ç¬¬ä¸ä¸ªæ¾å°çå¼ãå¦ææ¾ä¸å°ç²¾ç¡®å¹é
å¼ï¼åè¿åé误å¼ã
2ãvlookupå½æ°æ¯æåç´æ¹å(åæ¹å)è¿è¡æ°å¼æ¥æ¾ï¼è¿å对åºéã
ä¾å¦ï¼sheet1æ¯ä¸ä¸ªæ°æ®æºç综å表ï¼sheet2æ¯ä¸ä¸ªå¼ç¨ä½¿ç¨è¡¨ã
sheet1
A B C
1 å京 010 ...
2 å¹¿ä¸ 020 ...
3 æµ·å 030 ...
è¦å¨sheet2ä¸å¼ç¨âå京âçæ个æ°æ®å¼ï¼å¯ä»¥å¨sheet2çæä¸åå
æ ¼è¾å
¥ï¼
=vlookup("å京",sheet1!a:c,2,0)ï¼è¿å010ã
å
¬å¼ä¸å«ä¹ï¼âå京âï¼æ¯è¦æ¥æ¾æ¯å¯¹çå¼ï¼ä¹å¯ä»¥å¼ç¨åå
æ ¼ï¼âsheet1!a:câæ¯æ¥æ¾çèå´ï¼â2âæ¯æ¥æ¾å°å¹é
æ°å¼åè¦æ±è¿åç顺æ°åç对åºå¼ï¼â0âï¼æ¯å¹é
è¦æ±ä¸ºç²¾ç¡®å¹é
ãè¿åçå¼æ¯ï¼å¨æ¥æ¾èå´å
æåç´æ¹å对âå京âè¿è¡æ¥æ¾ï¼æ¾å°ç¬¬ä¸ä¸ªå¹é
å¼åæ¢ï¼è¿å对åºç第â2âåçå¼ã
å¯ä»¥åèï¼
http://wenku.baidu.com/view/ee2263cd050876323112129d.html