第四讲：电子表格软件Excel.ppt

《第四讲：电子表格软件Excel.ppt》由会员分享，可在线阅读，更多相关《第四讲：电子表格软件Excel.ppt（47页珍藏版）》请在三一办公上搜索。

1、第 4 讲 电子表格软件Excel 2003,4.1 多张表或多个工作簿的同时使用4.2 函数及其使用4.3 数据清单的管理4.4 数据保护,4.1 多张表或多个工作簿的同时使用管理工作簿就是对多张工作表的管理和操作。4.1.1 同一工作簿多张表的同时使用 1切换工作表单击工作表标签名，可使其变为当前工作表。按Ctrl+PageUp键切换到上一工作表，按Ctrl+PageDown切换到下一工作表。单击工作表标签左侧4个标签滚动按钮(见图4.1)可显示被遮挡的工作表标签名。,图4.1,2多个工作表的选定若在当前工作簿中选定了多个工作表，则对当前工作表中的操作，会重复在其他选定的工作表中。选定多个

2、工作表的方法如下所示。(1)选定一组相邻的工作表：单击首表标签，按住Shift键，单击要选定的末表标签。此时，选定表标签名均以白底显示，同时在窗口标题栏出现“工作组”字样。(2)选定一组不相邻的工作表：单击首表标签，按住ctrl键，分别单击要选定的表标签名。(3)选定工作簿中所有的工作表：在工作表标签名上右击，从弹出的快捷菜单中选择“选定全部工作表”命令。,3在一个公式中引用不同工作表的数据进行运算 一个公式中的操作数若来自同一工作簿的不同工作表，只要在表示操作数的单元格引用地址前，加上表名和字符“！”即可，它表示引用该工作表内的单元格数据。【例4.1】在“学生成绩簿|成绩统计表”表中登记的学

3、生的平均成绩，其中陈刚平均成绩的计算数据来自同一工作簿的“成绩表”工作表。操作步骤如下所示。打开“学生成绩簿”工作簿的“绩统计表”工作表，选择要计算平均成绩的单元格B2。,选择“自动求和|平均值”命令，在B2单元格中会看到被自动填写的求平均数函数公式“=AVERAGE()”。在圆括号中输入“成绩表!D2:G2”，其中“D2:G2”表示学生陈刚总成绩数据区的单元格引用地址。最后的计算公式是“=AVERAGE(成绩表!D2:G2)”。按回车键完成计算(见图4.2)。,图4.2,4隐藏和取消 隐藏工作表先选定要隐藏的工作表，选择“格式|工作表|隐藏”命令，即可实现表的隐藏。选择“格式|工作表|取消隐

4、藏”命令，在打开的对话框中选择要取消隐藏的工作表名称，单击“确定”按钮，就可恢复被隐藏的表内容。4.1.2 多个工作簿的同时使用 Excel允许同时打开多个工作簿，允许在某个工作簿中引用另一个工作簿的数据。1多个工作簿的切换 同时打开多个工作簿后，每个簿占据一个Excel工作窗口。可通过单击Windows任务栏上显示的工作,簿图标来切换。也可单击“窗口”命令，在其下拉菜单下方列出所有已打开的簿名称，单击要选择的簿名，即可切换到该工作簿。2在一个公式中引用不同工作簿的数据 在一个表中可引用不同工作簿的数据，只是在引用单元格地址前，必须加上用方括号(或单引号，双引号)括起的工作簿文件名，以指出被引

5、用的数据区域所属的工作簿文件。例如，在某一工作簿的某公式中引用“学生成绩簿”工作簿的“成绩表”工作表数据，则公式内容为：“=学生成绩簿.xls成绩表!D2:G11”。,4.2 函数及其使用 函数是预定义的内置公式，它处理数据与直接创建公式处理数据的方式相似。例如，使用公式对3个单元格中的数据求其平均值，公式形式可为“=(A1,A2,A3)/3”，而用函数AVERAGE可写为“=AVERAGE(A1:A3)”，两者运算结果一样。使用函数可减少输入工作量，还可减小输入出错。函数都由函数名和位于其后的一系列参数(用圆括号括起来)组成，即：函数名(参数1,参数2,)。4.2.1 函数的使用 向公式中插

6、入函数的常用方法如下所示。(1)单击编辑栏上的“插入函数”按钮，或选择“插入|函数”命令，打开对话框(见图4.3)，从中选取函数名。例如，选取“MAX”。,(2)“插入函数”对话框提供了查找函数的多种办法。例如，可单击“或选择类别”列表框右侧的下指箭头，打开列表框内容，从中选择函数类型(图4.3，选择“统计”类别中的“MAX”函数)。若不知道使用什么样的函数，可在“搜索函数”编辑框(参见图4.3)中输入有关操作目的的文字说明作为搜索关键词(可以是自然语言文字)。例如，输入“求最大数”，并单击“转到”按钮，系统将按照关键词的含义，自动采用智能方式查找相关函数，并在“选择函数”列表框中列出推荐的具

