EXCEL中如何使用公式将两列成绩取值最优？
发布网友
共3个回答
热心网友
=IF(COUNTIF(B2:C2,"S3"),"S3",IF(COUNTIF(B2:C2,"S2"),"S2",IF(COUNTIF(B2:C2,"S1"),"S1",IF(COUNTIF(B2:C2,"S0"),"S0",IF(COUNTIF(B2:C2,"黄牌"),"黄牌","红牌")))))
热心网友
结果是什么样的？
建立个对照表
用sumif函数
E3=SUMIF(F:F,B3,G:G)+SUMIF(F:F,C3,G:G)
D3="第"&RANK(E3,$E$3:$E$6)&"名"
下拉公式
F列数字，根据实际，填写,反正按从大到小，写数字就看
热心网友
估计应该是在D3单元格输入公式：
=CHOOSE(MAX(MATCH(B3:C3,{"红牌","黄牌","S0","S1","S2","S3"})), "红牌","黄牌","S0","S1","S2","S3")
复制并下拉即可
