第4章 Excel 2007电子表格.

Presentation on theme: "第4章 Excel 2007电子表格."— Presentation transcript:

1 第4章 Excel 2007电子表格

2 主要内容 工作簿、工作表、单元格的概念及基本应用 工作表的编辑与格式化 公式与常用函数的应用 数据图表化及图表的格式化
数据的排序、筛选、合并计算、分类汇总和数据透视表等操作 工作表的打印技巧

3 4.1 Excel 2007概述 Excel 2007是美国微软公司推出的Office 2007办公软件包中重要的软件之一，是当前最流行的电子表格处理软件，具有强大的表格制作和数据处理能力。较之于以前的Excel版本，它采用全新的更加人性化的用户界面、更加稳定安全的文件格式、无缝高效的沟通协作、面向21世纪的功能设计，为高效、愉快的商务办公提供可能。

4 Excel 2007的主要功能是能够方便快捷地制作各种电子表格，并对表格中的数据进行处理和管理，它是一种集文字、数据、图形、图表以及其它多媒体对象于一体的流行软件。目前，Excel 2007已成为国内外广大用户管理公司和个人财务、统计数据、绘制各种专业化表格的得力助手。

5 4.1.1 Excel 2007的启动与退出 Excel 2007的启动方法 方法1：使用桌面快捷图标启动 方法2：从“开始”菜单启动
方法3：使用已创建的工作簿启动

6 2. Excel 2007的退出方法 方法1：单击Excel 2007窗口右上角的“关闭”按钮。
方法2：单击“Office”按钮 ，选择Office菜单下的“退出Excel”命令。 方法3：双击Excel 2007窗口左上角的“Office”按钮。 方法4：直接按“Alt+F4”快捷键。

7 4.1.2 Excel 2007的窗口构成 启动Excel 2007后,会出现如图4.1所示的工作界面。系统会自动创建一个名为“Book1”的空白工作簿，工作簿内默认显示Sheet1、Sheet2和Sheet3三张工作表。每张工作表由行和列组成，行号位于工作表左侧，纵向排列，行号从1～ ；列标位于工作表顶部，用字母表示，有A、B、…Y、Z、AA、AB、…XFD共16384列。行列交叉处称为“单元格”，可用列标和行号表示。

8 图4.1 Excel 2007窗口介绍 工作表标签 标题栏 行号 页面显示控制区 控制按钮 列标 状态栏 垂直滚动条 水平滚动条
“帮助”按钮 名称框 活动单元格 “Office”按钮 编辑栏 快速访问工具栏 工具选项卡 功能区 图4.1 Excel 2007窗口介绍

9 4.2 工作簿、工作表及单元格的操作 工作簿 ：一个Excel文件称为一个工作簿，文件的扩展名为.XLSX，一个工作簿中可包含多种不同类型的工作表，默认为3张，可根据需要增加工作表的数量。 工作表：工作表是用户处理和存储数据的工作对象，由一系列单元格组成。最多可有 * 16384个单元格。 单元格：行和列的交叉处，表格的最小单位,每个单元格最多可填写32000字符。

10 4.2.1 工作簿的操作 1. 新建工作簿 2. 保存工作簿 3. 打开工作簿 4. 关闭工作簿 5. 保护工作簿
【“Office”按钮】→【新建】 2. 保存工作簿 【“Office”按钮】→【保存】 3. 打开工作簿 【“Office”按钮】→【打开】 4. 关闭工作簿 【“Office”按钮】→【关闭】 5. 保护工作簿 单击“审阅”选项卡的“更改”组中的“保护工作簿”命令按钮

11 4.2.2 工作表的操作 工作表的操作最便捷的方法是在选定工作表标签上单击右键，在其快捷菜单上有对工作表的各种操作命令：插入、删除、重命名、移动复制工作表、隐藏、保护工作表等等，如图4.2所示。 图4.2 工作表的操作

