(精品)第4章电子表格Excel 2003的使用

第,75,页 共,75,页,上一页,下一页,关 闭,目 录,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第,4,章 电子表格软件,Excel 2003,的使用,4.1 Excel 2003,的基本知识,4.2,工作表的编辑,4.3,工作表的格式化,4.4,数据的管理和分析,4.5,图表的创建与编辑,4.6,综合实例,4.7,上机实训,4.1 Excel 2003,介绍,4.1.1,任务一,Excel,概述,4.1.2,任务二,工作簿、工作表、单元格和单元地址,4.1.3,任务三,Excel,帮助,4.1.1,任务一,Excel,概述,1,Excel,的主要功能,2,Excel,的启动与退出,3,Excel,窗口,4,Excel,文件的新建、打开与保存,4.1.2,工作簿、工作表、单元格和单元地址,1,工作簿,Excel,文档称为“,Excel,工作簿”，其扩展名是“,.,xls,”,，,每一个工作簿可包含多达,255,个独立工作表。,2,工作表,Excel,工作表是互相独立的，,每个工作表由,256,列和,65536,行组成,。,3,单元格和单元格地址,单元格,是工作表的基本单元，是工作表中行和列的交点，,单元格地址,由单元格所在的“列名”和“行名”组成，如“,A1”,。,单元格区域,，如“,A1,：,C3”,。,4.1.3 Excel,帮助,打开帮助窗口：,“帮助”,“Microsoft Excel,帮助”命令或,【F1】,键 。,在,4.2.4,节中将介绍如何使用,Excel,帮助来学习函数,。,4.2,工作表的编辑,4.2.1,任务一,数据的输入,4.2.2,任务二,自动填充,4.2.3,任务三,数据有效性,4.2.4,任务四,公式和函数,4.2.5,任务五,数据的编辑,1,输入文本,输入数字字符串时应先输入英文标点单引号“,”,2,输入数值,注意：负数的输入（数字前加负号或将数字括在括号内）和分数的输入（先输,0,和空格，再输分数）。,3,输入日期和时间,使用斜杠（,/,）或连字符（,-,）分开年、月、日,。,日期在,Excel,系统内部是用,1900,年,1,月,1,日起至该日期的天数存储的，此天数称为序列数。,4.2.1,任务一,数据的输入,4.2.2,任务二,自动填充,【,例,1,】,使用自动填充功能完成如图所示的表格输入。,1,鼠标拖动填充等差数列,2,序列填充,序列：等比数列、等差数列（公差任意）、按年（月、工作日）变化的日期等。,3,自定义序列填充,用户还可以根据需要将常用的有规律的数据自行定义成序列,。,“工具”,“,选项”命令,“,自定义序列”选项卡,4.2.3,任务三,数据有效性,数据有效性，即在输入数据前，用户可以预先设置某一单元格或区域允许输入的数据类型、范围，并可设置数据输入提示信息和输入错误提示信息。,【,例,2】,打开“数据有效性设置,.,xls,”,，设置数据有效性，要求：,（,1,）当用户选中“性别”列的任一单元格时，在其右则显示一个下拉列表框箭头，并提供“男”和“女”的选择项供用户选择；,（,2,）当选中“成绩”列的任一单元格时，显示“请输入,0,100,的有效成绩”，其标题为“成绩”，当用户输入某一成绩值时，即进行检查，如果所输入的成绩不在指定的范围内，错误信息提示“成绩必须在,0,100,之间”，“停止”样式，同时标题为“成绩错误”。,（,1,）对“性别”列的数据有效性设置,必须使用英文逗号,（,2,）对“成绩”列的数据有效性设置,4.2.4,任务四,公式和函数,1,输入公式,必须以等号“”作为起始符,【,例,3】,在“公式计算,.,xls,”,工作表的,D2,单元格中输入公式计算利息，并复制公式到,D3:D7,中。（利息本金,年利率,存款年数）,操作符号,说明,+,加法,-,减法,*,乘法,/,除法,%,百分数,乘方,算术运算符,比较运算符,操作符号,说明,=,等于,大于,=,大于等于,不等于,公式中的运算符,文本运算符：,&,引用运算符,:,: ,2,使用函数,函数名（参数,1,，参数,2,，,）,类型有：统计函数、数学函数、日期与时间函数、逻辑函数、财务函数、查找函数、文本函数等。,【,例,4】,在“函数计算,.,xls,”,工作表中计算总分、平均分、最高分、最低分、总人数、男生人数和女生人数，并根据总分填写总评,A,和总评,B,情况。,总评,A,：,若总分,=240,则“合格”；若总分,=340,则“优”；总分,=240,合格,不合格,),；,总评,B,公式：,=IF(G11=340,优,IF(G11=340,优,IF(G11=240,良,差,),。,【,注意,】,公式中的标点符号都必须是英文标点符号。,【,例,5】,某职工计划向银行贷款￥,300,000,购房，分,10,年偿还，已知银行年利率为,8%,，计算分期付款每月所应偿还的金额。,输入公式：,=PMT(8%/12,10*12,300000),。,结果：,-3639.83,元。,注意：,付出的金额用负号表示，收到的金额为正号。年利率需转换成月利率。,【,例,6】,某企业每月偿还能力为,15000,元，已知银行年利率为,8%,，计算以一年为限企业所能贷款的金额。,输入公式：,=PV(8%/12,12,-15000),。,结果：,172436.73,元。,【,例,7】,某储户零存整取，每月存入,1000,元，年利率为,4%,，计算一年后可得金额。,输入公式：,=FV(4%/12,12,-1000),。,结果：,12222.46,元。,【,例,8】,打开“,VLOOKUP,函数练习,.,xls,”,，在,D11,单元格中计算项目金额为,14000,元的提成比例。,输入公式：,=VLOOKUP(14000,A2:B7,2),。,结果：,0.05,。,3.,函数帮助,Excel,的函数很多，利用,Excel,帮助，就可以快速学习各种函数的使用。,4.,单元格的绝对引用与相对引用,公式中所引用的单元格地址会随着公式位置的变化而变化，称为,相对引用,。,如“,C3,”,等都是相对引用。,单元格地址固定不变，称为,绝对引用,。在行号和列号前加,美元符号“,$,”,来表示，如,“,$A$1,”,。,单元格的,混合引用,：,即有绝对地址引用又有相对地址引用 ，如 “,B$1”,。,反复按,F4,可进行三种引用间的转换。,【,例,9】,计算“绝对引用,.,xls,”,工作表中销售人员的提成额。,4.2.5,任务五,数据的编辑,1,单元格的选定,（,1,）选定单个单元格,（,2,）选定连续区域单元格,（,3,）选定不连续单元格或区域,2,选定行、列或整个工作表,3,修改单元格内容,4,清除或删除单元格、行或列,注意区别清除和删除的不同,5,移动、复制单元格数据,（,1,）移动或复制单元格数据,（,2,）选择性粘贴,【,例,10】,把“函数计算,.,xls,”,工作表,Sheet1,计算的总分值复制到工作表,Sheet2,的总分列上。,6,插入单元格、行或列,7,撤销错误操作、重复上一次操作,8,查找和替换,4.3,工作表的格式化,4.3.1,任务一,工作表的重命名、插入、复制、,移动和删除,4.3.2,任务二,工作表窗口操作,4.3.3,任务三,单元格格式设置,4.3.4,任务四,设置条件格式,4.3.5,任务五,调整行高和列宽,4.3.6,任务六,自动套用格式,4.3.7,任务七,页面设置和打印,4.3.1,任务一,工作表的重命名、插入、复制、移动和删除,1,重命名工作表,【,例,11】,将,Sheet1,工作表重命名为“成绩分析表”。,2,插入工作表,3,移动或复制工作表,4,删除工作表,4.3.2,任务二,工作表窗口操作,1,工作表窗口的拆分,2,取消窗口拆分,“窗口”,“,撤销拆分窗口”命令,4.3.3,任务三,单元格格式设置,【,例,12】,打开“格式化工作表,.,xls,”,，,设置如下格式：,标题为黑体、,22,号、加粗、蓝色、浅绿色底纹、,A1,：,H11,区域跨列居中；,字段的格式：黑体、加粗、,12,号、水平居中对齐；,“姓名”数据：分散对齐；其余数据：水平居中对齐；,A2:H11,区域：四周粗框线、内部细框线；,“参加工作时间”数据按日期型数据“,2000-1-1”,方式重排；,“基本工资”数据保留两位小数。,“格式”,“,单元格”命令,1,数字格式,2,对齐方式,3,字体格式,4,边框,5,图案,4.3.4,任务四,设置条件格式,【,例,13】,打开“格式化工作表,.,xls,”,，将工作表中的“基本工资”大于或等于,2800,的数据加粗倾斜、加,25,灰度底纹显示。,“格式”,“,条件格式”命令,【,例,14】,打开“格式化工作表,.,xls,”,，利用“应发工资”列对“基本工资”列设置条件格式：,当实发工资大于或等于,3200,的，对应的基本工资字体颜色设置为红色且加粗；,当应发工资小于或等于,2500,的，对应的基本工资字体颜色设置为篮色且加粗倾斜。,4.3.5,任务五,调整行高和列宽,1,鼠标拖动法,2,菜单命令法,“,格式,”,“,行,”,（列）,“,行高,”,（列宽）,【,例,15】,将例,7,的字段行（第,2,行）的行高调整为,25,，并设置垂直水平居中对齐。,4.3.6,任务六,自动套用格式,“格式”,“,自动套用格式”命令,4.3.7,任务七,页面设置和打印,1,页面设置,（,1,）页面 （,2,）设置页边距,（,4,）工作表设置,可以重复打印标题,（,3,）页眉与页脚,2,打印预览,“常用”工具栏中的“打印预览”按钮,“文件”,“,打印预览” 命令,3,打印文件,“文件”,“,打印”命令,打印预览窗口,4.4,数据的管理和分析,4.4.1,任务一,数据的排序,4.4.2,任务二,数据的筛选,4.4.3,任务三,分类汇总,4.4.4,任务四,数据透视表,4.4.5,任务五,数据库函数,4.4.6,任务六,频度分析,4.4.7,任务七,数据保护,数据表（或称数据清单） 的基本概念,字段,字段名,记录,4.4.1,任务一,数据的排序,1,按单列排序,【,例,16】,打开文件“数据管理,.,xls,”,，,将工作表中的数据按“工资”进行升序排列。,（,1,）单击“基本工资”所在列的任一单元格；,（,2,）单击“常用”工具栏上的“升序”按钮。,工资升序排列,2,按多列排序,【,例,17】,打开文件“数据管理,.,xls,”,，,将工作表中的数据按“职称”进行降序排列，相同职称的按“工资”升序排列。,职称降序排列,职称相同的按工资升序排列,“数据”,“,排序”命令,4.4.2,任务二,数据的筛选,1,自动筛选,“数据”,“,筛选”,“,自动筛选”命令,【,例,18】,打开文件“数据管理,.,xls,”,，,筛选出“讲师”的记录。,操作步骤是：,在数据清单中，单击任一单元格。,单击“数据”,“,筛选”,“,自动筛选”命令，每个字段名旁边出现“下拉式”按钮。,单击“职称”旁的下拉按钮，在下拉式列表中选择“讲师”（筛选条件）。,【,例,19】,打开文件“数据管理,.,xls,”,，,筛选出“讲师”和“副教授”的记录。,操作步骤是：,在数据清单中，单击任一单元格。,单击菜单中的“数据”,“,筛选”,“,自动筛选”命令。,单击“职称”字段旁的按钮，在下拉式列表中选择“自定义”。,【,例,20】,打开文件“数据管理,.,xls,”,，,筛选出“工资”在,3500,元至,4500,元之间的记录。,【,例,21】,打开文件“数据管理,.,xls,”,，,筛选出“女”“讲师”的记录。,方法是先对“性别”字段设置筛选条件，然后对“职称”字段设置筛选条件 。,2,高级筛选,查询条件比较复杂或必须经过计算才能进行查询的，这就要使用高级筛选方式。,高级筛选的关键是建立,条件区域,设置筛选条件。选择数据表中空白区域作为条件区域。条件的设置应遵循以下要求：,l,字段名和条件应放在不同的单元格。,l,字段名最好采用复制、粘贴的方法放置在条件区域。,l,“,与”关系的条件必须出现在同一行。,如表示条件“工资在,250,元至,350,元之间”：,工资,工资,=3500,=3500,=3500,=4500,（,1,）筛选条件：,（,2,）“数据”,“,筛选”,“,高级筛选”命令,【,例,23,】,打开文件“数据管理,.,xls,”,，,筛选出姓李的副教授的记录。,筛选条件还可以使用通配符，如表示条件“姓王的教师”：,姓名,王*,4.4.3,任务三,分类汇总,【,例,24,】,打开文件“数据管理,.,xls,”,，,按“性别”统计“工资”的平均值。,操作步骤是：,按“性别”降序或升序排序；,单击“数据”,“,分类汇总”命令。,4.4.4,任务四,数据透视表,数据透视表是进一步的分类汇总。,1,创建数据透视表,【,例,25】,打开文件“数据管理,.,xls,”,，,统计不同性别、不同职称的教师的平均工资，结果放在新工作表中。,操作步骤是：,1.,在数据清单中，单击任一单元格；,2.“,数据”,“,数据透视表”命令；,3.,设置向导步骤之,1,；,4.,设置向导步骤之,2,；,5.,在向导步骤之,3,中设置“版式”；,6.,选择数据透视表的位置。,2,编辑数据透视表,（,1,）添加或删除字段,（,2,）改变汇总方式和布局,进入“数据透视表向导”,第三步，在“版式”对话框中修改布局；,打开,“数据透视表字段”对话框，可更改汇总方式。,4.4.5,任务五,数据库函数,数据库函数与一般的函数不同，它不能用于任一数据区域，只能用于数据清单中，并且还要附加一个条件区域。,数据库求和函数：,DSUM,(database, field, criteria),数据库求平均值函数：,DAVERAGE,(database, field, criteria),数据库求最大值函数：,DMAX,(database, field, criteria),数据库求最小值函数：,DMIN,(database, field, criteria),数据库计数函数：,DCOUNTA,(database, field, criteria),【,例,26】,打开文件“数据管理,.,xls,”,，,统计出女讲师的平均工资，计算结果放在,G1,单元格中。统计条件设置在,G6,为左上角的区域中。,操作步骤是：,在,G6,：,H7,单元格中建立条件区域：,性别,职称,女,讲师,在,G1,单元格中输入下面的公式,=,DAVERAGE(A1:D31,D1,G6:H7),，,或,=,DAVERAGE(A1:D31,工资,G6:H7),，,或,=,DAVERAGE(A1:D31,4,G6:H7),。,4.4.6,任务六,频度分析,频度分析是指求出指定区域中数值的频率分布。,语法：,FREQUENCY,(data_array, bins_array),【,例,27】,打开文件“频率分布数据,.,xls,”,，,统计出成绩在各区间的人数：小于,60,分、,60,69,分、,70,79,分、,80,89,分和,90,分以上。,操作步骤是：,1.,输入分段点成绩值，如单元格,G5,：,G8,。,2.,选择区域,F5,：,F9,，,输入公式“,=FREQUENCY(C2:C19, G5:G8)”,3.,按组合键,【Ctrl+Shift+Enter】,4.4.7,任务七,数据保护,1,保护工作簿与撤消保护工作簿,保护工作薄,结构,：对工作薄不能进行移动、删除、隐藏、取消隐藏、重命名以及新增工作表等操作。,保护工作薄,窗口,：每次打开工作薄时，工作薄窗口具有固定的位置和大小。,“工具”,“,保护”,“,保护工作薄”命令,2,工作薄文件的安全性设置,“工具”,“,选项”,“安全性”选项卡,3,单元格保护与撤消单元格保护,【,例,28】,打开文件“工资发放表,.,xls,”,，保护工作表中除“应发工资”外的单元格，使得被保护的单元格不可编辑、变更或删除；并且“所得税”计算公式不可见。,操作步骤：,选择,“,应发工资,”,区域,B3,：,B7,，单击,“,格式,”,“,单元格,”,菜单命令，并选择,“,保护,”,选项卡，,取消,选中,“,锁定,”,复选框。,选择,“,所得税,”,区域,C3,：,C7,，单击,“,格式,”,“,单元格,”,菜单命令，并选择,“,保护,”,选项卡，选择,“,锁定,”,和,“,隐藏,”,复选框。,单击,“,工具,”,“,保护,”,“,保护工作表,”,菜单命令，选择,“,保护工作表及锁定的单元格内容,”,，如图,4-68,所示。,单击,【,确定,】,按钮后即可保护除,“,应发工资,”,外的单元格，并隐藏了,“,所得税,”,计算公式。,4.5,图表的创建与编辑,4.5.1,任务一,创建图表,4.5.2,任务二,图表的编辑与图表的格式化,【,例,29】,打开文件“图表数据,.,xls,”,，,为工作表创建图表，,图表类型为“簇状柱形图”，,数据区域为,A3,：,D7,，,系列产生在列，,图表标题为“,2004,2007,年度销售情况统计表”，分类轴（,X,轴）标题为“年度”，数值轴（,Y,轴）标题为“元”，,图表放在,/,嵌入当前工作表中。,4.5.1,任务一,创建图表,操作步骤,:,1.,选定创建图表所用数据区域,2.“,插入”,“,图表”,;,或 “常用”工具栏上的“图表向导”按钮,3.,选择图表类型,4.,选择“数据区域”,5.,设置标题、,X,轴、,Y,轴,6.,选择生成的图表放置的位置,【,例,30】,打开文件“频率分布数据,.,xls,”,，按例,26,统计出成绩在各区间的人数后，给数据区域,F4:F9,和,H4:H9,创建三维饼图。,提示：,进入图表向导第,2,步时看不到正确数据，这时要选择“系列”选项卡。,1,调整图表的大小和位置,2,更改图表类型、数据源、图表选项,3,图表区域内的各种对象的格式设置：,双击图表对象,;,或,先,选择图表对象然后单击,“图表”工具栏中,的按钮,4.5.2,任务二,图表的编辑与图表的格式化,【,例,31,】,将例,29,生成的图表的“图表区”和“图例”添加底纹，改变数值轴标题的文字方向，并且把数值轴刻度的最小值改为,10000,，主要刻度单位为,30000,。,4.6,综合实例,4.6.1,实例,1,学生成绩表管理,此实例是日常使用的学生成绩表，它综合了工作表数据输入、格式设置、函数计算、高级筛选等内容，实现简单教务管理功能。,4.6.2,案例,2,企业销售统计分析,此实例对月销售情况进行统计分析，综合函数计算、数据透视表、图表等内容，实现按年度对不同分店进行销售总量的比较功能,。,(a),(b),4.6.3,实例,3,某企业卡拉,OK,大奖赛评分数据统计与分析,此实例是某企业举行卡拉,OK,大奖赛评分数据统计与分析进行统计分析，综合函数计算、复杂条件格式、单格元地址混引用等内容，统计各歌手最后得分，名次，并对评分较高及较低的评委的分数进行突出显示，以达到对评委监督的目的。,统计要求：,按照比赛规则，去掉一个最高分、去掉一个最低分，剩余分数的平均分作为选手的最后得分，用函数计算，最后得分保留两位小数。,利用,RANK,函数进行名次排列。,当某评委的评分比,8,个评委的平均分高,10%,以上，分数以红色显示；当评委分数低于,8,个平均分,10%,以上，分数以红色显示。,设置条件格式：,