第04章_数据分析与处理.ppt

1、第 4 章,办公中的数据分析与处理,2,【本章内容介绍】,在办公实践中，经常需要对大量的数据进行分析和处理。如职工工资表中工资的计算、汇总、分析，公司销售表中销售额的统计、汇总、合并计算以及人事考勤表中职工请假、旷工、出勤等情况的分析统计等。本章采用实例形式，以Excel软件操作为例介绍办公实践中常见的数据分析与处理方法，包括数据的排序、筛选、分类汇总、公式与函数运用以及数据关联表格的操作等。,3,【本章主要知识点】,Excel数据库表格的基本要求 Excel表格中内容填充的常用技巧 Excel数据库表格的特殊格式设置 Excel表格中数据有效性的设置 Excel数据库表格中的排序 Excel

2、数据库表格中的筛选 Excel数据库表格的分类汇总 Excel表格中公式和函数的使用 Excel中的关联表格操作 Excel公式使用中的常见错误信息,4,4.1 Excel数据分析与处理概述在办公业务中，除了文字和表格之外，还需要经常进行有关数据处理，有时候还需要将处理的数据结果用一种图表来进行表现。1Excel中的数据库表格简介所谓数据库就是与特定主题和目标相联系的信息集合。在Excel中，可以通过首先创建数据库表格，然后再利用其提供的功能菜单进行数据的分析与处理。如图4-1示，Excel中数据库实际上就是工作表中的一个区域，是一个二维表。,5,图4-1 Excel中的数据库表格样式说明：数

3、据库表格最好单独占据一个工作表，并且不能跟其他内容（包括数据库标题）直接相连，至少需要一个空行或空列隔开，如图4-1中，第2行作为一个空行，将数据库表格与其标题进行了隔离。同时，也不要使用空白行将列标志和第一条记录分开。,6,2Excel中的数据分析与处理 Excel中提供了很多种数据分析和数据处理功能，包括数据排序、数据筛选、分类汇总、数据透视、合并计算、关联表格处理等。 本章将对上一章的实例进一步进行介绍，包括对报名汇总表进行数据分析，例如：按照某个主题排序、按照指定条件筛选、按照指定字段进行分类汇总、对整个表格进行数据透视分析等；同时，本章还将利用Excel中的公式和函数进行决赛阶段成绩

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

5、直方向，而数值在水平方向，以使观察者的注意力集中在数值的比较上，而不在时间上。堆积条形图显示单个数据与整体的关系。 （3）折线图 用于显示各数据之间的变化趋势。分类在水平方向组织，而数值在垂直方向组织，以强调相对于时间的变化。 （4）饼图 用于显示组成数据系列的各数据项与数据项总和的比例。当只有一个数据系列，并且用于强调整体中的某一重要元素时，饼状图十分有效。如果要使小扇区更容易查看，可将这些小扇区组织为饼状图中的一个数据项，然后将该数据项在主图表旁边的小饼状图或小条形图中拆分显示。 （5）XY散点图 既可用于显示若干数据系列中的数字值的关系，也可将两组数字分别当作单个系列的 x 坐标和 y

6、坐标进行绘制，常用于科技数据处理。 制作数据图表时，图表类型的选取最好与源数据表内容以及制作目的相结合，对于不同的数据表，一定要选择最适合的图表类型，这样才能使表现的数据更生动、形象。,8,4.2 “报名信息汇总表”数据库表格的建立上一章我们介绍了Excel中空白报名汇总表的制作。本节主要介绍在Excel中如何制作该表格，效果如图4-2样式所示（版面所限，只是部分效果）。,图4-2 “报名信息汇总表”数据库表格样式,9,4.2.1 建立“报名汇总表”数据库框架将上一章制作好的汇总表框架打开，按照数据库表格的要求进行必要的内容编辑和格式设置，并调整好显示方式，如图4-3所示，等待进行各列数据的输

7、入。,图4-3 空白的“报名信息汇总表”表格,10,4.2.2 “编号”列的自动序列填充 对于“编号”列，因为输入的数据是有一定规律的，输入时可以考虑使用自动序列的填充方法。操作时，只要在图4-3所示表格的A3、A4分别输入1和2，然后选取这两个单元格，再拖动右下角的拖动柄到需要的位置即可。4.2.3 定义单元格数据的有效性 在创建Excel数据库的过程中，有些单元格中输入的数据没有限制，而有些单元格中输入的数据具有有效范围。例如：图4-3的报名汇总表中，“性别”列只能输入“男”、“女”之一；“年龄”列按照报名规定只能输入1635之间的数字；而“唱法类型”列只能输入“通俗”、“民族”、“美声”

