第三讲__公式和函数ppt课件

1、3.1 公式的基本概念1关于公式 公式是对工作表数据进行运算的方程式。公式可以进行数学运算，例如：加法和乘法，还可以比较工作表数据或合并文本。公式可以引用同一工作表中的其他单元格、同一工作簿不同工作表中的单元格，或者其他工作簿的工作表中的单元格。下面的示例将单元格 B4 中的数值加上 25，再除以单元格 D5、E5 和 F5 中数值的和。2构造公式构造公式 公式按特定次序计算数值。公式按特定次序计算数值。Microsoft Excel 中中的公式通常以等号的公式通常以等号 (=) 开场，用于表明之后的字符开场，用于表明之后的字符为公式。紧随等号之后的是需要进行计算的元素为公式。紧随等号之后的是

2、需要进行计算的元素操作数），各操作数之间以算术运算符分隔。操作数），各操作数之间以算术运算符分隔。Excel 将根据公式中运算符的特定顺序从左到右计将根据公式中运算符的特定顺序从左到右计算公式。当然，可以使用括号更改运算顺序。在下算公式。当然，可以使用括号更改运算顺序。在下例中，公式第一部分中的括号表明例中，公式第一部分中的括号表明 Excel 应首先计应首先计算算 B4+25，然后再除以单元格，然后再除以单元格 D5、E5 和和 F5 中数中数值的和。值的和。 =(B4+25)/SUM(D5:F5)3引用单元格引用单元格 公式可以引用常量值以及其他单元格。包含公式的单元格称为从属单元格，因为

3、其结果值将依赖于其他单元格的值。例如，如果单元格 B2 包含公式 =C2，则单元格 B2 就是从属单元格。 默认情况下，只要公式引用的单元格发生更改，从属单元格也将随之更改。例如，如果下列任意单元格中的值发生更改，则公式 =B2+C2+D2 的结果也将随之更改。 如果公式中使用了常量值而没有引用单元格例如，=30+70+110），则其结果只在更改公式本身时才会发生更改。公式也可以引用单元格区域或引用代表单元格区域的名称或标志。下面详细介绍有关单元格和区域引用： 引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在

4、多个公式中使用同一单元格的数值。还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序中的数据。引用不同工作簿中的单元格称为外部引用。引用其它程序中的数据称为远程引用。A1 和 R1C1 引用样式 引用样式 A1在默认状态下， Excel 运用 A1 引用类型。这种类型引用字母标志列从 A 到 IV ，共 256 列和数字标志行从 1 到 65536）。这些字母和数字被称为行和列标题。如果要引用单元格，请顺序输入列字母和行数字。例如，D50 引用了列 D 和行 50 交叉处的单元格。如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用。下

5、面是引用的示例。 R1C1 引用样式也可以使用同时统计工作表上行和列的引用样式。R1C1 引用样式对于计算位于宏内的行和列很有用。在 R1C1 样式中，Excel 指出了行号在“R后而列号在“C后的单元格的位置。关于引用的其他信息A相对引用和绝对引用 依靠要在 Excel 中运行的任务，您既可以使用相关单元格引用它们是与公式位置相关的单元格引用），也可以使用绝对引用它们是指向特定位置单元格的单元格引用）。如果美元号在字母和/或数字之前，比如 $A$1，列和/或行引用就是绝对的。当复制时，相对引用会自动调整，但绝对引用不会。 绝对引用与相对引用的区别相对引用相对引用 在创建公式时，单元格或单元格

6、区域的引用在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。在下通常是相对于包含公式的单元格的相对位置。在下面的示例中，单元格面的示例中，单元格 B6 包含公式包含公式 A5 ；Microsoft Excel 将在距单元格将在距单元格 B6 上面一个单元格上面一个单元格和左面一个单元格处的单元格中查找数值。这就是和左面一个单元格处的单元格中查找数值。这就是相对引用。相对引用。 在复制包含相对引用的公式时，在复制包含相对引用的公式时，Excel 将自动调将自动调整复制公式中的引用，以便引用相对于当前公式位整复制公式中的引用，以便引用相对于当前公式位置的其他单元格。在下

