B2:B4=SUMPRODUCT(ISNUMBER(FIND("交"&COLUMN(A:CU)&"組",MID(SUBSTITUTE(C2,"、",REPT(" ",99)),ROW($1:$9)*99-98,99)))*COLUMN(A:CU))
H2:L4=(0&LEFT(RIGHT(TRIM(MID(SUBSTITUTE($C2,"、",REPT(" ",99)),COLUMN(A1)*99-98,99)),{2,3}),{1,2}))+G2
E2=IF(ROW(A1)>SUM(B:B),"",LOOKUP(ROW(A1)-1,SUBTOTAL(9,OFFSET(B$1,,,{1;2;3})),A$2:A$4))
F2=IF(E2="","",-LOOKUP(1,-LEFT(INDEX(TRIM(MID(SUBSTITUTE(VLOOKUP(E2,A:C,3,),"、",REPT(" ",99)),ROW($1:$9)*99-98,99)),LOOKUP(COUNTIF(E$2:E2,E2)-1,IF({1},OFFSET(G$1:K$1,MATCH(E2,A:A,)-1,)),COLUMN(A:E))),ROW($1:$5))))
留言
張貼留言