1、第 4 章 电子表格Excel,主要内容及重点,本章作业,本章实验,4.1 Excel 2003的基本知识 4.2 工作薄操作4.3 数据录入 4.4 工作表操作4.5 使用公式和函数4.6 数据管理和分析 4.7 数据图表化,本章主要内容,本章重点：工作表的建立、编辑和格式化，图表制作，数据管理和分析,4.1 Excel 2003的基本知识,Office家族成员之一电子表格软件,4.1.0 Excel 2003的功能,返回,列号,名字框,行号,当前单元格,工作表标签,65536,3,256,Sheet1!B3,Excel 2003的工作窗口,4.1.1 Excel 2003的启动、退出和工作
2、窗口,编辑栏,显示和选定单元格、图表项、绘图对象等,取消操作,确定操作,“编辑公式”状态,Enter,Tab,Esc,或,或,编辑输入区,4.1.2 工作簿、工作表与单元格,工作簿是计算和存储数据的文件，也即Excel是以工作薄为单位处理和存储数据，其扩展名.xls。新建的第一个工作薄的默认名：book1.xls。每一个工作簿可由一个或多个工作表组成，在默认的情况下是由3个工作表组成的。,工作簿,工作表,一个工作表最多有65 536行、256列。行号：165536 列标：AIV 工作表由工作表标签区别，如sheet1、sheet2、Sheet3,工作表中行和列的交叉部分称为单元格，是存放数据的
3、最小单元。 单元格的地址表示形式： 列标+行号 如： A5、 D8 为了区分不同工作表的单元格，需要在地址前加工作表名称，如Sheet1! B3,单元格,返回,Excel工作薄操作的操作包括创建、打开及保存，工作薄窗口的拆分与合并，多窗口操作以及冻结窗口等。4.2.1 创建、打开和保存工作薄1.创建新工作薄 创建新工作薄的方法很多，可以从Windows环境直接创建，也可以在Excel软件环境新建一个工作薄，还可以利用模板创建一个具有一定格式的新工作薄。,4.2工作薄操作,返回,2.简单数据输入 新的空白工作表创建以后，只需像填表一样将数据输入即可，每个数据占用一个单元格。输入确认无误后按“En
4、ter”键或单击“”按钮。若输入有误，可使用“Backspace”或“Delete”键删除，重新输入。 当数据超出单元格宽度时，单元格自动扩展并暂时占用其右侧单元格，若右侧无内容，则继续占用，若有内容，则截断显示。,4.2工作薄操作,3.修改输入的内容 除使用“Backspace”或“Delete”键修改输入数据外。还有如下方法可修改输入内突： （1）单击单元格，直接输入覆盖（2）双击单元格或先单击单元格再按“F2”键，进入此单元格的数据编辑状态，此时可像在Word中一样用鼠标选中需修改的数据并用新数据覆盖。（3）双击单元格后在“编辑栏”修改。,4.2工作薄操作,4单元格/工作表的选定,5.行
5、高、列宽的调整 方法一：鼠标拖拽方法二：菜单精确调整“菜单|行（列）”6.保存工作薄 菜单；工具栏；“快捷键Ctrl+S”。7.打开工作薄方法一：双击该文件，打开文件同时启动Excel方法二：单击“文件”菜单下近期编辑过的文件方法三：菜单；工具栏；“快捷键Ctrl+O”,4.2工作薄操作,为解决在输入或浏览数据时屏幕显示不全的问题，可以拆分和合并工作薄。1.工作薄窗口的拆分 确定拆分位置（选定某单元格）“窗口|拆分”，即可拆分为4个窗口和4个滚动条。 也可以即将鼠标置于“拆分条”处，按住左键拖拽或双击，将工作薄分成上下或左右两个窗口2.工作薄窗口的合并“窗口|取消拆分”；将”拆分条“拖至原位置
6、或双击。,4.2.2 工作薄窗口的拆分与合并,不仅可以使用拆分的方法完成大型表格的输入或浏览，还可以使用“冻结窗格”和隐藏行列的方法来完成。1.冻结窗格和取消冻结窗格 1）冻结窗格确定冻结点（选定某单元格，该单元格以上所有行和以左所有列将被冻结）“窗口|冻结窗格” 。 2.取消冻结窗格“窗口|取消冻结窗格” 。,4.2.3 窗口的冻结与隐藏,2.隐藏和显示行列 1）隐藏行列隐藏行列可以将暂时不使用的多个行和列隐藏起来，以便为有限的屏幕窗口留出更多的空间浏览或输入其他数据 。方法一：选中要隐藏的行（列），“格式|行（列）|隐藏” ；方法二：选中要隐藏的行（列），用鼠标右击弹出快捷菜单 ；方法三：
7、选中要隐藏的行（列），用鼠标直接拖拽隐藏 ；2）取消隐藏行列方法一：选中“隐藏线”两侧的行（列），“格式|行（列）|取消隐藏” ；方法二：选中“隐藏线”两侧的行（列），用鼠标右击弹出快捷菜单 。,4.2.3 窗口的冻结与隐藏,无论是拆分窗口还是冻结窗口都没有产生新的窗口，而新建窗口则产生了新的窗口，方法“窗口|新建窗口”即可新建一个名为“原工作薄名.xls：2”的窗口。 对于多窗口，关闭一个窗口时，其他窗口不受影响。,4.2.4 多窗口操作,4.3数据录入,输入数据 直接输入 快速输入数据 自动填充数据 从外部导入数据计算数据,返回,Excel支持各类数据，如字符型、数值型、日期型数据以及公式
8、或函数等，这些数据既可用特殊的方法直接输入，也可以通过设置“单元格格式”使其自动被识别或转换。4.3.1各类型数据的录入,直接输入数据包括： 文字。文本型数据指汉字、英文字母、非数值型数据和一些符号数据。Excel能自动识别文本型数据，并在单元格中左对齐。如果要将一个纯数字作为文字输入，数字前要加单引号“ ”，对于已经输入的数值型数据可转换为文本型。还可在输入数据之前将单元格格式设置成文本型。 数值。默认情况下对纯数字自动识别为数字型且右对齐，Excel还可将+、-、E、/、%、.和空格作为数值类型的一部分。利用这些符号可以输入小数、分数、百分数、科学计数法表示的数以及带千位分隔符的数。如果数
9、字以“+”或数字“0”开头，“+”和“0”会自动省略；分数的输入应先输入“整数或0”和空格。还可以输入货币单位符号，,将数值表示成货币型数据。可以利用“单元格格式”对话框或“格式”工具栏中数值格式设置按钮进行设置。注意Excel数据输入与显示并不总是相同，当输入的数字太长（超过单元格的列宽或超过15位），以科学计数表示1.23E+04，当科学计数形式仍然超过单元格列宽时，屏幕会出现“#”，此时加大单元格列宽即可全部显示数据。注意，Excel计算时以输入数据而不是显示数值为准。 日期和时间。日期输入可用“/”或“”分隔符，如：2006/10/08，2006-10-08；时间输入用冒号“：”分隔，
10、如：21：56：15。两者之间用空格分隔。hh：mm (am/pm)，其中am/pm与分钟之间有空格，输入当天日期与时间分别是Ctrl+；和Ctrl+Shift+；。,返回,4.3.2快速输入数据,“记忆式”输入,“选择列表”输入（仅对文本数据有效）,“选择列表”命令，或者按“Alt+”键,返回,Excel提供了几种辅助输入方法以提高输入效率，如自动填充数据以及设置有效数据等。当输入的字与同一列中已输入的内容相匹配时，系统将自动填写其他字符；为使同一列输入的内容一致，可用选择列表输入。,1.自动填充数据,自动填充功能是Excel的一大特色，将鼠标置于被选定单元格右下角的黑色小方块（称为自动填充
11、柄），按住鼠标左键拖拽则Excel会根据已选定单元格中的数据类型自动填充。自动填充数据分三种情况：复制数据。选中一个单元格，直接拖曳，便会产生相同数据，如果不是相同数据，则需要在拖曳的同时按Ctrl 键填充序列数据。可通过“编辑/填充/序列”命令，在“序列”对话框中进行序列有关内容的选择填充用户自定义序列数据,返回,从外部导入数据,选择“数据获取外部数据”命令，可导入其他数据库（如Access、FoxPro、Lotus123等）产生的文件和文本文件。,返回,2.输入有效数据,可以通过设置数据有效性，来减少输错率，提高输入效率。此方法还可从一大型表格中找出不符合设定要求的数据，并做上标记。1）设
12、置数据有效性选定要设置数据有效性的单元格，选择“数据有效性”命令。在设置了数据有效性的单元格内输入数据时，若输入不符合设定要求的数据，Excel会给出相应的提示。2）用数据有效性审核数据对于已输入的数据，还可用此方法进行审核并圈释出错的数据，具体方法：“视图|工具栏|公式审核”命令。,返回,4.3.3编辑数据,单元格数据的编辑 单元格内容的清除和删除、移动和复制 数据清除仅对数据，数据删除针对单元格(含数据) 粘贴分常规“粘贴”和“选择性粘贴”两种。,4.3.4添加批注,1.添加批注 选择要添加批注的单元格，“插入|批注”2.查看批注 只需将鼠标悬停在有批注的文字的单元格上，批注会自动显示；要
13、批注一直显示：“右击显示/隐藏批注”或“视图|批注”。3.编辑批注 批注只能逐个修改4.删除批注,4.3.5 查找和替换,查找和替换是编辑处理过程中经常要执行的操作，Excel中除了可查找和替换文字外，还可以查找和替换公式。可以在一张工作表内的所有单元格中，在工作表的一个选定区中，在一张工作表或若干张工作表的当前选定区域中用另一串字符替换现有的字符；也可寻找和选定具有同类内容的单元格。,返回,4.4 工作表操作,前面介绍了如何向单元格中输入数据以及如何编辑数据，其操作对象主要是表格中的数据。下面介绍工作薄中单元格、工作表以及它们的格式设置等内容。4.4.1 单元格、行、列的编辑 可以添加或删除
14、单元格、一行、一列4.4.2 使用多个工作表 工作表的插入、移动、复制（包括不同工作薄）、删除（在要删除工作任何位置单击，选择“编辑|删除工作表”；用快捷菜单）、重命名、隐藏（格式|工作薄|隐藏）、改变工作表目录（“工具|选项|常规” ）、保护工作薄（仅结构和窗口）和工作表（数据)(工具|保护）、设置和取消打开文件密码（“工具|选项|安全性”；“文件|另存为|工具|常规选项或安全措施选项）等。,返回,4.4.3工作表格式设置,1.自动换行和合并单元格2.设置字符格式 用工具栏或菜单设置3.设置对其方式4.设置单元格背景5.设置表格线（格式菜单设置）6.使用格式刷7.调整列宽和行高8.自动套用格
15、式(选定单元格区域,“格式|自动套用”)9.使用条件格式1)设置条件格式：选定单元格,“格式|条件格式”)2)删除条件格式：选定单元格,“格式|条件格式”),（1）设置单元格格式,选择”格式单元格”命令或“格式”工具栏,（2）设置工作表格式,设置列宽和行高选择“格式列”、“格式行”菜单自动套用格式Excel提供了许多种漂亮专业的表格自动套用格式，可以快速实现工作表格式化。这通过“格式|自动套用格式”命令来实现。 使用条件格式条件格式可以使数据在满足不同的条件时，显示不同的格式。 “格式|条件格式”,条件格式,对学生成绩表完成进一步的格式化操作：设置标题行高为27,姓名列宽10,其它列宽为最合适
16、的列宽；将不及格的成绩设置成红色、加粗倾斜、浅灰色底纹，90分以上的成绩设置成蓝色，加双下划线。,4.4.4 工作表的打印,完成数据输入及格式设置后，还需将工作表打印出来。打印工作表时，要进行页面设置。在打印大型工作表时，还需在每一页上打印行标题和列标题。1.页面设置“文件|页面设置”（1）页面设置（2）设置页边距（3）设置页眉和页脚（4）设置工作表,4.4.4 工作表的打印,Excel 2003工作表的打印根据打印内容有3种情况：选定区域(最常用）选定工作表整个工作簿,返回,4.5 使用公式和函数,Excel的公式由运算符、数值、字符串、变量和函数组成。公式中可以引用同一个表、不同表、不同工
17、作薄中的工作表中的单元格。公式必须以等号“=”开始1.Excel中的运算符包括算术、比较、文字和引用运算符,返回,分析和处理Excel工作表的数据，离不开公式和函数。4.5.1 使用公式,引用运算符：引用运算符可以将单元格区域合并起来进行计算,四类运算符的优先级从高到低依次为：“引用运算符”、“算术运算符”、“文本运算符”、“关系运算符”，当优先级相同时，自左向右进行计算。,2.公式输入,公式一般可以直接输入：选择要输入公式的单元格，直接在单元格或编辑栏输入“=B3+B4”，然后按回车或单击编辑栏“”按钮。 在公式使用时，有可能要涉及到单元格引用，即公式中使用了其他单元格的地址名。 3.移动和
18、复制公式 当移动公式时，公式内的单元格引用不会更改；而当复制公式时，单元格引用将根据所引用类型而变化。公式的复制操作与单元格的操作类似。 根据引用类型不同，单元格引用分为相对引用、绝对引用、混合引用和三维引用四种。,例：有“学生成绩表.xls”,请计算每位学生的总分,）,相对引用。当公式在复制或填入到新位置时，公式不变，单元格地址随着位置的不同而变化，它是Excel 默认的引用方式，如：B1，A2：C4绝对引用。指公式复制或填入到新位置时，单元格地址保持不变。设置时只需在行号和列号前加“$”符号，如$B$1混合引用。指在一个单元格地址中，既有相对引用又有绝对引用，如$B1或B$1。$B1是列不
19、变，行变化；B$1是列变化，行不变。三维引用。指引用同一工作薄的其他工作表的单元格地址或其他工作薄的工作表中的单元格地址。引用格式为“工作表名称！单元格地址”或“工作薄名称工作表名称！单元格地址”。,单元格引用方式举例,例：设公式在 F2 单元格，公式：=D2+E2 其中D2、E2为相对引用公式：=$D$2+$E$2 其中D2、E2为绝对引用公式：=$D2+$E2 其中D2、E2为混合引用注意：该公式复制到其他单元格，各种引用的区别就会显现。,“绝对引用”示例,4.5.2 使用函数,函数是预先定义好的公式，它由函数名、括号及括号内的参数组成。其中参数可以是常量、单元格、单元格区域、公式及其他函
20、数。多个参数之间用“，”分隔。 函数的语法形式： 函数名称（参数1，参数2，） 1.函数输入有两种方法： 直接输入法和插入函数法（更为常用） 插入函数法操作步骤： 选要插入函数的单元格；从工具栏或“插入|函数”打开“插入函数”对话框；选择函数类型；在“函数参数”对话框中输入常量、单元格引用区域；确定即可。,下图为用函数计算示例,4.5.2 使用函数,在Excel中利用公式或函数进行计算时，还应注意以下几点：（1）在公式或函数中不允许出现空格（2）公式或函数中使用的标点或其他符号必须为英文半角形式（3）注意单元格地址的引用方式 2.常用函数简介 SUM、AVERAGE、MAX、MIN、COUNT
21、、COUNTIF、IF、,4.5.3 综合案例：员工业绩评估表,参见教材P166-167。4.5.4 出错检查,返回,4.6数据管理和分析,建立电子表格的主要目的是管理和使用其中的数据。Excel不仅具备数据的计算能力。而且还具有一定的数据管理功能。它能对数据进行排序、筛选、分类汇总以及合并计算等操作。要对工作表中的数据进行管理，首先要使工作表中的数据组织符合一定的形式。这种具有特殊数据组织形式的工作表叫做数据清单（或称数据列表）。,在学生成绩表中，如果数据行较多，查看、修改、更新某学生的数据。要找到某数据行就有难度了。但记录单提供了这类操作的方便！,返回,4.6.1数据清单,数据清单相当于一
22、张完整的报表，这张报表中的数据来自于整个工作表中的数据。使用数据清单可以进行简单的数据库操作，如排序、查询等。Excel将数据清单作为一个数据库来看待。1.数据清单的基本概念数据清单，又称为数据列表。是Excel工作表中单元格构成的矩形区域，即一张二维表。它与工作表的不同之处在于：数据清单中的每一行称为记录，每一列称为字段，第一行为表头数据清单中不允许有空行或空列；不能有数据完全相同的两行记录；字段名必须唯一，每一字段的数据类型必须相同。同列数据的类型、格式必须相同；每个单元格的数据前不要插入多余的空格。一般一个工作表内保存一个数据清单和一个清单标题。,2. 建立数据清单,方法一：直接向表中输
23、入数据清单的数据。 可直接输入数据清单的数据，但要注意数据清单在结构上的逻辑性，并且其中的数据不能使杂乱无章的，及必须符合数据清单数据的要求。 方法二：选择“数据”菜单，单击“记录单”菜单项，将会打开“成绩表”对话框。利用“记录单”可以添加、逐条或按条件浏览、编辑、删除记录，也可以还原记录内容 3.导入数据 如果数据清单中要使用的数据已经存在于数据库或其他格式的数据文件中，可以直接导入，方法“数据” “导入外部数据”“导入数据”菜单项打开“选取数据源”对话框，选择“文件类型”和“文件名”，单击“打开”按钮，设置相关属性，单击“确定”按钮，即可导入。,（1）使用记录单修改、删除或增加记录,“删除
24、”将删除工作中与记录单内容相对应的数据行,“上一条”、“下一条”可进行逐行数据查看。,选择“数据”|“记录单”菜单命令，Excel就会显示工作表的记录单！,“新建”可在数据表最后一行的下面添加一行,（2）用数据单进行条件查询 单击记录单中的“条件”就能进行条件查询！,条件！,查看姓王的学生,查找系别为自动控制数学大于或等于90分的学生,返回,4.6.2数据筛选,1、概念数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行 。2、设置筛选条件 用于设置筛选条件的运算符分为两类，一类是比较运算符，另一类是通配符。当筛选的条件多于一个时可以用“与”或“或”连接。3、E
25、xcel的数据筛选方式自动筛选 (数据|筛选|自动筛选）高级筛选 （数据|筛选|高级筛选）,自动筛选,筛选学生成绩表中数学系计算机成绩在8090分（包括80和90分）之间的所有记录。,高级筛选,使用“自动筛选”功能，对一列数据最多可以应用两个条件。如果要对一列数据应用三个或更多的条件，可使用高级筛选功能。高级筛选需在数据列表以外的区域开辟一条件区域并输入筛选条件。要得到正确的高级筛选结果，最重要的是建立正确的条件区域。在条件区域设置条件。条件局域至少两行，且首行为与数据列表相应列标题精确匹配的列标题，即数据清单中的字段名。在条件区域中，同一行的条件值之间是“与”的关系，不同行的条件值之间是“或
26、”的关系。确保条件区域与表格源之间数据至少有一个空行或空列。,高级筛选,要筛选出数学系总分200且计算机成绩80或自动控制系总分200的所有记录,4.6.3数据排序,将所有记录按照大小关系进行排列的操作称为排序。作用：以便于比较、查找、分类。除数值型数据可以排序外，文本、日期以及逻辑等类型的数据也可以进行排序。 排序可分为简单排序和复杂排序两种。排序标准：,4.6.3数据排序,1.简单排序（一个关键字） 方法一：使用简单自动筛选排序。选择“数据”“筛选”“自动筛选”命令。单击单元格右侧的下三角按钮，单击“降序(升序)排列。 方法二：单关键字排序。先确定排序的关键字，在Excel“常用”工具栏单
27、击 、 按钮,或 “数据/排序”命令即可。,4.6.3数据排序,2. 复杂排序（两个以上关键字，最多可使用三个关键字）“数据/排序”命令,“排序”对话框,对学生成绩表排序，首先按男女生排列，然后按“总分”降序排列，总分相同时再按“计算机”成绩降序排列。,分类汇总 是数据分析的一种手段，对数据清单按某个字段进行分类，将字段值相同的连续记录作为一类，进行求和、平均、计数等汇总运算。前提对分类字段排序，使同类数据连续排列。分类汇总有三个基本要素：分类字段；汇总方式；汇总项。分类汇总分为： 简单汇总（指对数据清单的某个字段仅统一做一种方式的汇总）嵌套汇总（指对同一个字段进行多种方式的汇总）,前提：对分
28、类字段排序,4.6.4 分类汇总,求学生成绩表中各系学生各门课程的平均成绩 。,步骤：1）首先对“部门”排序2）选择”数据分类汇总“命令,简单汇总,在上例求各系学生各门课程的平均成绩的基础上再统计各系别人数。 步骤：先按上例的方法求平均值，再在平均值汇总的基础上计数,嵌套汇总,注意：“替换当前分类汇总”复选框不能选中。,4.6.5 合并数据,如果要对多个字段进行分类汇总，需要需要利用数据透视表。合并计算用于对同一工作薄中多个工作表甚至多个工作薄中多个工作表中的数据进行统计计算。要想合并计算数据，首先必须为汇总信息定义一个目标区域，用来显示结果信息。此目标区域可位于与源数据相同的工作表上，或在另
29、一个工作表上或工作薄内。其次，选择要合并计算的数据源，此数据源可以来单个工作表、多个工作表或多个工作薄。将“员工业绩评估表”工作薄中“一月”和“二月”工作表中每位员工的”销售业绩额“和”基本业绩奖金“进行合并操作，并将其保存在”全年“工作表中。,4.6.6 数据透视表,如果要对多个字段进行分类汇总，需要利用数据透视表。利用“数据透视表和数据透视图”向导，分3步建立数据透视表：指定数据源选定数据区域决定透视表的放置位置,数据透视表,统计各系男女生的人数。 步骤：第1、2步默认，第3步布局对话框如图。,创建好数据透视表之后，可以通过“数据透视表”工具栏修改它，主要内容有： 更改数据透视表布局 改变
30、汇总方式,“数据透视表工具栏,“数据透视表字段”对话框,返回,4.7 数据图表化,返回,Excel可以根据工作表中的数据生成图形，这种图形称为图表。图表中数据的改变是动态的，当工作表的数据源发生变化时，图表中对应项的数据也自动更新。使用图表可以直观地显示出工作表中的数据，帮助用户分析数据，比较数据之间的差异。 4.7.1 图表的分类根据放置位置的不同，图表可分为两种类型：一种是嵌入式图表，他和创建图表的数据源放置在同一个工作表中，打印时也同时打印；另一种是独立图表。两种图表的创建方式大体相同。按照图表的外观，图表多达十多种类型（参见教材P177 表4.7-1）有二维图表和三维图表；每一类又有若
31、干种子类型。不同的图表类型有不同的应用范围和特点，用户可根据实际情况选择合适的图表类型。,4.7 数据图表化,2.创建图表 可通过“插入/图表”命令或单击“常用”工具栏中的 按钮，利用图表向导来创建图表。也可直接按F11键快速创建图表。 不管使用哪种方法创建图表，一般都要先选定创建图表的数据区域。选定的区域可以连续，也可不连续。但要注意，如若不连续，第二个区域应和第一个区域所在行或所在列有相同的矩形；若选定的区域有文字，则文字应在区域的最左列或最上行，用来说明图表中数据的含义。 在“员工业绩评估表”中打开“一月”工作表，首先按部门分类汇总，接着单击“-”按钮隐藏明细数据行。给出各部门的销售业绩
32、与总销售业绩的对比饼图。,4.7 数据图表化,图表的显示总会有许多不如人意的地方，用户还可以进一步修改与美化它。图表中的每一个对象都是可以修改的。3.编辑图表 调整图表的位置和大小 更改图表的类型 添加和删除数据 修改图表项 添加趋势线 设置三维视图格式,4.7 数据图表化,4.7.4 图表的格式化 图表的格式化是指对图标的各个对象进行格式设置，包括文字和数的格式、颜色和外观等。格式设置有三种方法。 方法一：“格式|图表选项” 方法二：指向图表对象并右击，在弹出的快捷菜单中选择该图表对象格式设置命令。 方法三：双击欲进行格式设置的图标对象。,图表组成,图表制作,根据学生成绩表中姓名、各科成绩产生一个簇状柱形图,返回,电子表格的数据输入、计算和编辑格式化：单元格、条件格式数据的排序（简单、复杂）、筛选（自动、高级）、分类汇总（简单，嵌套）、合并计算、数据透视表图表的建立、编辑,小 结,返回,返回,本章习题,教材P183-184 练习题,返回,本章实验,实验八：电子表格的基本操作实验九：公式和函数的应用实验十：数据管理与分析及图表的创建,