7、面的示例中，单元格置的其他单元格。在下面的示例中，单元格 B6 中中含有公式：含有公式：=A5，A5 是是 B6 左上方的单元格，当公左上方的单元格，当公式复制至单元格式复制至单元格 B7 时，其中的公式已经改为时，其中的公式已经改为 =A6，即单元格即单元格 B7 左上方单元格处的单元格。左上方单元格处的单元格。 绝对引用绝对引用 如果在复制公式时不希望如果在复制公式时不希望 Excel 调整引调整引用，那么请使用绝对引用。例如，如果公式将单元用，那么请使用绝对引用。例如，如果公式将单元格格 A5 乘以单元格乘以单元格 C1 (=A5*C1)，现在将公式复制，现在将公式复制到另一单元格中，那

8、么到另一单元格中，那么 Excel 将调整公式中的两个将调整公式中的两个引用。可以在不希望改变的引用前加上美元符号引用。可以在不希望改变的引用前加上美元符号（$），这样就能对单元格），这样就能对单元格 C1 进行绝对引用。如果进行绝对引用。如果要对单元格要对单元格 C1 进行绝对引用，请在公式中加入美进行绝对引用，请在公式中加入美元符号：元符号：=A5*$C$1 B标志和名字标志和名字 可以在工作表中使用列标志和行标可以在工作表中使用列标志和行标志引用这些行和列中的单元格。标志示例为志引用这些行和列中的单元格。标志示例为“价价钱钱”、“数量和数量和“总计总计”。也可以创建描述名来。也可以创建描

9、述名来代表单元格、公式或常量值。代表单元格、公式或常量值。公式中的标志和名称公式中的标志和名称 工作表每列的首行和每行的最左列通常含有标志工作表每列的首行和每行的最左列通常含有标志以描述数据。在公式中可以使用这些标志来引用相以描述数据。在公式中可以使用这些标志来引用相应的数据。也可以不用工作表中的标志，而用描述应的数据。也可以不用工作表中的标志，而用描述性的名称来代表单元格、单元格区域、公式或常量。性的名称来代表单元格、单元格区域、公式或常量。 使用标志代表单元格 在创建需要引用工作表中数据的公式时，可以使用工作表中的列标与行标来引用数据。例如，下例中包含列标为“产品1”、“产品2和“产品3的

10、列。如果要计算“产品1列的全部数值，可使用公式 =SUM(产品1)。或者，如果要引用“东部分支机构“产品3的数量即数值 110.00），可以使用公式 =产品3 东部。公式中“产品3和“东部之间的空格为区域运算符。该运算符表示 Microsoft Excel 将查找并返回行标为“东部”、列标为“产品3的单元格中的数值。 定义代表单元格的名称定义代表单元格的名称 如果要用的数据没有标志如果要用的数据没有标志或者存储在其他工作表中，可以创建名称来描述单或者存储在其他工作表中，可以创建名称来描述单元格或区域。公式中的描述性名称使人们更容易理元格或区域。公式中的描述性名称使人们更容易理解公式的含义。例如

11、，公式解公式的含义。例如，公式 =SUM(一季度销售额一季度销售额) 要比公式要比公式 =SUM(销售销售 !C20:C30) 更容易理解。在更容易理解。在本示例中，称号本示例中，称号“一季度销售额代表命名为一季度销售额代表命名为“销销售的工作表中的区域售的工作表中的区域 C20:C30。名称在所有工作。名称在所有工作表中都可以使用。例如，如果名称表中都可以使用。例如，如果名称“预计销售引预计销售引用了工作簿中第一个工作表的区域用了工作簿中第一个工作表的区域 A20:A30，则工，则工作簿中的所有工作表都使用名称作簿中的所有工作表都使用名称“预计销售来引预计销售来引用第一个工作表中的区域用第一

12、个工作表中的区域 A20:A30。名称也可以用。名称也可以用来代表不会改变的常量公式和数值。例如，可来代表不会改变的常量公式和数值。例如，可使用名称使用名称“销售税代表销售额的税率如销售税代表销售额的税率如 6.2%）。）。 C三维引用三维引用 如果要分析同一工作簿中多个工作表上如果要分析同一工作簿中多个工作表上的相同单元格或单元格区域中的数据，请使用三维的相同单元格或单元格区域中的数据，请使用三维引用。三维引用包含单元格或区域引用，前面加上引用。三维引用包含单元格或区域引用，前面加上工作表名称的范围。工作表名称的范围。Excel 使用存储在引用开始名使用存储在引用开始名和结束名之间的任何工作

