第四章 Excel 电子表格.ppt

1、上海交通大学网络教育学院,1,计算机应用基础II,上海交通大学网络教育学院,2,第一讲主要内容,复习第一章计算机基础知识 课堂练习：请大家自己一边做一边检查正确率 穿插内容 “Excel中常见问题”,上海交通大学网络教育学院,3,第四章：Excel电子表格,4.1考试内容与要求 4.2 知识要点 4.3 例题分析与解答 4.4 自测题,上海交通大学网络教育学院,4,4.1 考试内容与要求,4.1.1 Excel的基本知识 一、考试内容 工作簿的建立、保存与打开。 二、考试要求 （1）了解Excel的基本功能和运行环境； （2）掌握Excel的启动和退出； （3）了解Excel窗口的结构。,上海

2、交通大学网络教育学院,5,4.1 考试内容与要求,4.1.2 工作表的建立与编辑 （1）了解工作表的结构； （2）理解单元格地址表示； （3）熟练掌握数据输入和编辑操作； （4）掌握工作表格式化的基本操作； （5）熟练掌握工作表的基本操作； （6）掌握工作表的打印输出。,上海交通大学网络教育学院,6,4.1 考试内容与要求,4.1.3 公式与函数的使用 一、考试内容 单元格数据的类型、使用，相对、混合和绝对引用。 二、考试要求 （1）掌握公式的使用； （2）掌握单元格的引用； （3）掌握常用函数的使用； （4）掌握工作表之间的编辑操作。,上海交通大学网络教育学院,7,4.1 考试内容与要求,4

3、.1.4 数据处理 一、考试内容 数据的排序、筛选和分类汇总。 二、考试要求 （1）掌握数据查找； （2）掌握数据排序； （3）掌握数据筛选； （4）掌握数据的分类汇总。,上海交通大学网络教育学院,8,4.1 考试内容与要求,4.1.5 图表的使用 一、考试内容 数据图表的建立、编辑与使用。 二、考试要求 （1）了解图表类型； （2）掌握图表的创建； （3）掌握图表的编辑和打印。,上海交通大学网络教育学院,9,第四章：Excel电子表格,4.1考试内容与要求 4.2 知识要点 4.3 例题分析与解答 4.4 自测题,上海交通大学网络教育学院,10,4.2 知识要点,4.2.1 Excel的基本

4、知识 Excel的定义 Excel 2003应用软件是Microsoft公司推出的办公自动化软件中一个优秀的专门用于数据管理和数据分析等操作的电子表格软件。使用该软件能够快速方便地制作出各式各样的电子表格、完成许多复杂的数据运算，进行数据的分析和预测并且具有强大的制作图表的功能。,上海交通大学网络教育学院,11,2. Excel的启动和退出,（1）启动 方法一：使用Windows “开始”菜单： ”所有程序“”Microsoft Office” ”Microsoft Office Excel 2003”命令 。 方法二：在Windows桌面上双击Excel 2003快捷方式图标： 。 方法三：

5、用鼠标双击任一已存在的Excel 2003工作簿。,上海交通大学网络教育学院,12,（2）退出 在Excel 2003工作窗口中单击菜单栏的”文件”退出”。 单击Excel 2003 工作窗口右上角的关闭按钮。 双击Excel 2003工作窗口左上角的控制按钮。 按组合键ALT+F4结束Excel 2003任务。,上海交通大学网络教育学院,13,3. Excel窗口,（1）标题栏 （2）菜单栏 （3）工具栏 （4）编辑栏 （5）任务窗格 （6）状态栏,上海交通大学网络教育学院,14,（7）工作簿和工作表 工作簿 是Excel 2003 处理和存储数据、分析数据的文件。一个Excel 2003文

