上机试验2利用Excel关联表格制作歌唱比赛计分表.ppt

xs5716@163.com 利用Excel的关联表格制作歌唱比赛计分表 1．单击工作表标签上的“主界面”，选取该工作表。 2．单击“工具”|“选项”命令，弹出图4.2所示的“选项”对话框，从中选取“视图”标签，将“网格线”复选框设置为不选中状态，这样就取消了工作表中的网格线。 3．全选整个工作表，然后利用格式工具栏上的按钮设置工作表填充颜色为浅黄色。 根据计分规则，图4.12“歌唱得分”工作表中用来存放分数的Q5单元格的公式应该为: “=IF(SUM(E5:P5)0,(SUM(E5:P5)-MIN(E5:P5)- MAX(E5:P5))/(COUNT(E5:P5)-2),)”。 D5中的公式输入完成之后，通过鼠标拖动将其一直拖动到最后一行记录，则计算选手歌唱分数一列的公式复制完成。 4.6“素质得分”工作表的制作 “素质得分”工作表如图4.14所示，制作方法与“歌唱得分”工作表类似。 4.7“声乐得分”工作表的制作 “声乐得分”工作表如图4.15所示，制作方法与“歌唱得分”工作表类似，不同之处就是多了两列：“类别”和“系数”，他们用来确定难度系数，最终的分数结果还需要在歌唱得分方法的结果之后再乘上难度系数。 4.8“综合得分”工作表的制作 “综合得分”工作表效果如图4.16所示，制作中要多次采用跨工作表引用单元格数据 D5：“=IF(C5=”“,”“,VLOOKUP(C5,选手情况!$A$3:$B$21,2))”； E5：“=歌唱得分!Q5”； F5：“=素质得分!G5”； G5：“=声乐得分!S5”； H5：“=IF(SUM(E5:G5)=0,,SUM(E5:G5))”； I5：“=IF(H5,RANK(H5,$H$5:$H$24),)”； J5：“=IF(I5=1,一等奖,IF(I5=4,二等奖,IF(I5=9,三等奖,优秀奖)))” 4.9“评奖结果”工作表的制作 “评奖结果”工作表效果如图4.17所示，表格中空白单元格，根据比赛的综合成绩，将对应人员的名单输入或者通过复制得到即可。 4.10 Excel中公式出错的处理 在Excel中输入计算公式或函数后，经常会因为某些错误，在单元格中显示错误信息，使一些初学者手足无措。现将Excel中最常见的一些错误信息，以及可能发生的原因和解决方法列出如下，供大家参考。 图4.15 “声乐得分” 工作表 其中： D5单元格的公式为： “=IF(C5=,,VLOOKUP(C5,选手情况!$A$3:$B$21,2))”； 另外，根据计分规则，用来存放分数的S5单元格的公式为 “=IF(SUM(G5:R5)0,(SUM(G5:R5)-MIN(G5:R5)-MAX(G5:R5))/(COUNT(G5:R5)-2)*F5,)”； 用来存放名次的T5单元格的公式为： “=IF(S5,RANK(S5,$S$5:$S$24),)”。 图4.16 工作表“综合得分” 工作表 第5行中几个主要含公式和函数的单元格中，公式和函数形式分别如下所示： 图4.17 “评奖结果” 工作表 * * 上机试验3 某市举办的“青年歌手大奖赛”，经过初赛、复赛，目前已经进入决赛。在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定了如下的比赛计分规则。 每一个参加决赛选手的得分满分为100分，包括以下三大部分： 1、歌唱得分：每一位参赛选手自行选择一首歌曲演唱，满分90分，12个裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。 2、素质得分：两个题目，每题0.5分，共 1分，各由一个评委评分。 3、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲有不同的难度系数，分A、B、C三个级别，满分9分，12个裁判打分，方法同上，但是在得到的平均分数的基础上再乘上难度系数（A为1，B为0.8，C为0.6），才能得到该项分数。 以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖等级。 目标: 制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。 [问题分析和操作提示] 从上面的规则可以看到，需要进行几个单项成绩的计算和排名，同时需要进行几项成绩之和作为综合分的计算和排名，所以可以考虑利用Excel的关联表格操作。 所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总称。 本实例制作时，还需要注意以下几个问题： 1．各个表格中要尽量减少现场数据录入和计算的工作量。 2．可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时名次。 3．单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容错性。 从上面的的分析可知，该计分系统需要设置8个工作表，而Ex