8、中的一种。 为了保证数据库中输入的数据都在其有效范围内，并且设置用户选择对应单元格时能够提供提示信息，错误时可以给出错误提示，可以考虑使用Excel提供的“有效性”来为单元格设置条件。下面仅以设置“唱法类型”一列的数据有效性为例，说明设置数据有效性的操作方法。操作步骤如下：,11,（1）选定需要设置数据有效性的单元格区域E3：E62（根据实际报名人数，后者中行号可以适当扩大，作为教学用例，此处假设报名人数只有60人，下面一样处理）。 （2）选择“数据”|“有效性”命令，打开图4-4所示的“数据有效性”对话框。,图4-4 “数据有效性”对话框,12,（3）选择“设置”选项卡，在“允许”下拉列表中

9、，选择“序列”项。 （4）在随之出现“来源”文本框中输入“通俗，民族，美声”自定义序列。注意：序列中各项中间的逗号必须为英文状态符号。 （5）根据需要，可以对其余选项卡进行适当设置。 说明：数据有效性设置之前，必须首先选取所需单元格或区域；另外，数据有效性应该在输入之前设置，否则不会自动起作用。 数据有效性设置之后，单击“唱法类型”列中单元格，则会出现如图4-5所示的列表框，从而可以实现快速的选取录入。,图4-5“唱法类型”字段数据有效性的设置效果,13,办公实际使用的数据库中字段数往往很多，如果用滚屏的方法来处理数据很不方便。Excel为此专门提供了“记录单”功能，选择“数据”|“记录单”命

10、令，系统即可弹出如图4-6所示对话框，它显示数据记录的所有字段，提供了增加、修改、删除等功能。当数据库很大时，记录单将会显示出很大的优势，利用它可以简捷、精确地输入记录。图4-6 Excel中“记录单”样式,14,4.3 对“报名信息汇总表”进行数据分析本节介绍利用Excel中的有关功能，对“报名信息汇总表”进行数据分析的操作方法，主要包括数据的排序、筛选、分类汇总与数据透视等功能。4.3.1 对“报名信息汇总表”内容进行排序 在报名汇总表中，各个参赛选手的资料一般是按照其报名的先后顺序输入的。为了提高查找效率，有时需要对输入的数据重新进行整理，对此有效的方法就是排序。 Excel可以根据数据

11、库中的内容对数据记录进行排序。排序时，Excel 将利用指定的顺序重新排列行、列或各单元格。可以根据一列或多列的内容按升序或降序对数据库排序。1排序的依据 在按升序排序时， Excel 使用如下顺序： （1）数字从最小的负数到最大的正数排序； （2）文本以及包含数字的文本，按下列顺序排序：,15,（3）在逻辑值中，FALSE 排在 TRUE 之前； （4）所有错误值的优先级等效； （5）空格总是排在最后； （6）汉字的排序可以按笔画，也可按字典顺序排序，这可以通过有 关操作设置。 2按单个字段排序 按单个字段排序就是只根据某一个字段的内容进行排序。 操作方法为：将光标置于待排序列中；单击工具栏

12、上“升序”或“降序”按钮即可。 说明：按数据库某一列数据排序时，只需单击列中任一单元格而不用全选该列。,16,3按多级字段排序 按多级字段排序就是根据两列或三列的内容对数据库排序，它主要适用于第一或第二排序字段出现相同内容时，为了排序合理再加上第二或第三排序字段。 操作步骤如下： （1）将光标定位在待排序数据库的任一单元格中，之后选择“数据”|“排序”命令，打开如图4-7所示对话框。 （2）在“主要关键字”、“次要关键字”和“第三关键字”下拉列表框中，分别选择需要排序的字段，并且设置“递增”或者“递减”即可，本例不太需要该设置。 （3）选定所需的其他排序选项，然后单击“确定”按钮。 说明：按数

