H2:J7{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(COUNTIFS($C$2:$C$21,$C$2:$C$21,$D$2:$D$21,">"&$D$2:$D$21)=0,$D$2:$D$21/1%+ROW($2:$21)),ROW(A1)),2)),"")
B2:D21格式化=COUNTIF($H:$H,$B2)
L2:N7{=IFERROR(INDEX(B:B,RIGHT(LARGE(IF(COUNTIFS($C$2:$C$21,$C$2:$C$21,$D$2:$D$21,">"&$D$2:$D$21)+COUNTIFS($C$2:$C$21,$C$2:$C$21,$D$2:$D$21,$D$2:$D$21)=COUNTIF($C$2:$C$21,$C$2:$C$21),$D$2:$D$21/1%+ROW($2:$21)),ROW(A1)),2)),"")
I14:I18{=IFERROR(INDEX(C:C,RIGHT(LARGE(IF(MATCH(C$2:C$21,C:C,)=ROW($2:$21),ROUND(SUMIF(C:C,C$2:C$21,D:D)/COUNTIF(C:C,C$2:C$21),2)/1%%+ROW($2:$21)),ROW(A1)),2)),"")
J14:J18=IF(I14="","",ROUND(SUMIF(C:C,I14,D:D)/COUNTIF(C:C,I14),2))
留言
張貼留言