13、表。例如，和结束名之间的任何工作表。例如， =SUM(Sheet2:Sheet13!B5) 将计算包含在将计算包含在 B5 单单元格内所有值的和，单元格取值范围是从工作表元格内所有值的和，单元格取值范围是从工作表 2 到工作表到工作表 13。 使用三维引用来引用多个工作表上的同一单元格或使用三维引用来引用多个工作表上的同一单元格或区域区域工作簿必须包含多张工作表。有关使用三维引用的准则：使用三维引用可以引用其它工作表中的单元格，可以定义名称，还可以通过使用下列函数来创建公式： SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT

14、、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP 和 VARPA。三维引用不能用于数组公式中。三维用于不能与交叉引用运算符空格一起使用，也不能用在使用了智能引用的公式中。 三维引用具体操作方法：单击需要输入公式的单元格；三维引用具体操作方法：单击需要输入公式的单元格；键入键入 （等号），再输入函数名称，接着再键入左（等号），再输入函数名称，接着再键入左圆括号；单击需要引用的第一个工作表标签；按住圆括号；单击需要引用的第一个工作表标签；按住 SHIFT 键，单击需要引用的最后一个工作表标签；键，单击需要引用的最后一个工作表标签；选定需要引用的单元格或单元格区域

15、；完成公式。选定需要引用的单元格或单元格区域；完成公式。4输入公式输入公式单击将要在其中输入公式的单元格。键入单击将要在其中输入公式的单元格。键入 （等号（等号如果单击了如果单击了“编辑公式按钮或编辑公式按钮或“粘贴函数按钮，粘贴函数按钮，Microsoft Excel 将插入一个等号。输入公式内容。将插入一个等号。输入公式内容。按按 ENTER 键。键。3.2 函数的基本概念函数的基本概念Microsoft Excel 中包含许多预定义的或内置的公式，中包含许多预定义的或内置的公式，称之为函数。函数可用于进行简单或复杂计算。其称之为函数。函数可用于进行简单或复杂计算。其中，最常用的函数是中，

16、最常用的函数是 SUM 函数，该函数用于对单函数，该函数用于对单元格区域中的数值求和。元格区域中的数值求和。SUM 工作表函数将对单元格区域工作表函数将对单元格区域 D5:F5 中的数值求中的数值求和。和。使用函数计算数值函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。例如，SUM 函数对单元格或单元格区域进行加法运算，PMT 函数在给定的利率、贷款期限和本金数额基础上计算偿还额。参数 参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。将函

17、数用作另一个函数的参数也称嵌套函数）。构造构造 函数的结构以函数名称开始，后面是左圆括号、函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（形式出现，请在函数名称前面键入等号（=）。在）。在创建包含函数的公式时，公式选项板将提供相关的创建包含函数的公式时，公式选项板将提供相关的帮助。帮助。关于使用关于使用“公式选项板输入和编辑公式公式选项板输入和编辑公式输入公式输入公式 如果创建含有函数的公式，如果创建含有函数的公式，“公式选项公式选项板将有助于输入工作表函数。在公式中输入函数板

18、将有助于输入工作表函数。在公式中输入函数时，时，“公式选项板将显示函数的名称、各个参数、公式选项板将显示函数的名称、各个参数、函数功能和参数的描述、函数的当前结果和整个公函数功能和参数的描述、函数的当前结果和整个公式的结果。如果要显示式的结果。如果要显示“公式选项板公式选项板”，请单击编，请单击编辑栏上的辑栏上的“编辑公式按钮。编辑公式按钮。在本例中，使用了“公式选项板编辑嵌套的 SUM 函数。单元格区域 K10:K19 是函数的唯一参数，并显示在“Number1编辑框中。编辑框右侧显示了区域中各单元格的值。参数列表的下方显示了 SUM 函数的结果 (935)。而整个公式的结果将显示在底部，由