6、件就是一个工作簿，其扩展名为.xls。每个工作簿可包含多个工作表（最多为255），故可在一个工作簿中存放不同类型的相关信息的文件。在启动Excel后，系统就自动创建和打开一个空白的工作簿文件，默认名称为“book1”。,上海交通大学网络教育学院,15,工作表 用于存储和处理数据、分析数据的主要文档，也称为电子表格。它是由列和行构成的单元格组成。工作表是随着工作簿存储的，即存放在工作簿中。一个工作簿可包含多个工作表（默认三个工作表sheet1、sheet2、sheet3）。,上海交通大学网络教育学院,16,工作簿和工作表的保存 常用工具栏上的保存按钮；或者“文件”“保存”命令；或者使用快捷键ct

7、rl+s。 换名或者是存放在另外一个目录下，则可以单击“文件”“另存为”命令。,上海交通大学网络教育学院,17,（8）工作表结构 工作表的容量 工作表最大为256列和65536行的二维表格，列号为A、B、C Y、Z、AA、AB IV。行号为1、2、3 65536。 单元格 行列交叉的小方格为“单元格”，其名称用列号和行号组合而成。它是工作表操作的最小单位。 滚动条 用于显示窗口中无法显示的工作表内容。有水平和垂直两种。在垂直滚动条顶端还存在一个“水平拆分条”，在水平滚动条的顶端也存在一个“垂直拆分条”。,上海交通大学网络教育学院,18,4.EXCEL中的数据类型,数字（数值） 文字（文本） 逻

8、辑（也称布尔量，0或1） 错误值（熟记书中错误值对应的错误类型）,上海交通大学网络教育学院,19,（1）数字（数值） 数字数据 十进制数字、小数点、正负号、百分号、千位分隔符、指数符号、货币符号等组成。 日期和时间 数字数据中的两种特殊数据。日期通常表示格式为：yyyy-mm-dd，时间为：hh:mm:ss。 日期和时间的数值表示 存储日期为从1900年1月1日到今天的天数，时间则是从0时开始到现在的秒数除以全天的秒数86400得到的一个纯小数。数字数据在单元格中自动右对齐。,上海交通大学网络教育学院,20,（2）文字（文本） 文字数据的组成 由英文字母、汉字、数字、标点、符号等顺序排列而成。

9、文字数据统一采用Unicode编码储存，每个文字占用两个字节。 数字文本的输入 若要输入数字文本需要先输入半角单引号，再输入数字。 数字和文字数据的运算 对数值数据可以进行算术运算和比较运算，但对于文字数据则通常只允许比较运算。西文字符比较其ASCII码的大小，中文则比较拼音的大小，其实还是比较拼音的ASCII码。,上海交通大学网络教育学院,21,（3）逻辑（也称布尔量，0或1） 逻辑数据为两个特定的标识符：true和false，大小写皆可。允许参加算术运算。分别代表1和0。在单元格中输入逻辑数据时为居中对齐。若作为文字数据，也要先输入单引号。,上海交通大学网络教育学院,22,（4）错误值（熟

10、记书中错误值对应的错误类型） 当单元格中输入或编辑数据错误，则系统自动显示错误值。如：“#div/0”、“#VALUE”。,上海交通大学网络教育学院,23,工作簿文件的组织结构 一个Excel文档最多可以保存255张工作表。 一张工作表对多可有65536行，256列，即最多可有65536256个单元格。 每个单元格最多能保存32000个字符。,5.工作簿文件中的数据结构,上海交通大学网络教育学院,24,4.2.2 工作表的建立与编辑,1.建立工作表 (1)选定、插入、删除和重命名工作表 单个、连续多个SHIFT、不连续多个CTRL 选取插入工作表的位置 插入工作表 选取要删除的工作表编辑删除工

