C2:H4=IF(MOD(COLUMN(A1),2),INDEX(TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",99)),COLUMN($A:$I)*99-98,99)),INT(COLUMN(B1)/2)),IF(B2="","",$A2/SUMPRODUCT(N(MID($B2&",",ROW($1:$99),1)=","))))
K2{=INDEX(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)),LEFT(MIN(IF((COUNTIF(K$1:K1,TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>""),{1;2;3}/1%+COLUMN(A:I),399))),RIGHT(MIN(IF((COUNTIF(K$1:K1,TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99)))=0)*(TRIM(MID(SUBSTITUTE(B$2:B$4,",",REPT(" ",99)),COLUMN(A:I)*99-98,99))<>""),{1;2;3}/1%+COLUMN(A:I),399))))&""
L2{=IF(K2="","",SUM(IFERROR((C$2:G$4=K2)*D$2:H$4,)))
留言
張貼留言