办公自动化与运用第04章

1、第 4 章办公中的数据分析与处理办公自动化实用技术电子教案1水利水电出版社【本章内容容介绍】在办公实实践中，经常需需要对大大量的数数据进行行分析和和处理。如职工工工资表表中工资资的计算算、汇总总、分析析，公司司销售表表中销售售额的统统计、汇汇总、合合并计算算以及人人事考勤勤表中职职工请假假、旷工工、出勤勤等情况况的分析析统计等等。本章采用用实例形形式，以以Excel软软件操作作为例介介绍办公公实践中中常见的的数据分分析与处处理方法法，包括括数据的的排序、筛选、分类汇汇总、公公式与函函数运用用以及数数据关联联表格的的操作等等。办公自自动化实实用技术术电子子教案2水利水电电出版社社【本章主要要知识

2、点点】Excel数据据库表格格的基本本要求Excel表格格中内容容填充的的常用技技巧Excel数据据库表格格的特殊殊格式设设置Excel表格格中数据据有效性性的设置置Excel数据据库表格格中的排排序Excel数据据库表格格中的筛筛选Excel数据据库表格格的分类类汇总Excel表格格中公式式和函数数的使用用Excel中的的关联表表格操作作Excel公式式使用中中的常见见错误信信息办公自自动化实实用技术术电子子教案3水利水电电出版社社4.1Excel数据分分析与处处理概述述在办公业业务中，除了文文字和表表格之外外，还需需要经常常进行有有关数据据处理，有时候候还需要要将处理理的数据据结果用用一种

3、图图表来进进行表现现。1Excel中的数数据库表表格简介介所谓数据据库就是是与特定定主题和和目标相相联系的的信息集集合。在Excel中中，可以以通过首首先创建建数据库库表格，然后再再利用其其提供的的功能菜菜单进行行数据的的分析与与处理。如图4-1示，Excel中中数据库库实际上上就是工工作表中中的一个个区域，是一个个二维表表。办公自自动化实实用技术术电子子教案4水利水电电出版社社图4-1Excel中的数据据库表格格样式说明：数数据库表表格最好好单独占占据一个个工作表表，并且且不能跟跟其他内内容（包包括数据据库标题题）直接接相连，至少需需要一个个空行或或空列隔隔开，如如图4-1中，第2行作为一一

4、个空行行，将数数据库表表格与其其标题进进行了隔隔离。同同时，也也不要使使用空白白行将列列标志和和第一条条记录分分开。办公自自动化实实用技术术电子子教案5水利水电电出版社社2Excel中的数据据分析与与处理Excel中提供了了很多种种数据分分析和数数据处理理功能，包括数数据排序序、数据据筛选、分类汇汇总、数数据透视视、合并并计算、关联表表格处理理等。本章将对对上一章章的实例例进一步步进行介介绍，包包括对报报名汇总总表进行行数据分分析，例例如：按按照某个个主题排排序、按按照指定定条件筛筛选、按按照指定定字段进进行分类类汇总、对整个个表格进进行数据据透视分分析等；同时，本章还还将利用用Excel中的

5、公式式和函数数进行决决赛阶段段成绩的的计算，并将几几项不同同的比赛赛成绩进进行关联联表格的的综合处处理等。3Excel中的数据据图表制制作利用Excel，可以轻轻松、快快捷地制制作各种种数据图图表。这这些数据据图表可可将枯燥燥的数字字形象化化，并且且通过数数据图表表可以了了解到数数据之间间的相互互关系和和变化趋趋势。数数据图表表的类型型很多，在办公公实践中中，使用用较多的的主要有有柱形图图、条形形图、折折线图、饼图、散点图图等。（1）柱形图图用于显示示某一段段时间内内数据的的变化，或比较较各数据据项之间间的差异异。分类类在水平平方向组组织，而而数值在在垂直方方向组织织，以强强调相对对于时间间的

6、变化化。其中中堆积柱柱形图显显示了单单个数据据项与整整体的关关系，三三维透视视的柱形形图可比比较两个个坐标轴轴上的数数据点。办公自自动化实实用技术术电子子教案6水利水电电出版社社（2）条形图图用于显示示各数据据之间的的比较。分类在在垂直方方向，而而数值在在水平方方向，以以使观察察者的注注意力集集中在数数值的比比较上，而不在在时间上上。堆积积条形图图显示单单个数据据与整体体的关系系。（3）折线图图用于显示示各数据据之间的的变化趋趋势。分分类在水水平方向向组织，而数值值在垂直直方向组组织，以以强调相相对于时时间的变变化。（4）饼图用于显示示组成数数据系列列的各数数据项与与数据项项总和的的比例。当只

7、有有一个数数据系列列，并且且用于强强调整体体中的某某一重要要元素时时，饼状状图十分分有效。如果要要使小扇扇区更容容易查看看，可将将这些小小扇区组组织为饼饼状图中中的一个个数据项项，然后后将该数数据项在在主图表表旁边的的小饼状状图或小小条形图图中拆分分显示。（5）XY散点图既可用于于显示若若干数据据系列中中的数字字值的关关系，也也可将两两组数字字分别当当作单个个系列的的x坐标和y坐标进行行绘制，常用于于科技数数据处理理。制作数据据图表时时，图表表类型的的选取最最好与源源数据表表内容以以及制作作目的相相结合，对于不不同的数数据表，一定要要选择最最适合的的图表类类型，这这样才能能使表现现的数据据更生

