办公软件【第八章：公式、函数与统计】.ppt

1、董成杰 ,Office办公软件 第8章公式、函数与统计,第8章 公式、函数与统计,作为一个电子表格系统，除了进行一般的表格处理外，最主要的还是它的数据计算能力。本章的内容主要是通过对单元格引用方法及常用函数的基本应用方法的讨论，从而掌握比较复杂的公式和一般常用函数的使用方法。,本章学习内容,8.1 公式、函数与统计基础 8.2 输入函数 8.3 含有条件的函数 8.4 在函数和公式中使用定义名称,8.1.1 相对引用和绝对引用,相对单元格引用：在公式中，基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。如果复制公式，相对引用将自动调整。相对引用采用的形式为“A1”。 绝对单元格引

2、用：公式中单元格的精确地址，与包含公式的单元格的位置无关。绝对引用采用的形式为“$A$1”。 相对引用、绝对引用和混合引用间切换 选中包含公式的单元格。 在编辑栏中，选择要更改的引用。 按 F4 进行切换。,8.1.2 三维引用,所谓三维引用，是指跨工作表的单元格或单元格区域的引用。 地址形式：工作表名！单元格地址，当前工作表的单元格地址可以省略“工作表名！”。 例：=A1+Sheet2!A1+Sheet3!B1 说明：此公式表示对当前工作表的A1单元格、sheet2工作表的A1单元格和sheet3工作表的B1单元格中的数据求和。,8.1.3 求和按钮的使用,Excel提供了一些预定好的的公式

3、，称为函数，利用函数可以简化操作，还能实现许多普通运算所难以完成的运算。 函数的语法：函数名（参数）例：“SUM(A1:C5)” 函数有多个参数时，参数之间以逗号分隔。有些函数可能无参数，但括号不能省略。 在前面章节中我们已经使用过“常用工具栏”的“自动求和”按钮进行求和。在Office XP以后的版本中增加了求平均值、计数、最大值和最小值等的自动计算功能，使计算操作更加方便快捷。,8.2 插入函数,前面我们介绍了使用自动求和按钮及使用鼠标点击求和按钮右侧的箭头，完成求和、求平均值、最大值等操作。下面介绍使用“插入函数”对话框在公式及单元格中输入函数的方法。,8.2.1 插入函数对话框,使用“

4、插入函数”对话框可根据需要在工作表中插入的函数和参数，使用下列方法之一可以打开“插入函数”对话框。,8.2.2 函数对话框,搜索函数：输入一条简短的说明来描述想做什么（如：“日期”）。 选择类别：从下拉列表中，执行下列操作之一： 选择“常用函数”。最近插入的函数将按字母顺序显示在“选择函数”框中。 选择函数类别。类别中的函数将按字母顺序显示在“选择函数”框中。 选择“全部”。所有函数将按字母顺序显示在“选择函数”框中。 选择函数：执行下列一项操作： 单击函数名称，可立即在“选择函数”框下面看到函数语法和简短说明。 双击函数名称可在“函数参数”向导中显示函数及其参数，该向导可帮助添加正确的参数。

5、 有关该函数的帮助：在“帮助”窗口中为“选择函数”框中当前所选的函数显示引用帮助主题。,8.2.3 常用函数,Excel中提供了多种函数，其中比较常用的9种函数如下： 1. 求和函数 SUM （统计类） 返回某一单元格区域中所有数字之和。 语法：SUM(number1,number2, .) Number1, number2, .为 1 到 30 个需要求和的参数。 2. 算术平均值 AVERAGE （统计类） 返回参数的平均值（算术平均值）。 语法：AVERAGE(number1,number2,.) Number1, number2, .为需要计算平均值的 1 到 30 个参数。,8.2.

6、3 常用函数,3. 最大值函数 MAX（统计类） 返回一组值中的最大值。 语法：MAX(number1,number2,.) Number1, number2, .是要从中找出最大值的 1 到 30 个数字参数。 4. 最小值函数 MIN（统计类） 返回一组值中的最小值。 语法：MIN(number1,number2,.) Number1, number2,.是要从中找出最小值的 1 到 30 个数字参数。,8.2.3 常用函数,5. 计数函数 COUNT（统计类） 返回包含数字以及包含参数列表中的数字的单元格的个数。利用函数 COUNT 可以计算单元格区域或数字数组中数字字段的输入项个数。

7、语法：COUNT(value1,value2,.) Value1, value2, .为包含或引用各种类型数据的参数（1 到 30个），但只有数字类型的数据才被计算。 6. 取整函数 INT（数学与三角函数类） 将数字向下舍入到最接近的整数。 语法： INT(number) Number需要进行向下舍入取整的实数。此函数只有一个参数。请注意负数取整后的结果。,8.2.3 常用函数,7. 当前日期函数 TODAY（日期与时间类） 返回当前日期 语法：TODAY( ) Excel 可将日期存储为可用于计算的序列号。默认情况下，1900 年 1 月 1 日的序列号是 1 而 2008 年 1 月 1

8、 日的序列号是 39448，这是因为它距 1900 年 1 月 1 日有 39448 天。 此函数没有参数，但函数中的括号不能省略。,8.2.3 常用函数,8. 年份函数YEAR（日期与时间类） 返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。 语法：YEAR(serial_number) Serial_number为一个日期值，其中包含要查找年份的日期。 9. 四舍五入ROUND（数学与三角函数类） 返回某个数字按指定位数进行四舍五入后的数字。 语法：ROUND(number,num_digits) Number需要进行四舍五入的数字。 Num_digits指定的位数，按