11、作表 双击要重命名的工作表输入新名字,上海交通大学网络教育学院,25,(2)单元格区域的选定 选取单元格：单击单元格 选定单元格区域：拖动或与SHIFT组合 选取不连续区域：CTRL键 选取整行或整列：行标或列标 选取整张工作表：全选框 清除选定的单元格：工作表任意处单击。,上海交通大学网络教育学院,26,(3)输入数据,输入数据 输入负数 - 输入数字串前面加单引号 输入文本 单元格内换行，按Alt+Enter。 输入日期和时间 日期格式：年/月/日 年-月-日 时间格式：时:分:秒 数据的格式 默认时，数值数字右对齐，字符左对齐 右击欲操作的单元格，选择快捷菜单中“设置单元格格式”命令，选

12、择所需格式。,上海交通大学网络教育学院,27,2.数据输入方法,(1)从键盘直接输入 (2)从下拉列表中输入数据 (3)根据记忆系统输入数据 (4)使用填充功能输入数据 若数据或文本序列中所含数字变化有规律，则可以利用数据填充功能输入后续数据。,上海交通大学网络教育学院,28,自动填充数据,指向第2个单元格右下角的自动填充柄 ,填充相同数据和序列数据,上海交通大学网络教育学院,29,3.数据编辑方法,选择、剪切、复制和粘贴数据 重写和修改数据 插入和删除数据 插入数据：选择一个单元格或某一区域，单击菜单栏中的“插入”，根据需要插入相应的单元格、行或列。 清除数据 撤销和恢复 查找和替换,上海交

13、通大学网络教育学院,30,4.单元格格式设置,单元格格式对话框的打开 选定需要改变格式的单元格或区域，单击“格式”“单元格”选项。 具体包括“数字”、“对齐”、“字体”、“边框”、“图案”、“保护”6个选项卡。,上海交通大学网络教育学院,31,“格式”工具栏 行列尺寸设置 鼠标操作：将鼠标移到两列（行）的分解线上，当鼠标变成一个十字时，单击并拖曳鼠标即可调整相应的列宽（行高）。 菜单操作：选择需要调整的行（列），单击“格式”选项所对应的“行”或“列”，打开相应的子菜单选择具体命令。 条件格式设置,上海交通大学网络教育学院,32,5.编辑工作表实例 6.工作表的整体操作 （1）选择工作表 单击整

14、个工作表的左上角的方框。 鼠标指向工作表标签，右击鼠标，在快捷菜单中单击“选定全部工作表” （2）插入工作表 单击工作表标签”插入”工作表” （3）删除工作表 单击工作表标签”编辑”删除工作表”,上海交通大学网络教育学院,33,（4）移动和复制工作表,上海交通大学网络教育学院,34,（5）重命名工作表 双击工作表标签或右击选“重命名”或“格式”“工作表”“重命名”，输入新标签名。 （6）改变工作表标签颜色 右击选“工作表标签颜色”或“格式”“工作表”“工作表标签颜色”，在打开的对话框中选择所需颜色。,上海交通大学网络教育学院,35,7.工作表的打印输出,Excel默认打印区域为含数据的区域，如

15、果将某区域设为打印区域，那么以后打印时就打印该区域的数据了。 方法： 选择经常打印的单元格区域 选择“文件”打印区域”设置打印区域”命令,上海交通大学网络教育学院,36,工作表的页面设置,通过“文件”“页面设置”打开页面设置菜单，页面设置对当前工作表有效。 页面 ：设置缩放比例、纸张大小、打印方向。 页边距 ：上、下、左、右页边距，水平、垂直居中方式等。 页眉/页脚：设置页码、日期、时间、文件名、工作表名。,上海交通大学网络教育学院,37,4.2.3 公式与函数,1.单元格引用 相对引用： 列坐标行坐标例B6， A4 , C5:F8 绝对引用： $列坐标$行坐标例$B$6, $A$4 ,$C$