7、有相近统计功能的函数名称，通过对话框下方的简单函数功能说明的描述，选择需要的函数项。,若对需要使用的函数不太了解或不会使用，可单击对话框下方的“有关该函数的帮助”链接，即可获得该函数的帮助信息和示例描述。(3)选中所需函数名后，按“确定”按钮，打开“函数参数”对话框。显示有函数名称、函数功能、参数的说明，以及函数运算结果等信息。在对话框的“Number1”编辑框中设置函数参数数据区的取值范围。若数据区域不只一个，还要向“Number2”中输入数据区范围，甚至定义更多的Number项。,图4.3,4.3 数据清单的管理 数据清单的管理一般是指对工作表数据进行排序、筛选、分类和汇总等的操作。4.3

8、.1 数据清单的建立和编辑 上面提到的Sheet工作表对其行或列数据没有特殊要求。而称为“数据清单”工作表的格式则必须具备一些条件，以便用户可以通过“数据清单”对表中的数据进行排序、筛选、分类和汇总等操作，以实现日常所需要的统计工作。1.数据清单及其特点 什么是数据清单：数据清单是典型的二维表，是由工作表单元格构成的矩形区域(见图4.4)。它的特点如下所示。,图4.4,(1)矩形区域的第一行为表头，由多个列标识名组成。例如：姓名、性别、专业、生理、英语、计算机、综合测评等(参见图4.4)。这些列标识名在数据表中称作字段名，而字段名不能相同。所以数据清单的列就表示字段的数据，而每一行的数据表示一

9、条记录。所以数据清单是由多条记录组成的。(2)表头标识下是连续的数据区域，这个区域可以是整个工作表的数据，也可以是工作表数据的一部分。数据清单的处理功能只能在一个数据清单中使用。,所以在创建数据清单时应注意到：在一个工作表中宜创建一个数据清单。因为数据清单管理功能，如筛选等操作一次只能在一个数据清单中使用；若工作表中有多种数据，则数据清单与其它数据间至少须用一列或一行空白单元格隔开，以便对数据清单进行排序、筛选或插入汇总等操作。在更改数据清单前，要确保隐藏的行或列能全部显示出来。若清单中的行和列未被显示，说明这些数据有可能会被删除了。2建立一个数据清单【例4.2】建立学生成绩数据清单，字段名为

10、：姓名、性别、专业、生理、英语、计算机、综合测评。,建立列标识：创建列标识(字段名)，作为在数据清单的第一行，Excel 将使用这些列标识创建报表，并查找和组织数据。输入数据：输入数据时应确保同一列有同类型的数据值。输入方法有两种。一是在单元格内直接输入数据。二是利用“记录单”输入数据：选择“数据|记录单”命令，打开对话框。该记录单的标题栏上列出了当前数据清单所在的工作表名(即表示是该工作表的记录单)，左侧列出了所有字段名，如姓名、性别、专业、生理、英语、计算机、综合测评等，字段名旁边是数据编辑框，可直接在编辑框中输入记录的各个字段数据。输入的第一条记录内容后，单击“新建”按钮后，会出现一条空

11、记录等待数据输入。最后单击“关闭”按钮结束输入。,3编辑记录 编辑记录可在单元格中进行。也可在记录单中编辑记录。利用记录单对话框的“上一条”和“下一条”按钮查看已输入的记录数据。若删除当前记录，则单击“清除”按钮。单击“关闭”按钮，可保存修改。4.查找记录 若查找记录，单击“条件”按钮，输入相关的查找条件。例如，图4.5中给出的条件是：列出“平时成绩”“80”的学生。最后单击“上一条”或“下一条”按钮，可查看到符合给定条件的所有记录。,图4.5,4.3.2 数据排序 在新建立的数据清单中，数据是依照输入的先后随机排列的。排序功能可根据清单中的一列或多列内容对数据按升序(或降序)排列，或是以自定

12、义方式进行排序。排序前宜将原始数据区复制到空白区域或另外一个新工作表中，并在新的数据区上排序，以保护原始数据的完整性。1简单排序 利用“升序”或“降序”按钮，按照所选定的列数据对清单进行排序。首先激活作为排序标准的字段数据中任一单元格，单击“升序”(或“降序”)按钮，系统完成排序。,若选择的字段是日期型数据，则系统按照日期值的先后顺序排列。若选择的排序字段是字符型，则按照其ASCII码值的大小排列。注意汉字按照其拼音的顺序排列。2复杂排序 在排序字段值相同时，可使用多至三级的复杂排序。【例4.3】对学生成绩簿的“成绩表”工作表的数据清单，要求用“性别”、“专业”为主、次关键字以升序排序，用“计