8、动动、形象象。办公自自动化实实用技术术电子子教案7水利水电电出版社社4.2“报名信息息汇总表表”数据据库表格格的建立立上一章我我们介绍绍了Excel中空白报报名汇总总表的制制作。本本节主要要介绍在在Excel中如何制制作该表表格，效效果如图图4-2样式所示示（版面面所限，只是部部分效果果）。图4-2“报名信息息汇总表表”数据据库表格格样式办公自自动化实实用技术术电子子教案8水利水电电出版社社4.2.1建立“报报名汇总总表”数数据库框框架将上一章章制作好好的汇总总表框架架打开，按照数数据库表表格的要要求进行行必要的的内容编编辑和格格式设置置，并调调整好显显示方式式，如图图4-3所示，等等待进行行

9、各列数数据的输输入。图4-3空白的“报名信信息汇总总表”表表格办公自自动化实实用技术术电子子教案9水利水电电出版社社4.2.2“编号”列列的自动动序列填填充对于“编编号”列列，因为为输入的的数据是是有一定定规律的的，输入入时可以以考虑使使用自动动序列的的填充方方法。操操作时，只要在在图4-3所示表格格的A3、A4分别输入入1和2，然后选选取这两两个单元元格，再再拖动右右下角的的拖动柄柄到需要要的位置置即可。4.2.3定义单元元格数据据的有效效性在创建Excel数据库的的过程中中，有些些单元格格中输入入的数据据没有限限制，而而有些单单元格中中输入的的数据具具有有效效范围。例如：图4-3的报名汇汇

10、总表中中，“性性别”列列只能输输入“男男”、“女”之之一；“年龄”列按照照报名规规定只能能输入1635之间的数数字；而而“唱法法类型”列只能能输入“通俗”、“民民族”、“美声声”中的的一种。为了保证证数据库库中输入入的数据据都在其其有效范范围内，并且设设置用户户选择对对应单元元格时能能够提供供提示信信息，错错误时可可以给出出错误提提示，可可以考虑虑使用Excel提供的“有效性性”来为为单元格格设置条条件。下下面仅以以设置“唱法类类型”一一列的数数据有效效性为例例，说明明设置数数据有效效性的操操作方法法。操作作步骤如如下：办公自自动化实实用技术术电子子教案10水利水电电出版社社（1）选定需需要设

11、置置数据有有效性的的单元格格区域E3：E62（根据实实际报名名人数，后者中中行号可可以适当当扩大，作为教教学用例例，此处处假设报报名人数数只有60人，下面面一样处处理）。（2）选择“数据”|“有效性”命令，打开图图4-4所示的“数据有有效性”对话框框。图4-4“数据有效效性”对对话框办公自自动化实实用技术术电子子教案11水利水电电出版社社（3）选择“设置”选项卡卡，在“允许”下拉列列表中，选择“序列”项。（4）在随之之出现“来源”文本框框中输入入“通俗俗，民族族，美声声”自定定义序列列。注意意：序列列中各项项中间的的逗号必必须为英英文状态态符号。（5）根据需需要，可可以对其其余选项项卡进行行适

12、当设设置。说明：数数据有效效性设置置之前，必须首首先选取取所需单单元格或或区域；另外，数据有有效性应应该在输输入之前前设置，否则不不会自动动起作用用。数据有效效性设置置之后，单击“唱法类类型”列列中单元元格，则则会出现现如图4-5所示的列列表框，从而可可以实现现快速的的选取录录入。图4-5“唱法类型型”字段段数据有有效性的的设置效效果办公自自动化实实用技术术电子子教案12水利水电电出版社社办公实际际使用的的数据库库中字段段数往往往很多，如果用用滚屏的的方法来来处理数数据很不不方便。Excel为此专门门提供了了“记录录单”功功能，选选择“数数据”|“记录单”命令，系统即即可弹出出如图4-6所示对

13、话话框，它它显示数数据记录录的所有有字段，提供了了增加、修改、删除等等功能。当数据据库很大大时，记记录单将将会显示示出很大大的优势势，利用用它可以以简捷、精确地地输入记记录。图4-6Excel中“记录录单”样样式办公自自动化实实用技术术电子子教案13水利水电电出版社社4.3对“报名名信息汇汇总表”进行数数据分析析本节介绍绍利用Excel中的有关关功能，对“报报名信息息汇总表表”进行行数据分分析的操操作方法法，主要要包括数数据的排排序、筛筛选、分分类汇总总与数据据透视等等功能。4.3.1对“报名名信息汇汇总表”内容进进行排序序在报名汇汇总表中中，各个个参赛选选手的资资料一般般是按照照其报名名的先

14、后后顺序输输入的。为了提提高查找找效率，有时需需要对输输入的数数据重新新进行整整理，对对此有效效的方法法就是排排序。Excel可以根据据数据库库中的内内容对数数据记录录进行排排序。排排序时，Excel将利用指指定的顺顺序重新新排列行行、列或或各单元元格。可可以根据据一列或或多列的的内容按按升序或或降序对对数据库库排序。1排序的的依据在按升序序排序时时，Excel使用如下下顺序：（1）数字从从最小的的负数到到最大的的正数排排序；（2）文本以以及包含含数字的的文本，按下列列顺序排排序： 办公自自动化实实用技术术电子子教案14水利水电电出版社社0 1 2 3 4 5 6 7 8 9 - (空格) !

15、 # $ % & ( ) * , . / : ; ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z .（3）在逻辑辑值中，FALSE排在TRUE之前；（4）所有错错误值的的优先级级等效；（5）空格总总是排在在最后；（6）汉字的的排序可可以按笔笔画，也也可按字字典顺序序排序，这可以以通过有有关关操作设设置。2按单个个字段排排序按单个字字段排序序就是只只根据某某一个字字段的内内容进行行排序。操作方法法为：将将光标置置于待排排序列中中；单击击工具栏栏上“升升序”或或“降序序”按钮钮即可。说明：按按数据库库某一列列数据排排序时，只需

