F2:F5{=INDEX(B:B,RIGHT(LARGE(IF(MATCH(B$2:B$17,B:B,)=ROW($2:$17),MMULT(N(TRANSPOSE(B$2:B$17)=B$2:B$17),D$2:D$17)/1%+ROW($2:$17)),ROW(A1)),2))
另解F2:F5{=INDEX(B:B,RIGHT(MAX(IF(COUNTIF(F$1:F1,B$2:B$17)=0,SUMIF(B:B,B$2:B$17,D:D)/1%+ROW($2:$17))),2))
G2:G5=SUMIF(B:B,F2,D:D)
F欄也可以用SUMIF, 不過不確定舊版本能不能這樣寫, 老師可以試看看
回覆刪除=INDEX(B:B,MOD(LARGE(IF(MATCH(B$2:B$17,B:B,)=ROW($2:$17),SUMIF(B:B,B$2:B$17,D:D)/1%+ROW($2:$17)),ROW(A1)),100))
作者已經移除這則留言。
刪除習慣用RIGHT 因MOD 會有浮奌=INDEX(B:B,RIGHT(MAX(IF(COUNTIF(F$1:F1,B$2:B$17)=0,SUMIF(B:B,B$2:B$17,D:D)/1%+ROW($2:$17))),2))
刪除