12 4.2.3 单元格的操作 单元格的选定 单元格的重命名 单元格数据的输入 单元格的移动和复制 单元格的清除 单元格行、列的插入 单元格的合并

13 1. 单元格的选定 单个单元格的选定：单击所需的单元格。 多个连续单元格的选定：单击首单元格，然后按住“Shift”键单击末单元格。
多个不连续单元格的选定：按住“Ctrl”键单击各个不连续的单元格。 整行或整列的选定：单击行号或列标。 全部单元格的选定：单击行头、列头相交的全选按钮。 清除选定的单元格的选定：工作表任意处单击。

14 2. 单元格的重命名 选定要重命名的单元格，在其名称框内输入新的名称，按回车键确定即可。

15 3. 单元格数据的输入 （1）文本型 直接输入文字即可，对于数字字符输入：‘ 数字 （2）数值型
在单元格中键入的数字为常量；为避免将输入的分数视作日期，请在分数前冠以 0 和空格。 如1/2的输入：0 1/2 （3）日期和时间型 如果要在同一单元格中键入时期和时间，请在其中间用空格分离。 （4）数据自动输入 1）自动填充 指一批有规律的数据，例等差数列。 2）系统提供的序列数据 例：甲、乙、丙...；?3、6、9...；星期一 、星期二... 方法：输入一个数据，利用自动填充柄拖曳直至结束填充

16 4. 单元格的移动和复制 此类操作与 Word 基本相同，可以使用命令按钮的方式，也可以使用鼠标拖拽的方式完成。
注意：所复制的公式中的地址会根据所在行、列的变动而自动调整。

17 5. 单元格的清除 选定对象，直接按“Delete”键，或者选择“开始”选项卡的“编辑”组中“清除”命令下拉菜单中的“清除内容”命令，如图4.3所示。 全部清除 清除格式 清除内容（相当于按“DEL”键） 清除批注 图4.3 单元格的清除

18 6. 单元格行、列的插入 选定单元格，单击“开始”选项卡的“单元格”组中的“插入”命令按钮下的小箭头，则会弹出其子菜单，如图4.4所示，可自主选择对单元格、行、列或是整个工作表的插入。 图4.4 “插入”菜单

19 7. 单元格的合并 若要将多个单元格合并为一个单元格，则选中需要合并的单元格区域，在“开始”选项卡“对齐方式”组中选择“合并后居 中”的 按钮即可。 若要恢复合并前的效果，则单击“合并后居中”旁边的下拉三角按钮，在弹出的下拉菜单中选择“取消单元格合并”命令即可。

20 4.3 Excel格式设置 Excel格式设置主要有：字体的颜色与大小，对齐方式，行高与列宽，边框和底纹等。
图4.5 “开始”选项卡

21 4.3.1 单元格格式设置 用上面的命令按钮来实现单元格的格式设置是比较简单快捷，容易操作，但是不够细致，比如要给单元格加彩色边框或是要给数据加上美元单位等操作，用这种方法就不容易实现了。下面来介绍通过“单元格格式”对话框方式更加细致地设置单元格格式。 在选中单元格上单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，或是单击“开始”选项卡“单元格”组中“格式”命令按钮下面的箭头，在弹出的快捷菜单中选择“设置单元格格式”命令，均可打开“单元格格式”对话框，如图4.6所示。

22 图4.6 “设置单元格格式”对话框

23 ”数字“标签 默认G／通用格式表示： 　 数字右对齐，数字＞长度，科学记数法，公式以值显示； 文字左对齐，文字＞长度，右边列无内容延伸，有则截断显示； 设置数字格式按钮：货币、百分比、千分位、小数位数递增和递减。 ”对齐“标签 提供了：水平对齐、垂直对齐、自动换行、合并居中、方向等。 ”字体“标签 提供了：字体、字型、字号、效果、多种颜色和底线等。其设置方法与 Word 中字体的格式设置基本相同。 ”边框“标签 提供了：线型、位置、颜色等，还有斜线。 ”填充“标签 提供了：颜色背景、填充图案和图案的颜色。 选定需要操作的单元或区域，打开“单元格格式”对话框，选“图案”标签，在颜色和图案中选择强制性需的内容，可设置单元格的底纹。