16、单单击列中中任一单单元格而而不用全全选该列列。办公自自动化实实用技术术电子子教案15水利水电电出版社社3按多级级字段排排序按多级字字段排序序就是根根据两列列或三列列的内容容对数据据库排序序，它主主要适用用于第一一或第二二排序字字段出现现相同内内容时，为了排排序合理理再加上上第二或或第三排排序字段段。操作步骤骤如下：（1）将光标标定位在在待排序序数据库库的任一一单元格格中，之之后选择择“数据据”|“排序”命命令，打打开如图图4-7所示对话话框。（2）在“主主要关键键字”、“次要要关键字字”和“第三关关键字”下拉列列表框中中，分别别选择需需要排序序的字段段，并且且设置“递增”或者“递减”即可，本例

17、不不太需要要该设置置。（3）选定所所需的其其他排序序选项，然后单单击“确确定”按按钮。说明：按按数据库库多列数数据排序序时，只只需单击击数据库库中任一一单元格格而不用用全选整整个数据据库表格格，否则则会引起起数据的的混乱。4特殊排排序方式式除上述基基本排序序功能外外，Excel还提供了了一些特特殊的排排序功能能。它们们的操作作方法为为：（1）将光标标放在所所要排序序的数据据区域中中的任一一位置；（2）选择“数据”菜单中中的“排排序”项项；在打打开的对对话框中中选择“选项”按钮；办公自自动化实实用技术术电子子教案16水利水电电出版社社（3）在打开开如图4-8所示的“选项”对话框框中，分分别设置置

18、：自定定义排序序次序、排序方方向（按按行或按按列排序序）、排排序方法法（按字字母或按按笔画排排序）（4）按“确确定”按按钮完成成操作。图4-7数据库按按照多字字段排序序图图4-8其他排序序选项说明：在在“排序序选项”对话框框中，若若不选择择“方向向”和“方法”，系统统默认的的排序方方向为“按列排排序”，默认的的排序方方式为“字母排排序”。如选择择“笔画画排序”方法，就可实实现在开开会代表表名单、教材编编写人员员名单中中经常看看到的按按姓氏笔笔画排序序效果。办公自自动化实实用技术术电子子教案17水利水电电出版社社4.3.2对“报名名信息汇汇总表”内容进进行筛选选报名信息息汇总表表制作好好之后，有

19、时还还需要根根据指定定条件从从众多数数据中筛筛选特定定的记录录，比如如：筛选选职业是是“教师师”的报报名人员员，筛选选年龄在在30以上的人人员等等等。Excel中提供了了两种筛筛选方法法：“自自动筛选选”和“高级筛筛选”，它们可可以将那那些符合合条件的的记录显显示在工工作表中中，而将将其他不不满足条条件的记记录从视视图中隐隐藏起来来；或者者将筛选选出来的的记录送送到指定定位置存存放，而而原数据据不动。1自动筛筛选利用自动动筛选，可以很很方便地地从报名名汇总表表中筛选选出职业业是“教教师”的的报名人人员信息息。下面面以此为为例，说说明自动动筛选的的操作。操作步步骤如下下：（1）将鼠标标定位到到需

20、要筛筛选的数数据库中中任一单单元格。（2）选择“数据”|“筛选”|“自动筛选选”，使使“自动动筛选”项为选选中状态态（打上上对号），这时时在每个个字段名名旁出现现筛选器器箭头，如图4-9所示。（3）本例要要筛选出出来职业业是“教教师”的的报名人人员的情情况，需需要单击击“职业业”字段段名旁的的筛选器器箭头，从弹出出的菜单单中选择择“教师师”，然然后单击击鼠标左左键即可可。办公自自动化实实用技术术电子子教案18水利水电电出版社社图4-9使用自动动筛选器器筛选记记录说明：在在该菜单单中，选选择“升升序排列列”和“降序排排列”，则筛选选结果将将按照指指定顺序序排列；选择具具体学历历名称，将只显显示对

21、应应学历的的记录；单击“全部”显示所所有记录录；单击击“前10个”可以以显示最最高或最最低的一一些（默默认10个，可以以自行设设置）记记录。（4）在图4-9中，如果果下拉列列表太长长，不容容易找到到“教师师”项时时，还可可以选择择“自定定义”项，从出出现图4-10所示的对对话框中中，按照照图中样样式设置置本例的的筛选条条件。办公自自动化实实用技术术电子子教案19水利水电电出版社社图4-10“自定义自自动筛选选方式”对话框框说明：图图4-10中，运算算符除了了图中所所示外,还包括各各种其他他的数学学关系运运算,以及“始始于”、“止于于”、“包含”、“并并非起始始于”、“并非非结束于于”、“不包含

22、含”等字字符关系系运算。图中“或”表表示两个个条件中中一个成成立即可可，而“与”要要求两个个同时成成立。（5）按“确确定”按按钮，完完成操作作，筛选选结果如如图4-11所示。图4-11筛选出来来的职业业为教师师的选手手情况办公自自动化实实用技术术电子子教案20水利水电电出版社社说明：取取消数据据筛选的的操作方方法：如要取消消对某一一列的筛筛选，单单击该列列筛选器器箭头，然后再再单击“全部”。如要取消消对所有有列的筛筛选，可可选择“数据”|“筛选”|“全部显示示”命令令。如要撤消消数据清清单中的的筛选箭箭头，可可选择“数据”|“筛选”|“自动筛选选”命令令。2高级筛筛选从上面的的讲解可可以看到到

23、，自动动筛选可可以实现现同一字字段之间间的“与与”运算算和“或或”运算算，通过过多次进进行自动动筛选也也可以进进行不同同字段之之间的“与”运运算，但但是它无无法实现现多个字字段之间间的“或或”运算算。这时时就需要要使用高高级筛选选。需要说明明的是：对于数数据清单单，如果果要进行行高级筛筛选，必必须首先先设置筛筛选条件件区域。为此，必须在在该数据据清单上上方留出出若干空空行，以以便作为为条件区区域。另外，为为了使读读者可以以更好地地理解高高级筛选选，首先先列出一一个表格格，如表表4-1所示，该该表用来来对高级级筛选条条件区域域的逻辑辑关系进进行定义义。办公自自动化实实用技术术电子子教案21水利水

