Excel 2016数据处理与分析应用教程 (6)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。
• ① 输入等号(=)。 • ② 输入函数名称开始的几个字母,此时显示一个动态列表,其
中包含了与用户输入字母匹配的有效函数名。 • ③ 双击需要的函数,在“(”后,输入以逗号分隔的各个参数;
或者选择单元格或单元格区域作为参数。 • ④ 输入“)”,然后按回车键或单击编辑栏中的确认按钮 ,完
单元格中显示函数计算的结果 公式框中显示函数表达式
4
第 数章
函
4.1函数概述
• 4.1.1函数语法 • 函数的语法形式为:函数名(参数1,参数2,…)。
每个函数的函数名是惟一的,并且不区分大小写。 参数的个数与类型随着函数的不同而变化。参数可以
是常量、单元格引用、单元格区域、单元格区域名称、 公式或其他函数等。
• 有些函数没有参数,但是函数名后的圆括号不能省略。函数中的符号必须使用英文标点符号。
• ③ 将F2单元格中的公式复制到F3单元格,完成计算“财务02”
3. COUNT函数
• 语法格式:COUNT(value1,value2, ...) • 函数功能:计算包含数字的单元格以及参数列表中数字的个数。 • 参数说明: • 如果参数为数字、日期则被计算在内;文本、逻辑值不被计算在内。 • 相关函数 • (1)COUNTA:统计非空单元格的个数。 • (2)COUNTBLANK:统计空单元格的个数。
• ② 在F2单元格中输入公式 “ = AV E R A G E I F ( B 2 : B 1 2 , E 2 , C 2 : C 1 2 ) ” , 其 中 : B 2 : B 1 2 是 判 断条件的单元格区域;E2单元格是条件;C2:C12单元格区域是 求平均值的区域。
• 含义是如果B2:B12的班级中有E2中的班级名称,则对其高等数 学成绩求平均值。
B 2 : B 8 是 统 计 的 单 元 格 区 域 ; D 2 单 元 格 是 条 件 。
• 含义是统计B2:B8的学生中与D2中性别相同的男生人数。 • ③ 将E2单元格中的公式复制到E3单元格,自动计算出女
生人数。
5. MAX函数
• 语法格式:MAX(number1,number2, ...) • 函数功能:返回参数列表中的最大值。
在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。这样在复制单元格时,排序的范围就不会改变了。
8. SUMIF函数
• 语法格式:SUMIF(range,criteria,[sum_range]) • 函数功能:返回满足条件的单元格区域中数字之和。 • 参数说明:
参数range是用于条件判断的单元格区域; criteria是计算的条件,其形式可以为数字、表达式或
文本; sum_range是需要求和的实际单元格。 只有当range中的单元格满足criteria设定的条件时,才
• 方法三:使用求平均值AVERAGE函数。
①
在 G 2 单 元 格 中 输 入 公 式 “ = AV E R AG E ( B 2 : F 2 ) ” ;
②
复制G2单元格中的公式至G3~G5单元格中。设置保留2位小数位数
8. 排序函数RANK
• 语法格式:RANK(number,ref,[order]) • 函数功能ห้องสมุดไป่ตู้返回一个数字在数字列表中的排位顺序。 • 参数说明: n u m b e r : 需 要 排 位 的 数 字 或 单 元 格 。 r e f : 数 字 列 表 数 组 或 对 数 字 列 表 的 引 用 , 是 一 个 数 组 或 单 元 格
4.3 常用函数
• 4.3.1 数学函数 • 1. ABS函数 • 语法格式:ABS(number) • 函数功能:返回给定数值的绝对值。 •
2. INT函数
• 语法格式:INT(number) • 函数功能:返回给定数值向下取整为最接近的整数。
3. MOD函数
• 语法格式:MOD(number,divisor) • 函数功能:返回两数相除的余数。 • 参数说明: 参数number是被除数,divisor为除数。结果的正负号与除数相同 如果参数divisor为0,将会导致错误返回值#DIV/0!。
4.3.2 统计函数
• 1 . AV E R AG E 函 数 • 语法格式:AVERAGE(number1,number2, ...) • 函数功能:返回参数的算术平均值。 • 参数说明: • 如果参数是一个数组或引用,则只计算其中的数字,空白单元格、逻辑值或文本将被忽略。
2. AVERAGEIF函数
4.2.2 插入函数
• 1. 插入函数 • 系统提供了函数向导,引导用户正确输入函数,操作步骤
如下: • ① 单击编辑栏上的插入函数按钮 ,此时自动插入等号
(=),同时打开“插入函数”对话框。
• ② 根据计算要求,选择函数类别:然后选择具体的函 数。
常 用 函 数 : 最 近 插 入 的 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表中。
区域,用来说明排位的范围。其中的非数值型参数将被忽略。 o r d e r : 指 明 排 位 的 方 式 。 为 0 或 者 省 略 时 , 对 数 字 的 排 位 是 基
于降序排列的列表;不为0时,对数字的排位是基于升序排列的
【例4-7】计算选手的排名
• 分析:根据【例4-6】中选手的最终得分计算排名情况。
4.1.2函数类型
• Excel中内置函数有300多个,根据函数的功能和应用领域,函数主要分为如下的类型:
• 文本函数、信息函数、逻辑函数、查找和引用函数、日期和时间函数、统计函数、数学和三角函数、财 务函数、工程函数、多维数据集函数和兼容性函数等。
4.2函数输入
• 4.2.1手动输入函数
• Excel中有公式记忆式键入的功能,用户输入公式时会出现备选 函数列表,操作步骤如下:
• 语法格式:AVERAGEIF(range,criteria,[average_range]) • 函数功能:返回满足条件的单元格算术平均值。 • 参数说明:
参 数 r a n g e 是 用 于 条 件 判 断 的 单 元 格 区 域 ; c r i t e r i a 是 计 算的条件,其形式可以为数字、表达式或文本; average _range是需要求平均值的实际单元格。
6. MEDIAN函数
• 语法格式:MEDIAN (number1,number2, ...) • 函数功能:返回参数列表中的中值。
中值不同于平均值,中值又称中位数,其值是一组有序数中居于中间位置的 数,如果有偶数个数,则取最中间两个数的平均值。
数据{15、2、3、4}的中值是3.5;该组数据的平均值是6。
4.POWER函数
• 语法格式:POWER (number, power) • 函数功能:返回数值的乘幂。 • 参数说明:
参数number为底数 power为指数
5.ROUND函数
• 语法格式:ROUND (number, num_digits) • 函数功能:返回数值四舍五入的结果。 • 参数说明: 参数number为要四舍五入的数值 num_digits为指定保留的小数位数。如果num_digits为0,则取整到最接近的整数。
• ① 选中需要存放求和结果的单元格。 • ② 单击“开始”选项卡“编辑”选项组中的自动求和按钮 ,将自动插入用于求和的SUM函数。
4.2.3嵌套函数
• 嵌套函数是指在函数中使用另一个函数作为参数。 • 例如,嵌套函数IF(AVERAGE(A1:A3)>10,SUM(B1:B3),0) 完成的功能是:
某 个 函 数 类 别 : 此 类 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表 中。
全 部 : 所 有 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表 中 。
2. 自动求和
• 求和是一个频繁使用的函数,在“开始”选项卡的“编辑”选项组中有“自动求和”按钮 ,可以完成快速求和的 功能。操作步骤如下:
6. SUM函数
• 语法格式:SUM(number1,number2, ...) • 函数功能:返回参数中所有数字之和。 • 参数说明: • 如果参数是一个数组或引用,则只计算其中的数字,空白单元格、逻辑值或文本将被忽略。
7. SUMPRODUCT函数
• 语法格式:SUMPRODUCT(array1,array2, ...) • 函数功能:将数组间对应的元素相乘,并返回乘积之和 • 参数说明:
4. COUNTIF函数
• 语法格式:COUNTIF(range,criteria) • 函数功能:统计符合给定条件的单元格个数。 • 参数说明: • 参数range是要统计的单元格区域;criteria是统计条件,其形式可以为数字、表达式或文本。
【例4-5】统计不同性别的人数。
• ① 分别在D2、D3单元格中输入性别“男”和“女”。 • ② 在E2单元格中输入公式“=COUNTIF(B2:B8,D2)” :
对sum_range中对应的单元格求和。
【例4-3】按照销售员姓名汇总销售额。
• ① 在E2单元格中输入销售员姓名。例如,“王宏”。 • ② 在F2单元格中输入公式“=SUMIF(B2:B7,E2,C2:C7)”,其中:B2:B7是判断条件的单元格区域;
E2单元格是条件;C2:C7单元格区域是求和区域。 • 含义是如果B2:B7的销售员中有E2中指定的销售员,则对其销售额进行求和。
7. 求最小值MIN
• 语法格式:MIN (number1,number2, ...) • 函数功能:返回参数列表中的最小值。
【例4-6】统计选手的最终得分
• 方法一:使用求中值MEDIAN函数。
①
在G2单元格中输入公式“=MEDIAN(B2:F2)”;
②
复制G2单元格中的公式至G3~G5单元格中。设置保留2位小数位数,
数组参数必须具有相同的长度。 将非数值型的数组元素作为0处理
【例4-2】计算顾客购买水果的总价钱。
• 分析:
• B列中是水果的单价,C列是购买的数量,总价是每种水果的单价乘以数量的总和。
①
选中B6单元格
②
输入公式:“=SUMPRODUCT(B2:B4,C2:C4)”
•
计算B2C2+B3C3+B4C4
• 方法二:去除最高分和最低分后求平均值。 ① 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 位小数位数
当单元格区域A1:A3的平均值大于10时,将返回单元格区域B1:B3的总和; 否则返回值为0。 • ① 单击编辑栏上的“插入函数”按钮。 • ② 在“函数参数”的对话框中,将函数作为参数输入。在 “logical_text” 编 辑 栏 中 输 入 “ AV E R A G E ( A 1 : A 3 ) > 1 0 ” ; 在 “ v a l u e _ i f _ t r u e ” 编 辑 框 中 输入“SUM(B1:B3)”;在“value_if_false”编辑框中输入“0”。因为 AV E R A G E ( A 1 : A 3 ) 的 计 算 结 果 为 11 . 6 6 7 大 于 1 0 , 所 以 结 果 为 计 算
只有当range中的单元格满足criteria设定的条件时,才 对average _range中对应的单元格求和。
如 果 省 略 a v e r a g e _ r a n g e , 则 对 r a n g e 单 元 格 区 域 求 平
【例4-4】计算各班的高等数学平均成绩。
• ① 在E2、E3单元格中分别输入班级名称“财务01”和“财务 02”。
中包含了与用户输入字母匹配的有效函数名。 • ③ 双击需要的函数,在“(”后,输入以逗号分隔的各个参数;
或者选择单元格或单元格区域作为参数。 • ④ 输入“)”,然后按回车键或单击编辑栏中的确认按钮 ,完
单元格中显示函数计算的结果 公式框中显示函数表达式
4
第 数章
函
4.1函数概述
• 4.1.1函数语法 • 函数的语法形式为:函数名(参数1,参数2,…)。
每个函数的函数名是惟一的,并且不区分大小写。 参数的个数与类型随着函数的不同而变化。参数可以
是常量、单元格引用、单元格区域、单元格区域名称、 公式或其他函数等。
• 有些函数没有参数,但是函数名后的圆括号不能省略。函数中的符号必须使用英文标点符号。
• ③ 将F2单元格中的公式复制到F3单元格,完成计算“财务02”
3. COUNT函数
• 语法格式:COUNT(value1,value2, ...) • 函数功能:计算包含数字的单元格以及参数列表中数字的个数。 • 参数说明: • 如果参数为数字、日期则被计算在内;文本、逻辑值不被计算在内。 • 相关函数 • (1)COUNTA:统计非空单元格的个数。 • (2)COUNTBLANK:统计空单元格的个数。
• ② 在F2单元格中输入公式 “ = AV E R A G E I F ( B 2 : B 1 2 , E 2 , C 2 : C 1 2 ) ” , 其 中 : B 2 : B 1 2 是 判 断条件的单元格区域;E2单元格是条件;C2:C12单元格区域是 求平均值的区域。
• 含义是如果B2:B12的班级中有E2中的班级名称,则对其高等数 学成绩求平均值。
B 2 : B 8 是 统 计 的 单 元 格 区 域 ; D 2 单 元 格 是 条 件 。
• 含义是统计B2:B8的学生中与D2中性别相同的男生人数。 • ③ 将E2单元格中的公式复制到E3单元格,自动计算出女
生人数。
5. MAX函数
• 语法格式:MAX(number1,number2, ...) • 函数功能:返回参数列表中的最大值。
在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。这样在复制单元格时,排序的范围就不会改变了。
8. SUMIF函数
• 语法格式:SUMIF(range,criteria,[sum_range]) • 函数功能:返回满足条件的单元格区域中数字之和。 • 参数说明:
参数range是用于条件判断的单元格区域; criteria是计算的条件,其形式可以为数字、表达式或
文本; sum_range是需要求和的实际单元格。 只有当range中的单元格满足criteria设定的条件时,才
• 方法三:使用求平均值AVERAGE函数。
①
在 G 2 单 元 格 中 输 入 公 式 “ = AV E R AG E ( B 2 : F 2 ) ” ;
②
复制G2单元格中的公式至G3~G5单元格中。设置保留2位小数位数
8. 排序函数RANK
• 语法格式:RANK(number,ref,[order]) • 函数功能ห้องสมุดไป่ตู้返回一个数字在数字列表中的排位顺序。 • 参数说明: n u m b e r : 需 要 排 位 的 数 字 或 单 元 格 。 r e f : 数 字 列 表 数 组 或 对 数 字 列 表 的 引 用 , 是 一 个 数 组 或 单 元 格
4.3 常用函数
• 4.3.1 数学函数 • 1. ABS函数 • 语法格式:ABS(number) • 函数功能:返回给定数值的绝对值。 •
2. INT函数
• 语法格式:INT(number) • 函数功能:返回给定数值向下取整为最接近的整数。
3. MOD函数
• 语法格式:MOD(number,divisor) • 函数功能:返回两数相除的余数。 • 参数说明: 参数number是被除数,divisor为除数。结果的正负号与除数相同 如果参数divisor为0,将会导致错误返回值#DIV/0!。
4.3.2 统计函数
• 1 . AV E R AG E 函 数 • 语法格式:AVERAGE(number1,number2, ...) • 函数功能:返回参数的算术平均值。 • 参数说明: • 如果参数是一个数组或引用,则只计算其中的数字,空白单元格、逻辑值或文本将被忽略。
2. AVERAGEIF函数
4.2.2 插入函数
• 1. 插入函数 • 系统提供了函数向导,引导用户正确输入函数,操作步骤
如下: • ① 单击编辑栏上的插入函数按钮 ,此时自动插入等号
(=),同时打开“插入函数”对话框。
• ② 根据计算要求,选择函数类别:然后选择具体的函 数。
常 用 函 数 : 最 近 插 入 的 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表中。
区域,用来说明排位的范围。其中的非数值型参数将被忽略。 o r d e r : 指 明 排 位 的 方 式 。 为 0 或 者 省 略 时 , 对 数 字 的 排 位 是 基
于降序排列的列表;不为0时,对数字的排位是基于升序排列的
【例4-7】计算选手的排名
• 分析:根据【例4-6】中选手的最终得分计算排名情况。
4.1.2函数类型
• Excel中内置函数有300多个,根据函数的功能和应用领域,函数主要分为如下的类型:
• 文本函数、信息函数、逻辑函数、查找和引用函数、日期和时间函数、统计函数、数学和三角函数、财 务函数、工程函数、多维数据集函数和兼容性函数等。
4.2函数输入
• 4.2.1手动输入函数
• Excel中有公式记忆式键入的功能,用户输入公式时会出现备选 函数列表,操作步骤如下:
• 语法格式:AVERAGEIF(range,criteria,[average_range]) • 函数功能:返回满足条件的单元格算术平均值。 • 参数说明:
参 数 r a n g e 是 用 于 条 件 判 断 的 单 元 格 区 域 ; c r i t e r i a 是 计 算的条件,其形式可以为数字、表达式或文本; average _range是需要求平均值的实际单元格。
6. MEDIAN函数
• 语法格式:MEDIAN (number1,number2, ...) • 函数功能:返回参数列表中的中值。
中值不同于平均值,中值又称中位数,其值是一组有序数中居于中间位置的 数,如果有偶数个数,则取最中间两个数的平均值。
数据{15、2、3、4}的中值是3.5;该组数据的平均值是6。
4.POWER函数
• 语法格式:POWER (number, power) • 函数功能:返回数值的乘幂。 • 参数说明:
参数number为底数 power为指数
5.ROUND函数
• 语法格式:ROUND (number, num_digits) • 函数功能:返回数值四舍五入的结果。 • 参数说明: 参数number为要四舍五入的数值 num_digits为指定保留的小数位数。如果num_digits为0,则取整到最接近的整数。
• ① 选中需要存放求和结果的单元格。 • ② 单击“开始”选项卡“编辑”选项组中的自动求和按钮 ,将自动插入用于求和的SUM函数。
4.2.3嵌套函数
• 嵌套函数是指在函数中使用另一个函数作为参数。 • 例如,嵌套函数IF(AVERAGE(A1:A3)>10,SUM(B1:B3),0) 完成的功能是:
某 个 函 数 类 别 : 此 类 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表 中。
全 部 : 所 有 函 数 按 字 母 顺 序 显 示 在 “ 选 择 函 数 ” 列 表 中 。
2. 自动求和
• 求和是一个频繁使用的函数,在“开始”选项卡的“编辑”选项组中有“自动求和”按钮 ,可以完成快速求和的 功能。操作步骤如下:
6. SUM函数
• 语法格式:SUM(number1,number2, ...) • 函数功能:返回参数中所有数字之和。 • 参数说明: • 如果参数是一个数组或引用,则只计算其中的数字,空白单元格、逻辑值或文本将被忽略。
7. SUMPRODUCT函数
• 语法格式:SUMPRODUCT(array1,array2, ...) • 函数功能:将数组间对应的元素相乘,并返回乘积之和 • 参数说明:
4. COUNTIF函数
• 语法格式:COUNTIF(range,criteria) • 函数功能:统计符合给定条件的单元格个数。 • 参数说明: • 参数range是要统计的单元格区域;criteria是统计条件,其形式可以为数字、表达式或文本。
【例4-5】统计不同性别的人数。
• ① 分别在D2、D3单元格中输入性别“男”和“女”。 • ② 在E2单元格中输入公式“=COUNTIF(B2:B8,D2)” :
对sum_range中对应的单元格求和。
【例4-3】按照销售员姓名汇总销售额。
• ① 在E2单元格中输入销售员姓名。例如,“王宏”。 • ② 在F2单元格中输入公式“=SUMIF(B2:B7,E2,C2:C7)”,其中:B2:B7是判断条件的单元格区域;
E2单元格是条件;C2:C7单元格区域是求和区域。 • 含义是如果B2:B7的销售员中有E2中指定的销售员,则对其销售额进行求和。
7. 求最小值MIN
• 语法格式:MIN (number1,number2, ...) • 函数功能:返回参数列表中的最小值。
【例4-6】统计选手的最终得分
• 方法一:使用求中值MEDIAN函数。
①
在G2单元格中输入公式“=MEDIAN(B2:F2)”;
②
复制G2单元格中的公式至G3~G5单元格中。设置保留2位小数位数,
数组参数必须具有相同的长度。 将非数值型的数组元素作为0处理
【例4-2】计算顾客购买水果的总价钱。
• 分析:
• B列中是水果的单价,C列是购买的数量,总价是每种水果的单价乘以数量的总和。
①
选中B6单元格
②
输入公式:“=SUMPRODUCT(B2:B4,C2:C4)”
•
计算B2C2+B3C3+B4C4
• 方法二:去除最高分和最低分后求平均值。 ① 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 位小数位数
当单元格区域A1:A3的平均值大于10时,将返回单元格区域B1:B3的总和; 否则返回值为0。 • ① 单击编辑栏上的“插入函数”按钮。 • ② 在“函数参数”的对话框中,将函数作为参数输入。在 “logical_text” 编 辑 栏 中 输 入 “ AV E R A G E ( A 1 : A 3 ) > 1 0 ” ; 在 “ v a l u e _ i f _ t r u e ” 编 辑 框 中 输入“SUM(B1:B3)”;在“value_if_false”编辑框中输入“0”。因为 AV E R A G E ( A 1 : A 3 ) 的 计 算 结 果 为 11 . 6 6 7 大 于 1 0 , 所 以 结 果 为 计 算
只有当range中的单元格满足criteria设定的条件时,才 对average _range中对应的单元格求和。
如 果 省 略 a v e r a g e _ r a n g e , 则 对 r a n g e 单 元 格 区 域 求 平
【例4-4】计算各班的高等数学平均成绩。
• ① 在E2、E3单元格中分别输入班级名称“财务01”和“财务 02”。