《上机试验2利用Excel关联表格制作歌唱比赛计分表》由会员分享，可在线阅读，更多相关《上机试验2利用Excel关联表格制作歌唱比赛计分表（37页珍藏版）》请在金锄头文库上搜索。
1、上机试验上机试验2-2-利用利用ExcelExcel关关联表格制作歌唱比赛计分联表格制作歌唱比赛计分表表 某市举办的某市举办的“青年歌手大奖赛青年歌手大奖赛”，经过初赛、复赛，目前已经进入决赛。，经过初赛、复赛，目前已经进入决赛。在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定了如下的比赛计分规则。了如下的比赛计分规则。每一个参加决赛选手的得分满分为每一个参加决赛选手的得分满分为100分，包括以下三大部分：分，包括以下三大部分：1、歌唱得分：每一位参赛选手自行选择一首歌曲演唱，满分、歌唱得分：每一位参赛选手自行选择
2、一首歌曲演唱，满分90分，分，12个个裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。2、素质得分：两个题目，每题、素质得分：两个题目，每题0.5分，共分，共 1分，各由一个评委评分。分，各由一个评委评分。3、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲有不同的难度系数，分有不同的难度系数，分A、B、C三个级别，满分三个级别，满分9分，分，12个裁判打分，方个裁判打分，方法同上，但是在得到的平均分数的基础上再乘上难度系数（法同上，但是在得到的平均
3、分数的基础上再乘上难度系数（A为为1，B为为0.8，C为为0.6），才能得到该项分数。），才能得到该项分数。以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖等级。等级。2目标目标目标目标: :制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。 问题分析和操作提示问题分析和操作提示问题分析和操作提示问题分析和操作提示 从上面的规则
4、可以看到，需要进行几个单项成绩的计算和排名，同时需要进从上面的规则可以看到，需要进行几个单项成绩的计算和排名，同时需要进从上面的规则可以看到，需要进行几个单项成绩的计算和排名，同时需要进从上面的规则可以看到，需要进行几个单项成绩的计算和排名，同时需要进行几项成绩之和作为综合分的计算和排名，所以可以考虑利用行几项成绩之和作为综合分的计算和排名，所以可以考虑利用行几项成绩之和作为综合分的计算和排名，所以可以考虑利用行几项成绩之和作为综合分的计算和排名，所以可以考虑利用ExcelExcel的关联表的关联表的关联表的关联表格操作。格操作。格操作。格操作。所谓关联表格，是指在一个或者多个工作簿中有一定关
5、联关系的工作表的总所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总称。称。称。称。本实例制作时，还需要注意以下几个问题：本实例制作时，还需要注意以下几个问题：本实例制作时，还需要注意以下几个问题：本实例制作时，还需要注意以下几个问题：1 1各个表格中要尽量减少现场数据录入和计算的工作量。各个表格中要尽量减少现场数据录入和计算的工作量。各个表格中要尽量减少现场数据录入和计算的工作量。各个表格中要尽量减少现场数据录入和计算的工作量。2 2可以在比赛中
6、间随时查看比赛过的选手的各个单项成绩，确定对应的暂时可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时名次。名次。名次。名次。3 3单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容错性。错性。错性。错性。3从上面的的分析可知，该计分系统需要设置从上面
7、的的分析可知，该计分系统需要设置8个工作表，而个工作表，而Excel默认的工默认的工作表个数为作表个数为3个。所以，本实例操作时，首先需要工作表个数的添加以及名称个。所以，本实例操作时，首先需要工作表个数的添加以及名称的更改。的更改。操作步骤如下：操作步骤如下：1启动启动Excel，屏幕上有默认的，屏幕上有默认的Sheet1、Sheet2、Sheet3三个工作表。三个工作表。2在工作表标签上，单击选择在工作表标签上，单击选择Sheet 1，然后单击鼠标右键，从弹出的快捷菜，然后单击鼠标右键，从弹出的快捷菜单中选择单中选择“插入插入”，根据随后出现的系统提示操作，添加了一个新工作表，根据随后出现
8、的系统提示操作，添加了一个新工作表Sheet 4。说明：上述方法一次只能添加一个工作表，如果想添加多个工作表，请先选取说明：上述方法一次只能添加一个工作表，如果想添加多个工作表，请先选取多个工作表，然后再单击右键，选择多个工作表，然后再单击右键，选择“插入插入”，则会快速添加与选取个数一样多，则会快速添加与选取个数一样多的工作表。的工作表。3根据上面的说明，借助于根据上面的说明，借助于Shift键选择全部四个工作表，单击鼠标右键，选键选择全部四个工作表，单击鼠标右键，选择择“插入插入”，则快速添加了四个工作表，则快速添加了四个工作表Sheet 5、Sheet 6、Sheet 7、Sheet 8
9、。4分别双击工作表名称，将分别双击工作表名称，将8个工作表的名称依次更该为个工作表的名称依次更该为“主界面主界面”、“计分规计分规则则”、“选手情况选手情况”、“歌唱得分歌唱得分”、“素质得分素质得分”、“声乐得分声乐得分”、“综合得分综合得分”和和“评评奖结果奖结果”。5将工作簿文件以将工作簿文件以“歌手大奖赛记分歌手大奖赛记分”为名存盘。为名存盘。4.14.1工作表的添加和更名工作表的添加和更名工作表的添加和更名工作表的添加和更名4“主界面主界面”工作表效果如图工作表效果如图4.1所示，利用所示，利用“主界面主界面”工作表，可以快速看工作表，可以快速看到整个记分方案的完整组成，同时根据需要
10、将来可以快速切换到需要的其到整个记分方案的完整组成，同时根据需要将来可以快速切换到需要的其他工作表。他工作表。图4.1 “主界面”工作表效果该工作表制作中要用到工作表网格线的取消、单元格填充颜色设置、该工作表制作中要用到工作表网格线的取消、单元格填充颜色设置、艺术字标题设置、椭圆形按钮绘制以及超级链接建立等知识。艺术字标题设置、椭圆形按钮绘制以及超级链接建立等知识。4.2 “4.2 “主界面主界面主界面主界面” ”工作表的制作工作表的制作工作表的制作工作表的制作5操作步骤：操作步骤：操作步骤：操作步骤：1 1单击工作表标签上的单击工作表标签上的“主界面主界面”，选取该工作表。，选取该工作表。2
11、 2单击单击“工具工具”|“”|“选项选项”命令，弹出图命令，弹出图4.24.2所示的所示的“选项选项”对话框，从中选对话框，从中选取取“视图视图”标签，将标签，将“网格线网格线”复选框设置为不选中状态，这样就取消了复选框设置为不选中状态，这样就取消了工作表中的网格线。工作表中的网格线。3 3全选整个工作表，然后利用格式工具栏上的按钮设置工作表填充颜色为浅全选整个工作表，然后利用格式工具栏上的按钮设置工作表填充颜色为浅黄色。黄色。图4.2 在Excel的“选项” 对话框中取消网格线64利用与利用与Word中输入艺术字类似的方法，输入艺术字标题中输入艺术字类似的方法，输入艺术字标题“青年歌手青年
12、歌手大奖赛计分方案大奖赛计分方案”，并进行适当格式设置。，并进行适当格式设置。5利用利用“绘图绘图”工具栏上的椭圆工具绘制椭圆，并进行填充效果、线条工具栏上的椭圆工具绘制椭圆，并进行填充效果、线条颜色以及图形大小的设置。输入过艺术字标题和绘制椭圆图形后颜色以及图形大小的设置。输入过艺术字标题和绘制椭圆图形后,“主主界面界面”工作表如图工作表如图4.3所示。所示。图4.3 输入过艺术字标题和绘制椭圆图形后的“主界面”工作表76将上面制作的椭圆图形再复制将上面制作的椭圆图形再复制6个，并将他们放置到适当位置，最后个，并将他们放置到适当位置，最后一个需要放大。操作时，可以借助一个需要放大。操作时，可
13、以借助“绘图绘图”工具栏上的工具栏上的“绘图绘图”|“对齐和分对齐和分布布”命令来辅助完成。命令来辅助完成。7依次右击各个椭圆，从弹出的菜单中选择依次右击各个椭圆，从弹出的菜单中选择“添加文字添加文字”，分别输入相应，分别输入相应的文字，效果如图的文字，效果如图4.4所示。所示。图4.4 在椭圆中输入文字后的界面效果88绘制椭圆的目的，就是为了将来单击椭圆，就能快速打开对应的绘制椭圆的目的，就是为了将来单击椭圆，就能快速打开对应的工作表。为此，需要为他们设置超级链接。操作步骤如下：工作表。为此，需要为他们设置超级链接。操作步骤如下：（1）选中第一个椭圆）选中第一个椭圆“计分规则计分规则”，执行
14、，执行“插入插入”|“超级链接超级链接”命令，弹命令，弹出如图出如图4.5所示的所示的“编辑超级链接编辑超级链接”对话框。对话框。图4.5“编辑超级链接”对话框说明：选中椭圆后，按组合键Ctrl+K也可以快速打开“编辑超级链接”对话框。9（2）在）在“编辑超级链接编辑超级链接”对话框中，在对话框中，在“链接到：链接到：”区域中选择区域中选择“本文档中的本文档中的位置位置”，从右侧的，从右侧的“在这篇文档中选择位置在这篇文档中选择位置”中选择链接的对象，即中选择链接的对象，即“计分规计分规则则”工作表。工作表。说明：在图4.6对话框中，在“链接到：”区域中，选择“原有文件或Web页”，可以建立到
15、本地计算机上或者因特网上的文件上的超级链接；选择“新建文档”或 “电子邮件地址”，可以分别建立到一个新建文件上或者电子邮件上的超级链接。（3）在）在“编辑超级链接编辑超级链接”对话框中，单击对话框中，单击“屏幕提示屏幕提示”按钮，将弹出按钮，将弹出“设置超设置超级链接屏幕提示级链接屏幕提示”对话框，如图对话框，如图4.6所示，在所示，在“屏幕提示文字屏幕提示文字”框中输入相应框中输入相应文字，该文字在将来光标停留在椭圆上时，会以操作时的提示文字形式自文字，该文字在将来光标停留在椭圆上时，会以操作时的提示文字形式自动出现。动出现。图4.6 “设置超级链接屏幕提示”对话框（4）按照上述方法，依次为
16、各个椭圆对象设置相应的超级链接。）按照上述方法，依次为各个椭圆对象设置相应的超级链接。9对该工作表中各个对象的位置、大小再进行适当调整，对该工作表中各个对象的位置、大小再进行适当调整，“主界面主界面”工作工作表完成。表完成。104.3“计分规则计分规则”工作表的制作工作表的制作“计分规则计分规则”工作表如图工作表如图4.7所示，在其中输入了比赛计分规则的说明文字，所示，在其中输入了比赛计分规则的说明文字，以便将来观众或选手咨询时能够快速查询。制作本工作表时，主要难点是以便将来观众或选手咨询时能够快速查询。制作本工作表时，主要难点是如何解决大块文字在如何解决大块文字在Excel中的输入问题。中的
17、输入问题。图4.7 “计分规则” 工作表11一般情况下一般情况下Excel单元格中很少输入大量文字，特别是多行文字。而对于本工作单元格中很少输入大量文字，特别是多行文字。而对于本工作表，确实现在需要这样处理。解决的方法有以下几种：表，确实现在需要这样处理。解决的方法有以下几种：1拉大单元格方法。比如将拉大单元格方法。比如将A1单元格的宽度、高度均设置为足够大，然后在其单元格的宽度、高度均设置为足够大，然后在其中输入文字，分段换行时按中输入文字，分段换行时按AltEnter（不能像（不能像Word一样直接按一样直接按Enter）2设置单元格文字自动换行。利用上述方法将单元格拉大后，选中单元格，单
18、设置单元格文字自动换行。利用上述方法将单元格拉大后，选中单元格，单击右键，选择击右键，选择“设置单元格格式设置单元格格式”，如图，如图4.8所示，从弹出的对话框中，将所示，从弹出的对话框中，将“对齐对齐”标签下的标签下的“自动换行自动换行”复选框选上，则以后再输入文字一旦到单元格右端将会自复选框选上，则以后再输入文字一旦到单元格右端将会自动换行。动换行。图4.8 设置单元格内文本自动换行3利用文本框的方法。可以在工作表上绘制一个文本框，并将其调整到适当大小，利用文本框的方法。可以在工作表上绘制一个文本框，并将其调整到适当大小，在文本框将需要的文字录入，本实例中采用的就是该方式。在文本框将需要的
19、文字录入，本实例中采用的就是该方式。124.4“选手情况选手情况”工作表的制作工作表的制作“选手情况选手情况”工作表效果如图工作表效果如图4.9所示，制作时按照图中样式进行设置即可。所示，制作时按照图中样式进行设置即可。图4.9 “选手情况” 工作表制作该工作表的目的有两个：一是可以随时查看各个选手的基本情况；制作该工作表的目的有两个：一是可以随时查看各个选手的基本情况；二是将选手的编号、姓名在该表输入之后，以后在其它工作表需要选二是将选手的编号、姓名在该表输入之后，以后在其它工作表需要选手姓名时，只要输入编号，姓名利用查表法即可确认。手姓名时，只要输入编号，姓名利用查表法即可确认。另外，本工
20、作表中另外，本工作表中“编号编号”一列的输入可以考虑利用一列的输入可以考虑利用序列填充序列填充，“性别性别”和和“唱法类型唱法类型”两列可以考虑使用选择两列可以考虑使用选择列表法输入列表法输入。134.5“歌唱得分歌唱得分”工作表的制作工作表的制作“歌唱得分歌唱得分”工作表效果如图工作表效果如图4.10所示，因为选手出场顺序是通过抽签产所示，因为选手出场顺序是通过抽签产生，以后的打分是按照出场顺序而不是按照选手编号进行，所以需要设生，以后的打分是按照出场顺序而不是按照选手编号进行，所以需要设置置“出场序号出场序号”一列（该列通过序列填充输入）。一列（该列通过序列填充输入）。图4.10 “歌唱得
21、分” 工作表14另外，本工作表只有到现场比赛时，才能输入选手编号和裁判打分，从而确另外，本工作表只有到现场比赛时，才能输入选手编号和裁判打分，从而确定选手姓名，然后计算选手分数，最后确定选手名次。所以，大家在图定选手姓名，然后计算选手分数，最后确定选手名次。所以，大家在图4.10中只看到一行数据，这只是测试数据，用来验证函数和公式的正确性。中只看到一行数据，这只是测试数据，用来验证函数和公式的正确性。在图在图4.10中，姓名、分数、名次三列中每个单元格中都有函数或公式，一旦中，姓名、分数、名次三列中每个单元格中都有函数或公式，一旦编号和裁判打分输入之后，他们都会自动出现结果。编号和裁判打分输入
22、之后，他们都会自动出现结果。下面说明该工作表的制作过程：下面说明该工作表的制作过程：1.工作表表格框架的制作按照图按照图4.10所示样式，制作所示样式，制作“歌唱得分歌唱得分”工作表的表格框架，将工作表的表格框架，将“出场序号出场序号”一一列利用序列填充方法输入完整，对整个表格并做好格式设置。列利用序列填充方法输入完整，对整个表格并做好格式设置。2.利用VLOOKUP函数根据编号确定选手姓名该表中，该表中，“选手编号选手编号”列也就是列也就是C列按照抽签顺序在比赛时录入，列按照抽签顺序在比赛时录入，“姓名姓名”一列一列可以事先设置好公式，以便将来录入选手编号后，可以自动产生，这就需要可以事先设
23、置好公式，以便将来录入选手编号后，可以自动产生，这就需要使用垂直查找函数使用垂直查找函数VLOOKUP。（1）VLOOKUP函数的使用方法和简单举例VLOOKUP函数的作用函数的作用在表格或首列查找指定的数值，并由此返回表格或数组当前行中指定列处的在表格或首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。数值。15VLOOKUP函数的格式函数的格式VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。其中：。其中：Lookup_value为需要在数据表第一列中查找的数值为需要在数据表第一列中查找的数值,可以为数可
24、以为数值、引用或文字串；值、引用或文字串；Table_array为需要在其中查找数据的数据表或数据区域；为需要在其中查找数据的数据表或数据区域；Col_index_num为为table_array中待返回的匹配值的列序号。中待返回的匹配值的列序号。Range_lookup为一逻辑值，指明函数为一逻辑值，指明函数VLOOKUP返回时是返回时是精确匹配还是近似匹配。如果为精确匹配还是近似匹配。如果为TRUE或省略，则返回近似匹或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果的最大数值；如果r
25、ange_value为为FALSE，函，函数数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值将返回精确匹配值。如果找不到，则返回错误值#N/A。16图4.11 VLOOKUP函数使用举例 VLOOKUP函数的使用举例函数的使用举例比如：如图比如：如图4.11所示，如果想根据所示，如果想根据D2的代号到的代号到A2：B7数据区域中数据区域中查找查找E2单元格中的对应名称，则单元格中的对应名称，则E2单元格中的函数形式应该为：单元格中的函数形式应该为：=VLOOKUP(D2,$A$2:$B$7,2)其中，其中，$A$2:$B$7为采用引用格式，原因是如果该单元格需要向下为采用引用格式，原
26、因是如果该单元格需要向下拖动时，应该要求该数据区域不能变化，所以公式中该数据区域要拖动时，应该要求该数据区域不能变化，所以公式中该数据区域要使用绝对引用。使用绝对引用。17（2）函数中跨工作表以及跨工作簿的单元格引用）函数中跨工作表以及跨工作簿的单元格引用在办公实践的许多情况下，有时公式中都可能要在办公实践的许多情况下，有时公式中都可能要用到另一工作表单元格中的数据，如用到另一工作表单元格中的数据，如Sheet1工作表工作表F4的公式如果为：的公式如果为： （C4 D4E4）Sheet2！B1其中其中其中其中“Sheet2“Sheet2！B1”B1”表示工作表表示工作表表示工作表表示工作表Sh
27、eet2Sheet2中的中的中的中的B1B1单单单单元格地址。这个公式表示计算当前工作表元格地址。这个公式表示计算当前工作表元格地址。这个公式表示计算当前工作表元格地址。这个公式表示计算当前工作表Sheet1Sheet1中的中的中的中的C4C4、D4D4和和和和E4E4单元格数据之和与单元格数据之和与单元格数据之和与单元格数据之和与Sheet2Sheet2工作表的工作表的工作表的工作表的B1B1单单单单元格数据的乘积，结果存入当前工作表元格数据的乘积，结果存入当前工作表元格数据的乘积，结果存入当前工作表元格数据的乘积，结果存入当前工作表Sheet1Sheet1中的中的中的中的F4F4单元格。单
28、元格。单元格。单元格。18函数中还可以进行跨工作簿的单元格引用，此时地址的一般形式为：函数中还可以进行跨工作簿的单元格引用，此时地址的一般形式为：工作簿名工作表名！单元格地址工作簿名工作表名！单元格地址综上所述，到跨工作簿、工作表的单元格地址引用的方法分别如下：综上所述，到跨工作簿、工作表的单元格地址引用的方法分别如下：在当前工作表中引用本工作表中单元格，只需输入单元格的地址在当前工作表中引用本工作表中单元格，只需输入单元格的地址即可。即可。在当前工作表中引用本工作簿中其它工作表中单元格时，需首先在当前工作表中引用本工作簿中其它工作表中单元格时，需首先输入被引用的工作表名和一个感叹号输入被引用
29、的工作表名和一个感叹号“！”，然后再输入那个工作表，然后再输入那个工作表中的单元格地址。中的单元格地址。在当前工作表中引用另外工作簿中工作表的单元格时，需要首先在当前工作表中引用另外工作簿中工作表的单元格时，需要首先输入由中括号输入由中括号“”包围的引用的工作簿名称，然后输入被引用的工包围的引用的工作簿名称，然后输入被引用的工作表名称和一个感叹号作表名称和一个感叹号“！”，最后再输入那个工作表中的单元格的，最后再输入那个工作表中的单元格的地址。地址。19公式中跨工作表和跨工作簿的单元格引用例：例： 下图中，用来计算三门课程总成绩的单元格下图中，用来计算三门课程总成绩的单元格E3中的公式为：中的
30、公式为：=B3*$G$3+C3*$G$4+D3*$G$5+离散数学离散数学!B3+计算机成绩计算机成绩.xls计算机成绩计算机成绩!$B$20（3）根据编号利用VLOOKUP查找姓名根据上面对根据上面对VLOOKUP函数的使用说明，函数的使用说明，“歌唱得分歌唱得分”工作表中用来存工作表中用来存放姓名的放姓名的D5单元格的公式应该为单元格的公式应该为:“VLOOKUP(C5,选手情况选手情况!$A$3:$B$21,2)”“歌唱得分” 工作表“选手情况” 工作表21但是，此时如果将但是，此时如果将D5向下拖动时，下面的单元格中将出现如图向下拖动时，下面的单元格中将出现如图3.13所示的所示的“#
31、N/A”的错误信息，其原因主要是因为前面对应的错误信息，其原因主要是因为前面对应“编号编号”一列还没有输入内一列还没有输入内容。容。 因为公式引用的单元格没有内容引起的“#N/A”错误22（4）利用IF函数避免 “#N/A”错误要解决上图中出现的这种要解决上图中出现的这种“#N/A”错误，可以在使用该函数之前，错误，可以在使用该函数之前，先用一个先用一个IF函数控制一下，比如可以将函数控制一下，比如可以将D5单元格的公式变为单元格的公式变为:“=IF(C5=,VLOOKUP(C5,选手情况选手情况!$A$3:$B$21,2)”。这样，当编号一列没有输入时，则姓名什么也不显示（但函数确实已这样，
32、当编号一列没有输入时，则姓名什么也不显示（但函数确实已经存在），而当编号一旦输入，姓名一列就会自动利用经存在），而当编号一旦输入，姓名一列就会自动利用VLOOKUP函函数自动查找。数自动查找。（5）函数的复制D5的函数确定好之后，通过鼠标拖动将其一直拖动到最后一行记的函数确定好之后，通过鼠标拖动将其一直拖动到最后一行记录，则确定姓名一列的公式复制完成。录，则确定姓名一列的公式复制完成。说明：本节以后我们用到的公式和函数，为了避免某些单元格没有输说明：本节以后我们用到的公式和函数，为了避免某些单元格没有输入内容引起入内容引起“#N/A”错误，都采用了这种利用错误，都采用了这种利用IF函数进行公式
33、控制的方函数进行公式控制的方法法。23分数的计算就要使用公式来进行运算。分数的计算就要使用公式来进行运算。分数的计算就要使用公式来进行运算。分数的计算就要使用公式来进行运算。公式的有关知识：公式的有关知识：公式的有关知识：公式的有关知识：公式是对工作表中的数据进行计算和分析的一种等式，公式是对工作表中的数据进行计算和分析的一种等式，它可以对工作表数值进行算术运算、关系运算和逻辑运它可以对工作表数值进行算术运算、关系运算和逻辑运算，同样也可以对字符数据以及其它数据进行运算。算，同样也可以对字符数据以及其它数据进行运算。公式可以引用同一工作表中的其它单元格、同一工作簿公式可以引用同一工作表中的其它
34、单元格、同一工作簿不同工作表中的单元格，或者其它工作簿的工作表中的不同工作表中的单元格，或者其它工作簿的工作表中的单元格。单元格。利用公式计算选手歌唱得分利用公式计算选手歌唱得分24 在进行公式输入时，应该遵循以下规则：在进行公式输入时，应该遵循以下规则：在进行公式输入时，应该遵循以下规则：在进行公式输入时，应该遵循以下规则：（1）公式输入时必须以）公式输入时必须以“=”开头，公式中间不能包括空格。开头，公式中间不能包括空格。（2）公式中的单元格引用，可以在编辑区直接输入，也可以通过单）公式中的单元格引用，可以在编辑区直接输入，也可以通过单击该单元格或拖动单元格区域从而实现自动填入。击该单元格
35、或拖动单元格区域从而实现自动填入。（3）用算术运算符号：）用算术运算符号：+（加）、（加）、（减）、（减）、*（乘）、（乘）、/（除）、（除）、（乘方），字符运算符号（乘方），字符运算符号&(连接字符串连接字符串)以及关系运算符号以及关系运算符号（大于）（大于）、（小于）、（小于）、=（等于）等来说明公式的操作类型。（等于）等来说明公式的操作类型。（4）在公式中可以使用圆括号，也只能使用圆括号，并且允许圆括）在公式中可以使用圆括号，也只能使用圆括号，并且允许圆括号嵌套，但是应该注意嵌套时前后括号的匹配关系，计算顺序是先号嵌套，但是应该注意嵌套时前后括号的匹配关系，计算顺序是先内层再外层。内层再
36、外层。25根据计分规则，图根据计分规则，图4.12“4.12“歌唱得分歌唱得分”工作表中用来存放分数的工作表中用来存放分数的Q5Q5单元格的公式应该为单元格的公式应该为: : “=IF(SUM(E5:P5)0,(“=IF(SUM(E5:P5)0,(SUM(E5:P5SUM(E5:P5)-MIN(E5:P5)- )-MIN(E5:P5)- MAX(E5:P5)/(COUNT(E5:P5)-2),)”MAX(E5:P5)/(COUNT(E5:P5)-2),)”。D5D5中的公式输入完成之后，通过鼠标拖动将其一直拖动到最后一中的公式输入完成之后，通过鼠标拖动将其一直拖动到最后一行记录，则计算选手歌唱
37、分数一列的公式复制完成。行记录，则计算选手歌唱分数一列的公式复制完成。图4.12 “歌唱得分” 工作表26RANKRANK函数是专门进行排名次的函数，用来返回一个数值在一函数是专门进行排名次的函数，用来返回一个数值在一函数是专门进行排名次的函数，用来返回一个数值在一函数是专门进行排名次的函数，用来返回一个数值在一组数值中的排位。组数值中的排位。组数值中的排位。组数值中的排位。语法格式为：语法格式为：语法格式为：语法格式为：RANK(number,ref,order)RANK(number,ref,order)，共包括三个参数，共包括三个参数，共包括三个参数，共包括三个参数，其中：其中：其中：其
38、中：NumberNumber为需要找到排位的数字；为需要找到排位的数字；为需要找到排位的数字；为需要找到排位的数字；RefRef为包含一组数字的数为包含一组数字的数为包含一组数字的数为包含一组数字的数组或引用；组或引用；组或引用；组或引用；OrderOrder为一数字，指明排位的方式，为为一数字，指明排位的方式，为为一数字，指明排位的方式，为为一数字，指明排位的方式，为00或省略，按或省略，按或省略，按或省略，按降序排列排位，不为零，按升序排列进行排位。降序排列排位，不为零，按升序排列进行排位。降序排列排位，不为零，按升序排列进行排位。降序排列排位，不为零，按升序排列进行排位。 例：下图所示例
39、：下图所示例：下图所示例：下图所示D3D3单元格中的公式就为：单元格中的公式就为：单元格中的公式就为：单元格中的公式就为： “ “RANKRANK（C3C3，$C$3$C$3：$C$12$C$12）” ”RANK函数的使用举例利用利用RANK函数确定选手名次函数确定选手名次27根据上面介绍的根据上面介绍的RANK使用方法，用来存放名次的使用方法，用来存放名次的R5单元格的公单元格的公式为：式为：“=IF(Q5,RANK(Q5,$Q$5:$Q$24),)”。R5的函数确定好之后，通过鼠标拖动将其一直拖动到最后一行记的函数确定好之后，通过鼠标拖动将其一直拖动到最后一行记录，则确定选手名次一列的公式
40、复制完成。录，则确定选手名次一列的公式复制完成。“歌唱得分” 工作表284.6“4.6“素质得分素质得分”工作表的制作工作表的制作“素质得分素质得分”工作表如图工作表如图4.144.14所示，制作方法与所示，制作方法与“歌唱得分歌唱得分”工作表类似。工作表类似。图4.14 “素质得分” 工作表其中：其中：D5单元格的公式为单元格的公式为:“=IF(C5=,VLOOKUP(C5,选手情况选手情况!$A$3:$B$21,2)”；另外，根据计分规则，用来存放分数的另外，根据计分规则，用来存放分数的G5单元格的公式为单元格的公式为:“=IF(SUM(E5:F5)=0,SUM(E5:F5)”；用来存放名
41、次的用来存放名次的H5单元格的公式为单元格的公式为:“=IF(G5,RANK(G5,$G$5:$G$24),)”。294.7“4.7“声乐得分声乐得分”工作表的制作工作表的制作 “ “声乐得分声乐得分”工作表如图工作表如图4.154.15所示，制作方法与所示，制作方法与“歌唱得歌唱得分分”工作表类似，不同之处就是多了两列：工作表类似，不同之处就是多了两列：“类别类别”和和“系系数数”，他们用来确定难度系数，最终的分数结果还需要在歌，他们用来确定难度系数，最终的分数结果还需要在歌唱得分方法的结果之后再乘上难度系数。唱得分方法的结果之后再乘上难度系数。30图4.15 “声乐得分” 工作表其中：其中
42、：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),)”。314.8“4.8“综合得分综合得分”工作表的制作工作表的制作“综合得分综合得分”工作表效果如图工作
43、表效果如图4.164.16所示，制作中要多次采用所示，制作中要多次采用跨工作表引用单元格数据跨工作表引用单元格数据图4.16 工作表“综合得分” 工作表32D5D5：“=IF(C5=”“,”“,VLOOKUP(C5,“=IF(C5=”“,”“,VLOOKUP(C5,选手情况选手情况!$A$3:$B$21,2)”!$A$3:$B$21,2)”；E5E5：“=“=歌唱得分歌唱得分!Q5”!Q5”；F5F5：“=“=素质得分素质得分!G5”!G5”；G5G5：“=“=声乐得分声乐得分!S5”!S5”；H5H5：“=IF(SUM(E5:G5)=0,SUM(E5:G5)”“=IF(SUM(E5:G5)=
44、0,SUM(E5:G5)”；I5I5：“=IF(H5,RANK(H5,$H$5:$H$24),)”=IF(H5,RANK(H5,$H$5:$H$24),)”；J5J5：“=IF(I5=1,“=IF(I5=1,一等奖一等奖,IF(I5=4,IF(I5=4,二等奖二等奖,IF(I5=9,IF(I5=9,三等奖三等奖,优秀奖优秀奖)”)”第第5行中几个主要含公式和函数的单元格中，公式和函数形式分别如下所示：行中几个主要含公式和函数的单元格中，公式和函数形式分别如下所示：334.9“4.9“评奖结果评奖结果”工作表的制作工作表的制作“评奖结果评奖结果”工作表效果如图工作表效果如图4.174.17所示，
45、表格中空白单元格，根所示，表格中空白单元格，根据比赛的综合成绩，将对应人员的名单输入或者通过复制得据比赛的综合成绩，将对应人员的名单输入或者通过复制得到即可。到即可。图4.17 “评奖结果” 工作表344.10 Excel4.10 Excel中公式出错的处理中公式出错的处理 在在ExcelExcel中输入计算公式或函数后，经常会因为某些错误，中输入计算公式或函数后，经常会因为某些错误，在单元格中显示错误信息，使一些初学者手足无措。现将在单元格中显示错误信息，使一些初学者手足无措。现将ExcelExcel中最中最常见的一些错误信息，以及可能发生的原因和解决方法列出如下，常见的一些错误信息，以及可
46、能发生的原因和解决方法列出如下，供大家参考。供大家参考。35错误类型错误原因处理方法(1)#输入到单元格中的数值太长或公式产生的结果太长，单元格容纳不下。适当增加列的宽度(2)#div/0！ 当公式被零除时，将产生错误值#div/0！修改单元格引用，或者在用作除数的单元格中输入不为零的值。(3)#N/A当在函数或公式中没有可用的数值时，将产生错误值#N/A。如果工作表中某些单元格暂时没有数值，在其中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。(4)#NAME？在公式中使用了Microsoft Excel不能识别的文本。如所需的名称没有被列出，添加相应的名称。如名称存在拼写错误，修错误。(5)#NULL！ 当试图为两个并不相交的区域指定交叉点时，将产生以上错误。如果要引用两个不相交的区域，使用合并运算符。(6)#NUM！ 当公式或函数中某些数字有问题时，将产生该错误信息。检查数字是否超出限定区域，确认函数中使用的参数类型是否正确。(7)#REF！ 当单元格引用无效时，将产生该错误信息。更改公式；在删除或粘贴单元格之后，立即单击“撤消”按钮以恢复单元格。(8)#VALUE！使用错误参数或运算对象类型，或者自动更改公式功能不能更正公式。确认公式或函数所需的参数或运算符是否正确，并确认公式引用的单元格均有效。36结束！结束！