13、据库多列数据排序时，只需单击数据库中任一单元格而不用全选整个数据库表格，否则会引起数据的混乱。 4特殊排序方式 除上述基本排序功能外，Excel还提供了一些特殊的排序功能。它们的操作方法为： （1）将光标放在所要排序的数据区域中的任一位置； （2）选择“数据”菜单中的“排序”项；在打开的对话框中选择“选项”按钮；,17,（3）在打开如图4-8所示的“选项”对话框中，分别设置：自定义排序次序、排序方向（按行或按列排序）、排序方法（按字母或按笔画排序） （4）按“确定”按钮完成操作。,图4-7 数据库按照多字段排序 图4-8 其他排序选项,说明：在“排序选项”对话框中，若不选择“方向”和“方法”，

14、系统默认的排序方向为“按列排序”，默认的排序方式为“字母排序”。如选择“笔画排序”方法，就可实现在开会代表名单、教材编写人员名单中经常看到的按姓氏笔画排序效果。,18,4.3.2 对“报名信息汇总表”内容进行筛选报名信息汇总表制作好之后，有时还需要根据指定条件从众多数据中筛选特定的记录，比如：筛选职业是“教师”的报名人员，筛选年龄在30以上的人员等等。 Excel中提供了两种筛选方法：“自动筛选”和“高级筛选”，它们可以将那些符合条件的记录显示在工作表中，而将其他不满足条件的记录从视图中隐藏起来；或者将筛选出来的记录送到指定位置存放，而原数据不动。 1自动筛选 利用自动筛选，可以很方便地从报名

15、汇总表中筛选出职业是“教师”的报名人员信息。下面以此为例，说明自动筛选的操作。操作步骤如下： （1）将鼠标定位到需要筛选的数据库中任一单元格。 （2）选择“数据”|“筛选”|“自动筛选”，使“自动筛选”项为选中状态（打上对号），这时在每个字段名旁出现筛选器箭头，如图4-9所示。 （3）本例要筛选出来职业是“教师”的报名人员的情况，需要单击“职业”字段名旁的筛选器箭头，从弹出的菜单中选择“教师”，然后单击鼠标左键即可。,19,图4-9 使用自动筛选器筛选记录,说明：在该菜单中，选择“升序排列”和“降序排列”，则筛选结果将按照指定顺序排列；选择具体学历名称，将只显示对应学历的记录；单击“全部”显示

16、所有记录；单击“前10个”可以显示最高或最低的一些（默认10个，可以自行设置）记录。（4）在图4-9中，如果下拉列表太长，不容易找到“教师”项时，还可以选择“自定义”项，从出现图4-10所示的对话框中，按照图中样式设置本例的筛选条件。,20,图4-10 “自定义自动筛选方式”对话框,说明：图4-10中，运算符除了图中所示外,还包括各种其他的数学关系运算,以及“始于”、“止于”、“包含”、“并非起始于”、“并非结束于”、“不包含”等字符关系运算。图中“或”表示两个条件中一个成立即可，而“与”要求两个同时成立。 （5）按“确定”按钮，完成操作，筛选结果如图4-11所示。,图4-11 筛选出来的职业

17、为教师的选手情况,21,说明：取消数据筛选的操作方法： 如要取消对某一列的筛选，单击该列筛选器箭头，然后再单击“全部”。 如要取消对所有列的筛选，可选择“数据”|“筛选”|“全部显示”命令。 如要撤消数据清单中的筛选箭头，可选择“数据”|“筛选”|“自动筛选”命令。2高级筛选 从上面的讲解可以看到，自动筛选可以实现同一字段之间的“与”运算和“或”运算，通过多次进行自动筛选也可以进行不同字段之间的“与”运算，但是它无法实现多个字段之间的“或”运算。这时就需要使用高级筛选。 需要说明的是：对于数据清单，如果要进行高级筛选，必须首先设置筛选条件区域。为此，必须在该数据清单上方留出若干空行，以便作为条

18、件区域。 另外，为了使读者可以更好地理解高级筛选，首先列出一个表格，如表4-1所示，该表用来对高级筛选条件区域的逻辑关系进行定义。,22,表4-1 高级筛选条件区域设置的几种逻辑关系,下面以筛选出来职业为“教师”、唱法类型为“美声”或者“民族”的报名选手情况操作为例，说明高级筛选的使用方法。操作步骤如下： （1）在“报名信息汇总表”表格的上方插入几个空行，根据本例筛选的实际需要以及表4-1介绍的逻辑关系，建立高级筛选的条件区域，如图4-12所示A1:B3区域。,23,图4-12 设置高级筛选的“条件区域”,（2）单击“报名信息汇总表”数据库表格中的任一单元格。 （3）选择“数据”|“筛选”|“