24 4.3.2 行高与列宽的设置 在默认情况下，Excel工作表的每一个单元格具有相同的行高和列宽，但是输入到单元格中的数据却是多种多样。因此，用户可以设置单元格的列宽和行高，以便更好的显示单元格中的数据。 （1）鼠标拖曳法 （2）快捷菜单法 （3）“格式”菜单法

25 4.3.3 条件格式 条件格式是基于预先设定的条件，使单元格区域现实预定的外观格式显示。如果条件成立，则显示基于该条件设定的单元格区域格式，如果条件不成立，则不显示基于该条件设定的单元格区域格式 。 选定单元格区域，再单击“开始”选项卡“样式”组中“条件格式”命令按钮下面的箭头，在弹出的菜单中有5类快捷的条件格式设置方式：突出显示单元格规则、项目选取规则、数据条、色阶、图标集，通过这些菜单命令可以快速设置条件格式。

26 4.3.4 套用表格格式 为提高表格格式设置效率，Excel提供了“套用表格格式”功能。套用表格格式可以快速地为表格设置格式，Excel 2007表格格式预设了浅色、中等深浅和深色3大类型供用户选择。 单击“开始”选项卡“样式”组中的“套用表格格式”按钮，弹出“套用表格格式”下拉菜单，如图4.7所示，菜单中列示了预设的表格样式。选择所需样式，则会打开如图4.8所示的“创建表”对话框，拖动鼠标选择表格区域，即可完成对所选区域单元格的格式设置。

27 图4.7 “套用表格格式”下拉菜单 图4.8 “创建表”对话框

28 4.3.5 单元格样式 Excel 2007还提供了多种单元格样式，使用单元格样式可以快速地使每一个单元格都具有不同的格式。预设样式有好、差、适中样式、数据和模型样式、标题样式、主题单元格样式和数字格式5大类型供用户选择。 选中需要套用样式的单元格或区域，单击“开始”选项卡“样式”组中的“单元格样式”按钮，弹出“单元格样式”下拉菜单，如图4.9所示，菜单中列示了预设的单元格样式，选择所需样式即可。

29 图4.9 “单元格样式”下拉菜单

30 4.4 公式和函数的应用 如果只是为了在表格中输入一些数字和文本，那么Word完全可以取代它。在大型数据报表中，计算统计工作是不可避免的，Excel的强大功能正是体现在计算上，通过在单元格中输入公式和函数，可以对表中数据进行总计、平均、汇总以及其他更为复杂的运算，从而避免用户手工计算的繁杂和易错，数据修改后公式的计算结果也自动更新则更是手工计算无法做到的。

31 4.4.1 公式的应用 公式是单元格内一系列常量、变量、运算符的集合，由操作数据和运算符组成。 公式形式：＝操作数和运算符
例如=C5*1.15+D5和= C5+Sheet2!C5+C6 操作数：单元格、数字、字符、区域名、区域 运算运算符：算术　＋、－、*、／、%、、（） 字符运算符：＆ 例如：“西安”＆“世园会”=“西安世园会” 关系运算符： =、>、>=、<=、<、<> 如果在公式中同时使用多个运算符，优先级： 算术运算符>文本运算符>比较运算符,若要改变优先顺序，可使用圆括号。

32 以“工资表”为例，演示公式的应用，如图4.10所示。
公式输入的方法为：先选定要输入公式的单元格，从输入“ = ”开始，再输入公式的内容，然后按“Enter”键，或单击编辑栏上的 按钮，计算结果就显示在单元格中。 以“工资表”为例，演示公式的应用，如图4.10所示。 图4.10 公式应用示例

