I3:I10{=INDEX(F:F,SMALL(IF(MMULT(N(TRANSPOSE(SUMIF(OFFSET(B$3,,,ROW($1:$8)),B$3:B$10,D$3:D$10))<(TRANSPOSE(B$3:B$10)=F$3:F$5)*G$3:G$5),ROW(1:8)^0)+1>=COLUMN(A:I),ROW($3:$5),99),ROW(A1)))&""
J3:J10{=INDEX(C:C,SMALL(IF(B$3:B$10=I3,ROW($3:$10),99),COUNTIF(I$3:I3,I3)))&""
K3:K10{=IF(I3="","",IF(VLOOKUP(I3,F:G,2,)-SUMIF(I$2:I2,I3,K$2:K2)-INDEX(D:D,SMALL(IF(B$3:B$10=I3,ROW($3:$10)),COUNTIF(I$3:I3,I3)))>0,INDEX(D:D,SMALL(IF(B$3:B$10=I3,ROW($3:$10)),COUNTIF(I$3:I3,I3))),VLOOKUP(I3,F:G,2,)-SUMIF(I$2:I2,I3,K$2:K2)))
留言
張貼留言