19、高级筛选”命令，出现“高级筛选”对话框。 （4）如图4-13所示，在 “方式”选项区中，单击选中“将筛选结果复制到其他位置”按钮；在“列表区域”框中，软件自动输入了要筛选的数据区域“$A$6:$H$66”，也就是整个数据清单区域 ；在“条件区域”框中，输入设置好的包含筛选条件的区域“$A$1:$B$3”（可直接在该文本框中输入区域引用，也可用鼠标在工作表中选定条件区域）；在“复制到”文本框中输入时，首先将光标在其中定位，然后用鼠标在数据清单下方指定一个单元格（本例为“$A$71”），该单元格将作为放置筛选结果区域左上角的位置，筛选结果将在它的下方和右方进行排列。,24,（5）单击“确定”按钮，

20、高级筛选结果如图4-14所示（原来数据我们选择的“隐藏”操作，以便大家能够看到筛选出来的效果）。,图4-13“高级筛选”对话框以及输入内容设置,图4-14 筛选出职业为“教师”、唱法类型为“美声”或“民族”的选手情况,25,3高级筛选时的注意事项 使用高级筛选时，需要注意以下几个问题： （1）高级筛选必须指定一个条件区域，它可以与数据库表格在一张工作表上， 但是必须与数据库之间有空白行隔开；条件区域也可以与数据库表格不在一张工作表上。 （2）条件区域中的字段名必须与数据库中的完全一样，最好通过复制得到。 （3）如果“条件区域”与数据库表格在一张工作表上，在筛选之前，最好把光标放置到数据库中某一

21、单元格上，这样数据区域就会自动填上数据库所在位置，省去再次鼠标选择或者重新输入的麻烦。 （4）执行“将筛选结果复制到其他位置”时，在“复制到”文本框中输入或选取将来要放置位置的左上角单元格即可，不要指定某区域（因为事先无法确定筛选结果）。 （5）根据需要，条件区域可以定义多个条件，以便用来筛选符合多个条件的记录。 这些条件可以输入到条件区域的同一行上，也可以输入到不同行上。但是必须记住：两个字段名下面的同一行中的各个条件之间为“与”的关系，也就是条件必须同时成立才算符合条件；两个字段名下面的不同行中的各个条件之间为“或”的关系，也就是条件只要有一个成立就算符合条件。,26,4.3.3 对“报名

22、信息汇总表”中数据分类汇总使用分类汇总，能够在数据库适当位置加上统计结果，使数据库变得清晰易懂。 对于本例，可以对 “唱法类型”进行分类汇总，以便了解唱法类型的报名人数。 在执行分类汇总命令之前，首先应该对数据库按照待汇总字段进行排序。 下面以按照“唱法类型”分类汇总为例来说明分类汇总的操作。操作步骤如下： （1）对需要分类汇总的字段进行排序，从而使相同的记录集中。本例将光标定位到“唱法类型”列中某一单元格，然后运行工具栏上“排序”按钮即可。 （2）选定数据库中任意一个单元格。 （3）选择“数据”|“分类汇总”命令，出现图4-15所示的“分类汇总”对话框。,图4-15 “分类汇总”对话框,27

23、,（4）单击“分类字段”下拉列表按钮，选择“唱法类型”作为分类汇总的字段。 （5）在“汇总方式”下拉列表中，选择需要的统计函数。分类汇总可以支持求和、平均数、最大、最小、计数、乘积等共计11种函数。本例需要选择“计数”。 （6）在“选定汇总项”列表中，选中需要对其汇总计算的字段前面的复选框。本例中选上“姓名”复选框即可。 （7）可以根据需要选中相应的复选框，指定汇总结果的显示位置。 （8）单击“确定”按钮，可以得到分类汇总结果。如图4-16所示。,图4-16 分类汇总的结果显示,28,从图4-16可以看出，在显示分类汇总结果的同时，分类汇总的左侧自动显示一些分级显示按钮。其中：单击左侧的“+”