19、于单元格区域 K10:K19 之和小于或等于 5,000，所以结果为 0.05 或 5%。编辑公式 可以使用“公式选项板来编辑公式中的函数。选择含有公式的单元格，然后单击“编辑公式按钮以显示“公式选项板”。“公式选项板中将显示公式中的第一个函数及其所有参数。在函数的编辑栏中单击任意位置，可编辑第一个函数或同一公式中的其他函数。在进行函数操作时，如果操作错误，会提示错误信息，以下为EXCEL常见的8种错误提示，学生可以了解这8类错误提示的含义，这样增强处理问题的敏感性。具体如下：【#】为当列宽不够时，或使用了负的日期或负的时间。【#VALUE】为使用的参数或操作数类型错误。【#DIV/0！】为数

20、据被0除。【#NAME？】为EXCEL未识别公式的文本。【#N/A】为数值对函数或公式不可用。【#REF！】为单元格引用无效。【#NUM！】为公式或函数中使用无效数字值。【#NULL】为指定并不相交的两个区域的交点。用空格表示两个引用单元格之间的相交运算符。3.3 常用函数的应用一、逻辑函数1.IF函数假定公司根据发货量决定销售奖金，奖励幅度为百分之十或百分之十五。如果要根据某一条件的真或假来决定使用其中哪一个值，则可使用 IF 工作表函数。IF工作表函数根据发货量返回10%或15% 的奖金。IF工作表函数检查的条件要么为真要么为假。如果条件为真，则函数返回某一值；如果条件为假，则函数将返回另

