|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 wintroop 于 2020-9-24 08:55 编辑
先开一列火车,有空了再来研究简短写法
=IF(C2=MAX($C$2:$C$21),1,IF(C2>=LARGE($C$2:$C$21,MIN(COUNTIF($C$2:$C$21,MAX($C$2:$C$21))+2,20)),2,IF(C2>=LARGE($C$2:$C$21,MIN(COUNTIF($C$2:$C$21,">="&LARGE($C$2:$C$21,COUNTIF($C$2:$C$21,MAX($C$2:$C$21))+2))+3,20)),3,SUMPRODUCT((($C$2:$C$21>=C2)-($C$2:$C$21>=LARGE($C$2:$C$21,MIN(COUNTIF($C$2:$C$21,">="&LARGE($C$2:$C$21,COUNTIF($C$2:$C$21,MAX($C$2:$C$21))+2))+3,20))))/COUNTIF($C$2:$C$21,$C$2:$C$21))+3))) |
|