[IT认证]第4部分--电子表格处理课件

1、第四部分电子表格处理目 录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、Sheet32.工作表：处理和存储数据的基本工作单位(255)由若干个单元格组成最大：256列65536行4.1.1 Excel的基

2、本概念 列标（字母表示）：A Z、AA AZ、BABZ、 HA HZ、IA IV。行号（数字表示）：165536。3.单元格(1)工作表的最小单位,位置由列号行号决定。(2)最多可以输入32000个字符如A2，D5(3)单元格命名：工作簿名工作表名!单元格名表示：“工资.xls”工作簿Sheet2工作表中 的D4单元格。例如: 工资 Sheet2!D4 说明：若表示当前工作簿工作表中的单元格 可省略前两项D44.单元格区域 由多个单元格组成，可以连续也可以不连续。连续单元格的区域表示为： 左上角单元格名:右下角单元格名 例如：B2:D41 Excel 2003的窗口 4 打开工作簿2 新建工作

3、簿3 保存工作簿6 关闭工作簿5 查找工作簿4.1.2 工作簿的基本操作7 选定单元格数据编辑栏：显示、输入、修改单元格中的数据和公式全选按钮地址栏：当前单元格或区域的地址。 工作表标签栏移到首标签左移一个标签工作表格区1 Excel的窗口 (1)创建空白工作簿方法一: 启动 Excel- “Book1”。方法三： 、Ctrl+N。 (2)创建基于模板的工作簿“文件”/“新建”/“电子表格模板”标签中的模板。2 新建工作簿方法二：“文件”/“新建”/“常用”“工作簿”。 .选择存放位置 .输入文件名 .确定文件类型 -为已有工作簿保存副本 “文件”/“另存为”(1)新工作簿的保存 “文件”/“

4、保存”。 按钮。 3 保存工作簿Ctrl+s。(2)保存摘要信息 作用:便于查找操作。“文件”/“属性”/“摘要信息”(1)在Excel窗口中 .“文件”/“打开”。. 按钮。.组合键Ctrl+o。(2)在“我的电脑”或“资源管理器”中 双击 图标对应的文件。 4 打开工作簿操作步骤： (1)调出“打开”对话框； (2)单击“工具”按钮； (3)添入查找条件，单击“添至列表”按钮； (4)确定查询范围，单击“开始查找”按钮。如图： 5 查找工作簿 点击后将查找条件添至条件列表框“属性”与“取值”之间的关系为一次搜索条件命名并保存查找文件的属性依据 首先选为当前文件，然后执行关闭操作。.“文件”

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的数据录入、格式设置、公式与函数的

6、使用等编辑功能。 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

7、)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日期和时间型在单元

8、格中右对齐公式组成：常数、运算符、单元格名、函数等； 算术运算符 +、-、*、/、%等 关系运算符 、= 、 、= 、=、 文本运算符 & 引用运算符 ：和 ， (1)公式中的运算符 信息的输入之2公式的输入：-区域运算符,对两个引用之间的所有单元格进行引用。如SUM(A3：B4)-Shift，-联合运算符,将多个引用合并为一个引用。如SUM（A3，B4）-Ctrl(2)输入公式表达式 选定存放公式计算结果的单元格； =公式表达式 优点：一处修改，引用它的公式单元内容将跟着修改。 单击 或回车键确认。如:=SUM（ E3:E7） 含常数的公式： 引用单元格名的公式： 带函数的公式：如:=10*

9、22如:=C3*3+D3*7 如：A1表示使用单元格的相对地址。 （随公式单元格的位置变化而变化）三种引用输入时的转换： 选定单元格，按F4键。$A$1表示使用单元格的绝对地址。（不随公式位置的变化而变化）(3)使用函数 Excel提供了许多内置函数，为用户对数据进行运算和分析带来极大方便，包括：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本等。函数的语法形式：函数名（参数1，参数2）参数可以是常量、单元格、区域、区域名、公式或其他函数。1、函数的输入：方法二：使用“函数向导”。 “插入”/“函数”； 工具栏 按钮。方法一：自动求和按钮 ；方法二：右击状态栏选择函数，选定区域

10、， 结果也在状态栏。2、自动求和和自动计算。方法一:直接输入函数名及所需参数。(4)公式中单元格的引用 相对引用（地址）:直接使用列号行号（默认）。 如B4在公式的复制中有所区别。混合引用（地址）:在单元格的列号或行号前加$ 如B$4、$B4绝对引用（地址）:在单元格的列号和行号前加$ 如$B$4一、简单关联操作选定框选定起始数据单元格；沿一个方向拖动。 信息的输入之3自动输入鼠标在填充柄附近变为“ ”状； 使用填充柄时：（1）选中一个单元格复制：文本、数值、公式；注意：纯数字使用+拖动可实现等差填充； 使用+拖动可实现复制。填充：数字和文字混合体。如：B1、1B Excel的自动填充序列。

