excelÖдòÓ¡µÄÉ趨 - ͼÎÄ ÏÂÔØ±¾ÎÄ

¹¤¾ßÀ¸¡ú»æÍ¼¡±ÃüÁÔÚ×¼¿¼Ö¤ÐèÌî³äÎı¾µÄ²¿Î»²åÈë¾ØÐοò¡£ÎÒÃÇÓÃ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 ´òÓ¡()