excel公式与函数的应用课件

1、1excel-公式与函数的应用一、教学重点： 公式的应用公式的应用二、教学难点： 公式的编辑公式的编辑三、教学方法： 任务驱动法任务驱动法2excel-公式与函数的应用公式的规则公式的规则所有的excel公式都具有相同的基本结构：1、以等号（=）开头，后面跟随一个或多个运算码。2、运算码可以是值、单元格引用、单元格区域、或者函数名，其间以一个或多个运算符连接。3、运算符包括算术运算符、逻辑运算符或者字符连接符（&）等。3excel-公式与函数的应用公式的规则公式的规则1、excel在一个公式中允许使用的最大字符数为1024个。2、公式中运算符与运算码之间可以加入空格，以便分隔公式中的各个部分，

2、从而提高公式的可读性。4excel-公式与函数的应用公式的编辑公式的编辑1 1公式的输入公式的输入（1）选中要输入公式的单元格；（2）输入“=”作为准备输入公式的开始；（3）输入组成该公式的所有运算码和运算符；（4）按回车键对输入的公式表示确认。5excel-公式与函数的应用公式的编辑公式的编辑2 2公式的修改公式的修改（1）按f2键，可以直接编辑修改单元格中的内容；（2）双击该单元格，可以直接编辑修改单元格中的内容；（3）选择需要进行编辑的单元格，然后单击编辑栏，在编辑栏中对公式进行修改。当编辑结束后按【enter】键即可完成操作。如果要取消编辑操作，可以按esc键。6excel-公式与函数

3、的应用公式返回的错误信息返回值公式返回的错误信息返回值错误提示原因#name?使用了excel不能识别的名字#n/a使用了不能使用的值#null!指定了无效的“空”区域#num！公式中使用了无效数字值#ref!引用了无效单元格#value!不正确的参数或运算对象#列宽不够div/0!除数为07excel-公式与函数的应用3 3公式的移动和复制公式的移动和复制 公式移动和复制的操作方法与单元格移动和复制公式移动和复制的操作方法与单元格移动和复制类似类似 。 选择性粘贴 编辑清除应用公式的注意事项 公式中所有的运算符都必须在下输入 公式中的字符串常量需要以括起来8excel-公式与函数的应用一、在