13、算机”为第三关键字以降序排序。操作步骤如下所示。激活数据清单的任一单元格，选择“数据|排序”命令，打开排序对话框。,选择工作表中“性别”字段作为主要关键字，且按升序排序；选择“专业”字段为次要关键字，且按升序排列；选择“计算机”字段为第三关键字，且按降序排列。若选定数据区的首行是表头的字段名，则在“排序”对话框的“列表”区下选择“有标题行”单选项。单击“确定”按钮完成排序。从排序结果中可看到，系统对清单数据首先按照主关键字升序排列，在主关键字字段的数据有重复时，对重复数据又按次要关键字排序，在主关键字和次要关键字的数据都重复时，最后按第三关键字段排序。,3.用户自定义排序次序 对数据的排序一般

14、是按数值大小，或按英文字母顺序排序。用户也可自定义排序。操作是：选择“数据|排序|选项”命令，打开“排序选项”对话框(见图4.6)。在“自定义排序次序”下拉列表框中，可选择自定义顺序；也可在“方向”和“方法”栏中定义排序形式。,图4.6,4.排序的撤消 若想将排序后的数据清单恢复到排序前，仅通过使用“撤消”功能有时无法做到。有效的一种方法是排序前事先在数据清单中增加一个名为“编号”的字段，依次为每行数据记录建立编号1，2，3，。在对数据清单进行多次排序后，若要恢复原状，只要按照“编号”字段重新进行一次升序排序即可。4.3.3 数据筛选 利用筛选操作，只选择显示符合设定筛选条件的数据行，而隐藏其

15、它行。筛选有两种：自动筛选和高级筛选。“自动筛选”能满足大部分要求；而对需要建立复杂条件的筛选操作，可选择“高级筛选”。,1“自动筛选”自动筛选是在访问含有大量数据的数据清单中，快速获取所需数据的简单处理方法，仅显示需要看到的数据内容。特点是对单一字段定义筛选条件。操作步骤如下所示。(1)激活数据清单中的任一单元格(否则系统提示找不到列表)。选择“数据|筛选|自动筛选”命令，数据清单的每个列标题旁出现一个下指箭头(见图4.7)。(2)选择数据清单的某一个字段来设置筛选条件：选一列标题，单击其右侧的下指箭头，打开下拉列表，选择一项作为筛选数据的标准(图4.7中定义“性别”字段内数据值是“男”)，

16、选定标准后即执行筛选命令，给出结果。,图4.7,在列标题的下拉列表中包含的几个选项的作用。“全部”数据清单中的全部数据。“前10 个”指定从最大(或最小)数据值记录中筛选前若干项。设置方法是：单击字段下拉列表中的“前10 个”选项，打开“自动筛选前10 个”对话框，设置后单击“确定”按钮。“自定义”“自定义自动筛选方式”对话框可建立“与”或“或”的筛选条件。例如，若将学生成绩单中有关“计算机”字段中分值在80与90之间的记录筛选出来。字段中的确切值仅显示出数据清单中包括这个确切值的记录。,“空白”仅显示此列中含有空白单元格的数据记录。“非空白”仅显示此列中含有数据的记录。2取消筛选取消筛选结果

17、的常用方法有两种。(1)若要取消对某一列筛选操作结果，单击该列右端的下指箭头，从弹出的下拉菜单中选择“(全部)”选项，即可恢复全部数据的显示。(2)若要取消对所有列所做的筛选操作结果，选择“数据|筛选|全部显示”命令。3建立复杂条件的“高级筛选”“高级筛选”与“自动筛选”的区别如下所示。,(1)“高级筛选”是在多个字段间设置筛选条件，显示的条件表达式有多个，且在表的条件区域中输入条件表达式。条件区域内容需用户建立，这是使用高级筛选的前提。“自动筛选”以单一字段来建立筛选条件，不需建立条件区域。(2)“自动筛选”的条件不影响“高级筛选”的运行和结果。两种方法相互独立。【例4.4】使用高级筛选，完

18、成从“成绩表”工作表中筛选出药学专业男生的学生记录。操作步骤如下所示。建立条件区域。区域必须包括数据清单的所有的或与筛选相关的字段名。,条件区域建立过程：先在成绩表数据清单上方插入三行空行，首行输入所有的或在筛选中必要的字段名：性别和专业。第二行输入筛选条件，即：性别字段值：“男”，专业字段值：“药学”(见图4.8)。,图4.8,第三行为条件区域与数据清单的分隔线。单击数据清单内任意一个单元格，选择“数据|筛选|高级筛选”命令，打开“高级筛选”对话框，在此分别设置数据清单的数据区域、筛选条件区域和定义筛选结果存放位置。分别单击“数据区域”、“条件区域”文本框的“折叠对话框”按钮，可返回到数据清