24、电电出版社社（a）筛选字段A中符合A1条件或A2条件的所有记录（b）筛选字段A中符合A1条件并且字段B中符合B1条件的所有记录（c）筛选字段A中符合A1条件或字段B中符合B2条件的所有记录（d）筛选字段A中符合A1条件且字段B中符合B1条件以及字段A中符合A2条件且字段B中符合B2条件的所有记录ABA1A2ABA1B1ABA1B2ABA1B1A2B2表4-1高高级筛选选条件区区域设置置的几种种逻辑关关系下面以筛筛选出来来职业为为“教师师”、唱唱法类型型为“美美声”或或者“民民族”的的报名选选手情况况操作为为例，说说明高级级筛选的的使用方方法。操操作步骤骤如下：（1）在“报报名信息息汇总表表”表

25、格格的上方方插入几几个空行行，根据据本例筛筛选的实实际需要要以及表表4-1介绍的逻逻辑关系系，建立立高级筛筛选的条条件区域域，如图图4-12所示A1:B3区域。办公自自动化实实用技术术电子子教案22水利水电电出版社社图4-12设置高级级筛选的的“条件件区域”（2）单击“报名信信息汇总总表”数数据库表表格中的的任一单单元格。（3）选择“数据”|“筛选”|“高级筛选选”命令令，出现现“高级级筛选”对话框框。（4）如图4-13所示，在在 “方方式”选选项区中中，单击击选中“将筛选选结果复复制到其其他位置置”按钮钮；在“列表区区域”框框中，软软件自动动输入了了要筛选选的数据据区域“$A$6:$H$66

26、”，也就是是整个数数据清单单区域；在“条件区区域”框框中，输输入设置置好的包包含筛选选条件的的区域“$A$1:$B$3”（可直接接在该文文本框中中输入区区域引用用，也可可用鼠标标在工作作表中选选定条件件区域）；在“复制到到”文本本框中输输入时，首先将将光标在在其中定定位，然然后用鼠鼠标在数数据清单单下方指指定一个个单元格格（本例例为“$A$71”），该单单元格将将作为放放置筛选选结果区区域左上上角的位位置，筛筛选结果果将在它它的下方方和右方方进行排排列。办公自自动化实实用技术术电子子教案23水利水电电出版社社（5）单击“确定”按钮，高级筛筛选结果果如图4-14所示（原原来数据据我们选选择的“隐

27、藏”操作，以便大大家能够够看到筛筛选出来来的效果果）。图4-13“高级筛选选”对话话框以及及输入内内容设置置图4-14筛选出职职业为“教师”、唱法法类型为为“美声声”或“民族”的选手手情况办公自自动化实实用技术术电子子教案24水利水电电出版社社3高级筛筛选时的的注意事事项使用高级级筛选时时，需要要注意以以下几个个问题：（1）高级筛筛选必须须指定一一个条件件区域，它可以以与数据据库表格格在一张张工作表表上，但但是必必须与数数据库之之间有空空白行隔隔开；条条件区域域也可以以与数据据库表格格不在一一张工作作表上。（2）条件区区域中的的字段名名必须与与数据库库中的完完全一样样，最好好通过复复制得到到。

28、（3）如果“条件区区域”与与数据库库表格在在一张工工作表上上，在筛筛选之前前，最好好把光标标放置到到数据库库中某一一单元格格上，这这样数据据区域就就会自动动填上数数据库所所在位置置，省去去再次鼠鼠标选择择或者重重新输入入的麻烦烦。（4）执行“将筛选选结果复复制到其其他位置置”时，在“复复制到”文本框框中输入入或选取取将来要要放置位位置的左左上角单单元格即即可，不不要指定定某区域域（因为为事先无无法确定定筛选结结果）。（5）根据需需要，条条件区域域可以定定义多个个条件，以便用用来筛选选符合多多个条件件的记录录。这这些条件件可以输输入到条条件区域域的同一一行上，也可以以输入到到不同行行上。但但是必

29、须须记住：两个字字段名下下面的同同一行中中的各个个条件之之间为“与”的的关系，也就是是条件必必须同时时成立才才算符合合条件；两个字字段名下下面的不不同行中中的各个个条件之之间为“或”的的关系，也就是是条件只只要有一一个成立立就算符符合条件件。办公自自动化实实用技术术电子子教案25水利水电电出版社社4.3.3对“报名名信息汇汇总表”中数据据分类汇汇总使用分类类汇总，能够在在数据库库适当位位置加上上统计结结果，使使数据库库变得清清晰易懂懂。对于本例例，可以以对“唱法类类型”进进行分类类汇总，以便了了解唱法法类型的的报名人人数。在执行分分类汇总总命令之之前，首首先应该该对数据据库按照照待汇总总字段进

30、进行排序序。下面以按按照“唱唱法类型型”分类类汇总为为例来说说明分类类汇总的的操作。操作步步骤如下下：（1）对需要要分类汇汇总的字字段进行行排序，从而使使相同的的记录集集中。本本例将光光标定位位到“唱唱法类型型”列中中某一单单元格，然后运运行工具具栏上“排序”按钮即即可。（2）选定数数据库中中任意一一个单元元格。（3）选择“数据”|“分类汇总总”命令令，出现现图4-15所示的“分类汇汇总”对对话框。图4-15“分类汇总总”对话话框办公自自动化实实用技术术电子子教案26水利水电电出版社社（4）单击“分类字字段”下下拉列表表按钮，选择“唱法类类型”作作为分类类汇总的的字段。（5）在“汇汇总方式式”