24、形状按钮图标和“-”形状按钮图标分别可以展开和隐藏细节数据； “1”、“2”、“3”形状按钮表示显示数据的层次，“1”只显示总计数据，“2”显示部分数据以及汇总结果，“3”显示所有数据；“|”形状为级别条，用来指示属于某一级别的细节行或列的范围。 图4-17所示就是将分类汇总后细节数据隐藏后的显示效果。,图4-17 分类汇总后部分细节数据隐藏的显示效果,说明：分类汇总效果可以清除，操作时，先打开如图4-15所示的对话框，然后单击“全部删除”即可；但是为了保险，在汇总之前最好进行数据库备份。,29,4.4 利用Excel的关联表格制作歌唱比赛计分表在办公实践中的表格制作时，有些数据的存放和计算需

25、要使用多个工作表，并且相互之间需要相互引用和参照。这就需要使用Excel的关联表格操作来实现数据的处理。 某市举办的“青年歌手大奖赛”，经过初赛、复赛，目前已经进入决赛。 在决赛阶段，为了体现公平、公正、科学、合理的比赛原则，组委会拟定了如下的比赛计分规则。 每一个参加决赛选手的得分满分为100分，包括以下三大部分： 1、歌唱得分：每一位参赛选手自行选择一首歌曲演唱，满分90分，12个裁判分别打分，总分减去最高分和最低分之后的平均分为该项分数。 2、素质得分：两个题目，每题0.5分，共 1分，各由一个评委评分。 3、声乐得分：选手自己从指定歌曲中选择一首歌曲进行声乐表演，歌曲有不同的难度系数，

26、分A、B、C三个级别，满分9分，12个裁判打分，方法同上，但是在得到的平均分数的基础上再乘上难度系数（A为1，B为0.8，C为0.6），才能得到该项分数。 以上三项分数之和为该选手的总得分，按照该成绩的名次确定最终的获奖等级。,30,参加决赛的选手共20人，比赛前通过抽签确定出场顺序，比赛结束需要确定综合成绩一等奖1人，二等奖3人，三等奖5人，其余为优秀奖；同时综合成绩最好的3名将推荐到省里参加全省比赛；另外，为了比较单项成绩，还需要将单项成绩的前6名评出。 本节目标就是制作一套用来计算选手分数的表格，以便在现场快速计算选手成绩。 问题分析和操作提示 从上面的规则可以看到，需要进行几个单项成绩

27、的计算和排名，同时需要进行几项成绩之和作为综合分的计算和排名，所以可以考虑利用Excel的关联表格操作。 所谓关联表格，是指在一个或者多个工作簿中有一定关联关系的工作表的总称。 本实例制作时，还需要注意以下几个问题： 1各个表格中要尽量减少现场数据录入和计算的工作量。 2可以在比赛中间随时查看比赛过的选手的各个单项成绩，确定对应的暂时名次。 3单项成绩和综合成绩的计算，需使用公式和函数实现，并且公式要具有容错性。,31,4综合分数排名为最后名次，根据该名次利用公式确定获奖等级。 5多个工作表之间进行关联操作时，注意数据要能够随时实时变化。 6为了使各个表格之间切换方便，可以制作一个主界面工作表

28、，在上面利用有关图形建立超级链接，以便可以快速切换到对应的工作表。 按照以上的分析，本问题的解决可以通过制作8张关联的工作表来实现，分别用来作为“主界面”工作表、 “计分规则”工作表、“选手情况” 工作表、“歌唱得分” 工作表、“素质得分” 工作表、“声乐得分” 工作表、“综合得分” 工作表以及“评奖结果” 工作表。下面就介绍这些工作表的格式制作和公式设计。4.4.1工作表的添加和更名从上面的的分析可知，该计分系统需要设置8个工作表，而Excel默认的工作表个数为3个。所以，本实例操作时，首先需要工作表个数的添加以及名称的更改。 操作步骤如下： 1启动Excel，屏幕上有默认的Sheet 1、

29、Sheet 2、Sheet3 三个工作表。,32,2在工作表标签上，单击选择Sheet 1，然后单击鼠标右键，从弹出的快捷菜单中选择“插入”，根据随后出现的系统提示操作，添加了一个新工作表Sheet 4。 说明：上述方法一次只能添加一个工作表，如果想添加多个工作表，请先选取多个工作表，然后再单击右键，选择“插入”，则会快速添加与选取个数一样多的工作表。 3根据上面的说明，借助于Shift键选择全部四个工作表，单击鼠标右键，选择“插入”，则快速添加了四个工作表Sheet 5、Sheet 6、Sheet 7、Sheet 8。 4分别双击工作表名称，将8个工作表的名称依次更该为“主界面”、“计分规则

