利用Excel制作竞赛评分自动计算表.docVIP

利用Excel制作竞赛评分自动计算表评分表是各种竞赛中常用的一种统计和亮分办法。传统的评分表是用手工记录、手工或计算器计算，然后再人工排序，这样做不仅效率低下，而且容易出错。利用Excel中排序等相关功能制作的评分表，使总分计算实现自动化，名次排列也变成一个动态的过程，这样就使统计过程有很强的现场感，结果也会更具刺激性。下面以一个8名评委、10名选手参加的卡拉OK大奖赛为例，介绍一下评分自动计算表的制作。　　1. 制作一张表头内容如图1所示的工作表，并将其命名为“评分表1”。 图1 　　2. 在A2中输入“1”，A3中输入“2”，选中A2和A3两个单元格，并将鼠标置于被选中的两个单元格右下角的“十”字（即填充柄）上，按住鼠标左键向下拖动到A11，这样就快速地输入了参赛选手的序号。选手越多，这种自动填充方法的优势越明显。　　3. 在J2单元格输入“ SUM B2:I2 -MAX B2:I2 -MIN B2:I2 /6”。该公式的作用是：按照一般的比赛规则，去掉一个最高分、去掉一个最低分，剩余分数的平均分作为选手的最后得分。为使结果显示格式一致和精确度更高，可使最后得分比每个评委所给的分数多一到两位小数。例如，可以要求评委的打分统一为一位小数，“最后得分”则保留两位小数。　　4. 在K2单元格中输入“ RANK J2,$J$2:$J$11 ”。该公式的作用是算出J2单元格的数值在J2到J11各个单元格中的排序，即1号选手在所有选手中的名次。注意：K2中输入的单元格区域必须采用绝对引用（如果不是采用键盘直接输入，而是采用粘贴函数输入，可在要求指定区域参数时，用鼠标拖过从J2到J11的所有单元格后，在显示“J2:J11”时，将其选中后按下F4键，就会转化成“$J$2:$J$11”），否则就会出现错误。　　5. 选中J2和K2两个单元格，将鼠标置于被选中的两个单元格的右下角填充柄上，按住鼠标左键向下拖动到第11行，这样就可以算出所有选手的最后得分和名次。 6. 选中B2到I2的所有单元格，依次执行“格式→条件格式”，做如图2所示的设置。该设置的目的是：如果某个评委的打分严重偏离平均分，该评委的分数会以特殊颜色显示，提示评委注意。本例以10%为限，当评委的分数比平均分高10%以上，分数以红色显示；当评委分数低于平均分10%以上，分数以蓝色显示。将鼠标置于被选中的B2到I2的单元格区域的右下角填充柄上，按住鼠标左键向下拖动到第11行，这样就完成对所有评委打分的相同格式设置。制作好的空表如图3所示。 图2 图3 　　上述空表格设置好以后（显然，这些工作可以在赛前完成，如果比赛时情况万一发生了小的变化：比如，少了一个评委、多了一个选手等，都很容易对原来设计好的表进行适当修改），在比赛现场，对每一位参赛选手，只要依次输入各位评委的分数，该选手的最后得分和暂列名次就会自动显示出来（比赛已经完成的选手名次也会随着比赛的进行不断发生变化），异常分数也会相应显示，不仅当前的比赛选手能立即知道自己的得分和排名情况，而且所有已参赛的选手情况均一目了然，最终效果如图4所示。由于选手的名次是一个动态显示过程，其现场感和刺激性是不言而喻的。试想，如果利用大屏幕投影设备将其放大投出，主持人一边报出分数，一边看到跳跃的数字，是何等的激动人心！ 图4 细心的读者可能已经发现一个问题，就是1号选手和4号选手最后得分虽然同为9.60分，却一个显示第3名，另一个显示第2名，尽管这种情况在实际情况中并不多见，但是笔者为了引入下面的问题，还是凑出这么一组数据。为了解决这一问题，我们可以将K2单元格中的公式换成：“ COUNTIF $J$2:$J$11, J2 ”。其余操作同上，这样可以从得到的结果（并列第3名）中可以看出，1号选手和4号选手同分不同名次的问题已经得到解决。　　但是新的问题又产生了，那就是我们通常都习惯说“1号选手和4号选手是并列第2名”，而不像表中所显示的那样，说成是“并列第3名”。这个问题又如何解决呢？其实只要在第一种计算名次的方法中，将“评分表1”中的J列选中后，将鼠标置于被选中的区域上单击右键，在弹出的快捷菜单中执行“复制”命令。再次将鼠标置于被选中区域单击右键，执行快捷菜单中的“选择性粘贴”一项，在随后弹出的“选择性粘贴”对话框中选择粘贴内容为“数值”，这时我们会发现理想的显示方式出现了，最后的显示结果为并列第2名。　　为了重复使用这一劳动成果，以后使用时只要将上面评分表中的评委分数清除即可，在新的比赛中，只要依次输入新的分数，其新的结果立即就会显示出来。　　注意：清除原有数据时不要将“最后得分”和“名次”两列中的数据清除，因为这样会把相应的公式清除掉。当然，您也可以将评分表中的项目进行适当修改，以便进行其他自动计算工作。