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

利用Excel的关联表格制作歌唱比赛计分表上机试验[e-mail]2某市举办的“青年歌手大奖赛”，经过初赛、复赛，目前已经进入决赛。在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定了如下的比赛计分规则。每一个参加决赛选手的得分满分为100分，包括以下三大部分：1、歌唱得分：每一位参赛选手自行选择一首歌曲演唱，满分90分，12个裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。2、素质得分：两个题目，每题0.5分，共1分，各由一个评委评分。3、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲有不同的难度系数，分A、B、C三个级别，满分9分，12个裁判打分，方法同上，但是在得到的平均分数的基础上再乘上难度系数（A为1，B为0.8，C为0.6），才能得到该项分数。以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖等级。[e-mail]3目标:制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。[问题分析和操作提示]从上面的规则可以看到，需要进行几个单项成绩的计算和排名，同时需要进行几项成绩之和作为综合分的计算和排名，所以可以考虑利用Excel的关联表格操作。所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总称。本实例制作时，还需要注意以下几个问题：1．各个表格中要尽量减少现场数据录入和计算的工作量。2．可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时名次。3．单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容错性。[e-mail]4从上面的的分析可知，该计分系统需要设置8个工作表，而Excel默认的工作表个数为3个。所以，本实例操作时，首先需要工作表个数的添加以及名称的更改。操作步骤如下：1．启动Excel，屏幕上有默认的Sheet1、Sheet2、Sheet3三个工作表。2．在工作表标签上，单击选择Sheet1，然后单击鼠标右键，从弹出的快捷菜单中选择“插入”，根据随后出现的系统提示操作，添加了一个新工作表Sheet4。说明：上述方法一次只能添加一个工作表，如果想添加多个工作表，请先选取多个工作表，然后再单击右键，选择“插入”，则会快速添加与选取个数一样多的工作表。3．根据上面的说明，借助于Shift键选择全部四个工作表，单击鼠标右键，选择“插入”，则快速添加了四个工作表——Sheet5、Sheet6、Sheet7、Sheet8。4．分别双击工作表名称，将8个工作表的名称依次更该为“主界面”、“计分规则”、“选手情况”、“歌唱得分”、“素质得分”、“声乐得分”、“综合得分”和“评奖结果”。5．将工作簿文件以“歌手大奖赛记分”为名存盘。4.1工作表的添加和更名[e-mail]5“主界面”工作表效果如图4.1所示，利用“主界面”工作表，可以快速看到整个记分方案的完整组成，同时根据需要将来可以快速切换到需要的其他工作表。图4.1“主界面”工作表效果该工作表制作中要用到工作表网格线的取消、单元格填充颜色设置、艺术字标题设置、椭圆形按钮绘制以及超级链接建立等知识。4.2“主界面”工作表的制作[e-mail]6操作步骤：1．单击工作表标签上的“主界面”，选取该工作表。2．单击“工具”|“选项”命令，弹出图4.2所示的“选项”对话框，从中选取“视图”标签，将“网格线”复选框设置为不选中状态，这样就取消了工作表中的网格线。3．全选整个工作表，然后利用格式工具栏上的按钮设置工作表填充颜色为浅黄色。图4.2在Excel的“选项”对话框中取消网格线[e-mail]74．利用与Word中输入艺术字类似的方法，输入艺术字标题“青年歌手大奖赛计分方案”，并进行适当格式设置。5．利用“绘图”工具栏上的椭圆工具绘制椭圆，并进行填充效果、线条颜色以及图形大小的设置。输入过艺术字标题和绘制椭圆图形后,“主界面”工作表如图4.3所示。图4.3输入过艺术字标题和绘制椭圆图形后的“主界面”工作表[e-mail]86．将上面制作的椭圆图形再复制6个，并将他们放置到适当位置，最后一个需要放大。操作时，可以借助“绘图”工具栏上的“绘图”|“对齐和分布”命令来辅助完成。7．依次右击各个椭圆，从弹出的菜单中选择“添加文字”，分别输入相应的文字，效果如图4.4所示。图4.4在椭圆中输入文字后的界面效果[e-mail]98．绘制椭圆的目的，就是为了将来单击椭圆，就能快速打开对应的工作表。为此，需要为他们设置超级链接。操作步骤如下：（1）选中第一个椭圆“计分规则”，执行“插入”|“超级链接”命令，弹出如图4.5所示的“编辑超级链接”对话框。图4.5“编辑超级链接”对话框说明：选中椭圆后，按组合键Ctrl+K也可以快速打开“编辑超级链接”对话框。[e-mail]10（2）在“编辑超级链接”对话框中，在“链接到：”区域中选择“本文档中的位置”，从右侧的“在这篇文档中选择位置”中选择链接的对象，即“计分规则”工作表。说明：在图4.6对话框中，在“链接到：”区域中，选择“原有文件或Web页”，可以建立到本地计算机上或者因特网上的文件上的超级链接；选择“新建文档”或“电子邮件地址”，可以分别建立到一个新建文件上或者电子邮件上的超级链接。（3）在“编辑超级链接”对话框中，单击“屏幕提示”按钮，将弹出“设置超级链接屏幕提示”对话框，如图4.6所示，在“屏幕提示文字”框中输入相应文字，该文字在将来光标停留在椭圆上时，会以操作时的提示文字形式自动出现。图4.6“设置超级链接屏幕提示”对话框（4）按照上述方法，依次为各个椭圆对象设置相应的超级链接。9．对该工作表中各个对象的位置、大小再进行适当调整，“主界面”工作表完成。[e-mail]114.3“计分规则”工作表的制作“计分规则”工作表如图4.7所示，在其中输入了比赛计分规则的说明文字，以便将来观众或选手咨询时能够快速查询。制作本工作表时，主要难点是如何解决大块文字在Excel中的输入问题。图4.7“计分规则”工作表[e-mail]12一般情况下Excel单元格中很少输入大量文字，特别是多行文字。而对于本工作表，确实现在需要这样处理。解决的方法有以下几种：1．拉大单元格方法。比如将A1单元格的宽度、高度均设置为足够大，然后在其中输入文字，分段换行时按Alt＋Enter（不能像Word一样直接按Enter）2．设置单元格文字自动换行。利用上述方法将单元格拉大后，选中单元格，单击右键，选择“设置单元格格式”，如图4.8所示，从弹出的对话框中，将“对齐”标签下的“自动换行”复选框选上，则以后再输入文字一旦到单元格右端将会自动换行。图4.8设置单元格内文本自动换行3．利用文本框的方法。可以在工作表上绘制一个文本框，并将其调整到适当大小，在文本框将需要的文字录入，本实例中采用的就是该方式。[e-mail]134.4“选手情况”工作表的制作“选手情况”工作表效果如图4.9所示，制作时按照图中样式进行设置即可。图4.9“选手情况”工作表制作该工作表的目的有两个：一是可以随时查看各个选手的基本情况；二是将选手的编号、姓名在该表输入之后，以后在其它工作表需要选手姓名时，只要输入编号，姓名利用查表法即可确认。另外，本工作表中“编号”一列的输入可以考虑利用序列填充，“性别”和“唱法类型”两列可以考虑使用选择列表法输入。[e-mail]144.5“歌唱得分”工作表的制作“歌唱得分”工作表效果如图4.10所示，因为选手出场顺序是通过抽签产生，以后的打分是按照出场顺序而不是按照选手编号进行，所以需要设置“出场序号”一列（该列通过序列填充输入）。图4.10“歌唱得分”工作表[e-mail]15另外，本工作表只有到现场比赛时，才能输入选手编号和裁判打分，从而确定选手姓名，然后计算选手分数，最后确定选手名次。所以，大家在图4.10中只看到一行数据，这只是测试数据，用来验证函数和公式的正确性。在图4.10中，姓名、分数、名次三列中每个单元格中都有函数或公式，一旦编号和裁判打分输入之后，他们都会自动出现结果。下面说明该工作表的制作过程：1.工作表表格框架的制作按照图4.10所示样式，制作“歌唱得分”工作表的表格框架，将“出场序号”一列利用序列填充方法输入完整，对整个表格并做好格式设置。2.利用VLOOKUP函数根据编号确定选手姓名该表中，“选手编号”列也就是C列按照抽签顺序在比赛时录入，“姓名”一列可以事先设置好公式，以便将来录入选手编号后，可以自动产生，这就需要使用垂直查找函数VLOOKUP。（1）VLOOKUP函数的使用方法和简单举例①VLOOKUP函数的作用在表格或首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。[e-mail]16②VLOOKUP函数的格式VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。其中：Lookup_value为需要在数据表第一列中查找的数值,可以为数值、引用或文字串；Table_array为需要在其中查找数据的数据表或数据区域；Col_index_num为table_array中待返回的匹配值的列序号。Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。[e-mail]17图4.11VLOOKUP函数使用举例③VLOOKUP函数的使用举例比如：如图4.11所示，如果想根据D2的代号到A2：B7数据区域中查找E2单元格中的对应名称，则E2单元格中的函数形式应该为：=VLOOKUP(D2,$A$2:$B$7,2)其中，$A$2:$B$7为采用引用格式，原因是如果该单元格需要向下拖动时，应该要求该数据区域不能变化，所以公式中该数据区域要使用绝对引用。[e-mail]18（2）函数中跨工作表以及跨工作簿的单元格引用在办公实践的许多情况下，有时公式中都可能要用到另一工作表单元格中的数据，如Sheet1工作表F4的公式如果为：＝（C4＋D4＋E4）＊Sheet2！B1其中“Sheet2！B1”表示工作表Sheet2中的B1单元格地址。这个公式表示计算当前工作表Sheet1中的C4、D4和E4单元格数据之和与Sheet2工作表的B1单元格数据的乘积，结果存入当前工作表Sheet1中的F4单元格。[e-mail]19函数中还可以进行跨工作簿的单元格引用，此时地址的一般形式为：［工作簿名］工作表名！单元格地址综上所述，到跨工作簿、工作表的单元格地址引用的方法分别如下：①在当前工作表中引用本工作表中单元格，只需输入单元格的地址即可。②在当前工作表中引用本工作簿中其它工作表中单元格时，需首先输入被引用的工作表名和一个感叹号“！”，然后再输入那个工作表中的单元格地址。③在当前工作表中引用另外工作簿中工作表的单元格时，需要首先输入由中括号“[]”包围的引用的工作簿名称，然后输入被引用的工作表名称和一个感叹号“！”，最后再输入那个工作表中的单元格的地址。[e-mail]20公式中跨工作表和跨工作簿的单元格引用例：下图中，用来计算三门课程总成绩的单元格E3中的公式为：=B3*$G$3+C3*$G$4+D3*$G$5+离散数学!B3+[计算机成绩.xls]计算机成绩!$B$[e-mail]21（3）根据编号利用VLOOKUP查找姓名根据上面对VLOOKUP函数的使用说明，“歌唱得分”工作表中用来存放姓名的D5单元格的公式应该为:“＝VLOOKUP(C5,选手情况!$A$3:$B$21,