¹¤¾ßÀ¸¡ú»æÍ¼¡±ÃüÁÔÚ×¼¿¼Ö¤ÐèÌî³äÎı¾µÄ²¿Î»²åÈë¾ØÐοò¡£ÎÒÃÇÓÃVLOOKUPº¯ÊýʵÏÖSheet1¹¤×÷±íÖÐÊý¾ÝÓëSheet2±íÖÐ×¼¿¼Ö¤
¼äµÄ¹ØÁª£¨ÕÕÆ¬Ìî³ä²¿·Ö³ýÍ⣩¡£
Ê×ÏȽ«A21µ¥Ôª¸ñ×÷Ϊ²éѯµÄ¹Ø¼üÊýÖµ£¬ÔÚA20¡ªG20µ¥Ôª¸ñÖзֱðÊäÈë[ÐòºÅ]¡¢[ÐÕÃû]¡¢[¼¶±ð]µÈ×ֶΡ£ÔÚB21µ¥Ôª¸ñÖÐÊäÈ빫ʽ¡°=IF(VLOOKUP(A21,Sheet1!A2:I1000,2)=0,\
et1!A2:I1000,2))¡±£¬ÔÚC21µ¥Ôª¸ñÖÐÊäÈ빫ʽ
¡°=IF(VLOOKUP(A21,Sheet1!A2:I1000,6)=0,\et1!A2:I1000,6))¡±£¬ÆäËûÒÀ´ËÀàÍÆ¡£ÕâÑù¾Í½¨Á¢ÁË×¼¿¼Ö¤Ìî³äÊý¾ÝÓë
Sheet1±í¼äµÄ¹ØÁª¡£
ÏÂÒ»²½¾ÍÊÇÒª½¨Á¢Êý¾ÝÓë×¼¿¼Ö¤ÏàÓ¦¾ØÐοòµÄ¹ØÁª£¬ÒÔ×¼¿¼Ö¤ÖÐ[ÐÕÃû]×Ö¶ÎΪÀý£¬Êó±êµ¥»÷¸Ã¾ØÐοò£¬ÔÚ±à¼À¸ÓÒ²àÊäÈë¡°=¡±·ûºÅ£¬È»ºóÓÃÊó±êµã»÷ÐèÒªÌîÈëÊý¾ÝµÄB21µ¥Ôª¸ñ£¬ÕâÑù¾ØÐοò¾ÍºÍB21µ¥Ôª¸ñÖеÄÊý¾Ý½¨Á¢ÁËÁªÏµ¡£Ö»Òªµ¥Ôª¸ñÊý¾Ý±ä»¯£¬Ôò¾ØÐοòÊý¾ÝÒ²¸ú×ű仯£¬ÆäËûÒÀ´ËÀàÍÆ¡£ÕâÑù£¬Ö»ÒªÔÚA21µ¥Ôª¸ñÊäÈëÐòºÅÖµ£¬ÄÇôSheet1±íÖÐÏàÓ¦ÐòºÅÐеÄÊý¾Ý¾Í»áÔÚ×¼¿¼Ö¤Ä£°åÖÐ×Ô¶¯Éú³É¡£ÉÏÊö¹¤×÷Íê³Éºó£¬°´¡°Shift¡±¼üÑ¡ÖÐËùÓоØÐοò£¬ÉèÖþØÐοò¸ñʽ£¬°üÀ¨×ÖÌå¡¢´óС¡¢¶ÔÆë¡¢ÑÕÉ«ºÍÏßÌõµÈ£¬ÌرðÊÇÑ¡ÖÐÎÞÌî³äÉ«ºÍÎÞÏßÌõÉ«¡£
2£® ¶¨ÒåÃû³Æ£¬µ¼ÈëÕÕÆ¬
ͨ¹ýVLOOKUPº¯ÊýËäÈ»½¨Á¢Á˹¤×÷±í1ºÍ×¼¿¼Ö¤µÄÁªÏµ£¬µ«ÒªÔÚ×¼¿¼Ö¤ÖÐ×Ô¶¯Éú³ÉÕÕÆ¬ÔòÐèÒªÓõ½±È½Ï¸´Ôӵĺ¯Êý£¬²ÉÈ¡¶¨ÒåÃû
³ÆµÄ°ì·¨ ¡£¾ßÌå²½ÖèÈçÏ£º
£¨1£©¶¨ÒåÃû³Æ£ºÖ´ÐС°²åÈë¡úÃû³Æ¡ú¶¨Ò塱ÃüÁ´ò¿ªÈçͼ3Ëùʾ¡°¶¨ÒåÃû³Æ¡±¶Ô»°¿ò£¬½«Ãû³Æ¶¨ÒåΪ¡°A¡±£¬ÔÚ¡°ÒýÓÃλÖá±Îı¾¿òÖÐÊäÈ빫ʽ¡°=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)¡±£¬°´Ï¡²È·¶¨¡³°´Å¥·µ»Ø¡£¸Ã¹«Ê½½¨Á¢Á˲éѯÊý¾ÝµÄ¶¯Ì¬µØÖ·¡£½Ó×ÅÓÃͬÑù·½·¨£¬
ÔÙ¶¨ÒåÁíÒ»¸öÃû½Ð¡°X¡±µÄÃû³Æ£¬ÀïÃæ°üÀ¨¹«Ê½
¡°X=INDIRECT(ADDRESS(MATCH(Sheet2!$A$21,A,0),9,1,,\
1\£¬¸Ã¹«Ê½±íʾµ±¶¯Ì¬µØÖ·À¸ÎªSheet2±íÖÐA21µ¥Ôª¸ñÊýֵʱ£¬
·µ»ØSheet1±íÖеÚ9Áм´ÕÕÆ¬ËùÔÚÁе¥Ôª¸ñÄÚÈÝ¡£
£¨2£© µ¼ÈëͼƬ£ºÑ¡È¡sheet2¹¤×÷±í£¬Ö´ÐС°ÊÓͼ¡ú¹¤¾ßÀ¸¡ú¿Ø¼þ¹¤¾ßÏ䡱ÃüÁ´ò¿ª¡°¿ØÖƹ¤¾ßÏ䡱´°¿Ú¡£µã»÷ͼÏñ¿ò»òÎÄ×Ö¿ò°´Å¥£¬ÔÚ×¼¿¼Ö¤ÐèÒªÌî³äÕÕÆ¬µÄλÖû³öÏàͬ´óСµÄͼƬ¿ò»òÎÄ×Ö¿ò¡£Ñ¡È¡ÎÄ×Ö¿ò£¨Í¼Ïñ¿ò£©ºó£¬½«±à¼À¸ÖС°=EMBED(\¸Ä³É¡°=X¡±£¬Èçͼ4£¬ÕâÑùÖ»ÒªA21µ¥Ôª¸ñÊýÖµ±ä»¯Ê±£¬ÕÕÆ¬¿òÖÐÏàÓ¦µÄ
ÕÕÆ¬¾Í»á·¢Éú±ä»¯¡£
3£® ÖÆ×÷΢µ÷°´Å¥£¬±àдºê´úÂë
Ϊ²Ù×÷¸ü¼Ó·½±ã£¬¿ÉÒÔÖÆ×÷΢µ÷°´Å¥µ÷½ÚÐòºÅÖµ¡£Ö´ÐС°ÊÓͼ¡ú¹¤¾ßÀ¸¡ú´°Ì塱£¬´ò¿ª´°Ìå¶Ô»°¿ò£¬µã»÷΢µ÷Ïî±êÖ¾°´Å¥£¬½¨Á¢Î¢µ÷°´Å¥£¬ÓÒ»÷°´Å¥£¬´ò¿ª¡°ÉèÖÿؼþ¸ñʽ¡±¶Ô»°¿ò£¬²¢ÓëB19µ¥Ôª¸ñ½¨Á¢Á´½Ó£¬°´Ï¡²È·¶¨¡³°´Å¥¡£Ñ¡ÖÐA21µ¥Ôª¸ñ£¬ÔÚ±à¼À¸ÖÐÊäÈë¡°= B19¡±£¬´Ëʱµã»÷΢µ÷°´Å¥£¬¾Í¿É×Ô¶¯µ÷½ÚB19ºÍA21µ¥Ôª¸ñÐòºÅÖµ£¬¶ÔÓ¦µÄÊý¾Ý¾Í»áÔÚÄ£°åÖÐ×Ô¶¯Éú³É£¬Ð§¹ûÈçͼ5¡£½ÓÏÂÀ´¾ÍÊÇ´òÓ¡ÁË¡£
Ö´ÐС°¹¤¾ß¡úºê¡úVisul Basic±à¼Æ÷¡±ÃüÁÔÚVisul Basic±à¼Æ÷ÖÐ
Ñ¡Ôñ¡°²åÈë¡úÌí¼ÓÄ£¿é¡±£¬ÔÚ´úÂë´°¿ÚÊäÈëÒÔÏ´úÂ룺
Sub ´òÓ¡()