第4章 电子表格Excel_2003的使用.ppt

《第4章 电子表格Excel_2003的使用.ppt》由会员分享，可在线阅读，更多相关《第4章 电子表格Excel_2003的使用.ppt（75页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第第4章章 电子表格电子表格Excel_2003的使用的使用第 2 页 共 75 页4.1Excel2003介绍 rr4.1.14.1.1任务一任务一ExcelExcel概述概述 rr4.1.24.1.2任务二任务二 工作簿、工作表、单元格和单元地址工作簿、工作表、单元格和单元地址 rr4.1.34.1.3任务三任务三ExcelExcel帮助帮助 第 3 页 共 75 页4.1.1任务一Excel概述1Excel的主要功能2Excel的启动与退出3Excel窗口4Excel文件的新建、打开与保存第 4 页 共 75 页4.1.2工作簿、工作表、单元格和单元地址 1工作簿Excel文档称为“Ex

2、cel工作簿”，其扩展名是“.xls”，每一个工作簿可包含多达255个独立工作表。2工作表Excel工作表是互相独立的，每个工作表由256列和65536行组成。3单元格和单元格地址 单元格是工作表的基本单元，是工作表中行和列的交点，单元格地址由单元格所在的“列名”和“行名”组成，如“A1”。单元格区域，如“A1：C3”。第 5 页 共 75 页4.1.3Excel帮助 打开帮助窗口：“帮助”“Microsoft Excel帮助”命令或【F1】键。在4.2.4节中将介绍如何使用Excel帮助来学习函数。第 6 页 共 75 页4.2工作表的编辑 r4.2.14.2.1任务一任务一 数据的输入数据

3、的输入 r4.2.24.2.2任务二任务二 自动填充自动填充 r4.2.34.2.3任务三任务三 数据有效性数据有效性r4.2.44.2.4任务四任务四 公式和函数公式和函数 r4.2.54.2.5任务五任务五 数据的编辑数据的编辑 第 7 页 共 75 页1输入文本 输入数字字符串时应先输入英文标点单引号“”2输入数值注意：负数的输入（数字前加负号或将数字括在括号内）和分数的输入（先输0和空格，再输分数）。3输入日期和时间 使用斜杠（/）或连字符（-）分开年、月、日。日期在Excel系统内部是用1900年1月1日起至该日期的天数存储的，此天数称为序列数。4.2.1任务一数据的输入 第 8 页

4、 共 75 页4.2.2任务二自动填充【例1】使用自动填充功能完成如图所示的表格输入。第 9 页 共 75 页1鼠标拖动填充等差数列第 10 页 共 75 页2序列填充 序列：等比数列、等差数列（公差任意）、按年（月、工作日）变化的日期等。第 11 页 共 75 页3自定义序列填充 用户还可以根据需要将常用的有规律的数据自行定义成序列。“工具”“选项”命令“自定义序列”选项卡 第 12 页 共 75 页4.2.3任务三数据有效性数据有效性，即在输入数据前，用户可以预先设置某一单元格或区域允许输入的数据类型、范围，并可设置数据输入提示信息和输入错误提示信息。【例2】打开“数据有效性设置.xls”

5、，设置数据有效性，要求：（1）当用户选中“性别”列的任一单元格时，在其右则显示一个下拉列表框箭头，并提供“男”和“女”的选择项供用户选择；（2）当选中“成绩”列的任一单元格时，显示“请输入0100的有效成绩”，其标题为“成绩”，当用户输入某一成绩值时，即进行检查，如果所输入的成绩不在指定的范围内，错误信息提示“成绩必须在0100之间”，“停止”样式，同时标题为“成绩错误”。第 13 页 共 75 页（1）对“性别”列的数据有效性设置必须使用英文逗号第 14 页 共 75 页（2）对“成绩”列的数据有效性设置第 15 页 共 75 页4.2.4任务四公式和函数 1输入公式 必须以等号“”作为起始