31、下拉拉列表中中，选择择需要的的统计函函数。分分类汇总总可以支支持求和和、平均均数、最最大、最最小、计计数、乘乘积等共共计11种函数。本例需需要选择择“计数数”。（6）在“选选定汇总总项”列列表中，选中需需要对其其汇总计计算的字字段前面面的复选选框。本本例中选选上“姓姓名”复复选框即即可。（7）可以根根据需要要选中相相应的复复选框，指定汇汇总结果果的显示示位置。（8）单击“确定”按钮，可以得得到分类类汇总结结果。如如图4-16所示。图4-16分类汇总总的结果果显示办公自自动化实实用技术术电子子教案27水利水电电出版社社从图4-16可以看出出，在显显示分类类汇总结结果的同同时，分分类汇总总的左侧侧

32、自动显显示一些些分级显显示按钮钮。其中中：单击击左侧的的“+”形状按钮钮图标和和“-”形状按钮钮图标分分别可以以展开和和隐藏细细节数据据；“1”、“2”、“3”形状按钮钮表示显显示数据据的层次次，“1”只显示总总计数据据，“2”显示部分分数据以以及汇总总结果，“3”显示所有有数据；“|”形状为级级别条，用来指指示属于于某一级级别的细细节行或或列的范范围。图4-17所示就是是将分类类汇总后后细节数数据隐藏藏后的显显示效果果。图4-17分类汇总总后部分分细节数数据隐藏藏的显示示效果说明：分分类汇总总效果可可以清除除，操作作时，先先打开如如图4-15所示的对对话框，然后单单击“全全部删除除”即可可；

33、但是是为了保保险，在在汇总之之前最好好进行数数据库备备份。办公自自动化实实用技术术电子子教案28水利水电电出版社社4.4利用Excel的关联表表格制作作歌唱比比赛计分分表在办公实实践中的的表格制制作时，有些数数据的存存放和计计算需要要使用多多个工作作表，并并且相互互之间需需要相互互引用和和参照。这就需需要使用用Excel的关联表表格操作作来实现现数据的的处理。某市举办办的“青青年歌手手大奖赛赛”，经经过初赛赛、复赛赛，目前前已经进进入决赛赛。在决赛阶阶段，为为了体现现公平、公正、科学、合理的的比赛原原则，组组委会拟拟定了如如下的比比赛计分分规则。每一个参参加决赛赛选手的的得分满满分为100分，

34、包括括以下三三大部分分：1、歌唱得得分：每每一位参参赛选手手自行选选择一首首歌曲演演唱，满满分90分，12个裁判分分别打分分，总分分减去最最高分和和最低分分之后的的平均分分为该项项分数。2、素质得得分：两两个题目目，每题题0.5分，共1分，各由由一个评评委评分分。3、声乐得得分：选选手自己己从指定定歌曲中中选择一一首歌曲曲进行声声乐表演演，歌曲曲有不同同的难度度系数，分A、B、C三个级别别，满分分9分，12个裁判打打分，方方法同上上，但是是在得到到的平均均分数的的基础上上再乘上上难度系系数（A为1，B为0.8，C为0.6），才能能得到该该项分数数。以上三项项分数之之和为该该选手的的总得分分，按

35、照照该成绩绩的名次次确定最最终的获获奖等级级。办公自自动化实实用技术术电子子教案29水利水电电出版社社参加决赛赛的选手手共20人，比赛赛前通过过抽签确确定出场场顺序，比赛结结束需要要确定综综合成绩绩一等奖奖1人，二等等奖3人，三等等奖5人，其余余为优秀秀奖；同同时综合合成绩最最好的3名将推荐荐到省里里参加全全省比赛赛；另外外，为了了比较单单项成绩绩，还需需要将单单项成绩绩的前6名评出。本节目标标就是制制作一套套用来计计算选手手分数的的表格，以便在在现场快快速计算算选手成成绩。问题分析析和操作作提示从上面的的规则可可以看到到，需要要进行几几个单项项成绩的的计算和和排名，同时需需要进行行几项成成绩

36、之和和作为综综合分的的计算和和排名，所以可可以考虑虑利用Excel的关联表表格操作作。所谓关联联表格，是指在在一个或或者多个个工作簿簿中有一一定关联联关系的的工作表表的总称称。本实例制制作时，还需要要注意以以下几个个问题：1各个表表格中要要尽量减减少现场场数据录录入和计计算的工工作量。2可以在在比赛中中间随时时查看比比赛过的的选手的的各个单单项成绩绩，确定定对应的的暂时名名次。3单项成成绩和综综合成绩绩的计算算，需使使用公式式和函数数实现，并且公公式要具具有容错错性。办公自自动化实实用技术术电子子教案30水利水电电出版社社4综合分分数排名名为最后后名次，根据该该名次利利用公式式确定获获奖等级级

37、。5多个工工作表之之间进行行关联操操作时，注意数数据要能能够随时时实时变变化。6为了使使各个表表格之间间切换方方便，可可以制作作一个主主界面工工作表，在上面面利用有有关图形形建立超超级链接接，以便便可以快快速切换换到对应应的工作作表。按照以上上的分析析，本问问题的解解决可以以通过制制作8张关联的的工作表表来实现现，分别别用来作作为“主主界面”工作表表、“计分规规则”工工作表、“选手手情况” 工作作表、“歌唱得得分”工工作表表、“素素质得分分”工工作表、“声乐乐得分” 工作作表、“综合得得分”工工作表表以及“评奖结结果”工工作表表。下面面就介绍绍这些工工作表的的格式制制作和公公式设计计。4.4.