4、电子表格中应用公式一、在电子表格中应用公式算术运算符 + * / (乘方） %（百分比）比较运算符 = = 比较运算的结果为 true 或 false文本连接运算符: & 假如b2单元格内容：信息时代，d6单元格内容：abc。 则=b2&d6 的值是信息时代abc =“21世纪是”&b2 的值是21世纪是信息时代 9excel-公式与函数的应用一、在电子表格中应用公式一、在电子表格中应用公式运算符的优先级 ( ) 算术运算 连接运算 比较运算10excel-公式与函数的应用二、相对引用、绝对引用、混合引用二、相对引用、绝对引用、混合引用对单元格地址的三种引用方式 相对引用：直接使用单元格或单元

5、格区域的名称作为引用名称。a5+d6 =f4+f8绝对引用：在单元格名称的列标与行号前加符号“$”表示绝对引用。$a$4+$b$5混合引用：相对引用与绝对引用相结合=f$3+$d4说明：1、当公式使用相对引用时，把公式复制到一个新的位置，公式中的单元格引用也跟随变化，但相对于公式所在单元格的位置不变。2、在公式中使用绝对引用，不论公式复制到何处，公式中所引用的单元格不发生变化。11excel-公式与函数的应用二、相对引用、绝对引用、混合引用二、相对引用、绝对引用、混合引用跨表格数据的引用1.引用同一工作薄其他工作表中的单元格或区域 工作表标签名工作表标签名! !单元格地址单元格地址2.引用同一

6、工作薄多张工作表的相同单元格或区域 第一个工作表名：最后一个工作表名第一个工作表名：最后一个工作表名! ! 单元格地址单元格地址3.不同工作薄之间的数据引用 工作簿名工作簿名 工作表标签名工作表标签名! ! 单元格地址单元格地址12excel-公式与函数的应用三、电子表格中的常用函数三、电子表格中的常用函数1、求和函数： sum（ number1，number2number30）2、求平均数函数： average（ number1，number2number30 ）3、求最大值和最小值： max（ number1，number2number30 ） min（ number1，number2nu

7、mber30 ） 调用方法: “插入”“函数” 编辑栏上的fx按钮 工具栏上的自动填充 按钮13excel-公式与函数的应用利用公式或函数完成以下任务利用公式或函数完成以下任务任务1 ：计算销售数据完成“北京路分店”、山东路分店、香港路分店的上半年、下半年、全年、总计的计算。完成占销售总额比例的计算。任务2：完成“教学计划”表中黄色区域的计算。任务3：计算“人事表”中职称工资。职称工资=基本工资*10%14excel-公式与函数的应用任务任务4 4：计算：计算“第二单元第二单元 人事表人事表”中的工龄、校龄中的工龄、校龄4、取整函数int（ ） ：将数字向下舍入到最接近的整数 例： int（8

8、.9) 结果为8 例： int（8.9） 结果为998.98-8-8.9-9将数字向下舍入到最接近的整数将数字向下舍入到最接近的整数015excel-公式与函数的应用三、电子表格中的常用函数三、电子表格中的常用函数 5、返回当前系统日期：today（ ） today( ) 结果为 =int(today( )-人事表!e4)/365)16excel-公式与函数的应用6 6、条件函数、条件函数if(测试条件，满足条件时的输出结果，不满足条件时的输出结果） 例1：”学生成绩表” ，条件如下：大于等于300分显示及格，小于300分显示不及格 if(成绩表!g2=300,及格,不及格）17excel-公

9、式与函数的应用三、电子表格中的常用函数（续）三、电子表格中的常用函数（续）例2：计算业务津贴：博导350元，其余人为0。 =if(人事表!i4=“博导”,350,0)例3：根据学生成绩划分等级。 =if(c2=90, “优秀”, if(c2=80, “良好”, if(c2=70, “中等”, if(c2=60, “及格”,“不及格”)18excel-公式与函数的应用任务5：用if函数计算“ 人事表”中业务津贴：博导350元，其余人为0任务6：用if函数输入“ 人事表”中基本工资。教授1500，副教授1200，讲师1000，助教800.任务7：用if函数计算“ 人事表”中个人所得税。当应发工资合

10、计在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%)）19excel-公式与函数的应用三、电子表格中的常用函数三、电子表格中的常用函数7、计数函数count（）：格式：count（指定区域）：计算个数，只能计算数值型数据，不能计算文本、空单元格。例如：计算“学生成绩表”中学生的人数。 公式：=count(g3:g15

11、) 20excel-公式与函数的应用三、电子表格中的常用函数三、电子表格中的常用函数 8、countif函数：计算制定区域中满足给定条件的单元格数目。注意：该函数要求第二个参数为格式：countif(指定区域 , 条件)例1：计算“人事表”中低于平均工资的人数 =countif(k3:k26, “90)例3：计算“学生成绩表”中男生人数和女生人数 =countif(b3:b15,男) =countif(b3:b15,“女)21excel-公式与函数的应用排位函数排位函数rank函数格式：=rank(=rank(指定的单元格指定的单元格, ,排序的范围排序的范围,0,0或非或非0)0)如，如，=

12、rank(a1,$a$1:$a$5,0)=rank(a1,$a$1:$a$5,0)22excel-公式与函数的应用四、工作表的安全与保护四、工作表的安全与保护1、锁定与隐藏 锁定：工作表中选定的单元格区域不能被更改、移动、调整或删除； 隐藏：工作表中选定的单元格区域中的公式（包括函数）不会在编辑栏中显示。注意：“锁定”与“隐藏”操作只有在实施工作表保护后才有效，且必须先设置单元格格式中的保护功能，顺序不能颠倒。23excel-公式与函数的应用四、工作表的安全与保护四、工作表的安全与保护 2、工作表保护操作过程 选取所有单元格或需要保护的单元格区域，然后进行如下设置： 格式单元格保护锁定、隐藏选项 工具保护保护工作表3、工作表保护的其他选项 设置单元格格式 行、列的插入与删除 保护密码的设置4、取消对工作表的保护24excel-公式与函数的应用四、工作表的安全与保护四、工作表的安全与保护 4、保护单元格中的部分区域 注意：区域的保护也需要先设置