Excel表格制作培训教程 第6课 用公式和函数计算数据.ppt
第6课 用公式和函数计算数据,本课要点具体要求本课导读上机练习,本课要点,使用公式计算单元格和区域的引用使用函数计算,具体要求,熟悉输入公式、复制公式、删除公式的方法了解公式的常见错误掌握相对引用、绝对引用和混合引用掌握引用工作簿中元格的方法熟悉函数的格式、类型掌握插入函数及嵌套函数的方法掌握常用函数的使用,本课导读,Excel 2007提供了强大的计算功能，既包括基本的加减乘除运算，也包括复杂的函数计算。Excel 2007的计算功能是通过公式和函数来实现的。为了更好地使用它们，必须掌握单元格的相对引用和绝对引用。,6.1 使用公式计算,6.1.1 知识讲解6.1.2 典型案例计算“年度费用支出表”中的数据,6.1.1 知识讲解,公式是对工作表中的数据进行计算的等式，它遵循一个特定的语法或次序。公式以等号“=”开头，其中包括以下部分内容或全部内容：引用：引用单元格中的数据。运算符：一个标记或符号，指定表达式的计算类型，有数学、比较、逻辑和引用运算符等。常量：直接输入公式中的数字或文本值。函数：预先编写的公式，可以对一个或多个值进行计算，并返回一个或多个值。,6.1.1 知识讲解,1.输入公式2.复制公式3.删除公式4.公式常见错误解析,1.输入公式,输入公式的操作与输入文本和数据类似，同的是在输入一个公式时首先要输入“=”，以此作为开头，然后才是公式的表达式。,2.复制公式,复制公式的方法有如下两种：选择【开始】【剪贴板】组，通过单击【复制】、【粘贴】按钮，或者通过【Ctrl+V】和【Ctrl+C】组合键进行复制。通过拖动鼠标光标的方法。,3.删除公式,公式的删除方法与数据的删除方法不太一样。如果直接按【Delete】键删除公式，则单元格中的数据也将被删除。要删除单元格中不需要的公式且保留其中的数据，打开【选择性粘贴】对话框，在【粘贴】栏中选中单选项，如右图所示。,4.公式常见错误解析,在单元格中输入错误的公式可能产生以下错误值：“#”，列宽不足。“#VALUE!”，参数或操作数类型错误。“#DIV/0!”，当公式被0除时，会产生该错误值。“#NAME?”，使用Excel 2007不能识别的文本。“#N/A”，公式中没有可用数值。“#REF!”，单元格引用无效。“#NUM!”，公式或函数中使用了无效数字。“#NULL!”，指定并不相交的两个区域的交点。,6.1.2 典型案例计算“年度费用支出表”中的数据,案例目标本案例将使用公式对“年度费用支出表”中的数据进行计算，在计算过程中使用到输入和复制公式的方法，计算后的效果如右图所示。,制作思路（1）在G3单元格中输入公式计算所有部门的费用支出总额。（2）将G3单元格中的公式复制到其他需要计算数据的单元格中。,6.1.2 典型案例计算“年度费用支出表”中的数据,案例小结本案例快速计算了工作表中的数据，练习了在工作表中使用公式的方法。,6.1.2 典型案例计算“年度费用支出表”中的数据,6.2 单元格和区域的引用,在Excel 2007中使用公式和函数时需要对单元格地址进行引用，一个引用地址代表工作表中的一个或者多个单元格或单元格区域。,6.2 单元格和区域的引用,6.2.1 知识讲解6.2.2 典型案例在“成本计算表”中引用单元格,6.2.1 知识讲解,单元格和单元格区域引用的作用在于标识工作表中的单元格或单元格区域，并告知Excel在何处查找要使用的数据。通过单元格引用，可以在一个公式中使用工作表中不同单元格的数据，或者在多个公式中使用同一个单元格的数值；还可以引用同一个工作簿中其他工作表中的单元格的数据及其他工作簿中的数据。,6.2.1 知识讲解,1.相对引用 相对引用是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。2.绝对引用将公式复制到新位置时，公式中的单元格地址始终保持固定不变，结果与包含公式的单元格位置无关，这就是绝对引用。,6.2.1 知识讲解,3.混合引用 混合引用具有绝对列和相对行或绝对行和相对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、B$1等形式。4.在同一工作薄中引用其他工作表中的单元格 如果工作表中的单元格需要与同一工作簿中其他工作表中的单元格进行计算，可以使用在单元格引用的前面加上工作表的名称和感叹号“!”的方法。,6.2.1 知识讲解,5.引用其他工作簿中的单元格 如果引用其他工作簿中工作表的单元格，一般格式为“工作簿存储地址工作簿名称工作表名称！单元格地址”。,6.2.2 典型案例在“成本计算表”中引用单元格,案例目标本案例将在“成本计算表”中运用公式进行计算，通过练习掌握相对引用、绝对引用和混合引用单元格的方法。,6.2.2 典型案例在“成本计算表”中引用单元格,制作思路（1）运用相对引用计算总成本和单位成本。（2）运用绝对引用计算清仓消耗。（3）运用混合引用计算出库费用。,6.2.2 典型案例在“成本计算表”中引用单元格,案例小结本案例主要练习了单元格的相对引用、绝对引用和混合引用，这是用公式或函数计算数据时常用知识；而引用同一工作簿或其他工作簿中的数据一般多在制作大型表格时使用。,6.3 使用公式计算,6.3.1 知识讲解6.3.2 典型案例计算“聘用人员考核评分表”的数据,6.3.1 知识讲解,函数是一种预定义的公式，通过使用一些称为参数的数值以特定的顺序或结构进行计算。函数可用于进行简单或复杂的计算，下面介绍它的具体使用方法。,6.3.1 知识讲解,1.函数的格式2.函数的类型3.插入函数4.嵌套函数5.常用函数的使用,1.函数的格式,函数一般由等号、函数名称和参数3部分构成。以函数名称开始，然后是括号，括号里是以逗号隔开的计算参数。,2.函数的类型,函数的主要类型有如下几种：加载宏和自动化函数，使用加载项程序加载。数据库函数，用于对存储在列表或数据库中的数据进行分析。日期和时间函数，用于计算工作表中日期和时间的函数。工程函数，用于一些复数和进制转换的函数。财务函数，用于在财务方面进行计算的函数。,2.函数的类型,信息函数，针对工作表中数据信息的函数。逻辑函数，包括一些逻辑运算符的函数。查找和引用函数，对工作表中数据进行查找或引用的函数。数学和三角函数，在数学和三角函数中进行计算的函数。统计函数，对工作表中数据进行统计的函数。文本函数，针对工作表中文本字符的函数。,3.插入函数,插入相应的函数，方法有如下两种：选择【公式】【函数库】组，打开【插入函数】对话框，在其中选择函数，然后打开【函数参数】对话框，选择参数单元格。选择【公式】【函数库】组，在其中单击对应函数的下拉按钮，在弹出的下拉菜单中选择需要插入的函数即可。,4.嵌套函数,有些函数可以用做其他函数的参数，这称为嵌套函数。将函数嵌套使用能够简化函数参数，使数据的处理更清晰明了，也能实现更多的功能。嵌套函数时，将首先计算最深层的嵌套表达式，然后逐步向外扩展。,5.常用函数的使用,选择了【常用函数】类别之后，列表框中就将列出一般常用函数。SUM函数，对选择的单元格或单元格区域进行加法运算。AVERAGE函数，对选择的单元格或单元格区域进行算术平均值运算。IF函数，执行真（true）假（false）值判断，根据逻辑计算的真假值，返回不同的结果。MAX函数，对选择的单元格区域中的数据进行比较，找到其中最大的数字并返回到目标单元格中。,6.3.2 典型案例计算“聘用人员考核评分表”的数据,案例目标本案例将使用公式对“年度费用支出表”中的数据进行计算，在计算过程中使用输入和复制公式的方法，计算后的效果如右图所示。,制作思路（1）运用AVERAGE函数计算平均分。（2）运用SUM函数计算总分。（3）运用IF函数判断是否聘用员工。（4）运用MAX函数求出各项最高分。,6.3.2 典型案例计算“聘用人员考核评分表”的数据,案例小结本案例主要练习了插入函数的多种方法，以及4个常用函数的使用方法。,6.3.2 典型案例计算“聘用人员考核评分表”的数据,6.4 上机练习,6.4.1 用公式计算“奖金计算器”的数据 6.4.2 用函数计算“往来账款处理”中的数据,6.4.1 用公式计算“奖金计算器”的数据,本次练习将计算“奖金计算器”工作簿中的数据，练习公式的使用及单元格的引用方法，制作效果如右图所示。,6.4.1 用公式计算“奖金计算器”的数据,制作思路：在D6:D10单元格区域中，用“个人销售总额*奖金比例”的公式计算每位员工的奖金。由于奖金比例是固定的，因此采用绝对引用。在C11单元格中，将每位员工的销售总额加起来，计算销售一部的个人销售总额。在D11单元格中，将每位员工的奖金加起来，计算销售一部的奖金总额。按同样的方法计算销售二部的奖金。将两个部门的数据相加，在B3和C3单元格中分别计算总销售额和已发奖金。,6.4.2 用函数计算“往来账款处理”中的数据,本次练习将在“往来账款处理”工作簿中用函数处理往来账款，完成效果如右图所示。,6.4.2 用函数计算“往来账款处理”中的数据,制作思路：在F4:F11单元格区域中，用“应收金额-已收款金额”公式计算“未收款金额”。在H4:H11单元格区域中，用“开票日期+收款期”公式计算“到期日期”。在I4:I11单元格区域中用IF函数判断是否到期，判断的条件是“如果到期日期超过今天日期”，则返回“是”，否则返回“否”。在J4:J11单元格区域中用IF函数返回未到期金额，如果“到期日期超过今天日期”，则返回“未收款金额”，否则返回“0”。在J12单元格中用SUM函数计算未到期金额的总额。,