38、1工作表的的添加和和更名从上面的的的分析析可知，该计分分系统需需要设置置8个工作表表，而Excel默认的工工作表个个数为3个。所以以，本实实例操作作时，首首先需要要工作表表个数的的添加以以及名称称的更改改。操作步骤骤如下：1启动Excel，屏幕上上有默认认的Sheet1、Sheet2、Sheet3三个工作作表。办公自自动化实实用技术术电子子教案31水利水电电出版社社2在工作作表标签签上，单单击选择择Sheet1，然后单单击鼠标标右键，从弹出出的快捷捷菜单中中选择“插入”，根据据随后出出现的系系统提示示操作，添加了了一个新新工作表表Sheet4。说明：上上述方法法一次只只能添加加一个工工作表，如

39、果想想添加多多个工作作表，请请先选取取多个工工作表，然后再再单击右右键，选选择“插插入”，则会快快速添加加与选取取个数一一样多的的工作表表。3根据上上面的说说明，借借助于Shift键选择全全部四个个工作表表，单击击鼠标右右键，选选择“插插入”，则快速速添加了了四个工工作表Sheet 5、Sheet6、Sheet7、Sheet8。4分别双双击工作作表名称称，将8个工作表表的名称称依次更更该为“主界面面”、“计分规规则”、“选手手情况”、“歌歌唱得分分”、“素质得得分”、“声乐乐得分”、“综综合得分分”和“评奖结结果”。5将工作作簿文件件以“歌歌手大奖奖赛记分分”为名名存盘。4.4.2“主界面”工

40、作表表的制作作 “主界面面”工作作表效果果如图4-18所示，利利用“主主界面”工作表表，可以以快速看看到整个个记分方方案的完完整组成成，同时时根据需需要将来来可以快快速切换换到需要要的其他他工作表表。 办公自自动化实实用技术术电子子教案32水利水电电出版社社图4-18“主界面”工作表表效果该工作表表制作中中要用到到工作表表网格线线的取消消、单元元格填充充颜色设设置、艺艺术字标标题设置置、椭圆圆形按钮钮绘制以以及超级级链接建建立等知知识。操作步骤骤如下：1单击工工作表标标签上的的“主界界面”，选取该该工作表表。办公自自动化实实用技术术电子子教案33水利水电电出版社社2单击击“工具具”|“选项”命

41、令，弹出图图4-19所示示的“选选项”对对话框，从中选选取“视视图”标标签，将将“网格格线”复复选框设设置为不不选中状状态，这这样就取取消了工工作表中中的网格格线。3全选选整个工工作表，然后利利用格式式工具栏栏上的按按钮设置置工作表表填充颜颜色为浅浅黄色。图4-19在Excel的“选项项”对对话框中中取消网网格线办公自自动化实实用技术术电子子教案34水利水电电出版社社4利用与与Word中输入艺艺术字类类似的方方法，输输入艺术术字标题题“青年年歌手大大奖赛计计分方案案”，并并进行适适当格式式设置。5利用“绘图”工具栏栏上的椭椭圆工具具绘制椭椭圆，并并进行填填充效果果、线条条颜色以以及图形形大小的

42、的设置。输入过过艺术字字标题和和绘制椭椭圆图形形后,“主界面”工作表表如图4-20所示。图4-20输入过艺艺术字标标题和绘绘制椭圆圆图形后后的“主主界面”工作表表办公自自动化实实用技术术电子子教案35水利水电电出版社社6将上面面制作的的椭圆图图形再复复制6个，并将将他们放放置到适适当位置置，作后后一个需需要放大大。操作作时，可可以借助助“绘图图”工具具栏上的的“绘图图”|“对齐和分分布”命命令来辅辅助完成成。7依次右右击各个个椭圆，从弹出出的菜单单中选择择“添加加文字”，分别别输入相相应的文文字，效效果如图图4-21所示。图4-21在椭圆中中输入文文字后的的界面效效果办公自自动化实实用技术术电

43、子子教案36水利水电电出版社社8绘制椭椭圆的目目的，就就是为了了将来单单击椭圆圆，就能能快速打打开对应应的工作作表。为为此，需需要为他他们设置置超级链链接。操操作步骤骤如下：（1）选中第第一个椭椭圆“计计分规则则”，执执行“插插入”|“超级链接接”命令令，弹出出如图4-22所示的“编辑超超级链接接”对话话框。图4-22“编辑超级级链接”对话框框说明：选选中椭圆圆后，按按组合键键Ctrl+K也可以快快速打开开“编辑辑超级链链接”对对话框。办公自自动化实实用技术术电子子教案37水利水电电出版社社（2）在“编编辑超级级链接”对话框框中，在在“链接接到：”区域中中选择“本文档档中的位位置”，从右侧侧的

44、“在在这篇文文档中选选择位置置”中选选择链接接的对象象，即“计分规规则”工工作表。说明：在在图4-22对话框中中，在“链接到到：”区区域中，选择“原有文文件或Web页”，可可以建立立到本地地计算机机上或者者因特网网上的文文件上的的超级链链接；选选择“新新建文档档”或“电子子邮件地地址”，可以分分别建立立到一个个新建文文件上或或者电子子邮件上上的超级级链接。（3）在“编编辑超级级链接”对话框框中，单单击“屏屏幕提示示”按钮钮，将弹弹出“设设置超级级链接屏屏幕提示示”对话话框，如如图4-23所示，在在“屏幕幕提示文文字”框框中输入入相应文文字，该该文字在在将来光光标停留留在椭圆圆上时，会以操操作时

45、的的提示文文字形式式自动出出现。图4-23 “设置超级级链接屏屏幕提示示”对话话框办公自自动化实实用技术术电子子教案38水利水电电出版社社（4）按照上上述方法法，依次次为各个个椭圆对对象设置置相应的的超级链链接。9对该工工作表中中各个对对象的位位置、大大小再进进行适当当调整，“主界界面”工工作表完完成。4.4.3“计分规则则”工作作表的制制作“计分规规则”工工作表如如图4-24所示，在在其中输输入了比比赛计分分规则的的说明文文字，以以便将来来观众或或选手咨咨询时能能够快速速查询。制作本本工作表表时，主主要难点点是如何何解决大大块文字字在Excel中的输入入问题。图4-24“计分规则则”工工作表

