excel 公式与函数的应用

=F4+F8
绝对引用：在单元格名称的列标与行号前加符号“$”表示绝对引用。
混合引用：相对引用与绝对引用相结合
=F$3+$D4
说明：
1、当公式使用相对引用时，把公式复制到一个新的位置，公式中的单元 格引用也跟随变化，但相对于公式所在单元格的位置不变。 2、在公式中使用绝对引用，不论公式复制到何处，公式中所引用的单 元格不发生变化。

公式的编辑


1．公式的输入 （1）选中要输入公式的单元格； （2）输入“=”作为准备输入公式的开始； （3）输入组成该公式的所有运算码和运算符； （4）按回车键对输入的公式表示确认。 2．公式的修改 （1）按F2键，可以直接编辑修改单元格中的内容； （2）双击该单元格，可以直接编辑修改单元格中的内容； （3）选择需要进行编辑的单元格，然后单击编辑栏，在 编辑栏中对公式进行修改。 （4）如果公式的单元格返回一个错误，Excel会在单元格 的左上角显示一个小方块。激活单元格，可以看到一个智 能标签。单击该智能标签，可选择一个选项来更正错误。 当编辑结束后按【Enter】键即可完成操作。如果要取消编 辑操作，可以按Esc键。
按钮
利用公式或函数完成以下任务

任务1 ：计算销售数据完成“北京路分店”、山 东路分店、香港路分店的上半年、下半年、全 年、总计的计算。完成占销售总额比例的计算。 任务2：完成“教学计划”表中黄色区域的计算。


任务3：计算“人事表”中职称工资。职称工资 =基本工资*10%
任务4：计算“第二单元 人事表”中的工龄、校龄
二、相对引用、绝对引用、混合引用

跨表格数据的引用 1.引用同一工作薄其他工作表中的单元格或区域
工作表标签名!单元格地址

2..引用同一工作薄多张工作表的相同单元格或区 域
第一个工作表名：最后一个工作表名! 单元格地址

3.不同工作薄之间的数据引用
[工作簿名]工作表标签名! 单元格地址
三、电子表格中的常用函数

1、求和函数：
SUM（ number1，number2……number30）

2、求平均数函数：
AVERAGE（ number1，number2……number30 ）

3、求最大值和最小值： MAX（ number1，number2……number30 ） MIN（ number—“函数” 编辑栏上的fx按钮 工具栏上的自动填充
五、工作簿的保护

工具－>保护－>保护工作簿
结构选项：不能对其中的工作表进行删除、增加、 移动、隐藏、取消隐藏、重命名操作。 窗口选项：不能对工作簿窗口进行移动、缩放、关 闭、拆分等操作。 （例：格式－>工作表－>隐藏）
排位函数


Rank函数 格式：=RANK(指定的单元格,排序的范围,0或非0)
如，=RANK(A1,$A$1:$A$5,0)
四、工作表的安全与保护

1、锁定与隐藏
锁定：工作表中选定的单元格区域不能被更改、移 动、调整或删除； 隐藏：工作表中选定的单元格区域中的公式（包括 函数）不会在编辑栏中显示。
公式返回的错误信息返回值 错误提示
#NAME?
原因
使用了EXCEL不能识别的名字
#N/A
#NULL! #NUM！ #REF! #VALUE! ###### ＃DIV/0!
使用了不能使用的值
指定了无效的“空”区域 公式中使用了无效数字值 引用了无效单元格 不正确的参数或运算对象 列宽不够 除数为0

( ) > 算术运算 > 连接运算 > 比较运算
高
低
-（取负）> % > ^ > * / > + 例如：-5%*6/5+10
二、相对引用、绝对引用、混合引用
对单元格地址的三种引用方式 绝对引用
相对引用
混合引用

相对引用：直接使用单元格或单元格区域的名称作为引用名称。
＝A5+D6
＝$A$4+$B$5


三、电子表格中的常用函数


7、计数函数Count（）： 格式：Count（指定区域）：计算个数，只能 计算数值型数据，不能计算文本、空单元格。 例如：计算“学生成绩表”中学生的人数。
公式：=COUNT(G3:G15)
区域可以选择有数值的任意列
三、电子表格中的常用函数