19、单中，然后用鼠标实际圈划这些区域。此例中，对话框内设置各数据区引用内容如4.9所示。,图4.9,系统默认选择在原有区域内显示筛选结果，若使用系统默认值，则单击“确定”按钮。也可选择将结果存放到其它的位置，以保全原来数据。方法是点选对话框内“方式”区中“将筛选结果复制到其它位置”单选按钮，使“复制到”编辑框呈可修改状态，再单击此编辑框右侧的“折叠对话框”按钮退回到清单中，实际选定存放结果数据的位置。例如，本例图中，定义将筛选结果存入本工作表的数据清单下方，以A17为起始地址的数据区中。单击“确定”按钮，筛选结果如图4.10所示。,图4.10,此例是对“性别”=“男”和“专业”=“药学”两条件“与

20、”操作的结果。若要对所给两个条件执行“或”操作，则条件区域需要4个空行，将两个条件分别写在不同的行中，以便实现字段间的“或”操作。4.3.4 分类汇总 建立数据清单后，可依据某个字段将所有的记录分类，把字段值相同的连续记录作为一类，得到每一类的统计信息。对数据清单数据进行分析处理时，运用分类汇总功能，可免去一次次输入公式和调用函数对数据进行求和、求平均、乘积等操作，从而提高工作效率。另外，当进行分类汇总之后，还可对清单进行分级显示。,1创建分类汇总【例4.4】对学生成绩簿的成绩表(见图4.11)分类汇总显示整个情况，并统计各专业男女学生数。操作步骤如下所示。,图4.11,以字段“性别”为标准对

21、数据清单进行排序。选定经排序后的清单的任一单元格，选择“数据|分类汇总”命令，打开对话框，设置相关参数(图4.12，指定分类字段为“性别”)；对该分类字段中相同数据对应的记录，统计它们性别字段的总和，以“计数”作为汇总方式。单击“确定”按钮得出结果。,图4.12,2创建嵌套分类汇总 在例4.4中，若在“性别”汇总的前提下，再按性别做进一步的分类汇总，则需要创建嵌套分类汇总。操作步骤如下所示。(1)首先对源数据清单按“性别”、“专业”两个字段进行排序，其中“性别”作为排序的主关键字，“专业”作为次要关键字。(2)然后按前面的分类汇总过程，先对“性别”字段进行第一次分类汇总。(3)再选择字段“专业

22、”，进行第二次的分类汇总，即选择“数据|分类汇总”命令，打开对话框。对第二次分类汇总设置相应参数(见图4.13)。注意，将系统默认设置选取“替换当前分类汇总”复选框，改为取消选择。单击“确定”按钮，结果如图4.14所示。,图4.13,图4.14,3.分类汇 总删除选择“数据分类汇总全部删除”命令，即可删除分类汇总。,4.4 数据保护 4.4.1 工作簿文件的加密与共享时的设置 1.设置打开工作簿文件权限的密码用户若不知密码，则不能使用此文件。2.文件共享情况下的加密设置(1)设置文件修改权限密码，用户若不知密码，则不能修改此文件。(2)把文件设置成只读类型，以保护文件不被修改。3.工作簿保护的

23、操作步骤(1)打开要建立保护的工作簿文件。选择“工具|选项”命令，在打开对话框中选择“安全性”选项卡(见图4.14)。,图4.14,(2)在“打开权限密码”和“修改权限密码”文本框处输入相应的口令字。单击“高级”按钮，打开对话框，可在“选择加密类型”下拉列表框中选择加密类型。(3)单击“确定”按钮，打开“确认密码”对话框。在“重新输入密码”文本框中重复输入用户设置的密码值后，单击“确定”按钮，完成安全性设置。4.4.2 保护工作簿 1.保护工作簿的结构和窗口(1)什么是保护结构：保护工作簿结构是对工作簿不能进行移动、复制、删除、隐藏、新增工作表及改变表名称等操作。(2)什么是保护窗口：保护工作

24、簿窗口是对工作簿窗口不能执行移动、隐藏、关闭，以及改变大小等操作。,(3)保护结构和窗口操作：选择“工具|保护|保护工作簿”命令，在打开对话框(见图4.16)中选择“结构”或者“窗口”复选框。还可设置“密码”。单击“确定”按钮，启动工作簿保护功能。2取消对工作簿的保护 选择“工具|保护|撤消工作簿保护”命令。注意：若在保护工作簿时设有密码，只有在输入密码后方可做取消操作。,图4.16,图4.17,4.4.3 保护工作表可对使用的工作表进行保护。操作是：选择“工具|保护|保护工作表”命令，在打开对话框(见图4.17)作保护操作。,默认设置锁定全部单元格。可设置对其他用户共享该工作表时的访问权限。也可设置保护工作表密码。单击“确定”按钮后，启动工作表保护功能。,再见,