46、办公自自动化实实用技术术电子子教案39水利水电电出版社社一般情况况下Excel单元格中中很少输输入大量量文字，特别是是多行文文字。而而对于本本工作表表，确实实现在需需要这样样处理。解决的的方法有有以下几几种：1拉大单单元格方方法。比比如将A1单元格的的宽度、高度均均设置为为足够大大，然后后在其中中输入文文字，分分段换行行时按AltEnter（不能像像Word一样直接接按Enter）2设置单单元格文文字自动动换行。利用上上述方法法将单元元格拉大大后，选选中单元元格，单单击右键键，选择择“设置置单元格格格式”，如图图4-25所示，从从弹出的的对话框框中，将将“对齐齐”标签签下的“自动换换行”复复选

47、框选选上，则则以后再再输入文文字一旦旦到单元元格右端端将会自自动换行行。图4-25设置单元元格内文文本自动动换行办公自自动化实实用技术术电子子教案40水利水电电出版社社3利用文文本框的的方法。可以在在工作表表上绘制制一个文文本框，并将其其调整到到适当大大小，在在文本框框将需要要的文字字录入，本实例例中采用用的就是是该方式式。4.4.4“选手情况况”工作作表的制制作“选手情情况”工工作表效效果如图图4-26所示，制制作时按按照图中中样式进进行设置置即可。图4-26 “选手情况况”工工作表办公自自动化实实用技术术电子子教案41水利水电电出版社社制作该工工作表的的目的有有两个：一是可可以随时时查看各

48、各个选手手的基本本情况；二是将将选手的的编号、姓名在在该表输输入之后后，以后后在其它它工作表表需要选选手姓名名时，只只要输入入编号，姓名利利用查表表法即可可确认。另外，本本工作表表中“编编号”一一列的输输入可以以考虑利利用序列列填充，“性别别”和“唱法类类型”两两列可以以考虑使使用选择择列表法法输入。4.4.5“歌唱得分分”工作作表的制制作“歌唱得得分”工工作表效效果如图图4-27所示，因因为选手手出场顺顺序是通通过抽签签产生，以后的的打分是是按照出出场顺序序而不是是按照选选手编号号进行，所以需需要设置置“出场场序号”一列（该列通通过序列列填充输输入）。图4-27 “歌唱得分分”工工作表办公自

49、自动化实实用技术术电子子教案42水利水电电出版社社另外，本本工作表表只有到到现场比比赛时，才能输输入选手手编号和和裁判打打分，从从而确定定选手姓姓名，然然后计算算选手分分数，最最后确定定选手名名次。所所以，大大家在图图4-27中只看到到一行数数据，这这只是测测试数据据，用来来验证函函数和公公式的正正确性。在图4-27中，姓名名、分数数、名次次三列中中每个单单元格中中都有函函数或公公式，一一旦编号号和裁判判打分输输入之后后，他们们都会自自动出现现结果。下面说说明其该该工作表表的制作作过程：1.工作表表表格框架架的制作作按照图4-27所示样式式，制作作“歌唱唱得分”工作表表的表格格框架，将“出出场

50、序号号”一列列利用序序列填充充方法输输入完整整，对整整个表格格并做好好格式设设置。2.利用VLOOKUP函数根据据编号确确定选手手姓名该表中，“选手手编号”列也就就是C列按照抽抽签顺序序在比赛赛时录入入，“姓姓名”一一列可以以事先设设置好公公式，以以便将来来录入选选手编号号后，可可以自动动产生，这就需需要使用用垂直查查找函数数VLOOKUP。（1）VLOOKUP函数的使使用方法法和简单单举例VLOOKUP函数的作作用在表格或或首列查查找指定定的数值值，并由由此返回回表格或或数组当当前行中中指定列列处的数数值。办公自自动化实实用技术术电子子教案43水利水电电出版社社VLOOKUP函数的格格式VL

51、OOKUP(lookup_value,table_array,col_index_num,range_lookup)。其中：Lookup_value为需要在在数据表表第一列列中查找找的数值值,可以为数数值、引引用或文文字串；Table_array为需要在在其中查查找数据据的数据据表或数数据区域域；Col_index_num为table_array中待返回回的匹配配值的列列序号。Range_lookup为一逻辑辑值，指指明函数数VLOOKUP返回时是是精确匹匹配还是是近似匹匹配。如如果为TRUE或省略，则返回回近似匹匹配值，也就是是说，如如果找不不到精确确匹配值值，则返返回小于于lookup_v

52、alue的最大数数值；如如果range_value为FALSE，函数VLOOKUP将返回精精确匹配配值。如如果找不不到，则则返回错错误值#N/A。VLOOKUP函数的使使用举例例比如：如如图4-28所示，如如果想根根据D2的代号到到A2：B7数据区域域中查找找E2单元格中中的对应应名称，则E2单元格中中的函数数形式应应该为：=VLOOKUP(D2,$A$2:$B$7,2)其中，$A$2:$B$7为采用引引用格式式，原因因是如果果该单元元格需要要向下拖拖动时，应该要要求该数数据区域域不能变变化，所所以公式式中该数数据区域域要使用用绝对引引用。办公自自动化实实用技术术电子子教案44水利水电电出版社

53、社图4-28VLOOKUP函数使用用举例（2）函数中中跨工作作表以及及跨工作作簿的单单元格引引用在办公实实践的许许多情况况下，有有时公式式中都可可能要用用到另一一工作表表单元格格中的数数据，如如Sheet1工作表F4的公式如如果为：（C4D4E4）Sheet2！B1其中“Sheet2！B1”表示工作作表Sheet2中的B1单元格地地址。这这个公式式表示计计算当前前工作表表Sheet1中的C4、D4和E4单元格数数据之和和与Sheet2工作表的的B1单元格数数据的乘乘积，结结果存入入当前工工作表Sheet1中的F4单元格。办公自自动化实实用技术术电子子教案45水利水电电出版社社函数中还还可以进进

