假设比赛中有7个评委,他们打出的分数会被输入到C4:I23区域中。

    选择这个区域,【开始】——【条件格式】——【新建规则】

彩38     在弹出的对话框中选择“使用公式确定要设置格式的单元格”

    在文本框中输入公式:

  =AND(COUNTIF($C4:C4,C4)=1,C4=MAX($C4:$I4))

  然后单击“格式”按钮,设置所需的格式后单击“确定”关闭对话框。

    说明:公式的原理是选出从左到右第一次出现的最高分,也就是说要高亮显示的单元格必须具备两个条件,一是从左到右第一次出现,这个条件用COUNTF函数来实现;二是必须为最高分,这个条件用MAX函数实现。在有多个并列的最高分时,该公式可以避免所有最高分都被高亮显示。

    重复上述步骤再次新建一个条件格式规则,为最低分设置公式:

    =AND(COUNTIF($C4:C4,C4)=1,C4=MIN($C4:$I4))

    这样就可以让每位选手的最高分和最低分高亮显示了,是不是很醒目呢?

    如果评委更多,需要去掉两个最高分和两个最低分,要高亮显示这些被去掉的得分,该怎样设置条件格式的公式呢?

    假如有16个评委,评委的打分输入到C4:R23区域中,高亮显示两个最高分的公式可设置为:

    =OR(AND(COUNTIF($C4:C4,C4)<3,C4=MAX($C4:$R4)),AND(COUNTIF($C4:C4,C4)=1,C4=LARGE($C4:$R4,2)))

    说明:OR函数的两个参数分别对应最高分和次高分,OR函数的第一个参数“AND(COUNTIF($C4:C4,C4)<3,C4=MAX($C4:$R4))”为TRUE时选出唯一的最高分或从左到右第1次和第2次出现的最高分;当最高分唯一时,OR函数的第二个参数“AND(COUNTIF($C4:C4,C4)=1,C4=LARGE($C4:$R4,2))”为TRUE则选出从左到右第1次出现的次高分。

彩38     高亮显示两个最低分的公式可设置为:

    =OR(AND(COUNTIF($C4:C4,C4)<3,C4=MIN($C4:$R4)),AND(COUNTIF($C4:C4,C4)=1,C4=SMALL($C4:$R4,2)))

彩38     最后说明一下去掉一个最高分和一个最低分后计算平均值的公式,计算选手最后得分的公式为:

  =IFERROR(TRIMMEAN(C4:I4,0.3),"")

彩38     所用到的函数TRIMMEAN,其第二个参数为计算平均值所要排除数量占总数的百分比,如本例选手有7个得分,要去掉一个最高分和一个最低分即2个数值,该参数取值0。3(30%),因为7×0。3=2。1,向下舍入到2的倍数为2,即对称地去掉一个最高分和一个最低分。

    当选手有16个得分,要去掉两个最高分和两个最低分即4个数值时,该参数取值0。3(30%),因为16×0。3=4。8,向下舍入到2的倍数为4,即对称地去掉两个最高分和两个最低分。由于会自动向下舍入到2的倍数,实际上对于16去4取平均值的情况,该参数取值0。25-0。37都是可以的。