第三节 统计函数及其应用
主讲:夜空星辰
3.1常用统计函数
AVERAGE 平均值
MAX 最大值
MIN 最小值
LARGE 最大值
SMALL 最小值
SUBTOTAL 汇总
COUNTIF 单元格的个数
RANK 排位
FREOUENCY 频率分布
3.2 AVERAGE()语法及用法
AVERAGE返回参数平均值(算术平均)。
语法:AVERAGE(number1,number2, ...)
Number1, number2, ... 要计算平均值的1~30个参数数值。参数可以是数字,或者是涉及数字的名称、数组或引用
实例:计算各位裁判给出的平均分?
3.3 MAX()和MIN()语法及用法
MAX返回参数包含的数据集中的最大数值
MIN返回参数包含的数据集中的最小数值。函数MAX和MIN属于统计函数。
语法:MAX/MIN(numberl,number2,...)
numberl,number2,...为需要求最大值或最小值或包含需要求最大值或最小值数据的参数,最多可以有30个参数。数字、逻辑值、文本数字、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。
实例:计算去除最高分和最低分后的平均分?
3.4 LARGE()与SMALL()语法及用法
1、LARGE:返回数据集里第 k 个最大值。使用此函数可以根据相对标准来选择数值。
语法:LARGE(array,k)
Array 为需要从中选择第 k 个最大值的数组或数据区域。
K 为返回值在数组或数据单元格区域里的位置(从大到小排)。
说明:
l 如果数组为空,函数 LARGE 返回错误值 #NUM!。
l 如果 k < 0 或 k 大于数据点的数目,函数 LARGE 返回错误值 #NUM!。
l 如果区域中数据点的数目为 n,则函数 LARGE(array,1) 返回最大值,函数 LARGE(array,n) 返回最小值。
2、SMALL:返回数据集中第 k 个最小值。使用此函数可以返回数据集中特定位置上的数值。
语法:SMALL(array,k)
Array 为需要找到第 k 个最小值的数组或数字型数据区域。
K 为返回的数据在数组或数据区域里的位置(从小到大)。
说明:
l 如果 array 为空,函数 SMALL 返回错误值 #NUM!。
l 如果 k < 0 或 k 超过了数据点数,函数 SMALL 返回错误值 #NUM!。
l 如果 n 为数组中的数据点数,则 SMALL(array,1) 等于最小数值,SMALL(array,n) 等于最大数值。
实例:用统计函数找到销售业绩正数第三名和倒数第二名的销售业绩?
3.5 SUBTOTAL()语法及用法
SUBTOTAL()汇总
语法 :SUBTOTAL(function_num,ref1,ref2,...)
Function_num 为 101 到 111 之间的数字,指定使用何种函数在数据清单中进行分类汇总计算。
Function_Num 函数
101 AVERAGE 103 COUNTA 105 MIN 107 STDEV 109 SUM 111 VARP | 102 COUNT 104 MAX 106 PRODUCT 108 STDEVP 110 VAR |
Ref1, ref2, 为要进行分类汇总计算的 1 到 29 个区域或引用。
说明:
l 如果在 ref1, ref2,… 中有其他的分类汇总(嵌套分类汇总),将忽略这些嵌套分类汇总,以避免重复计算。
函数 SUBTOTAL 将忽略由数据清单筛选时产生的任何隐藏行。在需要对筛选过的数据清单中的可见数据进行分类汇总时,这一点很重要。
实例:制作出在筛选后的顺序序号,并对筛选后的天数人数进行求和统计?
3.6 COUNTIF()和RANK()语法及用法
1、COUNTIF()
语法:COUNTIF(range,criteria)
Range 为需要计算其中满足条件的单元格数目的单元格区域。
Criteria 为确定哪些单元格将被计算在内的条件,其形式可以为数字、表达式、单元格引用或文本。说明
2、RANK()
语法:RANK(number,ref,order)
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。
Order 为一数字,指明排位的方式。
l 如果 order 为 0(零)或省略,Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。
l 如果 order 不为零,Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。
实例:对总分数进行全年级排序统计
3.7 FREQUENCY()语法及用法
语法:FREQUENCY(data_array,bins_array)
Data_array 为一数组或对一组数值的引用,用来计算频率。如果 data_array 中不包含任何数值,函数 FREQUENCY 返回零数组。
Bins_array 为间隔的数组或对间隔的引用,该间隔用于对 data_array 中的数值进行分组。
l 如果 bins_array 中不包含任何数值,函数 FREQUENCY 返回 data_array 中元素的个数。
l 输入公式后,选中从公式单元格开始的单元格区域(比bins_array(分段点)单元格数多一个单元格)。按 F2,再按 Ctrl+Shift+Enter。如果公式未以数组公式的形式输入,则返回的结果为 1。
l bins_array(分段点)里的数据是否按升序排列不影响使用函数后所得到的最终数据。
实例:按区间进行人数统计?