54、行跨工工作簿的的单元格格引用，此时地地址的一一般形式式为：工作簿簿名工工作表名名！单元元格地址址综上所述述，到跨跨工作簿簿、工作作表的单单元格地地址引用用的方法法分别如如下： 在当当前工作作表中引引用本工工作表中中单元格格，只需需输入单单元格的的地址即即可。 在当当前工作作表中引引用本工工作簿中中其它工工作表中中单元格格时，需需首先输输入被引引用的工工作表名名和一个个感叹号号“！”，然后后再输入入那个工工作表中中的单元元格地址址。 在当当前工作作表中引引用另外外工作簿簿中工作作表的单单元格时时，需要要首先输输入由中中括号“ ”包围的引引用的工工作簿名名称，然然后输入入被引用用的工作作表名称称和

55、一个个感叹号号“！”，最后后再输入入那个工工作表中中的单元元格的地地址。例如：图图4-29中，用来来计算三三门课程程总成绩绩的单元元格E3中的公式式为：=B3*$G$3+C3*$G$4+D3*$G$5+离散数学学!B3+计算机成成绩.xls计算机成成绩!$B$4请各位读读者分析析其中跨跨工作表表、工作作簿引用用的方式式以及该该公式的的意义。办公自自动化实实用技术术电子子教案46水利水电电出版社社图4-29公式中跨跨工作表表和跨工工作簿的的单元格格引用（3）根据编编号利用用VLOOKUP查找姓名名根据上面面对VLOOKUP函数的使使用说明明，图4-27“歌唱得分分”工作作表中用用来存放放姓名的的

56、D5单元格的的公式应应该为:“VLOOKUP(C5,选手情况况!$A$3:$B$21,2)”办公自自动化实实用技术术电子子教案47水利水电电出版社社但是，此此时如果果将D5向下拖动动时，下下面的单单元格中中将出现现如图4-30所示的“#N/A”的错误信信息，其其原因主主要是因因为前面面对应“编号”一列还还没有输输入内容容。图4-30因为公式式引用的的单元格格没有内内容引起起的“#N/A”错误办公自自动化实实用技术术电子子教案48水利水电电出版社社（4）利用IF函数避免免 “#N/A”错误要解决图图4-30出现的这这种“#N/A”错误，可可以在使使用该函函数之前前，先用用一个IF函数控制制一下，

57、比如可可以将D5单元格的的公式变变为:“=IF(C5=,VLOOKUP(C5,选手情况况!$A$3:$B$21,2)”。这样，当当编号一一列没有有输入时时，则姓姓名什么么也不显显示（但但函数确确实已经经存在），而当当编号一一旦输入入，姓名名一列就就会自动动利用VLOOKUP函数自动动查找。（5）函数的的复制D5的函数确确定好之之后，通通过鼠标标拖动将将其一直直拖动到到最后一一行记录录，则确确定姓名名一列的的公式复复制完成成。说明：本本节以后后我们用用到的公公式和函函数，为为了避免免某些单单元格没没有输入入内容引引起“#N/A”错误，都都采用了了这种利利用IF函数进行行公式控控制的方方法。3利用

58、公公式计算算选手歌歌唱得分分分数的计计算就需需要使用用公式来来进行运运算。下下面介绍绍公式的的有关知知识：公式是对对工作表表中的数数据进行行计算和和分析的的一种等等式，它它可以对对工作表表数值进进行算术术运算、关系运运算和逻逻辑运算算，同样样也可以以对字符符数据以以及其它它数据进进行运算算。公式可以以引用同同一工作作表中的的其它单单元格、同一工工作簿不不同工作作表中的的单元格格，或者者其它工工作簿的的工作表表中的单单元格。办公自自动化实实用技术术电子子教案49水利水电电出版社社在进行公公式输入入时，应应该遵循循以下规规则：（1）公式输输入时必必须以“=”开头，公公式中间间不能包包括空格格。（2

59、）公式中中的单元元格引用用，可以以在编辑辑区直接接输入，也可以以通过单单击该单单元格或或拖动单单元格区区域从而而实现自自动填入入。（3）用算术术运算符符号：+（加）、（减）、*（乘乘）、/（除）、（乘方），字符符运算符符号&(连接字符符串)以及关系系运算符符号（大于）、（小于）、=（等于）等来说说明公式式的操作作类型。（4）在公式式中可以以使用圆圆括号，也只能能使用圆圆括号，并且允允许圆括括号嵌套套，但是是应该注注意嵌套套时前后后括号的的匹配关关系，计计算顺序序是先内内层再外外层。（5）单元格格中的公公式内容容显示在在编辑栏栏中，计计算结果果则显示示在单元元格内，如果希希望直接接在单元元格中显

60、显示为公公式，可可以使用用Ctrl+键（在Tab键的上面面），再再次键入入，则取取消公式式显示，重新显显示结果果。根据计分分规则，图4-37“歌唱得分分”工作作表中用用来存放放分数的的Q5单元格的的公式应应该为:“=IF(SUM(E5:P5)0,(SUM(E5:P5)-MIN(E5:P5)-MAX(E5:P5)/(COUNT(E5:P5)-2),)”。D5中的公式式输入完完成之后后，通过过鼠标拖拖动将其其一直拖拖动到最最后一行行记录，则计算算选手歌歌唱分数数一列的的公式复复制完成成。办公自自动化实实用技术术电子子教案50水利水电电出版社社4利用RANK函数确定定选手名名次名次的确确认对于于本例