使用Excel设计竞赛评分系统
来源： 系统之家 2015/01/15 15:47
今天系统之家小编就为大家介绍下如何使用Excel设计竞赛评分系统，其主要功能就是输入各种基础分值之后，其他所有需要的结果立马自动显示出来，十分方便。
机关、学校、企事业单位都会举办各种竞赛活动，竞赛的评分如果采用纯手工方式进行处理，不仅速度慢，而且可能出现差错，很大程度影响竞赛活动的实际效果。笔者运用Excel设计了一个评分系统，只要主持人把各评委的打分宣布完毕，工作人员同步进行分数录入，系统就会自动提示错误分值；自动将最高分、最低分分别用不同的颜色和字体进行区分显示；自动去掉最高分和最低分；自动计算每位选手的最后得分；自动生成参赛选手的得分名次；自动将得分较多的前三名用红色加粗字显示。如果此系统采用多媒体投影设备显示，能使整个竞赛评分过程显得快捷、紧凑、公开、透明，很能烘托竞赛气氛。此系统只要根据实际评委人数和选手数量稍加修改，即可广泛运用于各类竞赛活动的评分和统计。
设计步骤如下：
一、新建竞赛评分系统
新建“竞赛评分系统.xls”工作簿，C2:J2单元格中为各评委，B3:B12单元格中为各选手，K3:K12单元格为最后得分，L3:L12单元格为得分排名。C3:K12为记分区，所有的分数录入均在此区域（图1）。
二、设置数据有效性
如果评分标准为百分制，当录入数据不在此范围时，可采用“数据有效性”自动提示数据错误。
(1)用鼠标选择C3:K12数据录入的单元格。
(2)在“数据”菜单中，单击“有效性”。
(3)打开在“设置”选项卡，设置数据的有效范围（图2）。
(4)打开“出错警告”选项卡（图3，设置当录入数据超出设定范围时，弹出提示窗口的标题和内容。
三、显示最高、最低分
录入评委打分后，使用“条件格式”，通过字体和颜色，能将最高分、最低分与其他分值区别显示。
(1)使用鼠标选择C3:J3单元格区域，选择菜单“格式→条件格式”命令，打开“条件格式”对话框（图4）。
(2)单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”。将最高分的条件格式公式设置为：“=MAX($C3:$J3)=C3”。点击“格式”按钮，将字体设置为“加粗”和“红色”。
提示：如果条件格式公式为“=MAX(C3:J3)=C3”，引用则为相对引用，它会根据单元格的实际偏移量自动改变，显示结果就会出现差错。
(3)点击“添加”按钮，使用同样的方法，将最低分的公式设置为“=MIN($C3:$J3)=C3”，并将“字体”格式设置为“加粗”和“蓝色”（图5）。点击“确定”按钮，确认“条件格式”设置。
(4)将第三行的“条件格式”运用到以下各行。使用鼠标选择C3:J3单元格区域，把鼠标指向J3单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格J12，放开鼠标，即可完成填充。设置好后单击“确定”按钮，完成了“条件格式”的设置。
四、计算选手最后得分
(1)将最后得分精确度设置为小数点后两位。选择单元格K3:K12，右击鼠标，选择“设置单元格格式”。在“单元格格式”对话框的“数字”选项卡的“分类”区域中，选择“数值”，将“小数位数”设置为2（图6）。
(2)计算最后得分。
在最后得分单元格K3中输入以下公式:
=IF(COUNT(C3:J3)=0,"",(SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3))/(COUNT(C3:J3)-2))
公式中IF(COUNT(C3:J3)=0,""的作用是：当本行没有输入任何分数时，不进行计算，不显示任何内容。
公式中SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3) 的作用是：将本行分数进行求和，并减去最高分和最低分。
公式中COUNT(C3:J3)的作用是：进行评委人数统计。不用绝对数的目的主要是解决评委缺席和不确定问题。个别评委缺席，比赛照常进行，统计值不受影响。
单击K3单元格，把鼠标指向该单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格K12，放开鼠标，即可完成填充。
注：当录入分数到第二个时，分母为零，致使整个表格出现错误，这时可不理采。
五、统计选手得分名次
选定单元格L3，输入以下公式：
=IF(COUNT(C3:J3)=0,"",RANK(K3,$K$3:$K$12))
公式中RANK(K3,$K$3:$K$12) 的作用是：统计K3单元格在K3:K12中的排名。
公式中IF(COUNT(C3:J3)=0,""的作用是：当本行没有输入任何分数时，本单元格不显示任何内容。当K3没有值时，RANK(K3,$K$3:$K$12)会显示错误。
单击L3单元格，把鼠标指向该单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格L12，放开鼠标，即可将单元格L3的公式填充到以下各单元格。
六、突出显示优胜选手
使用鼠标选择L3:L12单元格区域，选择菜单“格式→条件格式”命令，打开“条件格式”对话框。
单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”。条件格式公式设置为：“=L3<=3”。点击“格式”按钮，将字体设置为“加粗”和“红色”（图7）。
通过以上步骤完成模板制作后，当主持人逐步宣布各评委的打分后，就可接着宣布去掉一个最高、去掉一个最低分，该选手的最后得分；当最后一名选手最后得分宣布完毕后，接着就可宣布本次比赛的前三名的选手姓名和得分了。中途不会有任何停顿和冷场。
本文来自于【系统之家】 www.xp85.com
机关、学校、企事业单位都会举办各种竞赛活动，竞赛的评分如果采用纯手工方式进行处理，不仅速度慢，而且可能出现差错，很大程度影响竞赛活动的实际效果。笔者运用Excel设计了一个评分系统，只要主持人把各评委的打分宣布完毕，工作人员同步进行分数录入，系统就会自动提示错误分值；自动将最高分、最低分分别用不同的颜色和字体进行区分显示；自动去掉最高分和最低分；自动计算每位选手的最后得分；自动生成参赛选手的得分名次；自动将得分较多的前三名用红色加粗字显示。如果此系统采用多媒体投影设备显示，能使整个竞赛评分过程显得快捷、紧凑、公开、透明，很能烘托竞赛气氛。此系统只要根据实际评委人数和选手数量稍加修改，即可广泛运用于各类竞赛活动的评分和统计。
设计步骤如下：
一、新建竞赛评分系统
新建“竞赛评分系统.xls”工作簿，C2:J2单元格中为各评委，B3:B12单元格中为各选手，K3:K12单元格为最后得分，L3:L12单元格为得分排名。C3:K12为记分区，所有的分数录入均在此区域（图1）。
二、设置数据有效性
如果评分标准为百分制，当录入数据不在此范围时，可采用“数据有效性”自动提示数据错误。
(1)用鼠标选择C3:K12数据录入的单元格。
(2)在“数据”菜单中，单击“有效性”。
(3)打开在“设置”选项卡，设置数据的有效范围（图2）。
(4)打开“出错警告”选项卡（图3，设置当录入数据超出设定范围时，弹出提示窗口的标题和内容。
三、显示最高、最低分
录入评委打分后，使用“条件格式”，通过字体和颜色，能将最高分、最低分与其他分值区别显示。
(1)使用鼠标选择C3:J3单元格区域，选择菜单“格式→条件格式”命令，打开“条件格式”对话框（图4）。
(2)单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”。将最高分的条件格式公式设置为：“=MAX($C3:$J3)=C3”。点击“格式”按钮，将字体设置为“加粗”和“红色”。
提示：如果条件格式公式为“=MAX(C3:J3)=C3”，引用则为相对引用，它会根据单元格的实际偏移量自动改变，显示结果就会出现差错。
(3)点击“添加”按钮，使用同样的方法，将最低分的公式设置为“=MIN($C3:$J3)=C3”，并将“字体”格式设置为“加粗”和“蓝色”（图5）。点击“确定”按钮，确认“条件格式”设置。
(4)将第三行的“条件格式”运用到以下各行。使用鼠标选择C3:J3单元格区域，把鼠标指向J3单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格J12，放开鼠标，即可完成填充。设置好后单击“确定”按钮，完成了“条件格式”的设置。
四、计算选手最后得分
(1)将最后得分精确度设置为小数点后两位。选择单元格K3:K12，右击鼠标，选择“设置单元格格式”。在“单元格格式”对话框的“数字”选项卡的“分类”区域中，选择“数值”，将“小数位数”设置为2（图6）。
(2)计算最后得分。
在最后得分单元格K3中输入以下公式:
=IF(COUNT(C3:J3)=0,"",(SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3))/(COUNT(C3:J3)-2))
公式中IF(COUNT(C3:J3)=0,""的作用是：当本行没有输入任何分数时，不进行计算，不显示任何内容。
公式中SUM(C3:J3)-MAX(C3:J3)-MIN(C3:J3) 的作用是：将本行分数进行求和，并减去最高分和最低分。
公式中COUNT(C3:J3)的作用是：进行评委人数统计。不用绝对数的目的主要是解决评委缺席和不确定问题。个别评委缺席，比赛照常进行，统计值不受影响。
单击K3单元格，把鼠标指向该单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格K12，放开鼠标，即可完成填充。
注：当录入分数到第二个时，分母为零，致使整个表格出现错误，这时可不理采。
五、统计选手得分名次
选定单元格L3，输入以下公式：
=IF(COUNT(C3:J3)=0,"",RANK(K3,$K$3:$K$12))
公式中RANK(K3,$K$3:$K$12) 的作用是：统计K3单元格在K3:K12中的排名。
公式中IF(COUNT(C3:J3)=0,""的作用是：当本行没有输入任何分数时，本单元格不显示任何内容。当K3没有值时，RANK(K3,$K$3:$K$12)会显示错误。
单击L3单元格，把鼠标指向该单元格右下角的填充柄，这时鼠标变成黑色的“十”形，按住鼠标左键，向下拖动至单元格L12，放开鼠标，即可将单元格L3的公式填充到以下各单元格。
六、突出显示优胜选手
使用鼠标选择L3:L12单元格区域，选择菜单“格式→条件格式”命令，打开“条件格式”对话框。
单击“条件1”下方的下拉箭头，在弹出的下拉列表中选择“公式”。条件格式公式设置为：“=L3<=3”。点击“格式”按钮，将字体设置为“加粗”和“红色”（图7）。
通过以上步骤完成模板制作后，当主持人逐步宣布各评委的打分后，就可接着宣布去掉一个最高、去掉一个最低分，该选手的最后得分；当最后一名选手最后得分宣布完毕后，接着就可宣布本次比赛的前三名的选手姓名和得分了。中途不会有任何停顿和冷场。
本文来自于【系统之家】 www.xp85.com
