B1{=SUM((MATCH(A1,A:A,)>MATCH(A$1:A1,A:A,))/COUNTIF(A$1:A1,A$1:A1))+1
另解{=IF(COUNTIF(A$1:A1,A1)=1,SUM(1/COUNTIF(A$1:A1,A$1:A1)),LOOKUP(1,0/(A1-OFFSET(A$1,,,MAX(ROW(A1)-1))=0),OFFSET(A$1,,1,MAX(ROW(A1)-1))))
另解{=MATCH(A1,INDEX(A:A,N(IF({1},SMALL(IF(MATCH(A$1:A$20,A$1:A$20,)=ROW($1:$20),ROW($1:$20),""),ROW($1:$20))))),)
F2=IF(COUNTIF(E$2:E2,E2)=1,SUM(1/COUNTIF(E$2:E2,E$2:E2)),LOOKUP(1,0/(E$1:E1=E2),F$1:F1))
留言
張貼留言