6、符 【例3】在“公式计算.xls”工作表的D2单元格中输入公式计算利息，并复制公式到D3:D7中。（利息本金年利率存款年数）第 16 页 共 75 页操作符号操作符号说明说明+加法加法-减法减法*乘法乘法/除法除法%百分数百分数 乘方乘方算术运算符算术运算符比较运算符比较运算符操作符号操作符号说明说明=等于等于 大于大于=大于等于大于等于不等于不等于公式中的运算符公式中的运算符文本运算符：文本运算符：文本运算符：文本运算符：&引用运算符引用运算符引用运算符引用运算符:,:,第 17 页 共 75 页2使用函数 函数名（参数1，参数2，）类型有：统计函数、数学函数、日期与时间函数、逻辑函数、财务

7、函数、查找函数、文本函数等。【例4】在“函数计算.xls”工作表中计算总分、平均分、最高分、最低分、总人数、男生人数和女生人数，并根据总分填写总评A和总评B情况。总评A：若总分=240则“合格”；若总分=340则“优”；总分=240,合格,不合格)；总评B公式：=IF(G11=340,优,IF(G11=340,优,IF(G11=240,良,差)。【注意】公式中的标点符号都必须是英文标点符号。第 19 页 共 75 页 【例5】某职工计划向银行贷款￥300,000购房，分10年偿还，已知银行年利率为8%，计算分期付款每月所应偿还的金额。输入公式：=PMT(8%/12,10*12,300000)。

8、结果：-3639.83元。注意：付出的金额用负号表示，收到的金额为正号。年利率需转换成月利率。第 20 页 共 75 页 【例6】某企业每月偿还能力为15000元，已知银行年利率为8%，计算以一年为限企业所能贷款的金额。输入公式：=PV(8%/12,12,-15000)。结果：172436.73元。第 21 页 共 75 页 【例7】某储户零存整取，每月存入1000元，年利率为4%，计算一年后可得金额。输入公式：=FV(4%/12,12,-1000)。结果：12222.46元。第 22 页 共 75 页 【例8】打开“VLOOKUP函数练习.xls”，在D11单元格中计算项目金额为14000元

9、的提成比例。输入公式：=VLOOKUP(14000,A2:B7,2)。结果：0.05。第 23 页 共 75 页3.函数帮助 Excel的函数很多，利用Excel帮助，就可以快速学习各种函数的使用。第 24 页 共 75 页4.单元格的绝对引用与相对引用公式中所引用的单元格地址会随着公式位置的变化而变化，称为相对引用。如“C3”等都是相对引用。单元格地址固定不变，称为绝对引用。在行号和列号前加美元符号“$”来表示，如“$A$1”。单元格的混合引用：即有绝对地址引用又有相对地址引用，如“B$1”。反复按F4可进行三种引用间的转换。第 25 页 共 75 页【例9】计算“绝对引用.xls”工作表中

10、销售人员的提成额。第 26 页 共 75 页4.2.5任务五数据的编辑 1单元格的选定（1）选定单个单元格（2）选定连续区域单元格（3）选定不连续单元格或区域2选定行、列或整个工作表3修改单元格内容4清除或删除单元格、行或列注意区别清除和删除的不同第 27 页 共 75 页5移动、复制单元格数据（1）移动或复制单元格数据（2）选择性粘贴 【例10】把“函数计算.xls”工作表Sheet1计算的总分值复制到工作表Sheet2的总分列上。第 28 页 共 75 页6插入单元格、行或列7撤销错误操作、重复上一次操作8查找和替换第 29 页 共 75 页4.3工作表的格式化 r4.3.14.3.1任务

11、一任务一 工作表的重命名、插入、复制、工作表的重命名、插入、复制、移动和删除移动和删除 r4.3.24.3.2任务二任务二 工作表窗口操作工作表窗口操作 r4.3.34.3.3任务三任务三 单元格格式设置单元格格式设置 r4.3.44.3.4任务四任务四 设置条件格式设置条件格式 r4.3.54.3.5任务五任务五 调整行高和列宽调整行高和列宽r4.3.64.3.6任务六任务六 自动套用格式自动套用格式 r4.3.74.3.7任务七任务七 页面设置和打印页面设置和打印 第 30 页 共 75 页4.3.1 任务一 工作表的重命名、插入、复制、移动和删除1重命名工作表【例11】将Sheet1工作

12、表重命名为“成绩分析表”。2插入工作表3移动或复制工作表4删除工作表第 31 页 共 75 页4.3.2任务二工作表窗口操作1工作表窗口的拆分2取消窗口拆分“窗口”“撤销拆分窗口”命令第 32 页 共 75 页4.3.3 任务三 单元格格式设置【例12】打开“格式化工作表.xls”，设置如下格式：标题为黑体、22号、加粗、蓝色、浅绿色底纹、A1：H11区域跨列居中；字段的格式：黑体、加粗、12号、水平居中对齐；“姓名”数据：分散对齐；其余数据：水平居中对齐；A2:H11区域：四周粗框线、内部细框线；“参加工作时间”数据按日期型数据“2000-1-1”方式重排；“基本工资”数据保留两位小数。第

13、33 页 共 75 页“格式”“单元格”命令1数字格式2对齐方式3字体格式4边框5图案第 34 页 共 75 页4.3.4任务四设置条件格式【例13】打开“格式化工作表.xls”，将工作表中的“基本工资”大于或等于2800的数据加粗倾斜、加25灰度底纹显示。“格式”“条件格式”命令第 35 页 共 75 页【例14】打开“格式化工作表.xls”，利用“实发工资”列对“基本工资”列设置条件格式：当实发工资大于或等于3200的，对应的基本工资字体颜色设置为红色且加粗；当应发工资小于或等于2500的，对应的基本工资字体颜色设置为篮色且加粗倾斜。第 36 页 共 75 页4.3.5任务五调整行高和列宽

14、 1鼠标拖动法 2菜单命令法“格式”“行”（列）“行高”（列宽）第 37 页 共 75 页【例15】将例7的字段行（第2行）的行高调整为25，并设置垂直水平居中对齐。第 38 页 共 75 页4.3.6任务六自动套用格式“格式”“自动套用格式”命令第 39 页 共 75 页4.3.7任务七页面设置和打印 1页面设置（1）页面（2）设置页边距第 40 页 共 75 页（4）工作表设置可以重复打印标题（3）页眉与页脚第 41 页 共 75 页2打印预览“常用”工具栏中的“打印预览”按钮“文件”“打印预览”命令3打印文件“文件”“打印”命令打印预览窗口第 42 页 共 75 页4.4数据的管理和分析

15、 r4.4.14.4.1任务一任务一 数据的排序数据的排序 r4.4.24.4.2任务二任务二 数据的筛选数据的筛选 r4.4.34.4.3任务三任务三 分类汇总分类汇总 r4.4.44.4.4任务四任务四 数据透视表数据透视表 r4.4.54.4.5任务五任务五 数据库函数数据库函数 r4.4.64.4.6任务六任务六 频度分析频度分析r4.4.74.4.7任务七任务七 数据保护数据保护第 43 页 共 75 页数据表（或称数据清单）的基本概念字段字段名记录第 44 页 共 75 页4.4.1任务一数据的排序1按单列排序【例16】打开文件“数据管理.xls”，将工作表中的数据按“工资”进行升

16、序排列。（1）单击“基本工资”所在列的任一单元格；（2）单击“常用”工具栏上的“升序”按钮。工资升序排列第 45 页 共 75 页2按多列排序【例17】打开文件“数据管理.xls”，将工作表中的数据按“职称”进行降序排列，相同职称的按“工资”升序排列。职称降序排列职称相同的按工资升序排列第 46 页 共 75 页“数据”“排序”命令第 47 页 共 75 页4.4.2任务二数据的筛选 1自动筛选“数据”“筛选”“自动筛选”命令【例18】打开文件“数据管理.xls”，筛选出“讲师”的记录。操作步骤是：在数据清单中，单击任一单元格。单击“数据”“筛选”“自动筛选”命令，每个字段名旁边出现“下拉式”

17、按钮。单击“职称”旁的下拉按钮，在下拉式列表中选择“讲师”（筛选条件）。第 48 页 共 75 页【例19】打开文件“数据管理.xls”，筛选出“讲师”和“副教授”的记录。操作步骤是：在数据清单中，单击任一单元格。单击菜单中的“数据”“筛选”“自动筛选”命令。单击“职称”字段旁的按钮，在下拉式列表中选择“自定义”。第 49 页 共 75 页【例20】打开文件“数据管理.xls”，筛选出“工资”在3500元至4500元之间的记录。【例21】打开文件“数据管理.xls”，筛选出“女”“讲师”的记录。方法是先对“性别”字段设置筛选条件，然后对“职称”字段设置筛选条件。第 50 页 共 75 页2高级

18、筛选查询条件比较复杂或必须经过计算才能进行查询的，这就要使用高级筛选方式。高级筛选的关键是建立条件区域设置筛选条件。选择数据表中空白区域作为条件区域。条件的设置应遵循以下要求：l字段名和条件应放在不同的单元格。l字段名最好采用复制、粘贴的方法放置在条件区域。l“与”关系的条件必须出现在同一行。如表示条件“工资在250元至350元之间”：工资工资工资工资工资工资工资工资=3500=3500=4500=3500=3500=4500=3500=3500=4500=4500（1）筛选条件：第 52 页 共 75 页（2）“数据”“筛选”“高级筛选”命令第 53 页 共 75 页 【例23】打开文件“数

19、据管理.xls”，筛选出姓李的副教授的记录。筛选条件还可以使用通配符，如表示条件“姓王的教师”：姓名姓名王王*第 54 页 共 75 页4.4.3任务三分类汇总【例24】打开文件“数据管理.xls”，按“性别”统计“工资”的平均值。操作步骤是：按“性别”降序或升序排序；单击“数据”“分类汇总”命令。第 55 页 共 75 页4.4.4任务四数据透视表 数据透视表是进一步的分类汇总。1创建数据透视表 【例25】打开文件“数据管理.xls”，统计不同性别、不同职称的教师的平均工资，结果放在新工作表中。第 56 页 共 75 页操作步骤是：操作步骤是：1.1.在数据清单中，单击任一单元格；在数据清单

20、中，单击任一单元格；2.“2.“数据数据”“”“数据透视表数据透视表”命令；命令；3.3.设置向导步骤之设置向导步骤之1 1；4.4.设置向导步骤之设置向导步骤之2 2；5.5.在向导步骤之在向导步骤之3 3中设置中设置“版式版式”；6.6.选择数据透视表的位置。选择数据透视表的位置。第 57 页 共 75 页2编辑数据透视表（1）添加或删除字段（2）改变汇总方式和布局 进入“数据透视表向导”第三步，在“版式”对话框中修改布局；打开“数据透视表字段”对话框，可更改汇总方式。第 58 页 共 75 页4.4.5任务五数据库函数 数据库函数与一般的函数不同，它不能用于任一数据区域，只能用于数据清单

21、中，并且还要附加一个条件区域。数据库求和函数：DSUM(database,field,criteria)数据库求平均值函数：DAVERAGE(database,field,criteria)数据库求最大值函数：DMAX(database,field,criteria)数据库求最小值函数：DMIN(database,field,criteria)数据库计数函数：DCOUNTA(database,field,criteria)第 59 页 共 75 页【例26】打开文件“数据管理.xls”，统计出女讲师的平均工资，计算结果放在G1单元格中。统计条件设置在G6为左上角的区域中。操作步骤是：在G6：H

22、7单元格中建立条件区域：性别性别性别性别职称职称职称职称女女讲师讲师在G1单元格中输入下面的公式=DAVERAGE(A1:D31,D1,G6:H7)，或=DAVERAGE(A1:D31,工资,G6:H7)，或=DAVERAGE(A1:D31,4,G6:H7)。第 60 页 共 75 页4.4.6任务六频度分析 频度分析是指求出指定区域中数值的频率分布。语法：FREQUENCY(data_array,bins_array)【例27】打开文件“频率分布数据.xls”，统计出成绩在各区间的人数：小于60分、6069分、7079分、8089分和90分以上。操作步骤是：1.输入分段点成绩值，如单元格G5

23、：G8。2.选择区域F5：F9，输入公式“=FREQUENCY(C2:C19,G5:G8)”3.按组合键【Ctrl+Shift+Enter】第 61 页 共 75 页4.4.7任务七数据保护1保护工作簿与撤消保护工作簿保护工作薄结构：对工作薄不能进行移动、删除、隐藏、取消隐藏、重命名以及新增工作表等操作。保护工作薄窗口：每次打开工作薄时，工作薄窗口具有固定的位置和大小。“工具”“保护”“保护工作薄”命令第 62 页 共 75 页2工作薄文件的安全性设置“工具”“选项”“安全性”选项卡第 63 页 共 75 页3单元格保护与撤消单元格保护 【例28】打开文件“工资发放表.xls”，保护工作表中除

24、“应发工资”外的单元格，使得被保护的单元格不可编辑、变更或删除；并且“所得税”计算公式不可见。操作步骤：选择“应发工资”区域B3：B7，单击“格式”“单元格”菜单命令，并选择“保护”选项卡，取消选中“锁定”复选框。选择“所得税”区域C3：C7，单击“格式”“单元格”菜单命令，并选择“保护”选项卡，选择“锁定”和“隐藏”复选框。单击“工具”“保护”“保护工作表”菜单命令，选择“保护工作表及锁定的单元格内容”，如图4-68所示。单击【确定】按钮后即可保护除“应发工资”外的单元格，并隐藏了“所得税”计算公式。第 64 页 共 75 页4.5图表的创建与编辑 r4.5.14.5.1任务一任务一 创建图

25、表创建图表 r4.5.24.5.2任务二任务二 图表的编辑与图表的格式化图表的编辑与图表的格式化 第 65 页 共 75 页 【例29】打开文件“图表数据.xls”，为工作表创建图表，图表类型为“簇状柱形图”，数据区域为A3：D7，系列产生在列，图表标题为“20042007年度销售情况统计表”，分类轴（X轴）标题为“年度”，数值轴（Y轴）标题为“元”，图表放在/嵌入当前工作表中。4.5.14.5.1任务一任务一 创建图表创建图表第 66 页 共 75 页操作步骤操作步骤:1.1.选定创建图表所用数据区域选定创建图表所用数据区域2.“2.“插入插入”“”“图表图表”;”;或或“常用常用”工工具栏

26、上的具栏上的“图表向导图表向导”按钮按钮 3.3.选择图表类型选择图表类型4.4.选择选择“数据区域数据区域”5.5.设置标题、设置标题、X X轴、轴、Y Y轴轴6.6.选择生成的图表放置的位置选择生成的图表放置的位置第 67 页 共 75 页 【例30】打开文件“频率分布数据.xls”，按例26统计出成绩在各区间的人数后，给数据区域F4:F9和H4:H9创建三维饼图。第 68 页 共 75 页提示：进入图表向导第2步时看不到正确数据，这时要选择“系列”选项卡。第 69 页 共 75 页1调整图表的大小和位置2更改图表类型、数据源、图表选项3图表区域内的各种对象的格式设置：双击图表对象;或先选

27、择图表对象然后单击“图表”工具栏中的按钮4.5.24.5.2任务二任务二 图表的编辑与图表的格式化图表的编辑与图表的格式化 第 70 页 共 75 页 【例31】将例29生成的图表的“图表区”和“图例”添加底纹，改变数值轴标题的文字方向，并且把数值轴刻度的最小值改为10000，主要刻度单位为30000。第 71 页 共 75 页4.6综合实例 4.6.1实例1学生成绩表管理 此实例是日常使用的学生成绩表，它综合了工作表数据输入、格式设置、函数计算、高级筛选等内容，实现简单教务管理功能。第 72 页 共 75 页4.6.2案例2企业销售统计分析 此实例对月销售情况进行统计分析，综合函数计算、数据

28、透视表、图表等内容，实现按年度对不同分店进行销售总量的比较功能。(a)(b)第 73 页 共 75 页4.6.3实例3某企业卡拉OK大奖赛评分数据统计与分析此实例是某企业举行卡拉OK大奖赛评分数据统计与分析进行统计分析，综合函数计算、复杂条件格式、单格元地址混引用等内容，统计各歌手最后得分，名次，并对评分较高及较低的评委的分数进行突出显示，以达到对评委监督的目的。第 74 页 共 75 页统计要求：按照比赛规则，去掉一个最高分、去掉一个最低分，剩余分数的平均分作为选手的最后得分，用函数计算，最后得分保留两位小数。利用RANK函数进行名次排列。当某评委的评分比8个评委的平均分高10%以上，分数以红色显示；当评委分数低于8个平均分10%以上，分数以红色显示。第 75 页 共 75 页设置条件格式：