Excelä¸å¯ä»¥å©ç¨æ°ç»å ¬å¼ä¸ç»å ¸çindexãsmallåmatchç»åèªå¨éæ©éå¤é¡¹ä¸çå¯ä¸å¼ã
软件çæ¬ï¼Office2007
æ¹æ³å¦ä¸ï¼
1.Sheet1ä¸æå¦ä¸éå¤æ°å¼ï¼å¨Sheet2ä¸èªå¨æåå¯ä¸å¼ï¼
2.Sheet2ä¸è¾å ¥å ¬å¼å¦ä¸ï¼
=INDEX(Sheet1!A:A,SMALL(IF(ROW(Sheet1!$A$1:Sheet1!$A$100)=IFERROR(MATCH(Sheet1!$A$1:Sheet1!$A$100,Sheet1!$A$1:Sheet1!$A$100,0),4^8),ROW(Sheet1!$A$1:Sheet1!$A$100),4^8),ROW(A1)))
å ¬å¼ç®è¦è§£éï¼å å©ç¨matchå½æ°ï¼å¯¹å½ååå æ ¼æ°å¼ï¼å¨A1:A100èå´å åºç°çä½ç½®è¿è¡å¤æï¼å¦æåºç°çä½ç½®è·å½åè¡å·ä¸è´ï¼ä¹å°±æ¯ç¬¬ä¸æ¬¡åºç°ï¼ï¼åè¿å该æ°å¼ï¼å¦æä¸ä¸è´ï¼ä¸æ¯ç¬¬ä¸æ¬¡åºç°ï¼ï¼åè¿å4ç8次æ¹ï¼æ é大çæ°ï¼ï¼ç¶åå©ç¨smallå½æ°å¯¹ç±è¿äºæ°æ®ç»æçæ°ç»è¿è¡ä»å°å°å°æåºï¼è¿æ ·ç¬¬ä¸æ¬¡åºç°çä¸å®å¨åé¢ï¼èæ é大çæ°å¨åé¢ï¼å¨å¯¹æåºåçæ°ç»ï¼å©ç¨indexå½æ°ä¾æ¬¡æåï¼rowï¼A1)ï¼åé¢çæ°æ®ï¼ä»èå¾å°ä¸éå¤çæ°å¼
3.Ctrl+Shift+Enterä¸é®ç»ææ°ç»å ¬å¼ï¼å¯ä»¥çå°å ¬å¼èªå¨è¡¥å äºå¤§æ¬å·ï¼è¿æ¯æ°ç»å ¬å¼çæ å¿ï¼
4.ä¸æå¡«å ï¼å¾å°ç»æå¦ä¸ï¼