计算机第四章 2.doc

第四章电子表格软件Excel 20034.1 大纲考点1、Excel的启动与退出，窗口的组成及各部分使用；2、工作簿、工作表、单元格和单元格区域的概念及各自的关系；3、工作簿的创建、打开、保存及关闭；4、创建工作表，各种数据类型的输入；5、公式和函数的使用，运算符的种类，单元格的引用；6、批注的使用；7、工作表的管理；行高和列宽的调整；编辑、移动和复制单元格中的数据；单元格、行、列的插入和删除；查找与替换；8、设置工作表中字体和数字格式，数据的对齐方式，工作表标题设置；边框的设置；9、建立Excel数据库的数据清单，记录单操作；数据的排序和筛选；数据的分类汇总10、图表的建立与编辑11、页面设置,使用分页符,打印预览,打印工作表。12、Word与Excel的协同操作。4.2 知识点一、Excel的功能Excel2003的主要功能为电子表格制作与编辑，它还可实现数据处理与分析及图表制作等。Excel对电子表格的处理能力很强，最大能处理256列、65536行的表格，并且能够自动处理和分析数据，在日常工作中应用较为广泛。当前流行的版本是Excel 2003。二、Excel的基本操作1.启动与退出启动方法如下：方法1：依次单击“开始”“程序”“Microsoft Excel ”命令；方法2：在“我的电脑”或“资源管理器”中双击任意一个XLS文件；方法3：单击“开始”“新建office文档”命令，在出现的对话框中选择“电子表格”；方法4：单击“开始”“运行”，输入Excel的启动命令。退出方法如下：方法1：使用Alt+F4快捷键；方法2：单击窗口右上角的“关闭”按钮；方法3：双击窗口左上角的控制图标或按下Alt+Space打开控制菜单，选择“关闭”；方法4：执行“文件”菜单中的“退出”命令。2.Excel的窗口界面Excel的工作窗口除包含一般窗口所具有的标题栏、菜单栏、工具栏、状态栏以外，还具有编辑栏、工作表编辑区、名称框等特有的部分。(1)名称框的作用显示当前单元格的地址，在名称框中输入一个单元格或单元格区域地址，可实现对单元格或单元格区域的选定；在进行输入公式时，可以在名称框中进行函数的选择。(2)编辑栏的作用显示和编辑当前单元格的内容。Excel的每个单元格中最多可以容纳32767个字符，在工作表编辑区中最多能显示出1024个字符，而在编辑栏内可以显示全部的字符。通过编辑栏，能直接修改当前单元格的内容。输入和编辑公式。通过编辑栏可以输入公式，所有的公式必须以“=”开始。(3)工作表编辑区这是用户的主要工作场所，能完成单元格数据的输入和编辑、查看整个工作表的内容和数据统计分析的结果等。三、Excel基本概念1.工作簿：是指以XLS为扩展名保存在磁盘上的文件，一个工作簿是由若干个工作表组成的，且至少要包含一个工作表。(1)新建工作簿方法：方法1：启动Excel之后，系统会自动创建一个包含3个空工作表（Sheet1、Sheet2和Sheet3）的空白工作簿。方法2：启动Excel后，执行“文件”“新建”命令，在打开的模板选择对话框中选择一个模板来创建具有一定建议内容和格式的工作簿。方法3：利用工具栏中的“新建”按钮新建工作簿，但只能用来建立空白工作薄。(2)打开工作簿方法1：在“我的电脑”或“资源管理器”中直接双击要打开的XLS文件；方法2：启动Excel后，使用“文件”“打开”命令，选择要打开的XLS文件。(3)工作簿的保存使用“文件”“保存”命令；使用Ctrl+S快捷键；使用工具栏上的“保存”按钮。通过“工具”“选项”命令，可以设置文档自动保存的时间间隔，每隔一定的时间会自动保存文档。(4)关闭工作簿使用“文件”“关闭”命令，可以关闭当前工作薄，如果做过修改且未被保存，系统会给出是否保存的提示。如果使用“文件”“退出”命令，则会关闭所有被打开的工作薄，退出Excel系统。2.工作表：由行列交叉组合而成的二维表，是组织和分析数据的地方，是工作薄的基本组成部分。(1)选定工作表选中单个工作表可以直接单击工作表标签，要同时选中多个必须按住Ctrl键进行选择。(2)新建工作表通过“插入”“工作表”命令可以在当前工作表之前插入一张新工作表，如果同时选中了多个工作表，则执行上述命令后会在选中的每一个工作表的前面都插入一个新工作表。插入的新工作表的名字与工作薄中已有工作表的数目有关，与已有工作表的名字无关。假设当前工作薄中现有3个工作表，如果没有对该工作簿进行任何删除工作表的操作，则插入一张新的工作表时，新的工作表的名字为“Sheet4”；如果现在将新插入的工作表“Sheet4”删除，然后再插入一张新的工作表，这时新工作表的名字为“Sheet5”。(3)删除工作表执行“编辑”“删除工作表”命令，可以将当前选中的工作表删除。一个工作薄中必须至少包含一个工作表。(4)工作表的重命名方法1：双击需要改名的工作表的标签，输入新名字。方法2：右击需要改名的工作表的标签，选择“重命名”命令，输入新名字。(5)工作表的移动或复制使用“编辑”“移动或复制工作表”命令，可以在同一个工作簿或不同的工作簿之间完成工作表的移动或复制，如果要将工作表移动或复制到其他工作簿，必须将两个工作簿同时打开。复制工作表时要在对话框中勾选“建立副本”。复制的工作表的名字一般是在原来的名字后面加“（2）”，如在同一个工作簿中复制工作表“Sheet1”后产生的新工作表的名字为“Sheet1（2）”。3.单元格：工作表中行列交叉的部分称为单元格，是最基本的数据处理单元，在一个单元格中可以输入各种类型的数据，例如文本类型、数字类型、日期时间类型、公式和函数等，但是不能插入图片信息。4.批注是用户添加的对单元格中数据的解释性信息，带有批注的单元格右上角有一小红三角，批注可以被修改、删除、复制或移动。使用“编辑”“选择性粘贴”可以只复制或移动单元格的批注、格式等一部分内容。5.相关知识点：(1)一个工作簿由若干个工作表组成，且一个工作簿中必须至少包含一个工作表；工作表只能依附于工作簿而存在，不能作为单独的文件存盘。(2)为了对工作表中的单元格加以区分和方便引用，为每个单元格赋予了一个唯一的编号，称为单元格地址。工作表中的列自左向右以英文字母命名（列标从A到IV），行自上而下以阿拉伯数字命名（行号从1到65536），单元格地址由其所在的行列唯一确定，格式为列标在前，行号在后。(3)一个工作簿中默认含有3个工作表，其默认数目可以通过“工具”“选项”菜单进行修改。当用户建立新的工作簿时，系统将按照设定生成工作表。工作簿中的工作表可以进行增加和删除，也可以对工作表进行改名和调整顺序。需要注意：一个工作簿中至少包含一个可见工作表。四、单元格中数据的输入格式数据的输入可以在单元格内直接进行，输入完毕按回车即可；除此以外，也可以在编辑栏内输入数据。Excel中的数据分为文本型、数值型和日期时间型三种。1.文本型数据一般文本可选择一定的输入法，直接向单元格中输入。文本型数据在单元格内默认为左对齐。纯数字字符串、前置符号的字符串等特殊类型的文本，在前面加单引号输入。如要输入文本型的“250002”，则输入“250002”；要输入“=3+5”，则输入“=3+5 ”，也可以输入“ =“=3+5” ”。2.数值型数据数值型数据中可包括09、号、小数点、千分位、/、%、E、e 等，一般的数值可在单元格中直接输入，数值型数据在单元格内默认为右对齐。特殊数值型数据的输入方式：(1)分数：使用前置数字加一个空格的形式输入。如：要输入“”，则输入“0 2/3”。若直接输入“2/3”，则单元格内显示“2月3日”，系统认为是日期型数据。要输入“”，则输入“1 2/3”即可；要输入“”，则输入“-1 2/3”。(2)负数：可在前面加负号，也可以将数字放在括号中。如：要输入“-8”，则可输入“-8” 或者“（8）”。但输入“（8）”并不表示数值信息，它是一个文本串。(3)带千分号的数字：在输入时从右向左每三位使用一个千分位符号。如：要使用千分位的形式输入“120020”，则输入“120,020”即可。如果没有遵守“三位一组”的划分规定，则输入后系统将认为是文本型数据。3.日期时间型数据这种数据输入时要严格遵守一定的格式，否则会被作为其他类型的数据处理。日期时间型数据在单元格内默认为右对齐。日期格式：yyyy/mm/dd、yyyy-mm-dd。时间格式：hh:mm:ss。时间数据可使用12小时制和24小时制两种方式。若使用12小时制时，时间后面可加空格am 或pm来指定上下午时间，如果不指定，则默认为是上午时间。输入日期时，如果只输入月份和天数，则默认年份为系统当前年。利用“Ctrl+；”和“Ctrl+shift+:”可分别快速地在当前单元格内插入当前日期和时间。4.数据的自动填充如果要向工作表中输入大量有规律的数据，如等差序列、等比数列等，可使用Excel提供的“自动填充”功能。实现自动填充数据的方法：(1) 先在当前单元格内输入第一个数据，然后按住当前单元格右下角的填充柄进行填充。在用这种方法对不同类型的数据进行填充时，会得到不同的填充结果，具体情形如表4.1所示。表4.1 单元格数据的自动填充当前单元格数据类型直接拖动填充柄的结果按住Ctrl键拖动填充柄的结果数值型相同序列差为1的等差序列日期型天数相差1的等差序列相同序列时间型相差1小时的等差序列相同序列文本型具备增减性变化的文本序列相同序列不具备增减性相同序列相同序列注：具备增减性的文本指的是类似于“甲、乙、丙、丁”、“星期一、星期二、星期三”等可以发生变化的文本。实际上，文本是否可以发生变化可以通过打开“工具”菜单中的“选项”命令，在出现的对话框中选择“自定义序列”选项卡，然后在其中完成序列的新建、修改和删除来实现。自动填充序列可以在行上进行，也可以在列上进行。如果序列是变化的，向右和向下填充为递增序列，向左和向上填充为递减序列。(2)使用编辑菜单中的“填充”“序列”命令，可以在行或列上得到任意的等差、等比和日期序列。(3)选中连续两个单元格，然后按住它们共同的填充柄进行填充，可以得到以这两个单元格中数据的差为公差的等差数列。五、Excel公式与函数公式是指将运算对象通过运算符连接起来、能经过计算得到结果的有意义的式子。函数是系统规定的完成特定运算的特殊公式。公式中参与运算的对象可以是普通数据、单元格地址和函数。1.运算符Excel中的运算符分为算术运算符（、*、/、%、）、比较运算符（=、=、）、文本运算符（&）和单元格引用符（：、，和空格）。(1)算术运算：用于完成基本的数学运算。需要注意的是在Excel中只提供了乘方运算，如要计算5的3次方，则使用公式“=53”；没有提供开方运算，要使用乘方运算实现开方。如要计算的值，则可以使用公式“=5（1/3）”。(2)比较运算：用于对两个值进行大小关系比较，其结果只有两种可能，要么为真（True），要么为假（False）。如公式“=38”的结果为False。在单元格中，比较运算的结果默认居中显示。(3)文本运算：用于将两个文本数据连接成一个文本数据。如A1单元格中的数据为“硕士研究生”，B1单元格中的数据为“入学考试”，则公式“A1&B1”的结果就是“硕士研究生入学考试”。注意：&运算符只能用于连接文本数据，不能连接其他类型的数据。如果在公式中参与连接运算的不是单元格地址，而是直接用普通文本连接，则文本要放在引号内。如公式“=“硕士研究生”&“入学考试”的结果也是“硕士研究生入学考试”。(4)单元格引用：用于合并多个单元格区域。冒号（：）用于连接两个单元格，表示一个单元格区域。如“A1:C5”表示的是以A1为左上角，C5为右下角的矩形区域内的单元格，包含的单元格的个数为15个。注意：“A:A”表示第A列，“1:1”表示第1行。空格和逗号（，）用于连接两个单元格区域，表示一个大的单元格区域。空格连接表示两个区域的交集，逗号连接表示两个区域的并集。如：“A1:C5 B2:D3”表示A1:C5和B2:D3的交集，即B2:C3，包含4个单元格；“A1:C5,B2:D3”表示A1:C5和B2:D3的并集，包含17个单元格。2.公式的输入：可以在编辑栏或单元格内直接输入，如果输入函数，也可以在名称框中进行选择或执行“插入”菜单中的“函数”命令。公式输入以后，系统会自动计算结果并显示出来，并且会随着参与运算的单元格中数据的变化而自动更新。3.使用公式时，要注意以下几个问题：(1)所有的公式都必须以等号(=)开头；(2)输入公式时必须在半角英文状态下；(3)如果有单元格中的数据参与运算，应在公式中使用单元格地址而不是其中的具体数值。如果公式使用不当，单元格内会显示错误信息。常见的公式错误信息如表4.2所示。表4.2 常见公式错误信息及含义错误信息含义!单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值VALUE!使用错误的参数或运算对象类型，或者公式自动更正功能不能更正公式DIV/0!公式被零除N/A函数或公式中没有可用数值REF!删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中NUM！公式或函数中某个数字有问题#NULL！使用了不正确的区域运算符或不正确的单元格引用，如试图为两个并不相交的区域指定交叉点4.相对引用与绝对引用在公式中使用单元格地址称为单元格引用。单元格引用有相对引用、绝对引用和混合引用三种方式。当公式被复制或移动到另一个单元格时，其中的单元格地址会随着公式位置的变化而发生相应的改变，这种称为相对引用，也是默认的引用方式。的值，则可以使用公式“=5（1/3）”。(2)比较运算：用于对两个值进行大小关系比较，其结果只有两种可能，要么为真（True），要么为假（False）。如公式“=38”的结果为False。在单元格中，比较运算的结果默认居中显示。(3)文本运算：用于将两个文本数据连接成一个文本数据。如A1单元格中的数据为“硕士研究生”，B1单元格中的数据为“入学考试”，则公式“A1&B1”的结果就是“硕士研究生入学考试”。注意：&运算符只能用于连接文本数据，不能连接其他类型的数据。如果在公式中参与连接运算的不是单元格地址，而是直接用普通文本连接，则文本要放在引号内。如公式“=“硕士研究生”&“入学考试”的结果也是“硕士研究生入学考试”。(4)单元格引用：用于合并多个单元格区域。冒号（：）用于连接两个单元格，表示一个单元格区域。如“A1:C5”表示的是以A1为左上角，C5为右下角的矩形区域内的单元格，包含的单元格的个数为15个。注意：“A:A”表示第A列，“1:1”表示第1行。空格和逗号（，）用于连接两个单元格区域，表示一个大的单元格区域。空格连接表示两个区域的交集，逗号连接表示两个区域的并集。如：“A1:C5 B2:D3”表示A1:C5和B2:D3的交集，即B2:C3，包含4个单元格；“A1:C5,B2:D3”表示A1:C5和B2:D3的并集，包含17个单元格。2.公式的输入：可以在编辑栏或单元格内直接输入，如果输入函数，也可以在名称框中进行选择或执行“插入”菜单中的“函数”命令。公式输入以后，系统会自动计算结果并显示出来，并且会随着参与运算的单元格中数据的变化而自动更新。3.使用公式时，要注意以下几个问题：(1)所有的公式都必须以等号(=)开头；(2)输入公式时必须在半角英文状态下；(3)如果有单元格中的数据参与运算，应在公式中使用单元格地址而不是其中的具体数值。如果公式使用不当，单元格内会显示错误信息。常见的公式错误信息如表4.2所示。表4.2 常见公式错误信息及含义错误信息含义!单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值VALUE!使用错误的参数或运算对象类型，或者公式自动更正功能不能更正公式DIV/0!公式被零除N/A函数或公式中没有可用数值REF!删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中NUM！公式或函数中某个数字有问题#NULL！使用了不正确的区域运算符或不正确的单元格引用，如试图为两个并不相交的区域指定交叉点4.相对引用与绝对引用在公式中使用单元格地址称为单元格引用。单元格引用有相对引用、绝对引用和混合引用三种方式。当公式被复制或移动到另一个单元格时，其中的单元格地址会随着公式位置的变化而发生相应的改变，这种称为相对引用，也是默认的引用方式。绝对引用时公式中的单元格地址在进行公式移动或复制时，不会发生任何变化。绝对引用时在引用的单元格地址的行号和列标前都加上一个“”符号。混合引用是指行和列中一个为相对引用，一个为绝对引用。在一个单元格内还可通过“工作簿名工作表名!单元格地址”的形式引用其它工作簿内的工作表中的单元格地址，如Book2Sheet1!E3，这称之为“三维地址引用”。5.函数Excel中提供了9类函数，在公式中引用函数的格式为：函数名（参数）。函数可以在公式中直接使用，输入方法可以使用“插入”“函数”命令、在名称框内选择所需的函数和在单元格内直接输入函数的方式。常见的函数如表4.3所示。表4.3 常见的函数及其使用函数名功能示例Sum求和=Sum（3,2,5） 结果为10Average求平均值=Average（3,2,5） 结果为3.3333Max求最大值=Max（3,2,5） 结果为5Min求最小值=Min（3,2,5） 结果为2六、工作表及单元格格式调整1.调整列宽方法1：用鼠标拖动列标的右边界进行调整；方法2：用鼠标双击列标右边界，可以将列宽自动调整为该列中最大数据的宽度；方法3：使用“格式”“列”“列宽”命令进行列宽的精确调整；方法4：使用“编辑”“选择性粘贴”命令可以进行列宽的复制。2.调整行高行高的调整操作类似于对列宽的调整，但行高不能被复制。3.单元格数据的修改对单元格内的数据进行修改，可以直接双击单元格，出现光标后进行修改或直接在编辑栏内修改。若选中单元格后直接输入新的内容将会覆盖原有内容。4.移动或复制单元格数据选中一个单元格或单元格区域，执行复制或剪切命令，选中的部分周围出现虚线框，然后选择目标位置后执行粘贴即可完成单元格中数据的复制或移动。如果只复制或移动单元格的格式、批注、列宽等部分特征，要使用“编辑”“选择性粘贴”命令。5.插入行（列）选中一行（列）或几行（列），单击“插入”“行（列）”命令，则在选择行（列）的上方（左侧）插入一行（列）或几行（列）。6.插入单元格选中一个单元格区域，单击“插入”“单元格”命令，然后在出现的对话框中选择“活动单元格右移”或“活动单元格下移”之后，则会相应的插入与选定单元格区域单元格数目相同的单元格。7.单元格的删除使用“编辑”“删除”命令，可以删除选中的单元格或整行（列）。8.单元格的合并选中一个单元格区域，然后使用格式工具栏中的“合并及居中”按钮可将单元格内的所有单元格合并为一个单元格，并使其中的内容居中对齐。该操作通常用于表格标题的设置。注意，Excel中不能完成单元格的拆分。9.工作表的格式化使用“格式”“单元格”命令，可以在出现的单元格格式对话框中完成数字格式、对齐方式、字体格式、边框和底纹格式的设置，也可以通过格式工具栏中相应的工具按钮来实现。七、数据清单Excel中的数据清单类似于数据库中的二维表，在数据清单中的每行表示一条记录，每列表示一个字段。可以对数据清单中的数据记录进行查找、排序、汇总等类似于数据库的操作。1.数据清单中的规则一个数据清单占用一个工作表，其中不能出现空行和空列，每一列的数据类型应相同。第一行的数据类型必须为文本型数据，是二维表的字段名称。2.记录单操作使用“数据”“记录单”命令，在出现的对话框中，可以逐条查看数据清单中的每一条记录，还可以完成数据记录的新建、删除、修改及条件查看等操作。其中新建操作是在记录单的末尾追加记录，而不能插入记录。删除操作删除记录后不能恢复。3.排序排序就是将数据清单中的记录按照一个或多个关键字进行顺序的重新排列，排序的方式可以为升序或降序，且不同的关键字可以使用不同的排序方式。排序的方法：(1)若排序的关键字只有一个，则可以将关键字所在列中任意一个单元格设置为当前单元格，然后单击工具栏中的“升序”或“降序”按钮即可；(2)若排序的关键字有多个，则可以把数据清单中的任意一个单元格设置为当前单元格，然后使用“数据”“排序”命令，在打开的对话框中可以分别设置主要、次要及第三关键字及其排序方式，然后确定即可；如果选中“有标题行”，则数据清单中的第一行不参与排序，否则，第一行也作为一条记录参与数据排序。4.筛选筛选是从数据清单中找出并显示满足指定条件的记录，不符合条件的记录只是被隐藏起来，而不是从数据清单中删除。Excel中提供了自动筛选和高级筛选二种形式，筛选使用“数据”“筛选”命令进行。使用自动筛选之后，数据清单中每个列的名称后面都会出现一个下拉箭头，可以通过选择其中的“自定义”命令设置各列的筛选条件，最后显示出来的数据记录是满足所有列的筛选条件的记录。5.分类汇总分类汇总是指把数据清单中的数据记录先根据一列分类，然后再分别对每一类进行汇总统计。汇总的方式有求和、计数、平均值、最值等。选择“数据”菜单中的“分类汇总”命令即可进行分类汇总。进行分类汇总之前，必须先进行排序，否则无法实现预期效果。排序的依据与分类的依据相同，即按谁分类就先按谁对数据进行排序。