11、日期型数据。（2）选中存在等差关系的连续单元格（至少两个）按等差关系进行自动填充一次只能沿一个方向填充向右边或下方拖动,关联值大,反之变小选定填充区域“编辑”菜单/ “填充”/“序列”。注：选定填充区域时必须包含初始值的单元格。二、复杂关联 操作“工具”/“选项”/“自定义序列”三、自定义填充序列常见的错误提示 “#” 单元格宽度不够。 “#Value!” 引用公式时有错误的输入类型 “#NUM!” 数字、参数有错误。 “#REF ” 单元格引用无效。 “#NAME!” 使用Excel不能识别的名称4.3.1 学习目标 4.3.2 实现方法和步骤4.3 案例2学生成绩管理 4.3.1 学习目标

12、 本节介绍如何使用Excel进行学生成绩管理。通过本案例的学习，可以掌握Excel的工作表的编辑、工作表格式设置、公式与函数的使用、工作表的基本操作等知识点。 1.建立字段（输入列标题） 依次在A1至F1单元格内输入各列数据的标题“学号”、“班级”、“姓名”、“实验成绩”、“期末成绩”、“总成绩”。2.信息的输入 其中学号（文本型）和班级，可以采用填充来实现。“总成绩”的计算公式为：总成绩=实验成绩20%+期末成绩80%。先利用公式计算“实验成绩”的平均分，然后用填充来实现公式复制。4.3.2 实现方法和步骤 选定对象：单元格、行、列“插入”菜单/“单元格”、“行”、“列”。 新的插入对象出现

13、在选定位置上方或左侧 (1)插入单元格、行、列3.插入新列“名次”和表标题“学生成绩管理”(2)删除（略）或单击右键，弹出快捷菜单，如图：选定行或列中任意一单元格；“格式”/“行/行高”、“列/列宽”。 调整行号的下方(或列号的右侧)分隔线。 使用菜单调整（精确） 拖动调整（快速）1、调整行高、列宽4.调整行高和列宽(4)复制列宽(3)自动调整 将鼠标对准某列右侧(行下方)的分隔线； 当指针变为 时，双击鼠标。选中基准列中的单元格；“复制”；选定目标列；“编辑”/“选择性粘贴”“列宽”选项。 数据格式字体对齐方式边框图案5.设置单元格的格式方法一：“格式”/“单元格” ；方法二：选定，快捷菜单

14、/“设置单元格格式”。设置单元格内容水平及垂直的对齐方式。设置各种数字的显示方式等。设置单元格的文字、边框和底纹利用单元格格式对话框三种方法： 双击工作表标签。 “格式”/“工作表”/“重命名”。 右击工作表标签，选择“重命名”命令。6.工作表的重新命名2、删除 两种方法 选定被删除的工作表。 “编辑”/“删除工作表”， (或快捷菜单) 。 工作表一旦删除，不能使用“撤消”命令恢复相关知识工作表的插入、删除1、插入两种方法 “插入”/“工作表”。 右击工作表标签,快捷菜单。（1）选定工作表标签;（2）方法一：“编辑”/“移动或复制工作表”。 方法二：沿标签栏拖动。（1）选定工作表标签;（2）方

15、法一：“编辑”/“移动或复制工作表”。 方法二： +拖动。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(即所有成绩)，然

16、后选择“格式”菜单中的“条件格式”命令，弹出“条件格式”对话框，如图4.24所示。首先设定条件为“单元格数值小于60分”，然后单击“格式”按钮，把颜色设为红色，再按“确定”按钮即可。 建立成绩分析工作表下面统计计算机课程的分数段、最高分、最低分、标准差等。 总人数：在C3中插入函数公式“=COUNT(计算机!F3:F22)”COUNT函数中的参数只有数字类型的数据才被计算，所以本例可以只能将任一成绩列作为参数。 90分以上人数：在D3中插入函数公式“=COUNTIF(计算机!F3:F22,=90)”；80-89分的人数：在D3中插入函数公式“=COUNTIF(计算机!F3:F22,=80)-C