30、”、“选手情况”、“歌唱得分”、“素质得分”、“声乐得分”、“综合得分”和“评奖结果”。 5将工作簿文件以“歌手大奖赛记分”为名存盘。4.4.2 “主界面”工作表的制作“主界面”工作表效果如图4-18所示，利用“主界面”工作表，可以快速看到整个记分方案的完整组成，同时根据需要将来可以快速切换到需要的其他工作表。,33,图4-18 “主界面”工作表效果,该工作表制作中要用到工作表网格线的取消、单元格填充颜色设置、艺术字标题设置、椭圆形按钮绘制以及超级链接建立等知识。 操作步骤如下： 1单击工作表标签上的“主界面”，选取该工作表。,34,2单击“工具”|“选项”命令，弹出图4-19所示的“选项”对

31、话框，从中选取“视图”标签，将“网格线”复选框设置为不选中状态，这样就取消了工作表中的网格线。 3全选整个工作表，然后利用格式工具栏上的按钮设置工作表填充颜色为浅黄色。,图4-19 在Excel的“选项” 对话框中取消网格线,35,4利用与Word中输入艺术字类似的方法，输入艺术字标题“青年歌手大奖赛计分方案”，并进行适当格式设置。 5利用“绘图”工具栏上的椭圆工具绘制椭圆，并进行填充效果、线条颜色以及图形大小的设置。输入过艺术字标题和绘制椭圆图形后,“主界面”工作表如图4-20所示。,图4-20 输入过艺术字标题和绘制椭圆图形后的“主界面”工作表,36,6将上面制作的椭圆图形再复制6个，并将

32、他们放置到适当位置，作后一个需要放大。操作时，可以借助“绘图”工具栏上的“绘图”|“对齐和分布”命令来辅助完成。 7依次右击各个椭圆，从弹出的菜单中选择“添加文字”，分别输入相应的文字，效果如图4-21所示。,图4-21 在椭圆中输入文字后的界面效果,37,8绘制椭圆的目的，就是为了将来单击椭圆，就能快速打开对应的工作表。为此，需要为他们设置超级链接。操作步骤如下： （1）选中第一个椭圆“计分规则”，执行“插入”|“超级链接”命令，弹出如图4-22所示的“编辑超级链接”对话框。,图4-22“编辑超级链接”对话框,说明：选中椭圆后，按组合键Ctrl+K也可以快速打开“编辑超级链接”对话框。,38

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

34、-23 “设置超级链接屏幕提示”对话框,39,（4）按照上述方法，依次为各个椭圆对象设置相应的超级链接。 9对该工作表中各个对象的位置、大小再进行适当调整，“主界面”工作表完成。4.4.3“计分规则”工作表的制作“计分规则”工作表如图4-24所示，在其中输入了比赛计分规则的说明文字，以便将来观众或选手咨询时能够快速查询。制作本工作表时，主要难点是如何解决大块文字在Excel中的输入问题。,图4-24 “计分规则” 工作表,40,一般情况下Excel单元格中很少输入大量文字，特别是多行文字。而对于本工作表，确实现在需要这样处理。解决的方法有以下几种： 1拉大单元格方法。比如将A1单元格的宽度、高

35、度均设置为足够大，然后在其中输入文字，分段换行时按AltEnter（不能像Word一样直接按Enter） 2设置单元格文字自动换行。利用上述方法将单元格拉大后，选中单元格，单击右键，选择“设置单元格格式”，如图4-25所示，从弹出的对话框中，将“对齐”标签下的“自动换行”复选框选上，则以后再输入文字一旦到单元格右端将会自动换行。,图4-25 设置单元格内文本自动换行,41,3利用文本框的方法。可以在工作表上绘制一个文本框，并将其调整到适当大小，在文本框将需要的文字录入，本实例中采用的就是该方式。4.4.4 “选手情况”工作表的制作“选手情况”工作表效果如图4-26所示，制作时按照图中样式进行设

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