16、5:$F$8 混合引用： 列坐标$行坐标 例 B$6, A$4, C$5:F$8 $列坐标行坐标 例 $B6, $A4, $C5:$F8 引用不同工作表单元格前面需加工作表名 如：sheet2!B2+B2,上海交通大学网络教育学院,38,2.公式的使用,（1）公式组成 公式形式：操作数和运算符 操作数：单元格引用、数字、字符、区域名、区域 公式使用：,上海交通大学网络教育学院,39,（2）运算符,算术运算符(+、-、*、/、%、等) 比较运算符(=、=、等) 文本运算符(& 等),上海交通大学网络教育学院,40,（3）公式的输入,选择存放计算结果的单元格。 输入等号“=”或直接单击“编辑公式”

17、或“插入函数”按钮。 输入公式内容，如：输入“C5+D5+E5”。 按【Enter】键或单 击编辑栏上的“确认”按 钮，结果出现在单元 格中。,上海交通大学网络教育学院,41,3.函数,函数使用形式：函数名（参数表） 例如：SUM(C2：F2)或SUM(C2,D2,E2,F2)或C2+D2+E2+F2 函数的输入方法： 1、直接输入法 2、粘贴函数法,上海交通大学网络教育学院,42,1、求和函数SUM（）,使用格式：SUM(number1,number2, .) 功能：计算一组数值的总合 说明：（1）Number1, number2, . 为需要求 和的参数。 （2）如果参数为数组或引用，只有

18、其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。 （3）如果参数为错误值或为不能转换成数字的文本，将会导致错误。,常用函数格式及功能说明,上海交通大学网络教育学院,43,例如: SUM(3, 2) 等于 5 SUM(“3”, 2, TRUE) 等于 6，因为文本值被转换成数字，而逻辑值TRUE被转换成数字 1。 SUM(“Abc”,8, 2) 等于 10，因为对非数值型的值的引用不能被转换成数值。 如果单元格 A2：E2 包含 5，15，30，40 和 50，则： SUM(A2:C2) 等于 50 SUM(B2:E2, 15) 等于 150,上海交通大学网络教育学院

19、,44,例：求C5:C8单元格的平均值，结果存放在C10单元格中。,单击C10,粘贴函数,选取函数类型和函数名,输入参数,在编辑框直接输入:AVERAGE(C5:C8),2、求平均值函数AVERAGE（）,使用格式：AVERAGE(number1,number2, .) 功能：计算一组数值的平均值 说明：Number1, number2, . 为需要平均值的参数。,上海交通大学网络教育学院,45,例：求C5:C8区域单元格数值个的最大值，存放在C10单元格中。,3、求最大和最小值函数MAX（）和MIN（）,使用格式：MAX(number1,number2, .) MIN(number1,num

20、ber2, .) 功能：求某个区域内或一组数值中的最大值（最小值）。 说明：Number1, number2, . 可以为数值或单元格引用。 例如：MAX（16，28，89，10）的值为89,上海交通大学网络教育学院,46,例：求C5:C8区域单元格的数值个数，结果存放在C10单元格中。,4、计数函数COUNT（）,使用格式： COUNT(number1,number2, .) 功能：计算出某个区域内数值的个数。 说明： Number1, number2, . 可以为数值或单元格引用。 当单元格是文字、逻辑值或空白时，则COUNT不计数。,上海交通大学网络教育学院,47,5、根据条件返回值函数

21、IF（）,使用形式：IF(条件表达式，返回值1，返回值2) 功能：根据条件表达式成立与否，返回不同的结果，条件表达式为 TRUE 则得返回值1，为 FALSE则得返回值2。 例：IF(B2C2,超过预算,OK) IF(B3C3,30,20) IF(E4=270 ,”优 秀“,“ “) 另外，需了解书上139页的表4-1常用函数表中的其他函数,上海交通大学网络教育学院,48,4.在单元格中输入公式 5.填充操作输入公式 在一个单元格输入公式后，把鼠标移到此单元格的右下角在鼠标变成十字后，单击并拖曳到需要填充公式的单元格区域。,上海交通大学网络教育学院,49,6.单元格中函数的输入方法,输入“=”