33 4.4.2 函数的应用 Excel 提供了丰富的函数，如财务函数、日期与时间函数、数值与三角函数、统计函数、查找与引用函数、数据库函数、文字函数、逻辑函数、信息函数等。 函数的基本形式为：=函数名（参数1，参数2，…） 使用函数的步骤如下： （1）选定欲插入函数的单元格。 （2）选择“公式”选项卡“函数库”组中的命令按钮或单击编辑栏上的按钮，弹出如图4.11所示“插入函数”对话框。 （3） 在“选择类别”列表框，选择所要的函数类别，如常用函数；在“选择函数”列表框，选择所要的函数，如 SUM 求和函数；依照提示，输入函数的各个参数；选择“确定”按钮。

34 图4.11 “插入函数”对话框

35 1. 常用函数 SUM：求和 AVERAGE：求平均值 COUNT：计数 MAX：求最大值 MIN：求最小值 IF：判断条件是否满足

36 2. 其它函数 COUNTIF：统计某区域中满足某种条件的单元格的数目 SUMIF：对满足条件的单元格求合
RANK：返回某数字在一列数字中相对于其他数值的大小排位 MODE：返回在某一数组或数据区域中出现频率最多的数值 INT：将数字向下舍入到最接近的整数 ROUNDUP：向上舍入数字 YEAR：返回某日期对应的年份，为 1900 到 9999 之间的整数 MONTH：返回月份值，介于1（一月）到12（十二月）之间的整数 WEEKDAY：返回星期的数值，介于1到7之间的整数 DAY：返回一个月中的第几天的数值，介于1到31之间 HOUR：返回时间值的小时数 MINUTE：返回时间值的分钟数，介于0到59之间的整数

37 4.4.3 公式中单元格的引用 相对引用：将计算公式复制或填充到其他单元格时，单元格引用会随着公式所在单元格的位置变更而变化。
绝对引用：公式和函数中的单元格地址是固定不变的，使用时无论公式被复制到哪个单元格，公式的结果都固定不变。 混合引用：一个单元格地址中，既有相对引用，又有绝对引用，如$D1或D$2。$D1是列不变，行变化。D$2是列变化，行不变。

38 3种引用的表示 相对引用：列坐标行坐标 例 B6， A4 , C5:F8 绝对引用：$列坐标$行坐标
注意：如果所引用单元格不属于当前工作簿的工作表，就必须在该单元格名称前加上其所在的工作簿名和工作表名以及叹号分隔符“！”。例如：成绩表.xlsx sheet1！C$6

39 4.5 数据的图表化 图表能够使数据更直观、形象，增强了数据的表现力。Excel提供的图表类型有11种之多，如柱形图、折线图、饼图、条形图、散点图等等，而且每一类还有很多子类型。

40 4.5.1创建图表 （1） 选定要用来创建图表的数据单元格，
（2）在“插入”选项卡的“图表”组中，单击“图表”右下角的对话框启动器 ，打开“插入图表”对话框，如图4.12左边所示，选择所需的图表类型，单击确定按钮即可创建一个图表，如图4.12右边所示。 图4.12 图表的创建

41 若想快速创建图表，则可直接单击“插入”选项卡“图表”组中相应的命令按钮，在下拉列表中选择所需的图表类型，如图4.13所示。
图4.13 图表的快速创建方式

42 4.5.2 编辑图表 图表创建完后，还可根据需要对图表中的数据进行修改。图表是根据工作表中的数据创建来的，因此图表与工作表中的数据是相互关联的，更改工作表中的数据，图表就会做出相应的更改；同理，修改图表也会改变工作表中的数据。

43 1. 添加数据 2. 删除数据 3. 修改数据 4. 添加图表标题 5. 添加坐标轴标题 6. 更改图表类型 7. 更改图表布局

44 图4.14 “设计”选项卡 图4.15 “布局”选项卡

