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

第,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,多个工作表的选定,若在当前工作簿中选定了多个工作表，则对当前工作表中的操作，会重复在其他选定的工作表中。选定多个工作表的方法如下所示。,(1),选定一组相邻的工作表：单击首表标签，按住,Shift,键，单击要选定的末表标签。此时，选定表标签名均以白底显示，同时在窗口标题栏出现“,工作组,”,字样。,(2),选定一组不相邻的工作表：单击首表标签，按住,ctrl,键，分别单击要选定的表标签名。,(3),选定工作簿中所有的工作表：在工作表标签名上右击，从弹出的快捷菜单中选择“选定全部工作表”命令。,3,在一个公式中引用不同工作表的数据进行运算,一个公式中的操作数若来自同一工作簿的不同工作表，只要在表示操作数的单元格引用地址前，加上表名和字符“！”即可，它表示引用该工作表内的单元格数据。,【,例,4.1】,在“学生成绩簿,|,成绩统计表”表中登记的学生的平均成绩，其中陈刚平均成绩的计算数据来自同一工作簿的“成绩表”工作表。操作步骤如下所示。,打开“学生成绩簿”工作簿的“绩统计表”工作表，选择要计算平均成绩的单元格,B2,。,选择“自动求和,|,平均值”命令，在,B2,单元格中会看到被自动填写的求平均数函数公式“,=AVERAGE()”,。,在圆括号中输入“成绩表,!D2:G2”,，,其中“,D2:G2”,表示学生陈刚总成绩数据区的单元格引用地址。最后的计算公式是“,=AVERAGE(,成绩表,!D2:G2)”,。,按回车键完成计算,(,见图,4.2),。,图,4.2,4,隐藏和取消,隐藏工作表先选定要隐藏的工作表，选择“格式,|,工作表,|,隐藏”命令，即可实现表的隐藏。选择“格式,|,工作表,|,取消隐藏”命令，在打开的对话框中选择要取消隐藏的工作表名称，单击“确定”按钮，就可恢复被隐藏的表内容。,4.1.2,多个工作簿的同时使用,Excel,允许同时打开多个工作簿，允许在某个工作簿中引用另一个工作簿的数据。,1,多个工作簿的切换,同时打开多个工作簿后，每个簿占据一个,Excel,工作窗口。可通过单击,Windows,任务栏上显示的工作,簿图标来切换。也可单击“窗口”命令，在其下拉菜单下方列出所有已打开的簿名称，单击要选择的簿名，即可切换到该工作簿。,2,在一个公式中引用不同工作簿的数据,在一个表中可引用不同工作簿的数据，只是在引用单元格地址前，必须加上用方括号,(,或单引号，双引号,),括起的工作簿文件名，以指出被引用的数据区域所属的工作簿文件。例如，在某一工作簿的某公式中引用“学生成绩簿”工作簿的“成绩表”工作表数据，则公式内容为：“,=,学生成绩簿,.,xls,成绩表,!,D,2:,G,11”,。,4.2,函数及其使用,函数是预定义的内置公式，它处理数据与直接创建公式处理数据的方式相似。例如，使用公式对,3,个单元格中的数据求其平均值，公式形式可为“,=(A1,A2,A3)/3”,，,而用函数,AVERAGE,可写为“,=AVERAGE(A1:A3)”,，,两者运算结果一样。使用函数可减少输入工作量，还可减小输入出错。函数都由函数名和位于其后的一系列参数,(,用圆括号括起来,),组成，即：函数名,(,参数,1,参数,2,),。,4.2.1,函数的使用,向公式中插入函数的常用方法如下所示。,(1),单击编辑栏上的“插入函数”按钮，或选择“插入,|,函数”命令，打开对话框,(,见图,4.3),，从中选取函数名。例如，选取“,MAX”,。,(2)“,插入函数”对话框提供了查找函数的多种办法。例如，可单击“或选择类别”列表框右侧的下指箭头，打开列表框内容，从中选择函数类型,(,图,4.3,，选择“统计”类别中的“,MAX”,函数,),。,若不知道使用什么样的函数，可在“搜索函数”编辑框,(,参见图,4.3),中输入有关操作目的的文字说明作为搜索关键词,(,可以是自然语言文字,),。例如，输入“求最大数”，并单击“转到”按钮，系统将按照关键词的含义，自动采用智能方式查找相关函数，并在“选择函数”列表框中列出推荐的具有相近统计功能的函数名称，通过对话框下方的简单函数功能说明的描述，选择需要的函数项。,若对需要使用的函数不太了解或不会使用，可单击对话框下方的“有关该函数的帮助”链接，即可获得该函数的帮助信息和示例描述。,(3),选中所需函数名后，按“确定”按钮，打开“函数参数”对话框。显示有函数名称、函数功能、参数的说明，以及函数运算结果等信息。在对话框的“,Number1”,编辑框中设置函数参数数据区的取值范围。若数据区域不只一个，还要向“,Number2”,中输入数据区范围，甚至定义更多的,Number,项。,图,4.3,4.3,数据清单的管理,数据清单的管理一般是指对工作表数据进行排序、筛选、分类和汇总等的操作。,4.3.1,数据清单的建立和编辑,上面提到的,Sheet,工作表对其行或列数据没有特殊要求。而称为“数据清单”工作表的格式则必须具备一些条件，以便用户可以通过“数据清单”对表中的数据进行排序、筛选、分类和汇总等操作，以实现日常所需要的统计工作。,1.,数据清单及其特点,什么是数据清单：数据清单是典型的二维表，是由工作表单元格构成的矩形区域,(,见图,4.4),。它的特点如下所示。,图,4.4,(1),矩形区域的第一行为表头，由多个列标识名组成。例如：姓名、性别、专业、生理、英语、计算机、综合测评等,(,参见图,4.4),。这些列标识名在数据表中称作字段名，而字段名不能相同。所以数据清单的列就表示字段的数据，而每一行的数据表示一条记录。所以数据清单是由多条记录组成的。,(2),表头标识下是连续的数据区域，这个区域可以是整个工作表的数据，也可以是工作表数据的一部分。数据清单的处理功能只能在一个数据清单中使用。,所以在创建数据清单时应注意到：,在一个工作表中宜创建一个数据清单。因为数据清单管理功能，如筛选等操作一次只能在一个数据清单中使用；,若工作表中有多种数据，则数据清单与其它数据间至少须用一列或一行空白单元格隔开，以便对数据清单进行排序、筛选或插入汇总等操作。,在更改数据清单前，要确保隐藏的行或列能全部显示出来。若清单中的行和列未被显示，说明这些数据有可能会被删除了。,2,建立一个数据清单,【,例,4.2】,建立学生成绩数据清单，字段名为：姓名、性别、专业、生理、英语、计算机、综合测评。,建立列标识：创建列标识,(,字段名,),，作为在数据清单的第一行，,Excel,将使用这些列标识创建报表，并查找和组织数据。,输入数据：输入数据时应确保同一列有同类型的数据值。输入方法有两种。,一是在单元格内直接输入数据。,二是利用“记录单”输入数据：,选择“数据,|,记录单”命令，打开对话框。该记录单的标题栏上列出了当前数据清单所在的工作表名,(,即表示是该工作表的记录单,),，左侧列出了所有字段名，如姓名、性别、专业、生理、英语、计算机、综合测评等，字段名旁边是数据编辑框，可直接在编辑框中输入记录的各个字段数据。输入的第一条记录内容后，单击“新建”按钮后，会出现一条空记录等待数据输入。最后单击“关闭”按钮结束输入。,3,编辑记录,编辑记录可在单元格中进行。也可在记录单中编辑记录。利用记录单对话框的“上一条”和“下一条”按钮查看已输入的记录数据。若删除当前记录，则单击“清除”按钮。单击“关闭”按钮，可保存修改。,4.,查找记录,若查找记录，单击“条件”按钮，输入相关的查找条件。例如，图,4.5,中给出的条件是：列出“平时成绩”“,80”,的学生。最后单击“上一条”或“下一条”按钮，可查看到符合给定条件的所有记录。,图,4.5,4.3.2,数据排序,在新建立的数据清单中，数据是依照输入的先后随机排列的。排序功能可根据清单中的一列或多列内容对数据按升序,(,或降序,),排列，或是以自定义方式进行排序。,排序前宜将原始数据区复制到空白区域或另外一个新工作表中，并在新的数据区上排序，以保护原始数据的完整性。,1,简单排序,利用“升序”或“降序”按钮，按照所选定的列数据对清单进行排序。首先激活作为排序标准的字段数据中任一单元格，单击“升序”,(,或“降序”,),按钮，系统完成排序。,若选择的字段是日期型数据，则系统按照日期值的先后顺序排列。若选择的排序字段是字符型，则按照其,ASCII,码值的大小排列。注意汉字按照其拼音的顺序排列。,2,复杂排序,在排序字段值相同时，可使用多至三级的复杂排序。,【,例,4.3】,对学生成绩簿的“成绩表”工作表的数据清单，要求用“性别,”,、“专业,”,为主、次关键字以升序排序，用“计算机”为第三关键字以降序排序。操作步骤如下所示。,激活数据清单的任一单元格，选择“数据,|,排序”命令，打开排序对话框。,选择工作表中“性别,”,字段作为主要关键字，且按升序排序；选择“专业,”,字段为次要关键字，且按升序排列；选择“计算机”字段为第三关键字，且按降序排列。,若选定数据区的首行是表头的字段名，则在“排序”对话框的“列表”区下选择“有标题行”单选项。,单击“确定”按钮完成排序。,从排序结果中可看到，系统对清单数据首先按照主关键字升序排列，在主关键字字段的数据有重复时，对重复数据又按次要关键字排序，在主关键字和次要关键字的数据都重复时，最后按第三关键字段排序。,3.,用户自定义排序次序,对数据的排序一般是按数值大小，或按英文字母顺序排序。用户也可自定义排序。操作是：选择“数据,|,排序,|选项”命令，打开“排序选项”对话框,(,见图,4.6),。在“自定义排序次序”下拉列表框中，可选择自定义顺序；也可在“方向”和“方法”栏中定义排序形式。,图,4.6,4.,排序的撤消,若想将排序后的数据清单恢复到排序前，仅通过使用“撤消”功能有时无法做到。有效的一种方法是排序前事先在数据清单中增加一个名为“编号”的字段，依次为每行数据记录建立编号,1,，,2,，,3,，,。在对数据清单进行多次排序后，若要恢复原状，只要按照“编号”字段重新进行一次升序排序即可。,4.3.3,数据筛选,利用筛选操作，只选择显示符合设定筛选条件的数据行，而隐藏其它行。,筛选有两种：自动筛选和高级筛选。“自动筛选”能满足大部分要求；而对需要建立复杂条件的筛选操作，可选择“高级筛选”。,1,“自动筛选”,自动筛选是在访问含有大量数据的数据清单中，快速获取所需数据的简单处理方法，仅显示需要看到的数据内容。特点是对单一字段定义筛选条件。,操作步骤如下所示。,(1),激活数据清单中的任一单元格,(,否则系统提示找不到列表,),。选择“数据,|,筛选,|,自动筛选”命令，数据清单的每个列标题旁出现一个下指箭头,(,见图,4.7),。,(2),选择数据清单的某一个字段来设置筛选条件：选一列标题，单击其右侧的下指箭头，打开下拉列表，选择一项作为筛选数据的标准,(,图,4.7,中定义“性别,”,字段内数据值是“男,”,),，选定标准后即执行筛选命令，给出结果。,图,4.7,在列标题的下拉列表中包含的几个选项的作用。,“,全部”数据清单中的全部数据。,“,前,10,个”指定从最大,(,或最小,),数据值记录中筛选前若干项。设置方法是：单击字段下拉列表中的“前,10,个”选项，打开“自动筛选前,10,个”对话框，设置后单击“确定”按钮。,“,自定义”“自定义自动筛选方式”对话框可建立“与”或“或”的筛选条件。例如，若将学生成绩单中有关“计算机”字段中分值在,80,与,90,之间的记录筛选出来。,字段中的确切值仅显示出数据清单中包括这个确切值的记录。,“,空白”仅显示此列中含有空白单元格的数据记录。,“,非空白”仅显示此列中含有数据的记录。,2,取消筛选,取消筛选结果的常用方法有两种。,(1),若要取消对某一列筛选操作结果，单击该列右端的下指箭头，从弹出的下拉菜单中选择“,(,全部,)”,选项，即可恢复全部数据的显示。,(2),若要取消对所有列所做的筛选操作结果，选择“数据,|,筛选,|,全部显示”命令。,3,建立复杂条件的“高级筛选”,“高级筛选”与“自动筛选”的区别如下所示。,(1)“,高级筛选”是在多个字段间设置筛选条件，显示的条件表达式有多个，且在表的条件区域中输入条件表达式。条件区域内容需用户建立，这是使用高级筛选的前提。“自动筛选”以单一字段来建立筛选条件，不需建立条件区域。,(2)“,自动筛选”的条件不影响“高级筛选”的运行和结果。两种方法相互独立。,【,例,4.4】,使用高级筛选，完成从“成绩表”工作表中筛选出药学专业男生的学生记录。操作步骤如下所示。,建立条件区域。区域必须包括数据清单的所有的或与筛选相关的字段名。,条件区域建立过程：先在成绩表数据清单上方插入三行空行，首行输入所有的或在筛选中必要的字段名：性别和专业。第二行输入筛选条件，即：性别字段值：“男”，专业字段值：“药学,”,(,见图,4.8),。,图,4.8,第三行为条件区域与数据清单的分隔线。,单击数据清单内任意一个单元格，选择“数据,|,筛选,|,高级筛选”命令，打开“高级筛选”对话框，在此分别设置数据清单的数据区域、筛选条件区域和定义筛选结果存放位置。分别单击“数据区域”、“条件区域”文本框的“折叠对话框”按钮，可返回到数据清单中，然后用鼠标实际圈划这些区域。此例中，对话框内设置各数据区引用内容如,4.9,所示。,图,4.9,系统默认选择在原有区域内显示筛选结果，若使用系统默认值，则单击“确定”按钮。也可选择将结果存放到其它的位置，以保全原来数据。方法是点选对话框内“方式”区中“将筛选结果复制到其它位置”单选按钮，使“复制到”编辑框呈可修改状态，再单击此编辑框右侧的“折叠对话框”按钮退回到清单中，实际选定存放结果数据的位置。例如，本例图中，定义将筛选结果存入本工作表的数据清单下方，以,A,17为起始地址的数据区中。单击“确定”按钮，筛选结果如图,4.10,所示。,图,4.10,此例是对“性别,”,=“,男”和“专业,”,=“,药学”两条件“与”操作的结果。,若要对所给两个条件执行“或”操作，则条件区域需要,4,个空行，将两个条件分别写在不同的行中，以便实现字段间的“或”操作。,4.3.4,分类汇总,建立数据清单后，可依据某个字段将所有的记录分类，把字段值相同的连续记录作为一类，得到每一类的统计信息。对数据清单数据进行分析处理时，运用分类汇总功能，可免去一次次输入公式和调用函数对数据进行求和、求平均、乘积等操作，从而提高工作效率。另外，当进行分类汇总之后，还可对清单进行分级显示。,1,创建分类汇总,【,例,4.4】,对学生成绩簿的成绩表,(,见图,4.11),分类汇总显示整个情况，并统计各专业男女学生数。操作步骤如下所示。,图,4.11,以字段“性别”为标准对数据清单进行排序。,选定经排序后的清单的任一单元格，选择“数据,|,分类汇总”命令，打开对话框，设置相关参数,(,图,4.12,，指定分类字段为“性别”,),；对该分类字段中相同数据对应的记录，统计它们性别字段的总和，以“计数”作为汇总方式。,单击“确定”按钮得出结果。,图,4.12,2,创建嵌套分类汇总,在例,4.4,中，若在“性别”汇总的前提下，再按性别做进一步的分类汇总，则需要创建嵌套分类汇总。操作步骤如下所示。,(1),首先对源数据清单按“性别”、“专业”两个字段进行排序，其中“性别”作为排序的主关键字，“专业”作为次要关键字。,(2),然后按前面的分类汇总过程，先对“性别”字段进行第一次分类汇总。,(3),再选择字段“专业”，进行第二次的分类汇总，即选择“数据,|,分类汇总”命令，打开对话框。对第二次分类汇总设置相应参数,(,见图,4.13),。注意，将系统默认设置选取“替换当前分类汇总”复选框，改为取消选择。单击“确定”按钮，结果如图,4.14,所示。,图,4.13,图,4.14,3.,分类汇,总删除,选择“数据,分类汇总,全部删除”命令，即可删除分类汇总。,4.4,数据保护,4.4.1,工作簿文件的加密与共享时的设置,1.,设置打开工作簿文件权限的密码,用户若不知密码，则不能使用此文件。,2.,文件共享情况下的加密设置,(1),设置文件修改权限密码，用户若不知密码，则不能修改此文件。,(2),把文件设置成只读类型，以保护文件不被修改。,3.,工作簿保护的操作步骤,(1),打开要建立保护的工作簿文件。选择“工具,|,选项”命令，在打开对话框中选择“安全性”选项卡,(,见图,4.14),。,图,4.14,(2),在“打开权限密码”和“修改权限密码”文本框处输入相应的口令字。单击“高级”按钮，打开对话框，可在“选择加密类型”下拉列表框中选择加密类型。,(3),单击“确定”按钮，打开“确认密码”对话框。在“重新输入密码”文本框中重复输入用户设置的密码值后，单击“确定”按钮，完成安全性设置。,4.4.2,保护工作簿,1.,保护工作簿的结构和窗口,(1),什么是保护结构：保护工作簿结构是对工作簿不能进行移动、复制、删除、隐藏、新增工作表及改变表名称等操作。,(2),什么是保护窗口：保护工作簿窗口是对工作簿窗口不能执行移动、隐藏、关闭，以及改变大小等操作。,(3),保护结构和窗口操作：选择“工具,|,保护,|,保护工作簿”命令，在打开对话框,(,见图,4.16),中选择“结构”或者“窗口”复选框。,还可设置“密码”。单击“确定”按钮，启动工作簿保护功能。,2,取消对工作簿的保护,选择“工具,|,保护,|,撤消工作簿保护”命令。,注意：,若在保护工作簿时设有密码，只有在输入密码后方可做取消操作。,图,4.16,图,4.17,4.4.3,保护工作表,可对使用的工作表进行保护。操作是：选择“工具,|,保护,|,保护工作表”命令，在打开对话框,(,见图,4.17),作保护操作。,默认设置锁定全部单元格。可设置对其他用户共享该工作表时的访问权限。也可设置保护工作表密码。单击“确定”按钮后，启动工作表保护功能。,再见,