22、后，再输入函数。 单击“插入”“函数”，在打开的插入函数对话框中选择所需函数。,上海交通大学网络教育学院,50,4.2.4 EXCEL数据处理,1.数据排序 排序前必须先选中数据区域。 通过菜单：“数据”排序”。 通过工具栏：点击“升序 ”和“降序 ”,上海交通大学网络教育学院,51,2.数据筛选,筛选是显示工作表中符合条件的数据。 （1）自动筛选 操作步骤： 打开工作表，选定一字段名， 打开【数据】【筛选】【自动筛选】， 单击字段名右边的“”按钮，选择筛选 条件即完成。,上海交通大学网络教育学院,52,（2）高级筛选,数据筛选时有多个条件，需用高级筛选。高级筛选除了能完成自动筛选的功能外，还

23、能完成任一条件的筛选。 操作步骤： 打开工作表 建立条件区域 选择数据区域 【数据】【筛选】【高级筛选】 菜单命令在对话框中输入条件区域 单击确定,上海交通大学网络教育学院,53,上海交通大学网络教育学院,54,3.数据的分类汇总,按某关键字段进行分类汇总，在分类汇总前必须按该关键字段排序。,(1)分类汇总,数据,数据分类汇总,分类字段,汇总方式,选定汇总项（可多个）,替换当前分类汇总,每组数据分页,汇总结果显示在数据下方,不分页,用于单个字段分类单种汇总方式。,数据分类汇总,全部删除,(2)撤消分类汇总,上海交通大学网络教育学院,55,4.2.5 EXCEL图表,1.图表概念 图表是将工作表

24、中的数据用图的形式表示出来，使数据更直观、形象。 用于生成图表的数据清单中一个单元格的数据成为数据点，一列或一 行单元格的数据成为数据系列。 图表有两种形式： 嵌入在工作表内，称为嵌入式图表。 一张独立的图表，称为工作表图表。,上海交通大学网络教育学院,56,2.图表类型,根据数据表示不同，可分为柱形图、条形图、折线图等12类。常用的是柱形图、折线图和饼图。,上海交通大学网络教育学院,57,3.图表的创建过程,嵌入式图表 在第4步骤图表位置中，选中“作为其中对象插入”选项。 工作表图表 在第4步骤图表位置中，选中“作为新工作表插入”选项。,上海交通大学网络教育学院,58,4.图表的编辑,（1）

25、改变图表类型 右击已创建的图表，选择快捷菜单中的“图表类型”命令，选择需要的类型。 （2）修改图表数据 修改工作表的数据或改变图形形状时，图形或者工作表数据会随之改变。 （3）添加和删除数据系列 添加数据系列:选中图表，图表添加数据命令，选择要添加的数据区域。 数据系列删除:删除工作表的数据系列则图表中的相关数据同时被删除，仅从图表中删除数据则选中图表中相关图形后按“Del”键即可。,上海交通大学网络教育学院,59,（4）添加图形和文本 默认情况下，绘图工具栏是打开的，否则可根据视图工具栏绘图三个步骤打开绘图工具栏，在绘图工具栏上选择相应的图形或文本后在图表中适当位置通过拖动鼠标来添加即可。

26、（5）嵌入式图表位置和大小的改变 单击图标，按住鼠标左键将其移动到指定位置。 选中图表，移动鼠标变成双向箭头时，按下鼠标左键拖动以改变图表大小。,上海交通大学网络教育学院,60,5.图表的打印,图表打印和工作表数据打印基本相同，可以根据需要设置打印方向，页边距等，打印预览里可以看到打印之后的效果。,上海交通大学网络教育学院,61,第四章：Excel电子表格,4.1考试内容与要求 4.2 知识要点 4.3 例题分析与解答 4.4 自测题,上海交通大学网络教育学院,62,4.3 例题分析与解答,一个Excel工作表中第5行第4列的单元格地址是_ A.5DB. 4E C. D5D. E4, C,上海

