F2:H2下拉{=INDEX(A:A,SMALL(IF(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,"、",))+1>=COLUMN($A:$I),ROW($2:$8),99),ROW(A1)))&""
G2改公式後下拉{=IFERROR(TRIM(MID(SUBSTITUTE(INDEX(B:B,SMALL(IF(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,"、",))+1>=COLUMN($A:$I),ROW($2:$8)),ROW(B1))),"、",REPT(" ",99)),RIGHT(SMALL(IF(LEN($B$2:$B$8)-LEN(SUBSTITUTE($B$2:$B$8,"、",))+1>=COLUMN($A:$I),ROW($2:$8)*10+COLUMN($A:$I)),ROW(B1)))*99-98,99)),"")
I2{=IFERROR(INDEX(D$2:D$8/(LEN(B$2:B$8)-LEN(SUBSTITUTE(B$2:B$8,"、",))+1),SMALL(IF(LEN(B$2:B$8)-LEN(SUBSTITUTE(B$2:B$8,"、",))+1>=COLUMN($A:$I),ROW($1:$7)),ROW(D1))),"")
留言
張貼留言