21、一值。此函数包含三个参数：要检查的条件、当条件为真时的返回值和条件为假时的返回值。=IF(logical_test,value_if_true,value_if_false) “Suyama的发货量为 $8,000，函数在单元格 C4 中如下所示： =IF(B4C2,超过预算,OK) 等于 超过预算 IF(B3C3,“超过预算”,“OK”) 等于 “OK”。 【应用实例二】 如果要给以 AverageScore 为名称所引用的数字设置字母级别，请参阅下表：可以使用下列嵌套 IF 函数：IF(AverageScore89,A,IF(AverageScore79,B,IF(AverageScore

22、69,C,IF(AverageScore59,D,F)在上例中，第二个 IF 语句同时也是第一个 IF 语句的参数 value_if_false。同样，第三个 IF 语句是第二个 IF 语句的参数 value_if_false。例如，如果第一个 logical_test (Average 89) 为 TRUE，则返回 “A”；如果第一个 logical_test 为 FALSE，则计算第二个 IF 语句，以此类推。2. AND函数和OR函数格式：AND(logical1, logical2, )功能：如果所有参数值均为TRUE，则返回TRUE；如果任一参数值为FALSE，则返回FALSE。例如

23、：AND(38,105)返回为FALSE；AND(128,105)返回为TRUE。格式：OR(logical1, logical2, )功能：如果所有参数值均为FALSE，则返回FALSE；如果任一参数值为TRUE，则返回TURE。例如：OR(38,105)返回为TURE；OR(38,25)返回为FALSE。3. NOT函数格式：NOT(logical)功能：如果参数值为TRUE，则返回FALSE；如果值为FALSE，则返回TURE。二、日期与时间函数NOW()函数和TODAY()函数NOW()函数：返回计算机系统内部时钟的当前日期和时间。格式为：NOW()，此函数无参数，但后面括号不能少。T

24、ODAY()函数：返回计算机系统的当前日期。格式为：TODAY()，此函数无参数，但后面括号不能少。YEAR()函数和MONTH()函数YEAR()函数：返回日期序列对应的年份数1990-9999）。格式：YEAR(文本型日期)。MONTH()函数：将序列数转换为对应的月份数。格式：MONTH(日期或序列数)。【应用实例】【应用实例】MONTH(6-May) 等于等于5；MONTH(35795) 等于等于 12；MONTH(35796) 等于等于1；MONTH(2019/04/01) 等于等于4。3. DAYS360函数格式：DAYS360(起始日期,终止日期)功能：按每年360天计算返回两个

25、日期之间相差的天数每月30天）。例如：=DAYS360(“2019/1/1”,“2019/1/1”)，结果360。三、数学与统计函数1.SUM函数格式：SUM(number1，number2，)功能：返回单元格区域中所有数值的和。2.SUMIF函数格式：SUMIF(范围，条件，求和范围)功能：根据指定条件对若干单元格求和。【应用实例】【应用实例】在在EXCEL工作表的工作表的B35：D37单元格区域内的数据如单元格区域内的数据如下图所示：下图所示：3.AVERAGE函数格式：AVERAGE(number1，number2，)功能：计算参数的算术平均数；参数可以是数值或包含数值的名称或引用。4.

26、MAX函数格式：MAX(number1，number2，)功能：返回一组数值中的最大值，忽略逻辑值及文本 字符。5.MIN函数格式：MIN(number1，number2，)功能：返回一组数值中的最小值，忽略逻辑值及文本字符。6. COUNT函数格式：COUNT(value1，value2，)功能：计算参数表中的数字参数和包含数字的单元格的个数。7. COUNTIF函数格式：COUNTIF(范围，条件)功能：计算某个区域中满足给定条件单元格的数目。【应用实例】参照下列数据表，求出各科成绩在80分以上学生的人数。对应的“公式选项板如下所示：四、文本函数1LEFT函数、MID函数、RIGHT函数L

27、EFT(文本，数字) 返回左边指定数字的字符串。MID(文本，起始位置，长度) 返回指定起始位置开始长度的字符串。RIGHT(文本，数字) 返回右边指定数字的字符串。【应用实例】2LEN函数格式：LEN(文本)功能：返回文本字符串中的字符个数。3TEXT函数和VALUE函数 格式：TEXT(value,format_text) VALUE(text) 功能：TEXT(value,format_text)函数将数值转换为按指定数字格式表示的文本。 VALUE(text)函数将代表数字的文本字符串转换成数字。五、数据库函数1DSUM函数和DAVERAGE函数格式：DSUM(数据库,字段,条件) D

28、AVERAGE(数据库,字段,条件)功能：DSUM函数求满足给定条件的数据库中记录的 字段列数据总和。 DAVERAGE函数返回满足条件的列表或数据库的列中数值的平均值 【应用实例】2DCOUNT函数和DCOUNTA函数格式：DCOUNT(数据库,字段,条件) DCOUNTA(数据库,字段,条件)功能：DCOUNT函数从满足给定条件的数据库记录的字段列中，计算包含数值的单元格数目。 DCOUNTA函数从给定数据库中，计算符合条件的非空单元格数目。3DMAX函数和DMIN函数格式： DMAX(数据库,字段,条件) DMIN(数据库,字段,条件)功能：DMAX函数返回满足给定条件的数据库记录的字段

29、列中的最大值。DMIN函数返回满足给定条件的数据库记录的字段列中的最小值。【应用实例】【应用实例】六、查找函数LOOKUP函数格式：LOOKUP()功能：从向量或数组中查找一个值。其中：第一个选项是从数组中查找一个值。第二个选项中lookup_value是要在look_vector中查找的值；lookup_vector是包含文本、数值或逻辑值的单元格区域，用来同lookup_value相比较。result_vector是包含文本、数值或逻辑值的单元格区域，用来同lookup_value相比较。【应用实例】【应用实例】 您公司有您公司有A01、A02、A03、B01、B02、B03六种产品，六种

30、产品，您要计算您要计算“销售点产品销售记录表中的销售点产品销售记录表中的“金额列的值。金额列的值。金额等于数量乘于该产品的单价，用适当的方法根据金额等于数量乘于该产品的单价，用适当的方法根据“产产品编号自动查找品编号自动查找“产品单价表中的产品单价表中的“单价单价”，并根据，并根据数量和查找到的单价计算相应金额。查找功能可以使用数量和查找到的单价计算相应金额。查找功能可以使用lookup或类似函数。销售点产品销售记录表如下：或类似函数。销售点产品销售记录表如下：产品单价表如下：分析：本题目要求利用“销售点产品销售记录表中产品编号，从“产品单价表中查找对应产品编号的价格，即将“销售点产品销售记录表中产品编号与“产品单价表” 中产品编号相比较，把产品编号相同