27、交通大学网络教育学院,63,4.3 例题分析与解答,剪切所选数据的快捷键是_ Ctrl+ CB. Ctrl + V C. Ctrl + XD. Ctrl + S, C 几个选项均是最常用的Excel快捷键。 Ctrl + C为“复制”的快捷键； Ctrl + V为“粘贴”的快捷键； Ctrl + X为“剪切”的快捷键； Ctrl + S为“保存文件”的快捷键。,上海交通大学网络教育学院,64,已知Al单元中的内容为数值3，B1单元中的内容为文字8(8)，则取值相同的一组公式是_。 A. =AVERAGE(A1:B1)，=AVERAGE(3,8) B. =COUNT(A1:B1)，=COUNT(

28、3,8) C. =MAX(A1:B1)， =MAX(3,8) D. =MIN(A1:B1)，=MIN(3,8), D 8 是作为文本处理的，8是作为数字处理的,4.3 例题分析与解答,上海交通大学网络教育学院,65,Al单元中的内容为数值3， B1单元中的内容为文字8(8),8 是作为文本处理的，8是作为数字处理的 AVERAGE(A1:B1) 的值是3， AVERAGE(3,8)的值是5.5； COUNT(A1:B1) 的值是1， COUNT(3,8)的值是2； MAX(A1:B1) 的值是3，MAX(3,8)的值是8； MIN(A1:B1) 的值是3，MIN(3,8)的值是3。,4.3 例

29、题分析与解答,上海交通大学网络教育学院,66,4.3 例题分析与解答, 在Excel中除第一行外，清单中的每一行被认为是数据的_ A字段B字段名 C标题行 D记录, D Excel可以用数据库的方式来管理数据，为用户提供了查询、排序、筛选和分类汇总等数据库操作的解决方法，在工作表的第一行，通常输入数据库的字段部分，从第二行开始则相当于数据库中的记录项。,上海交通大学网络教育学院,67,4.3 例题分析与解答,能够表现个体与整体之间关系的图表类型是_ A、柱形图 B、条形图 C、饼图 D、折线图, C 柱形图一般用来表现一段时期内数据的变化或各系列间数据的比较；条形图与柱形图功能项近；饼图可反映

30、了各数据在总体中所占的比例，即个体与整体之间的关系；折线图适合于反映数据的变动情况及变化趋势。,上海交通大学网络教育学院,68,4.3.2 操作题例题与解析 建立一个Excel工作簿，将其命名为My work 1, 保存在D盘的My work目录。定义Excel界面，令界面上显示“常用”、“格式”、“边框”和“绘图”工具栏。,4.3 例题分析与解答,上海交通大学网络教育学院,69,4.3 例题分析与解答,在同一个工作簿中复制“ABCD”工作表, 并将复制的表更名为“My table 1”. 然后在 My table 1 中将第一行的字体设为“黑体 小四号 加粗”。,上海交通大学网络教育学院,7

31、0,用Excel生成“九九表”。 本题主要考虑两个知识点：混合引用和公式复制。 操作步骤：在A2-A10填入数字19,在B1-J1填入19，由于当执行公式复制时，只有没被锁定的栏或列会有变化。在 B2 中输入= $A2*B$1，如图所示。复制 B2， 然后选取 B2:J10，粘贴，然后结果就出来了。或者通过拖拉B2单元格右下方的填充柄，覆盖整个9*9的范围。,4.3 例题分析与解答,上海交通大学网络教育学院,71,4.3 例题分析与解答,已知一个成绩表，共有1000条纪录，下表显示前15条纪录，如图所示。请在excel中操作，按课程分类汇总统计出两门课程期中和期末考试的平均分。,上海交通大学网络教育学院,72,4.4 例题分析与解答, 解题思路：分类汇总，首先进行排序，然后归类。 解题步骤： （1）首先将1000条数据按课程进行排序。（选择：菜单中 数据-排序）