37、到现场比赛时，才能输入选手编号和裁判打分，从而确定选手姓名，然后计算选手分数，最后确定选手名次。所以，大家在图4-27中只看到一行数据，这只是测试数据，用来验证函数和公式的正确性。 在图4-27中，姓名、分数、名次三列中每个单元格中都有函数或公式，一旦编号和裁判打分输入之后，他们都会自动出现结果。下面说明其该工作表的制作过程： 1.工作表表格框架的制作 按照图4-27所示样式，制作“歌唱得分”工作表的表格框架，将“出场序号”一列利用序列填充方法输入完整，对整个表格并做好格式设置。 2.利用VLOOKUP函数根据编号确定选手姓名 该表中，“选手编号”列也就是C列按照抽签顺序在比赛时录入，“姓名”

38、一列可以事先设置好公式，以便将来录入选手编号后，可以自动产生，这就需要使用垂直查找函数VLOOKUP。 （1）VLOOKUP函数的使用方法和简单举例 VLOOKUP函数的作用 在表格或首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,44, VLOOKUP函数的格式VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。其中： Lookup_value为需要在数据表第一列中查找的数值,可以为数值、引用或文字串；Table_array 为需要在其中查找数据的数据表或数据区域； Col_index_num 为 tab

39、le_array 中待返回的匹配值的列序号。 Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。 VLOOKUP函数的使用举例 比如：如图4-28所示，如果想根据D2的代号到A2：B7数据区域中查找E2单元格中的对应名称，则E2单元格中的函数形式应该为： =VLOOKUP(D2,$A$2:$B$7,2) 其

40、中，$A$2:$B$7为采用引用格式，原因是如果该单元格需要向下拖动时，应该要求该数据区域不能变化，所以公式中该数据区域要使用绝对引用。,45,图4-28 VLOOKUP函数使用举例,（2）函数中跨工作表以及跨工作簿的单元格引用 在办公实践的许多情况下，有时公式中都可能要用到另一工作表单元格中的数据，如Sheet1工作表F4的公式如果为： （C4 D4E4）Sheet2！B1 其中“Sheet2！B1”表示工作表Sheet2中的B1单元格地址。这个公式表示计算当前工作表Sheet1中的C4、D4和E4单元格数据之和与Sheet2工作表的B1单元格数据的乘积，结果存入当前工作表Sheet1中的F

41、4单元格。,46,函数中还可以进行跨工作簿的单元格引用，此时地址的一般形式为： 工作簿名工作表名！单元格地址 综上所述，到跨工作簿、工作表的单元格地址引用的方法分别如下： 在当前工作表中引用本工作表中单元格，只需输入单元格的地址即可。 在当前工作表中引用本工作簿中其它工作表中单元格时，需首先输入被引用的工作表名和一个感叹号“！”，然后再输入那个工作表中的单元格地址。 在当前工作表中引用另外工作簿中工作表的单元格时，需要首先输入由中括号“ ”包围的引用的工作簿名称，然后输入被引用的工作表名称和一个感叹号“！”，最后再输入那个工作表中的单元格的地址。 例如：图4-29中，用来计算三门课程总成绩的单

42、元格E3中的公式为： =B3*$G$3+C3*$G$4+D3*$G$5+离散数学!B3+计算机成绩.xls计算机成绩!$B$4 请各位读者分析其中跨工作表、工作簿引用的方式以及该公式的意义。,47,图4-29 公式中跨工作表和跨工作簿的单元格引用,（3）根据编号利用VLOOKUP查找姓名 根据上面对VLOOKUP函数的使用说明，图4-27“歌唱得分”工作表中用来存放姓名的D5单元格的公式应该为: “VLOOKUP(C5,选手情况!$A$3:$B$21,2)”,48,但是，此时如果将D5向下拖动时，下面的单元格中将出现如图4-30所示的“#N/A”的错误信息，其原因主要是因为前面对应“编号”一列

43、还没有输入内容。,图4-30 因为公式引用的单元格没有内容引起的“#N/A”错误,49,（4）利用IF函数避免 “#N/A”错误 要解决图4-30出现的这种“#N/A”错误，可以在使用该函数之前，先用一个IF函数控制一下，比如可以将D5单元格的公式变为: “=IF(C5=“,“,VLOOKUP(C5,选手情况!$A$3:$B$21,2)”。 这样，当编号一列没有输入时，则姓名什么也不显示（但函数确实已经存在），而当编号一旦输入，姓名一列就会自动利用VLOOKUP函数自动查找。 （5）函数的复制 D5的函数确定好之后，通过鼠标拖动将其一直拖动到最后一行记录，则确定姓名一列的公式复制完成。 说明：