45 4.5.3 格式化图表 方法1：通过“布局”选项卡“当前所选内容”组实现。
方法2：通过“布局”选项卡“标签”组、“坐标轴”组和“背景”组实现。 方法3：在选中对象上单击右键，在快捷菜单中选择相应的“设置XXX格式”来实现。

46 图4.16 “设置图表区格式”对话框

47 4.6 数据分析与管理 Excel 2007不仅具有简单数据计算处理的能力，还具有数据库管理的一些功能，更可贵的是，Excel 2007在制表、作图等数据分析方面的能力比一般数据库更胜一筹，淋漓尽致地发挥了在表处理方面的优势，它可对数据进行排序、分类、筛选、汇总、透视等操作。

48 4.6.1 数据排序 数据排序是指按一定规则对数据进行整理和排序，为数据的进一步处理作好准备。Excel 2007既可快速实现单字段的简单排序，也可以通过自定义排序实现多字段的复杂排序。 1. 简单排序 简单排序是指对单一字段按升序或降序进行排列。 单击排序字段列的任意一个单元格，直接单击“数据”选项卡“排序和筛选”组中的升序 和降序 按钮，即可实现对该列字段的排序。

49 2. 复杂排序 如果要对多个字段进行排序时，则需要使用自定义排序。
单击排序字段列的任意一个单元格，直接单击“数据”选项卡“排序和筛选”组中排序按钮 ,打开如图所示对话框。 图4.17 “排序”对话框

50 4.6.2 数据筛选 数据筛选可以快速地选出所需的数据，即从众多的数据中挑选出符合某种条件的数据显示出来，而隐藏不符合条件的其它数据（不显示）。数据筛选有两种方式，一种是自动筛选，另一种是高级筛选。

51 1. 自动筛选 可对单个字段或多个字段建立筛选，多个字段之间的筛选是逻辑“与”的关系。 (1) 选定数据清单中的任意一个单元格。
(2) 选择“开始”选项卡“编辑”组中“排序和筛选”命令按钮下拉菜单中的“筛选”命令或单击“数据”选项卡“排序和筛选”组中的筛选按钮 ，字段名均变成下拉列表的名称框，如图4.18左边所示。 (3) 单击“性别”下拉列表框，勾选“女”，则数据清单中只显示所有女生的记录，其余的全被隐藏，如图4.18右边所示。

52 图4.18 筛选所有女生的记录项

53 2. 高级筛选 如果要进行筛选的字段较多，筛选条件较复杂，此时就需要采用高级筛选了，高级筛选不仅能够实现逻辑“与”的关系，还能实现逻辑“或”的关系。 使用高级筛选必须先建立一个条件区域，用来指定数据筛选条件。条件区域的第一行是所有筛选条件的行标题，这些行标题必须与数据表中的行标题完全一致；其他行用来输入筛选条件。 注意：条件区域与数据清单不能相连，必须用空白行或列将其隔开。

54 图4.19 筛选条件的输入

55 （1）输入筛选条件。 （2）选定数据清单中的任意一个单元格。 （3）选择“数据”选项卡“排序和筛选”组中的“高级”命令按钮，打开“高级筛选”对话框。 （4）在对话框的“方式”选项组中确定筛选结果显示的位置；在“列表区域”输入要筛选的数据区域；在“条件区域”输入含有筛选条件的单元格区域。如果要筛选掉重复的记录，需选中下方的复选项。

56 图4.20 筛选出性别是女生或者数学成绩大于90的男生的所有记录

57 4.6.3 分类汇总 分类汇总是将工作表数据按照指定某个字段（称为关键字段）进行分类，并按类进行数据汇总（求和、求平均、求最大值、求最小值、计数等）,结果分级，以便显示和隐藏每个分类汇总的明细数据行。 前提：先分类（即排序），再按分类字段进行汇总。