9、此位数进行四舍五入。如参数为负值取小数点左侧的位数。,8.2.3 常用函数,10. 字符串函数mid（字符串类） 从字符串中返回指定数目的字符。 语法：mid(string, start, length) 11. 字符串函数len（字符串类） 返回一个包含字符串中的字符数或存储某个变量所需的名义字节数的整数。 语法：len(string|varname),8.2.4 函数的嵌套,在引用函数时，函数的参数又引用了函数，称为“函数的嵌套”。被嵌套的函数，必须返回与当前参数使用的数值类型相同的数值。如果嵌套函数返回的数值类型不正确，Excel将显示“VALUE！”错误值。,8.3 含有条件的函数,这

10、里主要介绍IF函数、IF函数的嵌套、 COUNTIF、 SUMIF等含有条件函数的应用，同学们应熟练掌握IF函数、IF函数的嵌套的应用。,8.3.1 IF函数,IF函数属于逻辑函数的一种，应用十分广泛。主要用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此IF函数也称之为条件函数。 语法: IF(logical_test,value_if_true,value_if_false) Logical_test表示计算结果为 TRUE 或 FALSE 的逻辑表达式。例如，A10=100 就是一个逻辑表达式，如果单元格 A10 中的值等于 100，表达式的值即为 TRUE，否则为 FALS

11、E。本参数可使用任何比较运算符。 Value_if_true: logical_test 为 TRUE 时返回的值。 Value_if_false: logical_test 为 FALSE 时返回的值。 使用if函数的嵌套可以构造复杂的检测条件，函数 IF 最多可以嵌套七层。,例：成绩判定,任务分析 本任务可使用上面讲到的IF函数完成。 根据所定规则，我们在综合评定中写公式（以单元格H3为例）： =IF(G3=60,合格,不合格) 语法解释为，如果单元格G3的值大于60，则执行第二个参数即在单元格H3中显示“合格”，否则执行第三个参数即在单元格H3中显示“不合格”。,例：实施步骤,插入函数

12、直接输入,8.3.2 IF函数的嵌套,在我们实际工作中，经常遇到需返回两种以上值得情况，例如我们把上面的任务做一个小小的修改，总评成绩的结果不再是“合格”与“不合格”，而是“优”、“良”、“及格”、“不及格”四种结果。,例：IF嵌套,完成总评成绩的计算（IF嵌套） 要求：60分以下为不及格；6080为及格；8090为良；90100为优。 任务分析： 此任务可使用IF函数的嵌套方法来完成，可先判断该学生成绩是否小于60分，如小于则“不及格”，否则继续判断该学生的成绩是否小于80分，如小于则为“及格”，否则继续判断该学生的成绩是否小于90分，如小于则为“良”，否则就不需要再继续判断了，该学生的成绩

13、为“优”。判断流程如图所示。,实施步骤,8.3.3 COUNTIF与SUMIF函数的使用,Excel 还提供了其他一些函数，可依据条件来分析数据。例如，如果要计算单元格区域中某个文本字符串或数字出现的次数，则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本字符串或数字求和，则可使用 SUMIF 工作表函数。,8.3.3.1 COUNTIF 函数,计算区域中满足给定条件的单元格的个数。 语法：COUNTIF (range, criteria) Range为需要计算的单元格区域。 Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以

14、表示为 32、“32”、“32” 或 “苹果”。 计算第一列中苹果所在单元格的个数 计算第二列中值大于 55 的单元格个数,8.3.3.2 SUMIF函数,根据指定条件对若干单元格求和。 语法：SUMIF (range, criteria, sum _ range) range 为用于条件判断的单元格区域。 criteria 为确定哪些单元格将被求和的条件，其形式可以为数字、表达式或文本。 sum_ range 是需要求和的实际单元格。 示例:分别计算下表中张三和李四的销售额的总计,8.4 在公式和函数中使用定义名称,除了可以使用行号、列标表示单元格或单元格区域外，Excel允许给单元格或单元

15、格区域命名。使用名称引用单元格区域时，既可简化输入，又无需记住区域的起始和终止位置。,8.4.1 定义名称,用以下两种方法可以定义名称。 1. 利用编辑栏的名称框定义名称 首先选定要命名的单元格区域，然后单击编辑栏左端的名称框，输入名称，按回车键。 2. 利用“插入”菜单定义名称 首先选定要命名的单元格区域，选择【插入】【名称】【定义】命令，弹出“定义名称”对话框，在“当前工作簿中的名称”文本框中输入名称，此时在“引用位置”文本框中显示单元格区域，单击“添加”“确定”按钮。,8.4.2 使用名称,名称定义后，从名称框的下拉列表中可以查看到已定义的名称。从中选定某一名称，它所表示的区域也同时被选

16、中。由于名称与它所表示的区域之间具有确定的关系，因此可以在公式和函数中使用名称。 当在新建的公式或函数中引用了已被命名的单元格区域，系统会自动用定义的名称替代该单元格区域的引用。 以求张三的销售额为例，张三销售额的计算公式便可以写成“=SUMIF(姓名,张三,销售额)”。这样使得公式更直观，更易于理解。,8.4.3 删除名称,选择【插入】【名称】【定义】命令，出现“定义名称”对话框，选中需要删除的名称，单击“删除”“确定”按钮。,小结,本章主要讲述了有关单元格地址的引用，使用“自动求和”按钮进行计算及如何使用Excel函数等操作。 作为一个电子表格系统，除了进行一般的表格处理外，最主要的还是它的数据计算能力。在Excel中，可以在单元格中输入公式或者使用Excel提