44、本节以后我们用到的公式和函数，为了避免某些单元格没有输入内容引起“#N/A”错误，都采用了这种利用IF函数进行公式控制的方法。 3利用公式计算选手歌唱得分 分数的计算就需要使用公式来进行运算。下面介绍公式的有关知识： 公式是对工作表中的数据进行计算和分析的一种等式，它可以对工作表数值进行算术运算、关系运算和逻辑运算，同样也可以对字符数据以及其它数据进行运算。 公式可以引用同一工作表中的其它单元格、同一工作簿不同工作表中的单元格，或者其它工作簿的工作表中的单元格。,50,在进行公式输入时，应该遵循以下规则： （1）公式输入时必须以“=”开头，公式中间不能包括空格。 （2）公式中的单元格引用，可以

45、在编辑区直接输入，也可以通过单击该单元格或拖动单元格区域从而实现自动填入。 （3）用算术运算符号：+（加）、（减）、*（乘）、/（除）、（乘方），字符运算符号&(连接字符串)以及关系运算符号（大于）、0,(SUM(E5:P5)-MIN(E5:P5)-MAX(E5:P5)/(COUNT(E5:P5)-2),“)”。 D5中的公式输入完成之后，通过鼠标拖动将其一直拖动到最后一行记录，则计算选手歌唱分数一列的公式复制完成。,51,4利用RANK函数确定选手名次 名次的确认对于本例最好使用专门的排序函数RANK。 RANK函数是专门进行排名次的函数，用来返回一个数值在一组数值中的排位。 语法格式为：

46、RANK(number,ref,order)，共包括三个参数，其中： Number 为需要找到排位的数字；Ref 为包含一组数字的数组或引用； Order 为一数字，指明排位的方式，为 0 或省略，按降序排列排位，不为零，按升序排列进行排位。 例如：下面图4-31所示D3单元格中的公式就为： “RANK（C3，$C$3：$C$12）” ，请读者根据上面的说明解释其意义。,图4-31 RANK函数的使用举例,52,根据上面介绍的RANK使用方法，用来存放名次的R5单元格的公式为： “=IF(Q5“,RANK(Q5,$Q$5:$Q$24),“)”。 R5的函数确定好之后，通过鼠标拖动将其一直拖动到

47、最后一行记录，则确定选手名次一列的公式复制完成。4.4.6“素质得分”工作表的制作“素质得分”工作表如图4-32所示，制作方法与“歌唱得分”工作表类似。,图4-32 “素质得分” 工作表,53,其中： D5单元格的公式为: “=IF(C5=“,“,VLOOKUP(C5,选手情况!$A$3:$B$21,2)”； 另外，根据计分规则，用来存放分数的G5单元格的公式为: “=IF(SUM(E5:F5)=0,“,SUM(E5:F5)”； 用来存放名次的H5单元格的公式为: “=IF(G5“,RANK(G5,$G$5:$G$24),“)”。4.4.7“声乐得分”工作表的制作“声乐得分”工作表如图4-33

48、所示，制作方法与“歌唱得分”工作表类似，不同之处就是多了两列：“类别”和“系数”，他们用来确定难度系数，最终的分数结果还需要在歌唱得分方法的结果之后再乘上难度系数。,54,图4-33 “声乐得分” 工作表,其中： 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,

49、$S$5:$S$24),“)”。,55,4.4.8“综合得分”工作表的制作“综合得分”工作表效果如图4-34所示，制作中要多次采用跨工作表引用单元格数据，第5行中几个主要含公式和函数的单元格中，公式和函数形式分别如下所示： 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,“三等奖“,“优秀奖“)”,56,图4-34工作表“综合得分” 工作表,4.4.9“评奖结果”工作表的制作“评奖结果”工作表效果如图4-35所示，表格中空白单元格，根据比赛的综合成绩，将对应人员的名单输入或者通过复制得到即可。,57,图4-35 “评奖结果” 工作表,4.4.10 Excel中公式出错的处理在Excel中输入计算公式或函数后，经常会因为某些错误，在单元格中显示错误信息，使一些初学者手足无措。现将Excel中最常见的一些错误信息，以及可能发生的原因和解决方法列出如下，供读者参考。,