D2{=IFERROR(INDEX(TRIM(MID(SUBSTITUTE(B$2:B$15,"、",REPT(" ",99)),COLUMN(A:I)*99-98,99)),INT(SMALL(IF((TRIM(MID(SUBSTITUTE(B$2:B$15,"、",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>"")*(COUNTIF(D$1:D1,TRIM(MID(SUBSTITUTE(B$2:B$15,"、",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0),ROW($1:$14)/1%+COLUMN(A:I)),1)%),RIGHT(SMALL(IF((TRIM(MID(SUBSTITUTE(B$2:B$15,"、",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>"")*(COUNTIF(D$1:D1,TRIM(MID(SUBSTITUTE(B$2:B$15,"、",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0),ROW($1:$14)/1%+COLUMN(A:I)),1))),"")
G2:K15=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",99)),COLUMN(A1)*99-98,99))
E2{=IFERROR(INDIRECT(TEXT(SMALL(IF((G$2:K$15<>"")*(COUNTIF(E$1:E1,G$2:K$15)=0),ROW($2:$15)/1%+COLUMN(G:K)),1),"!R0C00"),),"")
留言
張貼留言