[IT认证]第4部分 电子表格处理.ppt

第四部分,电子表格处理,目 录,4.1 Excel 2003 基础,4.2 案例1教师工资管理表的建立与编辑,4.3 案例2学生成绩管理,4.4 案例3教师工资管理的数据库应用,掌握Excel工作簿的建立、编辑、工作表的格式化、图表和数据库的应用。,本章重点,4.1 Excel 2003基础,4.1.1 Excel的基本概念,4.1.2 工作簿的基本操作,1.工作簿：,处理并存储数据的文件。,工作簿由若干张工作表组成。,一个工作簿默认时含3张工作表： Sheet1、Sheet2、Sheet3,2.工作表：,处理和存储数据的基本工作单位(255),由若干个单元格组成,最大：256列65536行,4.1.1 Excel的基本概念,列标（字母表示）：A Z、AA AZ、BABZ、 HA HZ、IA IV。 行号（数字表示）：165536。,(1)工作表的最小单位,位置由列号行号决定。,(2)最多可以输入32000个字符,如A2，D5,(3)单元格命名：工作簿名工作表名!单元格名,表示：“工资.xls”工作簿Sheet2工作表中 的D4单元格。,例如: 工资 Sheet2!D4,说明：若表示当前工作簿工作表中的单元格 可省略前两项,D4,由多个单元格组成，可以连续也可以不连续。,连续单元格的区域表示为： 左上角单元格名:右下角单元格名 例如：B2:D4,1 Excel 2003的窗口,4 打开工作簿,2 新建工作簿,3 保存工作簿,6 关闭工作簿,5 查找工作簿,4.1.2 工作簿的基本操作,7 选定单元格,地址栏：当前单元格或区域的地址。,工作表格区,1 Excel的窗口,方法一: 启动 Excel- “Book1”。,方法三： 、Ctrl+N。,(2)创建基于模板的工作簿,“文件”/“新建”/“电子表格模板”标签中的模板。,2 新建工作簿,方法二：“文件”/“新建”/“常用”“工作簿”。,.选择存放位置 .输入文件名 .确定文件类型,-为已有工作簿保存副本,“文件”/“另存为”,(1)新工作簿的保存,“文件”/“保存”。, 按钮。,3 保存工作簿,Ctrl+s。,(2)保存摘要信息,作用:便于查找操作。 “文件”/“属性”/“摘要信息”,(1)在Excel窗口中,.“文件”/“打开”。 . 按钮。 .组合键Ctrl+o。,(2)在“我的电脑”或“资源管理器”中 双击 图标对应的文件。,4 打开工作簿,操作步骤： (1)调出“打开”对话框； (2)单击“工具”按钮； (3)添入查找条件，单击“添至列表”按钮； (4)确定查询范围，单击“开始查找”按钮。 如图：,5 查找工作簿,点击后将查找条件添至条件列表框,“属性”与“取值”之间的关系,为一次搜索条件命名并保存,查找文件的属性依据,首先选为当前文件，然后执行关闭操作。,.“文件”/“关闭”。,6 关闭工作簿,.Ctrl+F4或Ctrl+W。,.双击控制菜单或单击后选择“关闭”。,.“关闭”按钮。,方法：,连续多个： 整 行: 单击行号。 整 列: 单击列号。 全部单元格:单击全选按钮，或Ctrl+A。,单个：单击目标单元格、输入单元格名称或利用键盘。,7 选定单元格,不连续多个：ctrl键,矩形区域 沿对角线拖动； Shift键单击； 左上角单元格名称：右下角单元格名称,4.2.1 学习目标,4.2.2 实现方法和步骤,4.2 案例1教师工资管理表的建立与编辑,4.2.1 学习目标,本节介绍如何使用Excel进行教师工资管理。通过本案例的学习，可以掌握Excel的数据录入、格式设置、公式与函数的使用等编辑功能。,1.建立字段（输入列标题）,4.2.2 实现方法和步骤,2. 信息的输入之1常量的输入 (1)输入文本,1）字符,默认:文本左对齐,单元格中分行：定位光标，Alt+Enter。,注意： 与=“ ”必须是英文标点符号。,2）纯数字 输入格式: 数字 或 = “数字”,056001 或 =“056001”,方法二：选定单元格，设置单元格格式/数字/文本,(2) 输入数值 整数、小数或科学计数法等方式输入。可在数字中加千分号,如 “1,450”,(1)分数前加“0”和空格。 如：2/3表示日期2月3日。,注意：,(2)正数“”可以省略，负数“”或“（）”。 如：100和（100）表示同一个数。,(3)Excel数值输入与数值显示未必相同。如单元格数字格式设置为带两位小数，如果输入三位小数，则末位将进行四舍五入。Excel在进行计算时是以输入数值为准的。但有一种情况例外，Excel的数字精度为15位，超过15位，Excel会将多余的数字转换为0。,数值：右对齐, 日期 分隔符：/ 或 -,(3) 输入日期和时间型,如: 2005/5/1 、 5/1/2005 200551、512005 51、 5/1,格式规定：年月日、月日年、月日。,！空格, 时间,如 2:30:21 AM 、 2:30 a,如 2:30,AM、PM大小写均可。,如果省略默认为上午。,hh:mm:ss A,AM/P,PM,日期和时间型在单元格中右对齐,公式组成：常数、运算符、单元格名、函数等；,算术运算符 +、-、*、/、%等 关系运算符 、= 、 、= 、=、 文本运算符 & 引用运算符 ：和 ，,(1)公式中的运算符, 信息的输入之2公式的输入,：-区域运算符,对两个引用之间的所有单元格进行引用。如SUM(A3：B4)-Shift,，-联合运算符,将多个引用合并为一个引用。如SUM（A3，B4）-Ctrl,(2)输入公式表达式 选定存放公式计算结果的单元格； =公式表达式,优点：一处修改，引用它的 公式单元内容将跟着修改。,单击 或回车键确认。,如：A1表示使用单元格的相对地址。 （随公式单元格的位置变化而变化）,三种引用输入时的转换： 选定单元格，按F4键。,$A$1表示使用单元格的绝对地址。 （不随公式位置的变化而变化）,(3)使用函数,Excel提供了许多内置函数，为用户对数据进行运算和分析带来极大方便，包括：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本等。,函数的语法形式： 函数名（参数1，参数2） 参数可以是常量、单元格、区域、区域名、公式或其他函数。,1、函数的输入：,方法二：使用“函数向导”。 “插入”/“函数”； 工具栏 按钮。,方法一：自动求和按钮 ； 方法二：右击状态栏选择函数，选定区域， 结果也在状态栏。,2、自动求和和自动计算。,方法一:直接输入函数名及所需参数。,(4)公式中单元格的引用,相对引用（地址）:直接使用列号行号（默认）。 如B4,在公式的复制中有所区别。,混合引用（地址）:在单元格的列号或行号前加$ 如B$4、$B4,绝对引用（地址）:在单元格的列号和行号前加$ 如$B$4,一、简单关联,操作,选定框,选定起始数据单元格；,沿一个方向拖动。, 信息的输入之3自动输入,使用填充柄时： （1）选中一个单元格 复制：文本、数值、公式；,注意：纯数字使用+拖动可实现等差填充； 使用+拖动可实现复制。,填充：数字和文字混合体。如：B1、1B Excel的自动填充序列。 日期型数据。,（2）选中存在等差关系的连续单元格（至少两个）按等差关系进行自动填充,一次只能沿一个方向填充 向右边或下方拖动,关联值大,反之变小,选定填充区域 “编辑”菜单/ “填充”/“序列”。 注：选定填充区域时必须包含初始值的单元格。,二、复杂关联,操作,“工具”/“选项”/“自定义序列”,三、自定义填充序列,常见的错误提示,“#” 单元格宽度不够。 “#Value!” 引用公式时有错误的输入类型 “#NUM!” 数字、参数有错误。 “#REF ” 单元格引用无效。 “#NAME!” 使用Excel不能识别的名称,4.3.1 学习目标,4.3.2 实现方法和步骤,4.3 案例2学生成绩管理,4.3.1 学习目标,本节介绍如何使用Excel进行学生成绩管理。通过本案例的学习，可以掌握Excel的工作表的编辑、工作表格式设置、公式与函数的使用、工作表的基本操作等知识点。,1.建立字段（输入列标题） 依次在A1至F1单元格内输入各列数据的标题“学号”、“班级”、“姓名”、“实验成绩”、“期末成绩”、“总成绩”。 2.信息的输入 其中学号（文本型）和班级，可以采用填充来实现。“总成绩”的计算公式为：总成绩=实验成绩20%+期末成绩80%。先利用公式计算“实验成绩”的平均分，然后用填充来实现公式复制。,4.3.2 实现方法和步骤, 选定对象：单元格、行、列 “插入”菜单/“单元格”、“行”、“列”。,(1)插入单元格、行、列,3.插入新列“名次”和表标题“学生成绩管理”,(2)删除（略）,或单击右键，弹出快捷菜单，如图：,选定行或列中任意一单元格； “格式”/“行/行高”、“列/列宽”。,调整行号的下方(或列号的右侧)分隔线。, 使用菜单调整（精确）, 拖动调整（快速）,1、调整行高、列宽,4.调整行高和列宽,(4)复制列宽,(3)自动调整,将鼠标对准某列右侧(行下方)的分隔线； 当指针变为 时，双击鼠标。,选中基准列中的单元格； “复制”； 选定目标列； “编辑”/“选择性粘贴”“列宽”选项。,数据格式 字体 对齐方式 边框 图案,5.设置单元格的格式,方法一：“格式”/“单元格” ；,方法二：选定，快捷菜单/“设置单元格格式”。,设置单元格内容水平及垂直的对齐方式。,设置各种数字的显示方式等。,设置单元格的文字、边框和底纹,利用单元格格式对话框,三种方法： 双击工作表标签。 “格式”/“工作表”/“重命名”。 右击工作表标签，选择“重命名”命令。,6.工作表的重新命名,2、删除,两种方法 选定被删除的工作表。 “编辑”/“删除工作表”， (或快捷菜单) 。,工作表一旦删除，不能使用“撤消”命令恢复,相关知识工作表的插入、删除,1、插入,两种方法 “插入”/“工作表”。 右击工作表标签,快捷菜单。,（1）选定工作表标签; （2）方法一：“编辑”/“移动或复制工作表”。 方法二：沿标签栏拖动。,（1）选定工作表标签; （2）方法一：“编辑”/“移动或复制工作表”。 方法二： +拖动。,3、移动,4、复制, 使用RANK函数填充名次 在G3单元格处输入公式“RANK(F3,F$3:F$22,1)”,然后拖动填充柄向下填充，即可得到每人的名次。此处排名次用到了RANK函数，它的语法为： RANK(number,ref,order) 其中：number为需要找到排位的数字；Ref为包含一组数字的数组或引用；Order为一数字，指明排位的方式。如果 order 为0或省略，将ref按降序排列；如果order不为零按升序排列。,7.使用函数和条件格式进行成绩分析,利用条件格式把不及格的分数用红色突出显示 选择D3:F23(即所有成绩)，然后选择“格式”菜单中的“条件格式”命令，弹出“条件格式”对话框，如图4.24所示。首先设定条件为“单元格数值小于60分”，然后单击“格式”按钮，把颜色设为红色，再按“确定”按钮即可。, 建立成绩分析工作表 下面统计计算机课程的分数段、最高分、最低分、标准差等。 总人数：在C3中插入函数公式“=COUNT(计算机!F3:F22)” COUNT函数中的参数只有数字类型的数据才被计算，所以本例可以只能将任一成绩列作为参数。 90分以上人数：在D3中插入函数公式“=COUNTIF(计算机!F3:F22,“=90“)”； 80-89分的人数：在D3中插入函数公式“=COUNTIF(计算机!F3:F22,“=80“)-COUNTIF(计算机!F3:F22,“=90“)”； 70-79分的人数：在F3中插入函数公式“=COUNTIF(计算机!F3:F22,“=70“)-COUNTIF(计算机!F3:F22,“=80“)”；,60-69分的人数：在G3中插入函数公式“=COUNTIF(计算机!F3:F22,“=60“)-COUNTIF(计算机!F3:F22,“=70“)”； 60分以下人数：在D3中插入函数公式“=COUNTIF(计算机!F3:F22,“60“)”； 所占比例：在D4中插入函数公式“=D3/$C3”,拖动填充柄向右填充至H4，并设置D4至H4的格式为百分比，小数位数为1位； 最高分：在I4中插入函数公式“=MAX(计算机!F3:F22)”； 最低分：在J4中插入函数公式“=MIN(计算机!F3:F22)”； 用STDEV函数计算标准差在I4中插入函数公式“=STDEV(计算机!F3:F22)”。,8.页面设置与打印,(1)利用“图表向导”创建,“插入”/“图表”（根据提示进行设置）, 选定制作图表的数据区域,(2)“图表向导”按钮,“图表”工具栏或常用工具栏 。,9.创建图表,图表分为嵌入式图表和图形图表两种。,嵌入式图表和数据表放在同一工作表中，图形图 表是指图表单独放在一张工作表中。,嵌入式图表 图形图表 （作为新表）,(1)编辑图表中的数据系列,(2)更改图表类型,(3)图表的格式化,10.图表的编辑, 添加数据系列, 鼠标拖动。（只适用于嵌入式图表）, “图表”/“添加数据”。,操作方法（三种）,注意：只有选定图表时才出现“图表”菜单！,“图表”/“数据源”，利用“系列”对话框。,选定图表；,(1)编辑图表中的数据系列,向图表添加数据,添加前,添加后,工作表中数据删除或修改,对应系列自动更改。,操作: . 选定图表中要删除的数据系列。 . ；快捷菜单/清除。,一：, 删除数据系列,选定图表,三种方法：,（1）右击图表，快捷菜单/图表类型。 （2）“图表”/图表类型。 （3）“图表”工具栏。,(2)更改图表类型,图表标题,数值轴标题,(3)图表的格式化,各对象的格式化操作基本相似，都是在其相应的格式对话框中进行设置，打开格式对话框的方法有以下三种： 直接双击要格式化的对象。 右击选定对象，在弹出的快捷菜单中选择相应选项。 单击要格式化的对象，选择“格式”菜单中的相应选项,Excel,4.4 案例3-教师工资管理的数据库应用,4.4.1 学习目标,4.4.2 预备知识数据库的基本概念,4.4.3 学习方法和步骤,本节介绍如何使用Excel实现对数据的查找、排序、筛选等数据库管理功能。,4.4.1 学习目标,数据库是结构化的数据的集合,又称为“数据模型”,1.数据库的概念,4.4.2 数据库的基本概念,2.关系型数据库,具有二维表的形式,特点: .每一列代表一个基本的数据项。,.行、列的顺序任意，但非空。,.不能有相同的行。,.每一列的名字唯一,且类型一致。,数据清单:即一个数据库,概念：,数据库结构：所有字段的集合。,记录: 表中的“行”,一组字段值的集合,描述实体对象。,字段：表中的“列”，包括一个字段名和若干个字段值。,数据库结构,1. 建立数据库,2. 数据库的编辑,4.4.3 实现方法和步骤,操作步骤： 打开一张空白工作表； 在第一行各列输入字段名； 输入各记录的值,方法一：直接输入 方法二：记录单,直接输入数据 下一个字段： 下一条记录: ,1. 建立数据库,记录单的使用： 选定标题行中的任一单元格； “数据”/“记录单” （如果数据清单中没有数据， 会弹出一个提示框，单击“确定”即可。）,标题栏为所在工作表标签的名称,添加一条空白记录,浏览已添加的记录,在“记录单”内修改： “数据”/“记录单”； 用“上一条”、“下一条”滚动条调出后修改。,在单元格中直接修改:双击被修改的单元格,编辑数据,2. 数据库的编辑,增加记录： 在工作表数据区域增加空行； 利用“记录单”。,编辑记录或字段,删除记录, 选定记录，“编辑”/“删除”； 可使用“撤销”命令进行撤销。, 在“记录单”中，找到记录，选择“删除”。 无法撤销。,(3)增加、删除字段 增加字段：插入空列； 删除字段：删除列。,3.查找基本工资在2000元以上（包括2000元）的记录,方 法,单击数据清单中任一单元格;,“数据”/记录单-单击“条件”;,输入条件后,单击“下一条”;,.若有多条记录满足条件,利用“上一条”、“下一条”按钮进行浏览。,说明：排序方向(升、降) 关键字:用于排序的字段名(一个或多个),4.排序操作,将排序数据库所在工作表选为当前工作表， 选定数据清单中的任一单元格： “数据”/“排序” （一个或多个字段） “排序”按钮 （一个字段）,注意:字符和汉字的排序按字母ASCII码来排列，例如：ab,Aa,方 法,分类：自动筛选和高级筛选,筛选：把数据库中满足条件的记录显示出来，而 其它记录隐藏起来(不是删除),以利于分析。,5.数据筛选之1自动筛选,(1)自动筛选,“数据”/“筛选”/ “自动筛选”。,筛选条件（条件区域的建立） .在工作表空白处建立；,6.数据筛选之2高级筛选,实现复杂条件的筛选，并且筛选结果可以放在新的工作表中。,.条件中用到的字段名写在同一行中且连续,必须与数据清单中的完全一致(建