B9:D11{=SUM(COUNTIF($A9,"*"&(RIGHT(SMALL(IFERROR(MID($A$2:$A$6,MMULT(ISERR(-MID($A$2:$A$6,COLUMN($A:$I),1))*ISNUMBER(-MID($A$2:$A$6,COLUMN($B:$J),1)),ROW($2:$10)),{1,2,3,4})*1,)+ROW(1:5)*10^8,ROW($1:$5)*4),8)/RIGHT(SMALL(IFERROR(LEFT($A$2:$A$6,{1,2})*1,)+ROW(1:5)*10^2,ROW($1:$5)*2),2)))*B$2:B$6)
F2:F6{=LOOKUP(1,-MID(A2,MATCH(1,ISERR(-MID(A2,ROW($1:$8),1))*ISNUMBER(-MID(A2,ROW($2:$9),1)),)+1,{1,2,3,4}))/LOOKUP(1,-LEFT(A2,{1,2}))
B14:D16=SUMPRODUCT(COUNTIF($A14,"*"&$F$2:$F$6)*B$2:B$6)
留言
張貼留言