å设åæ°æ®å¨Sheet1ï¼æå°Sheet2
å¨A2è¾å
¥ï¼
=INDEX(Sheet1!B:B,SMALL(IF(MATCH(Sheet1!$B$2:$B$100&"",Sheet1!$B$2:$B$100&"",0)=ROW($A$2:$A$100)-1,ROW($A$2:$A$100),4^8),ROW(1:1)))&""ï¼æ°ç»å
¬å¼ï¼å³åæ¶æShift+Ctrl+Enterç»æå
¬å¼ç¼è¾ï¼ä¼èªå¨çæä¸å¯¹ï½ï½ï¼ä¸æã
B2è¾å
¥ï¼=IF(A2="","",COUNTIF(Sheet1!B:B,A2))ï¼ä¸æ
追é®è½åªå¨ä¸å¼ å·¥ä½ç°¿ä¸åå°ä¹ï¼
追çè¿å°±æ¯å¨ä¸ä¸ªå·¥ä½ç°¿ä¸åï¼ä½ æ¯è¯´å¨ä¸ä¸ªå·¥ä½è¡¨ä¸å§ï¼å¯ä»¥å
D2è¾å
¥ï¼
=INDEX(B:B,SMALL(IF(MATCH($B$2:$B$100&"",$B$2:$B$100&"",0)=ROW($A$2:$A$100)-1,ROW($A$2:$A$100),4^8),ROW(1:1)))&""ï¼æ°ç»å
¬å¼
E2è¾å
¥ï¼=IF(D2="","",COUNTIF(B:B,D2))