17、OUNTIF(计算机!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(计算机!F

18、3:F22)”； 最低分：在J4中插入函数公式“=MIN(计算机!F3:F22)”； 用STDEV函数计算标准差在I4中插入函数公式“=STDEV(计算机!F3:F22)”。8.页面设置与打印 (1)利用“图表向导”创建 “插入”/“图表”（根据提示进行设置） 选定制作图表的数据区域(2)“图表向导”按钮“图表”工具栏或常用工具栏 。9.创建图表图表分为嵌入式图表和图形图表两种。注意标签 嵌入式图表和数据表放在同一工作表中，图形图表是指图表单独放在一张工作表中。嵌入式图表 图形图表 （作为新表）(1)编辑图表中的数据系列 (2)更改图表类型 (3)图表的格式化 10.图表的编辑 添加数据系列

19、鼠标拖动。（只适用于嵌入式图表） “图表”/“添加数据”。 操作方法（三种）注意：只有选定图表时才出现“图表”菜单！“图表”/“数据源”，利用“系列”对话框。选定图表；(1)编辑图表中的数据系列 向图表添加数据添加前添加后工作表中数据删除或修改,对应系列自动更改。操作: . 选定图表中要删除的数据系列。 . ；快捷菜单/清除。 一：工作表中的数据不变, 只删除图表中的系列。二： 删除数据系列选定图表三种方法：（1）右击图表，快捷菜单/图表类型。（2）“图表”/图表类型。（3）“图表”工具栏。 (2)更改图表类型图表标题数值轴标题分类轴标题数据序列图例网格线绘图区图表区(3)图表的格式化 各对象

20、的格式化操作基本相似，都是在其相应的格式对话框中进行设置，打开格式对话框的方法有以下三种： 直接双击要格式化的对象。 右击选定对象，在弹出的快捷菜单中选择相应选项。 单击要格式化的对象，选择“格式”菜单中的相应选项Excel4.4 案例3-教师工资管理的数据库应用4.4.1 学习目标4.4.2 预备知识数据库的基本概念 4.4.3 学习方法和步骤 本节介绍如何使用Excel实现对数据的查找、排序、筛选等数据库管理功能。 4.4.1 学习目标 数据库是结构化的数据的集合指一定的逻辑层次关系又称为“数据模型”关系模型数据库模型层次模型网状模型1.数据库的概念 4.4.2 数据库的基本概念2.关系型

21、数据库 具有二维表的形式特点: .每一列代表一个基本的数据项。 .行、列的顺序任意，但非空。.不能有相同的行。.每一列的名字唯一,且类型一致。数据清单:即一个数据库概念：数据库结构：所有字段的集合。 记录: 表中的“行”,一组字段值的集合,描述实体对象。字段：表中的“列”，包括一个字段名和若干个字段值。字段值字段名数据库结构字段(列）记 录1. 建立数据库2. 数据库的编辑4.4.3 实现方法和步骤操作步骤：打开一张空白工作表；在第一行各列输入字段名；输入各记录的值 方法一：直接输入方法二：记录单直接输入数据下一个字段：下一条记录: 1. 建立数据库记录单的使用： 选定标题行中的任一单元格；

22、“数据”/“记录单” （如果数据清单中没有数据，会弹出一个提示框，单击“确定”即可。）标题栏为所在工作表标签的名称添加一条空白记录浏览已添加的记录在“记录单”内修改： “数据”/“记录单”； 用“上一条”、“下一条”滚动条调出后修改。 在单元格中直接修改:双击被修改的单元格编辑数据2. 数据库的编辑增加记录：在工作表数据区域增加空行；利用“记录单”。编辑记录或字段删除记录 选定记录，“编辑”/“删除”； 可使用“撤销”命令进行撤销。 在“记录单”中，找到记录，选择“删除”。 无法撤销。(3)增加、删除字段 增加字段：插入空列； 删除字段：删除列。3.查找基本工资在2000元以上（包括2000元

23、）的记录输入条件方 法 单击数据清单中任一单元格;“数据”/记录单-单击“条件”;输入条件后,单击“下一条”; .若有多条记录满足条件,利用“上一条”、“下一条”按钮进行浏览。说明：排序方向(升、降)关键字:用于排序的字段名(一个或多个) 4.排序操作 将排序数据库所在工作表选为当前工作表， 选定数据清单中的任一单元格： “数据”/“排序” （一个或多个字段） “排序”按钮 （一个字段）注意:字符和汉字的排序按字母ASCII码来排列，例如：ab,Aa方 法 分类：自动筛选和高级筛选筛选：把数据库中满足条件的记录显示出来，而其它记录隐藏起来(不是删除),以利于分析。 5.数据筛选之1自动筛选(1)自动筛选“数据”/“筛选”/“自动筛选”。筛选条件（条件区域的建立） .在工作表空白处建立；6.数据筛选之2高级筛选 实现复杂条件的筛选，并且筛选结果可以放在新的工作表