58 以成绩表为例，将各门课的成绩按专业进行求和汇总：
（1）将工作表按“专业”进行简单排序后，把鼠标定位于表中任意单元格中。 （2）选择“数据”选项卡“分级显示”组中的“分类汇总”命令按钮，打开“分类汇总”对话框，如图4.21左边所示。 （3）在“分类字段”下拉列表框中，选择字段名称“专业”；在“汇总方式”下拉列表框中，选择汇总方式“平均值”；在“选定汇总项”列表中，选择各门课程字段名；选择“汇总结果显示在数据下方”复选框；按“确定”按钮后即将汇总结果显示在数据下方，如图4.21右边所示。

59 图4.21 分类汇总示例

60 4.6.4 数据透视表 数据透视表是一种对大量数据进行快速汇总和建立交叉列表的交互式报表，它不仅可以快速合并、比较大量的数据，也可以随时更改布局和显示用户需要的区域明细数据，查看数据表格不同层面的汇总信息、分析结果以及摘要数据。使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并做出有关企业中关键数据的决策。 分类汇总仅仅适合于按一个字段进行分类，对一个或多个字段进行汇总，如果要对多个字段进行分类并汇总，则可用数据透视表来实现。

61 （2）选择“插入”选项卡“表”组中的“数据透视表”命令按钮，打开“创建数据透视表”对话框。
以工资表为例，既要统计各部门的平均工资人数总计，又要统计各职称下的平均工资和人数总计。 （1）选定数据清单中的任意一个单元格。 （2）选择“插入”选项卡“表”组中的“数据透视表”命令按钮，打开“创建数据透视表”对话框。 图4.22 “创建数据透视表”对话框

62 （3）单击“确定”，即可创建一个空的数据透视表，如图4.23所示。
图4.23 空的数据透视表和数据透视表字段列表

63 将字段拖动到“报表筛选”区域，则可以根据此字段对报表实现筛选，可以显示每类项目相关的报表。在这里可以将较大范围的分类拖动到此区域，以实现报表筛选。
将字段拖动到“行标签”区域，则此字段中的每类项目会成为一行，可以将希望按行显示的字段拖动到此区域。 将字段拖动到“列标签”区域，则此字段中的每类项目会成为一列，可以将希望按列显示的字段拖动到此区域。 将字段拖动到“数值”区域，则会自动计算此字段的汇总信息（如求和、计数、平均值、方差等）。在这里可以将任何希望汇总的字段拖动到此区域。

64 图4.24 数据透视表示例

65 4.6.5 合并计算 分类汇总操作是针对一个工作表的汇总计算，而合并计算是对一个或多个工作表中的数据进行汇总，以产生合并报告，并把合并报告放在指定的单元格中。合并计算要求欲进行合并计算的数据必须具有相同的结构（行或列标题）。

66 以课程安排表为例，在课程安排统计表中来统计各门课程的上课总人数和课时总数。
（1）选定合并数据放置的首单元格。 （2）选择“数据”选项卡“数据工具”组中的“合并计算”命令按钮，打开“合并计算”对话框，在“合并计算”对话框中，如图4.25所示，选择函数的求和项；引用位置则选择所需合并计算的所有数据区，如有多个数据区时，可选择添加按钮进行多个数据区的选择性；标签位置处，如果要合并的字段在首行，则勾选首行，若要合并的字段在左侧，则勾选最左列。

67 图4.25 “合并计算”对话框

68 （3）最后点“确定”即可生成所需合并的结果，如下图所示。
图4.26 合并计算示例结果

69 4.7 工作表的打印 当建立了一张工作表格，并对其进行相应的修饰后，最终的结果往往需要通过打印机打印输出到纸张上，Excel 2007可以打印出各种报表。 打印步骤： 设置打印的区域 页面设置 打印预览 打印

70 本章小结 本章主要介绍工作表的编辑与格式化，公式与函数的应用，数据的图表化，以及数据的排序、筛选、合并计算、分类汇总和数据透视表等操作。
通过本章学习，使大家掌握Excel 2007 的使用技巧，能够方便快捷地制作各种电子表格，并对表格中的数据进行处理和管理。