å°±æ¿ä¸é¢çè¿å¼ 人å表æ¥è¯´ï¼å ¶ä¸å°±åºç°äºä¸äºéåè®°å½ï¼éè¦å¿«éæ¾åºæ¥ã
ç°å¨ï¼ç¨ä¸ä¸ªå°å ¬å¼å³å¯æå®ãå建ä¸ä¸ªè¾ å©åBï¼ç¨æ¥åæ¾å¤æéå¤çæ å¿ï¼è¥æéå¤å°±æ âéå¤âï¼ä¸éå¤å°±ä»ä¹ä¹ä¸æ ãå¨B2åå æ ¼ä¸è¾å ¥å ¬å¼å¹¶å车ï¼
=IF(COUNTIF(A:A,A2)>1,"éå¤","")
ç¶åéè¿åä¸æå¨B2åå æ ¼å³ä¸è§çå°æ¹åå¡«å ææé®ï¼æåå»å¡«å æï¼ï¼è¿æ ·å°±ä¼èªå¨å¡«å æ¯ä¸è¡çéå¤æ åµãä¹ååçï¼ææéå¤çæ°æ®è¡å°±é²åºäºâ马èâã
为äºä½¿éå¤ç项ç®çèµ·æ¥æ´æ¾ç¼ï¼æ们è¿å¯ä»¥ç»éå¤ç项ç®å ä¸é¢è²ãéè¿âå¼å§âæ¡ä»¶æ ¼å¼âçªåºæ¾ç¤ºåå æ ¼è§åâéå¤å¼âè·¯å¾è¿è¡è®¾ç½®ã
å¨âéå¤å¼â对è¯æ¡ä¸ï¼ç»âéå¤âå¼è®¾ç½®æç§å¡«å è²åæåé¢è²ï¼ç¶åç¡®å®ã
ä¹åæ们çå°ï¼ææéå¤ç项ç®å°±è¢«æ ä¸æå®çé¢è²äºã
å¦æ人åç»è®°è¡¨ä¸åºç°äºéå¤ç身份è¯å·ç ï¼æä¹å¼å¢ï¼è¯·çä¸è¡¨ï¼å¯å¯éº»éº»çæ°åï¼ä½ è½ä¸ä¸åå¤æåºæå 个éå¤çåï¼
å ¶å®ï¼æ们åªéå¨åå æ ¼B2ä¸ä½¿ç¨å¦ä¸ä¸ªå ¬å¼ï¼
=IF(COUNTIF(A:A,A2&"*")>1,"éå¤","")
åæ ·ç¨å¡«å æå°è¿ä¸ªå ¬å¼åºç¨å°åç»çåè¡ä¸ï¼å°±ä¼èªå¨ç»åºéå¤ç身份è¯åºç°çä½ç½®ã
ä¸ä¸ªè¡¨æ ¼å¯éç¨ä¸è¿°æ¹æ³æ¥æ¥éãé£ä¹ï¼å¦æè¦æ¯è¾ä¸¤ä¸ªè¡¨æ ¼ä¸çéå¤æ°æ®ï¼è¯¥æä¹åå¢ï¼ä»¥ä¸é¢ç两个表为ä¾ï¼è¡¨Sheet1ä¸åæ¾äºä¸åå·ç ï¼è¡¨Sheet2ä¸åæ¾äºå¦ä¸åå·ç ï¼ä¸¤è¡¨ä¸å¯è½ä¼æéå¤åºç°ãé£ä¹ï¼å¦ä½æ¯è¾ä¸¤ä¸ªè¡¨æ ¼æ¾åºææéå¤é¡¹å¢ï¼
æ们å¨è¡¨Sheet1çBå设置ä¸åå¤æéå¤ï¼å®ä½å°è¡¨Sheet1çB2åå æ ¼ï¼è¾å ¥å¦ä¸å ¬å¼ï¼
=COUNTIF(SHEET2!A2:A12,A2)
å车ä¹åç¨è¯¥åå æ ¼å³ä¸è§çå¡«å æåä¸å¡«å å°è¯¥åæåï¼å¯ä»¥çå°æææ 示为1çå³æ¯ä¸¤è¡¨ä¸éå¤åºç°ç人åã
ç±äºæ们对ä¸éå¤ç项并ä¸å ³å¿ï¼å®ä»¬ä¼å¹²æ°æ们çè§çº¿ï¼å æ¤éè¦å°ä¸éå¤ç项ç®å±è½æãéè¿âæ°æ®âçéâå½ä»¤ï¼ç¹å»çéæ¡çå°ä¸è§è¿è¡çé设置ï¼éæ©å¼ä¸º1ç项ç®è¿è¡çéã
çéä¹åæ们çå°ï¼çªå£ä¸å°±åªå©ä¸éå¤ç项ç®ï¼è¿ä¸å°±ä¸ç®äºç¶äºã
æåï¼å¯¹ä¸è¿°å½æ°ç®å讲解ä¸ä¸ï¼
COUNTIFå½æ°ç两个åæ°ï¼ç¬¬ä¸ä¸ªåæ°SHEET2!A2:A12å®ä¹è¡¨Sheet2çè¦æ¯è¾çæ°æ®åºåA2å°A12ï¼ç¬¬äºä¸ªåæ°A2代表å½å表ï¼è¡¨Sheet1ï¼çå½åå æ æå¨è¡¨æ ¼ï¼ç»è®¡A2ç¸åç个æ°ã
å¦ææåçè¿åå¼ä¸º0ï¼å说æå½ååå æ ¼æ²¡æä¸å¦ä¸ä¸ªè¡¨ä¸éå¤çæ°æ®ã