8、COUNTIF函数：计算制定区域中满足给定条件的单 元格数目。 注意：该函数要求第二个参数为文本字符串。 格式：COUNTIF(指定区域 , 条件)

4、取整函数INT（ ） ：将数字向下舍入到最接 近的整数
例： ＝INT（8.9) 例： ＝INT（－8.9） 结果为8 结果为－9
-9
8.9 -8 0
8.9 8 9
将数字向下舍入到最接近的整数
三、电子表格中的常用函数

5、返回当前系统日期：TODAY（ ）
＝TODAY( ) 结果为 =INT(TODAY( )-人事表!E4)/365)

比较运算符
＝ > < >= <= <> 比较运算的结果为 TRUE 或 FALSE

文本连接运算符:
&
假如B2单元格内容：信息时代，D6单元格内容： ABC。 则=B2&D6 的值是信息时代ABC =“21世纪是”&B2 的值是21世纪是信息时代
一、在电子表格中应用公式

运算符的优先级
6、条件函数

IF(测试条件，满足条件时的输出结果，不满 足条件时的输出结果）
例1：”学生成绩表” ，条件如下：大于等于300分显 示及格，小于300分显示不及格
＝IF(成绩表!G2>=300,及格,不及格）
三、电子表格中的常用函数（续）

例2：计算业务津贴：博导350元，其余人为0。
=IF(人事表!I4=“博导”,350,0)
例1：计算“人事表”中低于平均工资的人数

=COUNTIF(K3:K26, “<”&K28)
例2：计算“学生成绩表”成绩达到优秀以上的人数
=COUNTIF(C2:C10,“>90")
例3：计算“学生成绩表”中男生人数和女生人数
=COUNTIF(B3:B15,"男") =COUNTIF(B3:B15,“女")

例3：根据学生成绩划分等级。
=IF(C2>=90, “优秀”, IF(C2>=80, “良好”, IF(C2>=70, “中等”, IF(C2>=60, “及格”,“不及格”))))

任务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(K3<800,0,IF(K3<=1300,(K3-800)*5%,(K31300)*10%+500*5%)）



授课方式：实验课 课时安排：8学时 一、教学目标及要求 掌握在电子表格中应用公式的方法 相对引用与绝对引用 电子表格中的常用函数 工作表的安全与保护 工作簿的保护 二、教学内容 公式的规则、公式的编辑、应用公式的注意事项、算术运算符、比较运算符、连接 运算符、运算符优先级 三、教学重点：公式及函数的应用 四、教学难点：if函数的嵌套 五、教学方法：任务驱动法 六、教学手段：机房 七、板书设计：见课件 八、教学过程

注意：“锁定”与“隐藏”操作只有在实施工 作表保护后才有效，且必须先设置单元格格式 中的保护功能，顺序不能颠倒。
四、工作表的安全与保护

2、工作表保护操作过程
选取所有单元格或需要保护的单元格区域，然后进 行如下设置： 格式－>单元格－>保护－>锁定、隐藏选项 工具－>保护－>保护工作表

3、工作表保护的其他选项
设置单元格格式 行、列的插入与删除 保护密码的设置

4、取消对工作表的保护
四、工作表的安全与保护

4、保护单元格中的部分区域
注意：区域的保护也需要先设置单元格的保护选项。 设置单元格的保护选项 设定“允许用户编辑区域”； 保护工作表

5、撤消保护单元格区域：删除区域
公式的规则

所有的Excel公式都具有相同的基本结构：一个等 号（=）后面跟随一个或多个运算码，运算码可以 是值、单元格引用、单元格区域、或者函数名， 其间以一个或多个运算符连接。这些运算符包括 算术运算符、逻辑运算符或者字符连接符（&） 等。 Excel在一个公式中允许使用的最大字符数为 1024个。公式中运算符与运算码之间可以加入空 格，以便分隔公式中的各个部分，从而提高公式 的可读性。
3．公式的移动和复制
公式移动和复制的操作方法与单元格移动和复制 类似 。
选择性粘贴 编辑——清除

应用公式的注意事项 公式中所有的运算符都必须在英文半角状态下 输入 公式中的字符串常量需要以英文双引号（“”） 括起来
一、在电子表格中应用公式