取B2:B14不重複姓名D2 {=IFERROR(INDEX(TRIM(MID(SUBSTITUTE(B$2:B$14,",",REPT(" ",99)),COLUMN(A:T)*99-98,99)),INT(MIN(IF((COUNTIF(D$1:D1,TRIM(MID(SUBSTITUTE(B$2:B$14,",",REPT(" ",99)),COLUMN(A:T)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$14,",",REPT(" ",99)),COLUMN(A:T)*99-98,99))<>""),ROW($1:$13)/1%+COLUMN(A:T),9999))%),RIGHT(MIN(IF((COUNTIF(D$1:D1,TRIM(MID(SUBSTITUTE(B$2:B$14,",",REPT(" ",99)),COLUMN(A:T)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$14,",",REPT(" ",99)),COLUMN(A:T)*99-98,99))<>""),ROW($1:$13)/1%+COLUMN(A:T),9999)),2)),"")
E2:K2 下拉=IF(ISERR(FIND($D2,LOOKUP(1,0/(E$1=INT($A$2:$A$14)),$B$2:$B$14)))+($D2=""),"","V")
M2=COUNTIF(B:B,"*"&D2&"*")
A16=SUM(M2:M12)
A17=SUMPRODUCT(LEN(B2:B14)-LEN(SUBSTITUTE(B2:B14,",",)))+13
留言
張貼留言