excel-公式与函数的应用ppt课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,公式与函数的应用,1,一、教学重点：,公式的应用,二、教学难点：,公式的编辑,三、教学方法：,任务驱动法,2,公式的规则,所有的,Excel,公式都具有相同的基本结构：,1、,以等号（,=,）开头，后面跟随一个或多个运算码。,2、,运算码可以是,值、单元格引用、单元格区域、或者函数名，其间以一个或多个运算符连接。,3、,运算符包括,算术运算符、逻辑运算符或者字符连接符（,&,）等。,3,公式的规则,1、Excel,在一个公式中允许使用的最大字符数为,1024,个。,2、,公式中运算符与运算码之间可以加入空格，以便分隔公式中的各个部分，从而提高公式的可读性。,4,公式的编辑,1,公式的输入,（,1,）选中要输入公式的单元格；,（,2,）输入,“,=,”,作为准备输入公式的开始；,（,3,）输入组成该公式的所有运算码和运算符；,（,4,）按回车键对输入的公式表示确认。,5,公式的编辑,2,公式的修改,（,1,）按,F2,键，可以直接编辑修改单元格中的内容；（,2,）双击该单元格，可以直接编辑修改单元格中的内容；,（,3,）选择需要进行编辑的单元格，然后单击编辑栏，在编辑栏中对公式进行修改。,当编辑结束后按,【Enter】,键即可完成操作。如果要取消编辑操作，可以按,Esc,键。,6,公式返回的错误信息返回值,错误提示,原因,#,NAME?,使用了,EXCEL,不能识别的名字,#,N/A,使用了不能使用的值,#,NULL!,指定了无效的“空”区域,#,NUM！,公式中使用了无效数字值,#,REF!,引用了无效单元格,#,VALUE!,不正确的参数或运算对象,#,列宽不够,DIV/0!,除数为0,7,3,公式的移动和复制,公式移动和复制的操作方法与单元格移动和复制类似。,选择性粘贴,编辑,清除,应用公式的注意事项,公式中所有的运算符都必须在,英文半角状态,下输入,公式中的字符串常量需要以,英文双引号（“”）,括起来,8,一、,在电子表格中应用公式,算术运算符,+*/(乘方）,%（,百分比）比较运算符,=,比较运算的结果为,TRUE,或,FALSE,文本连接运算符:&,假如,B2,单元格内容：信息时代，,D6,单元格内容：,ABC,。,则,=B2&D6,的值是,信息时代,ABC,=“21,世纪是”,&B2,的值是,21,世纪是信息时代,9,一、,在电子表格中应用公式,运算符的优先级,()算术运算 连接运算 比较运算,高,低,-,（取负）,%,*/,+,-,例如：,-5%*6/5+10,10,二、相对引用、绝对引用、混合引用,对单元格地址的三种引用方式,绝对引用 相对引用 混合引用,相对引用：,直,接使用单元格或单元格区域的名称作为引用名称。,A5+D6 =F4+F8,绝对引用：在单元格名称的列标与行号前加符号“$”表示绝对引用。,$,A$4+$B$5,混合引用：相对引用与绝对引用相结合,=F$3+$D4,说明：,1,、当公式使用,相对引用,时，把公式复制到一个新的位置，公,式中的单元格引用也跟随变化，但相对于公式所在单元格的位置不变,。,2,、在公式中使用,绝对引用,，不论公式复制到何处，,公式中所引用的单元格不发生变化,。,11,二、相对引用、绝对引用、混合引用,跨表格数据的引用,1.,引用同一工作薄其他工作表中的单元格或区域,工作表标签名,!,单元格地址,2.,引用同一工作薄多张工作表的相同单元格或区域,第一个工作表名：最后一个工作表名,!,单元格地址,3.,不同工作薄之间的数据引用,工作簿名,工作表标签名,!,单元格地址,12,三、电子表格中的常用函数,1、求和函数：,SUM（number1,，,number2number30,）,2、,求平均数函数：,AVERAGE（number1,，,number2number30）,3、,求最大值和最小值：,MAX（,number1,，,number2number30,）,MIN（,number1,，,number2number30,）,调用方法,:,“插入”,“,函数”,编辑栏上的,fx,按钮,工具栏上的自动填充 按钮,13,利用公式或函数完成以下任务,任务,1,：计算销售数据完成“北京路分店”、山东路分店香港路分店的上半年、下半年、全年、总计的计算。完成占销售总额比例的计算。,任务,2,：完成“教学计划”表中黄色区域的计算。,任务,3,：计算“人事表”中职称工资。职称工资,=,基本工资*,10%,14,任务,4,：计算“第二单元 人事表”中的工龄、校龄,4、,取整函数,INT（）,：,将数字向下舍入到最接近的整数,例：,INT（8.9),结果为8,例：,INT（8.9）,结果为9,9,8.9,8,-8,-8.9,-9,将数字向下舍入到最接近的整数,0,15,三、电子表格中的常用函数,5、返回当前系统日期：,TODAY（）,TODAY(),结果为,=INT(TODAY()-,人事表!,E4)/365),16,6、条件函数,IF(,测试条件，满足条件时的输出结果，不满足条件时的输出结果）,例1：,”,学生成绩表,”,，条件如下：大于等于,300,分显示及格，小于,300,分显示不及格,IF(,成绩表!,G2=300,及格,不及格）,17,三、电子表格中的常用函数（续）,例2：计算业务津贴：博导350元，其余人为0。,=,IF(,人事表!,I4=“,博导”,350,0),例,3,：根据学生成绩划分等级。,=,IF(C2=90,“优秀”,IF(C2=80,“良好”,IF(C2=70,“中等”,IF(C2=60,“及格”,“不及格”),18,任务,5,：用,IF,函数计算“人事表”中业务津贴：博导350元，其余人为0,任务,6,：用,IF,函数输入“人事表”中基本工资。教授,1500,，副教授,1200,，讲师,1000,，助教,800.,任务,7,：用,IF,函数计算“人事表”中个人所得税。当应发工资合计在,800,（含,800,）元以下时，不交税；当应发工资在,800,以上，,1300,以下，超过,800,部分按,5%,计税。当应发工资在,1300,以上，超过,1300,部分按,10%,计税，,800-1300,之间的部分按,5%,计税。如计算秦小宁公式如下：,=IF(K3800,0,IF(K3=1300,(K3-800)*5%,(K3-1300)*10%+500*5%),19,三、电子表格中的常用函数,7,、计数函数,Count,（）：,格式：,Count,（指定区域）：计算个数，,只能计算数值型数据,，,不能计算文本、空单元格,。,例如：计算“学生成绩表”中学生的人数。,公式：,=COUNT(G3:G15),区域可以选择有数值的任意列,20,三、电子表格中的常用函数,8,、,COUNTIF,函数：,计算制定区域中满足给定条件的单元格数目。,注意：该函数要求第二个参数为,文本字符串,。,格式：,COUNTIF(,指定区域 ,条件),例1：计算“人事表”中低于平均工资的人数,=,COUNTIF(K3:K26,“90),例,3,：计算“学生成绩表”中男生人数和女生人数,=COUNTIF(B3:B15,男,),=COUNTIF(B3:B15,“,女,),21,排位函数,Rank,函数,格式,：,=RANK(,指定的单元格,排序的范围,0,或非,0),如，,=RANK(A1,$A$1:$A$5,0),22,四、工作表的安全与保护,1、锁定与隐藏,锁定：,工作表中选定的单元格区域,不能被更改、移动、调整或删除；,隐藏：,工作表中选定的单元格区域,中的公式（包括函数）不会在编辑栏中显示。,注意：“锁定”与“隐藏”操作只有在实施工作表保护后才有效，且必须先设置单元格格式中的保护功能，顺序不能颠倒。,23,四、工作表的安全与保护,2,、工作表保护操作过程,选取所有单元格或需要保护的单元格区域，然后进行如下设置：,格式单元格保护锁定、隐藏选项,工具保护保护工作表,3,、工作表保护的其他选项,设置单元格格式,行、列的插入与删除,保护密码的设置,4,、取消对工作表的保护,24,四、工作表的安全与保护,4、保护单元格中的部分区域,注意：区域的保护也需要先设置单元格的保护选项。,设置单元格的保护选项,设定“允许用户编辑区域”；,保护工作表,5、撤消保护单元格区域：删除区域,25,五、工作簿的保护,工具保护保护工作簿,结构选项：不能对其中的工作表进行删除、增加、移动、隐藏、取消隐藏、重命名操作。,窗口选项：不能对工作簿窗口进行移动、缩放、关闭、拆分等操作。,（,例：格式工作表隐藏）,26,