G2=IF(E2="","",SUMPRODUCT(FREQUENCY(ROW(INDIRECT(SUMIF(E$1:E1,E2,F$1:F1)+1&":"&SUMIF(E$2:E2,E2,F$2:F2))),MMULT((ROW($2:$14)>=COLUMN(B:N))*(A$2:A$14=E2),B$2:B$14*(A$2:A$14=E2)))*C$2:C$15)/F2)
另解=SUMPRODUCT((FREQUENCY(ROW(OFFSET($A$1,SUMIF($E1:E$1,E2,$F1:F$1),,F2)),MMULT(N(ROW($1:$13)>=COLUMN(A:M)),($A$2:$A$14=E2)*$B$2:$B$14)*($A$2:$A$14=E2)))*$C$2:$C$15)/F2
留言
張貼留言