Excel 2016数据处理与分析 第4章 函数

【例4-4】计算各班的高等数学平均成绩。
① 在E2、E3单元格中分别输入班级名称“财务01”和“财务02”。
② 在F2单元格中输入公式“=AVERAGEIF(B2:B12,E2,C2:C12)”， 其中：B2:B12是判断条件的单元格区域；E2单元格是条件；C2:C12单 元格区域是求平均值的区域。
含义是如果B2:B12的班级中有E2中的班级名称，则对其高等数学成绩 求平均值。
③ 将F2单元格中的公式复制到F3单元格，完成计算“财务02”班的平 均成绩。
3. COUNT函数
语法格式：COUNT(value1,value2, ...) 函数功能：计算包含数字的单元格以及参数列表中数字的 个数。 参数说明： 如果参数为数字、日期则被计算在内；文本、逻辑值不被 计算在内。 相关函数 （1）COUNTA：统计非空单元格的个数。 （2）COUNTBLANK：统计空单元格的个数。
方法三：使用求平均值AVERAGE函数。 ① 在G2单元格中输入公式“=AVERAGE(B2:F2)”； ② 复制G2单元格中的公式至G3~G5单元格中。设置
保留2位小数位数
8. 排序函数RANK
语法格式：RANK(number,ref,[order]) 函数功能：返回一个数字在数字列表中的排位顺序。 参数说明： number：需要排位的数字或单元格。 ref：数字列表数组或对数字列表的引用，是一个数组或单元格区域， 用来说明排位的范围。其中的非数值型参数将被忽略。 order：指明排位的方式。为0或者省略时，对数字的排位是基于降 序排列的列表；不为0时，对数字的排位是基于升序排列的列表。
4.3 常用函数
4.3.1 数学函数 1. ABS函数 语法格式：ABS(number) 函数功能：返回给定源自值的绝对值。2. INT函数
语法格式：INT(number) 函数功能：返回给定数值向下取整为最接近的整数。
3. MOD函数
语法格式：MOD(number,divisor)
函数功能：返回两数相除的余数。
① 选中B6单元格
② 输入公式：“=SUMPRODUCT(B2:B4,C2:C4)”
计算B2C2+B3C3+B4C4
8. SUMIF函数
语法格式：SUMIF(range,criteria,[sum_range]) 函数功能：返回满足条件的单元格区域中数字之和。 参数说明： 参数range是用于条件判断的单元格区域； criteria是计算的条件，其形式可以为数字、表达式或文本； sum_range是需要求和的实际单元格。 只有当range中的单元格满足criteria设定的条件时，才对 sum_range中对应的单元格求和。 如果省略sum_range参数，则对range中的单元格求和。
4.3.2 统计函数
1. AVERAGE函数 语法格式：AVERAGE(number1,number2, ...) 函数功能：返回参数的算术平均值。 参数说明： 如果参数是一个数组或引用，则只计算其中的数字， 空白单元格、逻辑值或文本将被忽略。
2. AVERAGEIF函数
语法格式：AVERAGEIF(range,criteria,[average_range]) 函数功能：返回满足条件的单元格算术平均值。 参数说明： 参数range是用于条件判断的单元格区域；criteria是计算的 条件，其形式可以为数字、表达式或文本；average _range 是需要求平均值的实际单元格。 只有当range中的单元格满足criteria设定的条件时，才对 average _range中对应的单元格求和。 如果省略average_range，则对range单元格区域求平均值。
参数说明：
参数必须是逻辑值TRUE或FALSE。如果指定的单元格区域 包含非逻辑值，则AND函数将返回错误值#VALUE!。
2．OR函数
语法格式：OR(logical1,logical2…) 函数功能：返回参数列表逻辑“或”的结果。只要有一个参 数为TRUE时，返回TRUE；所有参数均为FALSE时，返回 FALSE。 参数说明：
单元格中显示函数计算的结果 公式框中显示函数表达式
4.2.2 插入函数
1. 插入函数 系统提供了函数向导，引导用户正确输入函数，操作步骤如下： ① 单击编辑栏上的插入函数按钮 ，此时自动插入等号（=）， 同时打开“插入函数”对话框。
② 根据计算要求，选择函数类别：然后选择具体的函数。
常用函数：最近插入的函数按字母顺序显示在“选择函数”列表中。 某个函数类别：此类函数按字母顺序显示在“选择函数”列表中。 全部：所有函数按字母顺序显示在“选择函数”列表中。
【例4-3】按照销售员姓名汇总销售 额。
① 在E2单元格中输入销售员姓名。例如，“王宏”。
② 在F2单元格中输入公式 “=SUMIF(B2:B7,E2,C2:C7)”，其中：B2:B7是判断 条件的单元格区域；E2单元格是条件；C2:C7单元格 区域是求和区域。
含义是如果B2:B7的销售员中有E2中指定的销售员，
方法二：去除最高分和最低分后求平均值。
① G2单元格中输入公式“=(SUM(B2:F2)-MAX(B2:F2)MIN(B2:F2))/3”，
SUM(B2:F2)是计算出5个评委的总分； MAX(B2:F2)求出5个评委的最高分； MIN(B2:F2) 求出5个评委的最低分。
② 复制G2单元格中的公式至G3~G5单元格中。设置保留2位 小数位数
① 重新在H2单元格中输入公 “=RANK(G2,$G$2:$G$5)”；
② 复制H2单元格中的公式至H3~H5单元格中
4.3.3 逻辑函数
1. AND函数
语法格式：AND(logical1,logical2…)
函数功能：返回参数列表逻辑“与”的结果。当所有参数均 为TRUE时，返回TRUE（真）；只要有一个参数为FALSE 时，返回FALSE（假）。
4. COUNTIF函数
语法格式：COUNTIF(range,criteria) 函数功能：统计符合给定条件的单元格个数。 参数说明： 参数range是要统计的单元格区域；criteria是统计条 件，其形式可以为数字、表达式或文本。
【例4-5】统计不同性别的人数。
① 分别在D2、D3单元格中输入性别“男”和“女”。 ② 在E2单元格中输入公式“=COUNTIF(B2:B8,D2)” ：
4.2.3嵌套函数
嵌套函数是指在函数中使用另一个函数作为参数。 例如，嵌套函数IF(AVERAGE(A1:A3)>10,SUM(B1:B3),0)完成的功能是：当单元格 区域A1:A3的平均值大于10时，将返回单元格区域B1:B3的总和；否则返回值为0。 ① 单击编辑栏上的“插入函数”按钮。 ② 在“函数参数”的对话框中，将函数作为参数输入。在 “logical_text”编辑栏 中输入“AVERAGE(A1:A3)>10”；在“value_if_true”编辑框中输入 “SUM(B1:B3)”；在“value_if_false”编辑框中输入“0”。因为 AVERAGE(A1:A3)的计算结果为11.667大于10，所以结果为计算SUM(B1:B3)，等 于6。
语法格式：MEDIAN (number1,number2, ...) 函数功能：返回参数列表中的中值。
中值不同于平均值，中值又称中位数，其值是一组有序数中居于中间位置的 数，如果有偶数个数，则取最中间两个数的平均值。
数据{15、2、3、4}的中值是3.5；该组数据的平均值是6。
7. 求最小值MIN
参数说明：
参数number是被除数，divisor为除数。结果的正负 号与除数相同
如果参数divisor为0，将会导致错误返回值#DIV/0!。
4．POWER函数
语法格式：POWER (number, power) 函数功能：返回数值的乘幂。 参数说明：
参数number为底数 power为指数
7. SUMPRODUCT函数
语法格式：SUMPRODUCT(array1,array2, ...) 函数功能：将数组间对应的元素相乘，并返回乘积之和 参数说明：
数组参数必须具有相同的长度。 将非数值型的数组元素作为0处理
【例4-2】计算顾客购买水果的总价 钱。
分析：
B列中是水果的单价，C列是购买的数量，总价是每种 水果的单价乘以数量的总和。
B2:B8是统计的单元格区域； D2单元格是条件。
含义是统计B2:B8的学生中与D2中性别相同的男生人数。 ③ 将E2单元格中的公式复制到E3单元格，自动计算出女生人数。
5. MAX函数
语法格式：MAX(number1,number2, ...) 函数功能：返回参数列表中的最大值。
6. MEDIAN函数
③ 在“函数参数”对话框中，输入参数。若参数是单元格 区域，可以先单击“拾取器”按钮 。
2. 自动求和
求和是一个频繁使用的函数，在“开始”选项卡的“编辑” 选项组中有“自动求和”按钮 ，可以完成快速求和的功能。 操作步骤如下： ① 选中需要存放求和结果的单元格。 ② 单击“开始”选项卡“编辑”选项组中的自动求和按钮 ， 将自动插入用于求和的SUM函数。
语法格式：MIN (number1,number2, ...) 函数功能：返回参数列表中的最小值。
【例4-6】统计选手的最终得分
方法一：使用求中值MEDIAN函数。 ① 在G2单元格中输入公式“=MEDIAN(B2:F2)”； ② 复制G2单元格中的公式至G3~G5单元格中。设置
保留2位小数位数，
5．ROUND函数
语法格式：ROUND (number, num_digits) 函数功能：返回数值四舍五入的结果。 参数说明： 参数number为要四舍五入的数值 num_digits为指定保留的小数位数。如果num_digits 为0，则取整到最接近的整数。
6. SUM函数
语法格式：SUM(number1,number2, ...) 函数功能：返回参数中所有数字之和。 参数说明： 如果参数是一个数组或引用，则只计算其中的数字， 空白单元格、逻辑值或文本将被忽略。
第4章 函数
4.1函数概述
4.1.1函数语法
函数的语法形式为：函数名(参数1,参数2,…)。
每个函数的函数名是惟一的，并且不区分大小写。
参数的个数与类型随着函数的不同而变化。参数可以是常 量、单元格引用、单元格区域、单元格区域名称、公式或其 他函数等。
有些函数没有参数，但是函数名后的圆括号不能省略。 函数中的符号必须使用英文标点符号。
参数必须是逻辑值TRUE或FALSE。如果指定的单元格区域 包含非逻辑值，则OR函数将返回错误值#VALUE!。
【例4-7】计算选手的排名
分析：根据【例4-6】中选手的最终得分计算排名情 况。 在H2单元格中输入公式“=RANK(G2,G2:G5)”； 复制H2单元格中的公式至H3~H5单元格中。可以看 出结果出现了三个第1名的错误情况，原因是当公式 复制到H3单元格时改变为“=RANK(G3,G3:G6)”， 注意范围变化为：G3:G6，而正确的范围是应该为 G2:G5，所以范围参数需要使用单元格的绝对引用法， 即，$G$2:$G$5；或者单元格的相对引用法，即 G$2:G$5。这样在复制单元格时，排序的范围就不会
4.1.2函数类型
Excel中内置函数有300多个，根据函数的功能和应用 领域，函数主要分为如下的类型： 文本函数、信息函数、逻辑函数、查找和引用函数、 日期和时间函数、统计函数、数学和三角函数、财务 函数、工程函数、多维数据集函数和兼容性函数等。
4.2函数输入
4.2.1手动输入函数
Excel中有公式记忆式键入的功能，用户输入公式时会出现备选函数 列表，操作步骤如下：
① 输入等号（=）。
② 输入函数名称开始的几个字母，此时显示一个动态列表，其中包含 了与用户输入字母匹配的有效函数名。
③ 双击需要的函数，在“（”后，输入以逗号分隔的各个参数；或者 选择单元格或单元格区域作为参数。
④ 输入“）”，然后按回车键或单击编辑栏中的确认按钮 ，完成函 数输入。如果单击编辑栏中的取消按钮 ，则放弃函数输入。