精华内容
下载资源
问答
  • excel常用统计函数

    2019-04-25 11:06:04
    统计函数是一些预定义的公式,它们使用一些称为参数的特定数值按特定的顺序或结构进行统计分析计算。统计函数中的参数可以是数字,或者是涉及数字的名称、数组或引用。
  • EXCEL中的计算统计函数。我们大致分成7类来介绍统计函数。 1. ** 求和SUM,SUMIF,SUMIFS** SUM(Number1,[Number2],……):计算单元格区域中所有数值的和 SUMIF(range,criteria,[sum_range]);对满足的单元格求和 ...

    EXCEL中的计算统计类函数。我们大致分成7类来介绍统计类函数。

    1. ** 求和SUM,SUMIF,SUMIFS**

    SUM(Number1,[Number2],……):计算单元格区域中所有数值的和

    SUMIF(range,criteria,[sum_range]);对满足的单元格求和

    SUMIFS (sum_rang,criteria_range1,criteria1,[criteria_range2,criteria2],…):对一组给定条件指定的单元格求和。
    关键的参数:

    a. sum_range求和区域,在SUMIF函数中放在最后,在SUMIFS函数中放在最前面

    b. criteria_range条件区域,SUMIF函数中只有一个,SUMIFS函数中可以有任意多个。

    c. criteria条件值,SUMIF函数中只有一个,SUMIFS函数中可以有任意多个。

    举个例子,下表是爱数据学院的学生成绩单
    在这里插入图片描述

    现在想要统计所有人所有课程的总成绩,所有男生的总成绩,所有男生Python课程的总成绩。计算公式如下
    在这里插入图片描述

    SUMIF函数计算男生总成绩
    在这里插入图片描述

    SUMIFS函数计算男生Python的总成绩
    在这里插入图片描述

    2. 求平均值AVERAGE,AVERAGEIF,AVERAGEIFS

    AVERAGE(number1,[number2],…):返回其参数的算数平均值;参数可以是数值或者包含数值的名称、数组或引用。

    AVERAGEIF(range,criteria,[average_range]):查找给定条件指定的单元格的平均值(算数平均值)

    AVERAGEIFS (average_range,criteria_range1,criteria1,[criteria_range1,criteria2],…):查找一组给定条件指定的单元格的平均值(算数平均值)

    AVERAGEIF和AVERAGEIFS函数的用法与SUMIF和SUMIFS函数的用法是一样的,只不过一个是求平均值一个是求和。这里不再重复介绍了。

    3. 计数COUNT,COUNTIF,COUNTIFS

    COUNT (value1,[value2],…):计算区域中包含数字的单元格个数

    COUNTIF (range,criteria):计算某个区域中满足给定条件的单元格数目

    COUNTIFS (criteria_range1,criteria1,[criteria_range1,criteria2],…):统计一组给定条件所指的单元格数

    举个例子,我们想要统计学生总数,男生人数,以及学习python的男生人数。实现方式如下图:
    在这里插入图片描述

    统计学生总数就是用COUNT函数,统计成绩的单元格个数即学生总人数

    用COUNTIF函数统计男生总人数(单个限制条件)
    在这里插入图片描述

    用COUNTIFS函数统计学习python的男生人数(多个限制条件)
    在这里插入图片描述

    4. 最值MAX,MIN

    MAX(number1,[number2],…):返回一组数值中的最大值,忽略逻辑值及文本

    MIN(number1,[number2],…):返回一组数值中的最小值,忽略逻辑值及文本
    在这里插入图片描述

    5. 保留小数位数ROUND,ROUNDUP,ROUNDDOWN,INT

    ROUND(number,num_digits):按指定的位数对数值进行四舍五入

    ROUNDUP(number,num_digits):向上舍人数字

    ROUNDDOWN(number,num_digits):向下舍入数字

    ROUND函数是四舍五入,ROUNDUP函数是除0以外的所有数值都进位,ROUNDDOWN函数是所有的数值都舍去。看下面的例子理解起来会更清晰。以3.14159为例
    在这里插入图片描述

    6. 取整,平方根INT,SQRT

    INT(number):将数值向下取整为最接近的整数

    SQRT(number):返回数值的平方跟

    7. 分段统计FREQUENCY

    FREQUENCY(data_array,bins_array):以一列垂直数组返回一组数据的频率分布

    此函数用于分段统计频率分布,例如统计各个分数段的学生人数
    在这里插入图片描述

    先选中I2:I6即你需要填充的单元格,按F2,之后输入公式=FREQUENCY(E2:E11,G2:G6),然后按ctrl+shift+enter 就完成啦。
    在这里插入图片描述

    展开全文
  • Excel 常用统计函数

    2020-02-24 15:14:42
    有了数据我们就可以进行或简单或复杂的计算了,我们也可以把这样的操作算作统计、分析。从数据中找到奇妙的发现。

    基础版-统计函数

    先说一些简单的函数吧

    函数用途练习示例
    MAX取得一组数据中的最大值=MAX(D1:D10)
    MIN取得一组数据中的最大值=MIN(D1:D10)
    SUM忽略不符合数据类型的单元格,不参与运算=MIN(D1:D10)
    COUNT计数:所有数字的个数=COUNT(D1:D11)
    COUNTA计数:非空值的单元格计数,包括:数字、文本等等=COUNTA(D1:D11)
    AVERAGE平均值:只对数字类型单元格=AVERAGE(D1:D11)

    进阶版-统计函数

    函数用途示例
    COUNTIF带条件计数=countif(D1:D10 , ">80")在某个数据区域中,统计满足大于80条件的数据的个数。
    SUMIF带条件求和同上
    AVERAGEIF带条件求平均数同上
    COUNTIFS条件计数 – 里面的多条件是且的关系) 条件区域不分先后找到两列数据中,都大于60分的数据=COUNTIFS(I3:I9,"<60",J3:J9,"<60"),然后统计个数
    SUMIFS条件求和找到两列数据中,都大于60分的数据=SUMIFS(I3:I9,"<60",J3:J9,"<60"),然后求和
    AVERAGEIFS条件求平均数找到两列数据中,都大于60分的数据=AVERAGEIFS(I3:I9,"<60",J3:J9,"<60"),然后求平均数
    FREQUENCY在一组数据中,按照指定的划分规则(分段),进行统计
    RANK数值排名

    COUNTIFS/SUMIFS/AVERAGEIFS

    这三个函数的特点都是 ****IFS —— 多条件。

    COUNTIFS

    我们做个练习,做出如下的表格:
    在这里插入图片描述

    1. 使用函数先模拟数据 =RANDBETWEEN(0,100)
    2. 将数据固定下来
      【复制】-【选择性粘贴】
      在这里插入图片描述

    请问:班里有几名同学,所有科目都及格了?

    SUMIFS

    在这里插入图片描述

    展开全文
  • Excel常用财务函数和统计函数.doc
  • (2)描述统计量计算 ① 集中趋势计算 算术平均数:AVERAGE、AVERAGEA 几何平均数:GEOMEAN 调和平均数:HARMEAN 中位数:MEDIAN 众数:MODE 四分位数:QUARTILE K百分比数值点:PERCENTILE 内部平均值:TRIMMEAN ②...

    1、频数分布

    FREQUENCY函数
    在这里插入图片描述

    2、基础统计

    2.1、平均数系列

    1. 算术平均数:AVERAGE、AVERAGEA
    2. 几何平均数:GEOMEAN
    3. 调和平均数:HARMEAN
    4. 中位数:MEDIAN
    5. 众数:MODE
    6. 四分位数:QUARTILE
    7. K百分比数值点:PERCENTILE
    8. 内部平均值:TRIMMEAN

    2.2、方差系列

    1. 平均差:AVEDEV
    2. 样本标准差:STDEVA、STDEV
    3. 总体的标准偏差:STDEVP、STDEVPA
    4. 样本方差:VAR、VARA
    5. 总体方差:VARP、VARPA
    6. 样本偏差平方和:DEVSQ

    2.3、计数排序系列

    1. 计数:COUNT、COUNTA
    2. 极值:MAX、MAXA、MIN、MINA、LARGE、SMALL
    3. 排序:RANK、PERCENTRANK

    2.4、其它

    1. 偏斜度:SKEW
    2. 峰度:KURT
    3. 标准化值z:STANDARDIZE

    3、概率论与数理统计

    3.1、离散型分布

    1. 排列:PERMUT
    2. 概率之和:PROB
    3. 二项分布:BINOMDIST、CRITBINOM、NEGBINOMDIS
    4. 超几何分布:HYPGEOMDIST
    5. 泊松分布:POISSON

    3.2、连续型分布

    1. 正态分布:NORMDIST、NORMINV
    2. 标准正态分布: NORMSDIST、NORMSINV
    3. 对数正态分布:LOGINV、LOGNORMDIST
    4. 卡方分布:CHIDIST、CHIINV
    5. t分布:TDIST、TINV
    6. F分布:FDIST、FINV
    7. β概率分布:BETADIST、BETAINV
    8. 指数分布:EXPONDIST
    9. 韦伯分布:WEIBULL
    10. Г分布:GAMMADIST、GAMMAINV、 GAMMALN、GAMMALN

    3.3、相关性与回归分析

    1. 相关分析:COVAR、CORREL、PEARSON、FISHER、FISHERINV
    2. 线性回归分析:FORECAST、RSQ、LINEST、INTERCEPT、SLOPE、STEYX、TREND
    3. 曲线回归:LOGEST、GROWTH

    3.4、假设检验

    1. 均值极限误差计算:CONFIDENCE
    2. 方差假设检验:FTEST
    3. 均值假设检验:TTEST、ZTEST
    4. 拟合优度和独立性检验:CHITEST

    4、总结

    统计系列的函数有很多,但是在excel中常用的是本篇博客第二章的函数,这些函数也比较容易理解与使用。对于第三章的函数,需要有概率论与数据统计的基础,但是拥有统计学基础的人若是想要使用这些函数,怕是也不会使用Excel进行计算,所以这些函数可能有点鸡肋?

    展开全文
  • AVERAGE1.2 单一条件下的平均值计算:AVERAGEIF1.3 多条件下的平均值计算:AVERAGEIFS2 按指定条件计数2.1 条件判断介绍2.2 单字段多条件计数2.3 多字段多条件计数3 认识COUNTA与COUNTBLANK函数3.1 检查数据填写的...

    1 平均值计算

    1.1 无条件平均值计算:AVERAGE

    通常我们习惯直接使用AVERAGE函数进行数据的平均值计算,其实该函数可以与其他函数组合运用,来满足更复杂的平均值计算。

    实例:如图数据所示,要求在不进行排序的情况下计算出销量前5名销售的平均销量。
    在这里插入图片描述
    思路解析:在B16单元格输入数组公式:{=AVERAGE(LARGE(B3:B13,ROW(1:5)))},公式先利用LARGE函数求得销量前5名销售的销量数组,再用AVERAGE函数求出平均值。

    涉及的函数:ROW、LARGE、AVERAGE

    思考:如果需求改为“要求在不进行排序的情况下计算出销量末尾5名销售的平均销量”,该如何实现?
    答案:在B19单元格输入数组公式:{=AVERAGE(SMALL(B3:B13,ROW(1:5)))},该公式和上述公式类似,只是将large函数换成了small函数。

    注意:需按Ctrl+Shift+Enter三键输入数组公式,才能得到想要的结果。

    1.2 单一条件下的平均值计算:AVERAGEIF

    AVERAGEIF函数:AVERAGEIF(条件区域,条件,[求平均值区域])

    下面数据是一份销售报表,由于星期六、星期日为休息日,无销售额发生,因此金额栏中的值为0(用“-”代替)。
    在这里插入图片描述
    需求1:分别计算星期一至星期五的平均销售额,用于分析星期一至星期五的平均销售额变动情况。
    在这里插入图片描述

    思路:在N4单元格输入公式:=AVERAGEIF($H$3:$H$30,M4,$I$3:$I$30),然后向下填充公式,公式以具体星期作为条件,使用AVERAGEIF函数计算平均值。

    思考:如果上述问题使用AVERAGE函数实现,要怎么做?
    答案:在O4单元格输入数组公式:{=AVERAGE(IF($H$3:$H$30=M4,$I$3:$I$30))},然后向下填充公式,公式结合IF函数用逻辑判断H3到H30单元格区域的内容是否与M4单元格的内容相等,然后返回I3到I30单元格区域中属于M4单元格的值,最后用AVERAGE函数求平均值。

    需求2:计算2014年5月1日至28日之间有效工作日的日均销售额。
    在这里插入图片描述

    思路:在N12单元格输入公式:=AVERAGEIF(I3:I30,">0"),这里的有效工作日是指非周六周日,因此只需要计算金额列非0单元格的平均值即可。

    注意:当AVERAGEIF函数省略第三参数时,将使用第一参数同时作为条件判断与数值计算的区域。

    1.3 多条件下的平均值计算:AVERAGEIFS

    AVERAGEIF函数:AVERAGEIFS(求平均值区域,条件区域1,条件1,[条件区域2,条件2],[条件区域3,条件3],……)

    需求3:沿用1.2的数据,要求计算中旬有效工作日的平均销售额。
    在这里插入图片描述

    思路:该需求有两个条件,一个是中旬(即11日至20日),一个是有效工作日(即金额列非0的单元格),因此在N16单元格输入公式:=AVERAGEIFS(I3:I30,G3:G30,">=2014年5月11日",G3:G30,"<=2014年5月20日",I3:I30,">0")。

    注意:AVERAGEIFS函数的条件区域和条件值组成一组条件参数,最多允许设置127组条件参数(一般不会设置这么多条件)。

    2 按指定条件计数

    2.1 条件判断介绍

    在这里插入图片描述
    COUNTIF函数和COUNTIFS函数可以统计满足一定条件的单元格个数,条件参数中可以使用比较运算符和通配符。上面展示了COUNTIF函数常用的公式用法,这些公式同样适用于COUNTIFS函数,在仅有一个条件参数的情况下,两者的运算结果完全相同。
    设置COUNTIF函数或COUNTIFS函数的条件参数时,需要注意以下一些情况:

    • 判断条件 “<>”:这个条件参数表示不等于 ”真空”,“真空”表示单元格内没有任何数据,是真正的空单元格,设置这个判断条件可统计非真空单元格的个数;
    • 判断条件 “><”:这个条件参数仅表示统计大于 “<”符号的文本,注意区分 “<>”和 “><”的区别;
    • 判断条件 “=”:这个条件参数表示等于 “真空”,可用于统计真正空单元格的个数;
    • 判断条件“”:这个条件参数表示包含真空单元格及空文本,其中的 “空文本”一般是指由公式计算得到的结果;
    • 判断条件 “*”:这个条件参数代表所有文本,包括空格以及空文本,但不包含真空单元格,也不包含数值、逻辑值、错误值等数据单元格;
    • 判断条件 “<>”””:这个条件参数的含义并不代表“不等于空文本”,而仅仅只表示不等于单个双引号 (“ );
    • 判断条件 “?*”:这个条件参数表示统计所有单元格长度不为0的文本单元

    2.2 单字段多条件计数

    数据
    在这里插入图片描述

    需求1:统计销售量大于等于1000且小于1300的记录个数。
    在这里插入图片描述

    思路:该需求属于单个字段列多个条件的类型,可使用COUNTIFS函数进行统计,其参数中每两个参数形成一组关联条件区域和条件表达式(最大可以包含127组条件),因此在K15单元格输入公式: =COUNTIFS(L2:L10,">=1000",L2:L10,"<1300") *得到结果。

    扩展:上述需求除了可以用COUNTIFS函数直接统计外,还可以通过两个COUNTIF函数分别统计再算差额,在K16单元格输入公式:=COUNTIF(L2:L10,">=1000")-COUNTIF(L2:L10,">=1300") 得到结果;也可以在COUNTIF函数中运用数组参数作为计数条件,然后与数组相乘取得求和运算中的正负符号,最后用SUM函数求和得到差额。在K17单元格输入公式:=SUM(COUNTIF(L2:L10,">="&{1000,1300})*{1,-1}) 得到结果。

    2.3 多字段多条件计数

    需求2:沿用2.2的数据,统计工号以A或E开头的员工的汽油销售笔数。
    在这里插入图片描述
    思路:该需求属于多字段(工号和商品)多条件的类型,可直接使用COUNTIFS函数实现,分别统计工号以 “A”开头的员工的“汽油”销售笔数以及工号以 “E”开头的员工的“汽油”销售笔数,相加即可得到结果。在K22单元格输入公式:=COUNTIFS(J2:J10,“A*”,K2:K10,"*汽油")+COUNTIFS(J2:J10,“E*”,K2:K10,"*汽油") 得到结果。

    扩展:上述需求也可以运用COUNTIFS函数对“工号”和“商品”两个字段进行多字段多条件计数,条件为模糊条件,需要运用通配符“*”代表任意字符,对于“工号”字段条件,运用数组解决逻辑“或”的关系,计算结果为数组{2,2},最后用SUM函数求和。在K23单元格输入公式:=SUM(COUNTIFS(J2:J10,{“A*”,“E*”},K2:K10,"*汽油")) 得到结果。

    3 认识COUNTA与COUNTBLANK函数

    3.1 检查数据填写的完整性

    COUNTA函数可以返回单元格区域非空单元格个数;COUNTBLANK函数可以统计指定单元格区域中空白单元格的个数。
    在这里插入图片描述
    实例:如上表所示,固定资产清单中存在一些缺项,运用COUNTA和COUNTBLANK函数确认固定资产清单是否填写完毕。

    要求:运用COUNTA和COUNTBLANK函数,从“内容是否完整”和“是否存在空值”两个不同的角度实现数据检查的功能。

    思路
    (1)用COUNTA函数统计当前行中的字段填写个数,如果非空单元格个数与第二行中的标题个数相同,则返回空值(表示已填写完整),如果有缺项,则返回“缺项”。在K3单元格输入公式:=IF(COUNTA(A3:I3)=COUNTA($A$2:$I$2),"",“缺项”) ,然后向下填充得到结果。
    (2)用COUNTBLANK函数统计当前行中是否存在空值,如果计算结果大于0,即有空值,表示填写未完成,公式返回“缺项”,否则返回空文本。在L3单元格输入公式:=IF(COUNTBLANK($A3:$I3),“缺项”,""),然后向下填充得到结果。

    3.2 空与非空的判断

    运用COUNTA和COUNTBLANK函数进行数据统计时,需要注意数据表中空值和非空值的判断。
    在这里插入图片描述
    实例:如图所示,从表面上看,员工信息表中的性别字段均无内容,但实际上已被设置了6种真假空的情况。

    核查思路
    (1)用COUNTA函数统计当前行中的字段填写个数,如果非空单元格的个数为4,则返回空值(表示已填写完整),如果有缺项,则返回“缺项”。在U3单元格输入公式:=IF(COUNTA($O3:$R3)=4,"",“缺项”) ,然后向下填充得到结果。
    (2)用COUNTBLANK函数统计当前行中是否存在空值,如果计算结果大于0,即有空值,表示填写未完成,公式返回“缺项”,否则返回空文本。在V3单元格输入公式:=IF(COUNTBLANK($O3:$R3),“缺项”,""),然后向下填充得到结果。

    注意
    (1)COUNTA函数返回包含文本、假空单元格、逻辑值或错误值的结果,只有真空单元格不被计数,其参数可以是引用,也可以内存数组(指按Ctrl+shift+enter得到的结果)。
    (2)而COUNTBLANK函数则返回单元格区域中单元格为空单元格或公式计算结果为空文本的个数,其参数只能是单元格引用,不能是内存数组。

    4 应用SUMPRODUCT函数计算

    4.1 应用SUMPRODUCT函数进行多条件求和计算

    SUMPRODUCT函数将给定的几组数组中数组间对应的元素相乘,并返回乘积之和。利用这一特性,可以用该函数进行多条件求和、计数以及其他相关的数值计算。

    数据
    在这里插入图片描述
    需求1:根据上面数据明细,按品种、站点对“金额”进行分类汇总。
    在这里插入图片描述
    思路
    在I3单元格输入公式:=SUMPRODUCT(($B$2:$B$24=$H3)*($C$2:$C$24=I$2),$D$2:$D$24),将公式填充至K3单元格,然后向下填充至K7单元格。
    (1)构造数组1,用于判断数据表中的区域单元格引用是否为对应单元格给定的油品品种;构造数组2,用于数据表中的区域单元格引用是否为对应单元格给定的站点名称。两个数据条件相乘,数据均满足这两个条件时返回1,如果有一个或一个以上不满足,则返回0。
    (2)SUMPRODUCT函数将两个条件数组乘积再与目标计算字段“金额”所在的单元格区域进行数据相乘,并将乘积求和,得到计算结果。

    注意
    (1)用SUMPRODUCT函数进行多条件计算,要求条件单元格引用区域与目标计算字段单元格引用的数据区域大小相同。
    (2)用SUMPRODUCT函数进行多条件求和计算,也可以使用如下公式:=SUMPRODUCT((条件数组1)*(条件数组2)*……*(条件数组n)*(求和数据区域))。
    即多个“条件数组”与“求和数据区域”直接连乘。为了避免目标计算字段数据中存在空格或其他文本项造成计算出现#VALUE!的情况,建议使用通用格式:
    =SUMPRODUCT((条件数组1)*(条件数组2)*……*(条件数组n),(求和数据区域))

    4.2 应用SUMPRODUCT函数进行多条件计数计算

    在这里插入图片描述
    需求2:沿用4.1的数据,统计0号轻柴油单笔加油量在2000以上的笔数。

    思路:使用SUMPRODUCT函数计算时,如果想要得到满足条件记录的个数,只需用多个条件数组相乘。在I12单元格输入公式:=SUMPRODUCT(($B$2:$B$24=“0号轻柴油”)*($D$2:$D$24>=2000)) 得到结果。

    4.3 应用SUMPRODUCT函数进行排序

    在这里插入图片描述
    需求3:根据对应日期的经营总额进行排序

    思路
    在J17输入公式:=SUMPRODUCT(($I17<$I$17:$I$21)*1)+1 得到结果
    (1)用“对应日期的经营总额”在“全部日期经营总额单元格区域”中进行比较,当“对应日期的经营总额”<“全部日期经营总额单元格区域值”成立时,返回TRUE,否则返回FALSE,所以比较结果返回一个由TRUE和FALSE构成的逻辑数组,然后将逻辑数组乘以1,得到一个由0和1构成的新数组。
    (2)SUMPRODUCT再对由0和1构成的新数组求和,表示在“全部日期经营总额单元格数据区域”中比“对应日期经营总额”大的数据个数,所以“对应日期经营总额”在“全部日期经营总额单元格数据区域”内排列的位次,应该是比“对应日期经营总额”大的数据个数加1。
    (3)再将公式向下复制,则依次对“全部日期经营总额单元格数据区域”中每一个数据重复进行上述判断求和,从而排除数据区域中每一个数据的位次。如果区域中有相同的数,则计算结果也是相同的,因而排出来的位次也相同。

    5 按指定条件求和

    5.1 单字段多条件求和

    在这里插入图片描述
    需求:根据评分表、评分规则、系数表计算出各油站的月综合得分,以此考核油站的当月工作情况。

    思路
    在B16单元格输入数组公式:{=SUM((B$3:B$11=A16)*SUMIF(G$3:G$6,C$3:E$11,H$3:H$6)*TRANSPOSE(H$9:H$11))} ,然后向下填充得到结果。
    (1)转换等级为对应分值:利用SUMIF函数与COUNTIF函数类似“单字段多条件计数”的原理,使用C3:E11单元格的评分等级进行条件求和,转换为对应分值,计算结果为与C3:E11对应的9行3列的数组;
    (2)计算评分项目系数:将SUMIF得到的评分分值乘以每个项目对应的系数,得到实际得分值。由于系数为3行1列,而SUMIF的结果为3列,因此使用TRANSPOSE函数进行转置。
    (3)条件求和:将SUMIF与系数相乘的结果再乘以加油站名称判断,得到对应加油站的考评分项的分值。
    (4)最后用SUM函数求和,得到对应加油站的综合得分。

    5.2 多字段多条件求和

    在这里插入图片描述
    需求:计算7月份工号以G或P开头的员工汽油的销售量。

    解析:该需求涉及了三个字段的条件:日期、工号、商品

    思路
    在L14单元格输入公式:=SUM(SUMIFS(N2:N10,K2:K10,IF(MONTH(K2:K10)=7,K2:K10),L2:L10,{“G*”,“P*”},M2:M10,"*汽油")) 得到结果。
    公式运用SUMIFS函数进行多字段多条件求和,其中日期条件使用IF函数和MONTH函数,计算得到月份为“7月”的一组数组条件,而“工号”和“商品”两个字段条件为模糊条件,需要运用通配符“*”进行模糊匹配,对于“工号”字段条件,运用横向数组解决逻辑“或”的关系,最后用SUM函数求和,得到数组计算结果。

    注意:SUMIFS和SUMIF的参数顺序不同,SUMIFS中将求和区域作为第一参数,而在SUMIF中则是第三参数,使用时注意区分。

    5.3 二维区域条件求和

    SUMIF函数除了可以在一维数据区域中进行条件求和外,还可以在二维数据表中进行条件求和。
    在这里插入图片描述
    需求1:统计所有“康师傅”方便面的销售量合计。
    在S15单元格输入公式:=SUMIF(T3:Z11,“康师傅”,U3:AA11) 得到结果。

    需求2:统计所有工号为A开头的销售量合计。
    在S18单元格输入公式:=SUMIF(S3:Y11,“A*”,U3:AA11) 得到结果。

    思路:在条件区域中查找满足条件的值,如在T3:Z11数据区域中查找值为“康师傅”的单元格,然后根据满足条件值,在第三参数所指定的目标区域中求和运算。

    注意:使用SUMIF函数进行二维区域条件求和时,要求条件区域与求和区域需要尺寸相同,且相对位置要保持一一对应,这样才能得到正确结果,否则将不能得到正确结果。

    6 FREQUENCY函数技巧二则

    6.1 认识FREQUENCY函数

    FREQUENCY函数的作用是计算一组数据的频率分布,该函数的语法格式如下:
    FREQUENCY(data_array,bins_array)

    参数说明

    • data_array:参数data_array为一数组或对一组数组的引用,用于计算频率。如果data_array中不包含任何数值,函数FREQUENCY返回零数组。
    • bins_array:参数bins_array为间隔的数组或对间隔的引用,该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值,函数FREQUENCY返回data_array中元素的个数。

    函数特性
    (1)该函数的两个参数均支持数组和单元格区域的引用,当第二参数为二维数组或引用时,采用“先行后列”的方式进行统计。
    (2)分段点若有重复,只在首次分段点统计数值出现的个数,其余分段点返回0。
    (3)函数会忽略文本、逻辑值和空单元格,只对数值进行统计。

    6.2 分段统计数值结果

    在这里插入图片描述
    需求:统计各分数段(即[0,60)、[60,70)、[70,80)、[80,90)、[90,100])的人数。

    思路
    根据规则,在H2:H6单元格区域设置各分数段的分段点,然后同时选中I2:I6单元格区域,输入多单元格数组,公式如下:
    {=FREQUENCY($D$2:$D$21,$H$2:$H$5-0.001)}
    需要注意这里的花括号{}不是输入内容,而是编写完等号及左边的公式后按Ctrl+shift+enter键得到。

    FREQUENCY函数返回的元素个数会比bins_array参数中的元素个数多1个,多出来的元素表示超出最大间隔的数值个数。

    此外,在按间隔统计时,FREQUENCY函数是按包括间隔上限,但不包括下限进行统计(即左闭右开)。

    根据该函数的这些特征,设计公式时,需要在给出的间隔区间数据基础上进行必要修正,才能得到正确的结果:

    • 间隔区间要少取一个,取H2:H5数据区域,而不是表中显示的H2:H6;
    • 在给出的间隔区间上限值的基础上减去一个较小的值0.001,调整间隔区间上下限的开闭区间关系。

    6.3 计算连续相同值的最多个数

    在这里插入图片描述
    需求:根据上图的NBA篮球赛连胜场次统计数据中,计算两个球队各自的比赛最长连胜(得3分)的场次。

    思路
    根据需求,在W3单元格输出如下数组公式:
    {=MAX(FREQUENCY(IF(M3:V3=3,COLUMN(M3:V3)),IF(M3:V3<>3,COLUMN(M3:V3))))}
    然后向下拖动公式。

    该数组公式中的IF公式分别表示满足条件的数据赋值“列号”,其他赋值FALSE,忽略逻辑值后,以列号用作间隔区间,对第一参数中的连续列号进行分段统计个数,最后通过MAX函数取得最大值。

    7 RANK函数

    7.1 使用RANK函数对学生成绩排名

    **RANK函数语法:RANK(number,ref,order) ,其中Number 为需要找到排位的数字,Ref 为数字列表数组或对数字列表的引用(Ref 中的非数值型参数将被忽略),Order 为一数字,指明排位的方式。 **

    • 如果 order 为 0(零)或省略,WPS表格对数字的排位是基于 ref 为按照降序排列的列表。
    • 如果 order 不为零,WPS表格对数字的排位是基于 ref 为按照升序排列的列表。

    在这里插入图片描述
    需求:根据考试成绩表,按总分分别进行班级内部排名和全年级排名。

    思路
    班级内部排名:在I3单元格输出如下公式:=RANK(H3,$H$3:$H$7),然后向下拖动公式。二班的排名类似,只需将调整排名数据。
    年级内排名:全年级成绩区域包含一班和二班两个数据区域,中间不连续。RANK函数可以忽略引用区域中的非数值参数,而该函数的第二参数引用区域可以为单一数据区域,也可以是联合属于区域,因此公式可以为=RANK(H3,($H$3:$H$7,$H$10:$H$15))(联合区域)或者 =RANK(H3,$H$3:$H$15)(完整区域)。

    注意:RANK函数重复数的排名相同,但重复数会影响后续数值的排名,从出现跳跃式名次。

    7.2 认识RANK.EQ函数和RANK.AVG函数

    RANK.EQ函数和RANK.AVG函数是RANK函数的升级版,其中

    • RANK.EQ函数:返回一列数字的数字排位。 其大小与列表中其他值相关;如果多个值具有相同的排位,则返回该组值的最高排位。
    • RANK.AVG函数:返回一列数字的数字排位:数字的排位是其大小与列表中其他值的比值;如果多个值具有相同的排位,则将返回平均排位。

    RANK函数、RANK.EQ函数、RANK.AVG函数这三者的语法类似。
    在这里插入图片描述
    RANK.EQ函数和RANK.AVG函数两者的共同点:
    (1)可以对数据进行升序或降序排名;
    (2)排名范围只能是单元格引用,不支持数组引用;
    (3)支持联合单元格区域,如使用公式 =RANK.AVG(B2,Sheet1:Sheet3!B:B)来实现多表联合排名

    说明:RANK.EQ函数和RANK.AVG函数在相同数值中出现不同排名是由其返回值决定的。

    8 计算百分位排名

    PERCENTRANK 函数用于返回特定数值在一个数据组中的百分比排位,利用该函数可以对目标数据按一定数量比例进行分级。
    PERCENTRANK 函数语法:
    *PERCENTRANK(array,x,significance) *
    参数
    (1)Array:定义相对位置的数组或数字区域。
    (2)x:数组中需要得到其排位的值。
    (3)Significance:可选,一个用来标识返回的百分比值的有效位数的值。如果省略,函数PERCENTRANK保留三位小数。

    说明
    (1)若数组array为空,则PERCENTRANK 返回错误值 #NUM!。
    (2)若 significane < 1,则PERCENTRANK 返回错误值 #NUM!。
    (3)若数组里没有与x相匹配的值,则PERCENTRANK 将进行插值以返回正确的百分比排位
    在这里插入图片描述
    需求:按各公司营业额的大小排序,排名前20%的公司评定为A级单位,在级别列填入评定结果。

    思路:在C2单元格中输入如下公式,并填充至C15单元格:
    =IF(PERCENTRANK($B$2:$B$15,B2,2)>=0.8,“A级单位”,"")
    该公式使用PERCENTRANK函数计算B2单元格的值在B2:B15单元格区域的数据组中的百分比排位,保留2位小数后的结果为0.92(B2:B15单元格区域中小于B2单元格值得个数有12个,大于B2单元格值的个数有1个,百分比排位计算过程为12/(1+12)≈0.92),最后用IF函数判断大于或等于0.8时为“A级单位”。

    9 剔除极值,计算平均得分

    TRIMMEAN 函数返回数据集的内部平均值:先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。
    语法:TRIMMEAN(array,percent)
    参数
    (1)array 为需要进行整理并求平均值的数组或数值区域。
    (2)percent 为计算时所要除去的数据点的比例,例如,如果 percent = 0.3,在 20 个数据点的集合中,就要除去 6 个数据点 (20 x 0.3):头部除去3个,尾部除去 3 个。

    **说明 **

    • 如果 percent < 0 或 percent > 1,函数 TRIMMEAN 返回错误值 #NUM!。
    • 函数 TRIMMEAN 将除去的数据点数目向下舍入为最接近的 2 的倍数。如果 percent = 0.1,30 个数据点的 10% 等于 3 个数据点。函数 TRIMMEAN 将对称地在数据集的头部和尾部各除去一个数据。

    在这里插入图片描述

    需求:根据上面的评分表,要求在最终得分列计算“去掉一个最高分和一个最低分”后的平均得分。

    思路:在J2单元格填入公式 =TRIMMEAN(B2:I2,2/COUNTA(B2:I2)),然后向下填充,公式中的2/COUNTA(B2:I2)作为百分比参数,表示从B2到I2的非空单元格中去除2个数据点。
    注意:当存在多个相同极值(最大值或最小值)时,TRIMMEAN函数只会按要求各剔除其中一个,然后求平均值

    10 众数的妙用

    众数是指一组数值中出现频率最高(次数最多)的数值。
    在这里插入图片描述
    需求:根据上面的选手评分表计算出每个选手得分频率最高的分值。

    思路:在J2单元格输入公式=MODE(B2:I2),然后向下填充。

    扩展:MODE函数还可以在数值和字符混合的数据表中计算出现频率最高的内容。
    在这里插入图片描述
    如上面数据表所示,计算出现频率最高的内容,P1单元格的公式如下:
    {=INDEX(M2:M20,MODE(MATCH(M2:M20,M2:M20,0)))}
    MODE函数只能对数值进行计算,要在数值和字符混合的数据表中计算出现频率最高的内容,首先需要用MATCH函数将数据表中的值全部转为数值,其计算结果表示每一个数据在整个数据表中出现的位次,再用MODE函数从中计算出频率最高的位次,最后用INDEX函数,在数据表中查找对应定位的值,从而得到出现频率最高的值。

    如果需要对数据列表中存在的空值等情况进行容错,公式可改为=INDEX(M2:M20,MODE(MATCH(M2:M20&"",M2:M20&"",0)))。
    如果空值不计算在有效数据内,公式可改为=INDEX(M2:M20,MODE(IF(M2:M20<>"",MATCH(M2:M20,M2:M20,0))))

    注意*:当数据表中的多个数字出现最高频率相同时,则MODE函数按照先列后行、从上到下的原则返回第1个出现频率最高的数值。

    以上分享的干货均来自书籍《Excel2013实战技巧精粹》,更多技巧可自行查阅原书。

    展开全文
  • 今天和大家分享一组常用函数公式的使用方法,用心掌握这些函数,工作效率会大大提升。欢迎转发给更多有需要的人。一、相关概念(一)函数语法由函数名+括号+参数组成例: 求和函数:SUM(A1,B2,…) 。参数与参数之 间用...
  • Excel是办公室自动化中非常重要的一款...Excel函数共包含11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。
  • EXCEL 2016统计函数整理

    2018-11-28 15:58:29
    函数 名称 数学含义 AVEDEV 平均差 同平均数的的离差绝对值的算术平均数 AVERAGE 算术平均值 只统计数字 AVERAGEA 算术平均值 除数字外,还包含字符串与布尔值 COUNT 计数 计算样本数量 COUNTA 计数 ...
  • 财务工作中越来越离不开Excel了,这个日常最能提高生效效率的工具,对于很多人来说,运用的并不好,今天小编给大家整理了最常用的一些函数。01文本、日期与百分比连接要求:下面为日期与文本进行连接。E2单元格输入...
  • Excel常用函数汇总及函数的组合用法:包括快捷键和输入的技巧、常用逻辑类、日期时间类、文本类、查找与引用类、数学与三角类、统计函数,以及综合多个函数的用法
  • Excel中的数据统计分析,离不开函数公式,相对于一些“高大上”的应用技巧,函数,公式是基础,也是灵魂。一、求和。(一)单条件求和。函数:Sumif。功能:对满足条件的单元格求和。语法结构:=Sumif(条件范围,条件,...
  • 我们在利用excel表格统计数据时,常常需要使用各种excel自带的公式( 使用方法: =函数名(参数…)),下面将简单介绍下我们常使用到的几种方法。 MIN函数:找到某区域中的最小值 MAX函数:找到某区域中的最大值 ...
  • 数据分析EXCEL常用统计函数

    千次阅读 2019-02-13 21:51:51
    count家族:(计数) count:对含有数值的单元格进行计数 counta:对有值的单元格进行计数(数值,文本,逻辑值) countblank:对空格单元格进行计数 countif:对满足单个条件进行计数 countif(条件区域,条件) ...
  • 当我们整理好的数据,需要对数据统计,比如,我们要看平均销量,每月销量多少 每年的销售多少,每款产品的销量多少,这就用到我们的函数操作
  • Excel常用函数大全

    2015-08-21 15:16:20
    Excel常用函数大全 沃们在使用Excel制作表格整理数据的时候,常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法,以及这些函数在实际应用中的实例剖析,并配...
  • 单元格地址引用1、相对引用、绝对引用、混合引用,快捷键F4;例如多列变一列、一列变多列的相对引用,增长率的相对引用,本利和的混合...5、F9功能键逐步解析函数:选中需要解释的部分-F9,会出来这个函数的运行结...
  • 常用EXCEL 函数命令

    2018-11-22 15:36:12
    加入这列是方便在序号列使用公式统计行数。  在序号列输入公式  =SUBTOTAL(2,B$1:B2)  12、用QQ聊天的excel公式  =HYPERLINK("tencent://message/?uin="&C3,"点击联系他")
  • 目录 SUM SUMPRODUCT COUNT MAX 和 MIN RANK AVERAGE STDEV SUM 功能:计算指定区域的和 语法格式:=SUM(区域) 连续区域 不连续区域 ...我们也可以用SUM函数实现同样的效果 ...
  • 这次给大家带来几个Excel中的常用函数,在日常使用excel时,熟练掌握函数的运用技巧,使工作效率显著提高。隔行添加颜色将横向单元格每间隔1行添加颜色点击(开始)(样式)--(条件格式)--(新建规则)--点击第六...
  • 数据分析常用Excel函数都在这!

    万次阅读 2016-11-23 18:08:54
    excel是我们工作中经常使用的一种工具,对于数据分析的朋友来说,这也是处理数据最最基础的工具,然并不是每个人都掌握得很好,玩得很溜哦,主要是... 1、先说说统计函数吧:  large,small,max,min,median
  • Excel常用技巧——工作中最常用的30个Excel函数公式

    万次阅读 多人点赞 2018-11-20 19:06:53
    Excel常用技巧--工作中最常用的30个Excel函数公式   目录 一、数字处理 1、取绝对值 2、取整 3、四舍五入 二、判断公式 1、把公式产生的错误值显示为空 2、IF多条件判断返回值 三、统计公式 1、统计两个...
  • Excel常用公式与函数总结(Excel 2010)

    千次阅读 2019-02-10 21:53:56
     在Excel中输入函数时是不区分大小写的,无论是大写还是小写,结束函数编辑时,Excel会自动转化为大写。对于函数中的参数,当参数是文本时,要用双引号引起来,单纯的数字不需要加双引号,除非这个数字是文本性数字...
  • Excel常用函数功能、用法及实例剖析  我们在使用Excel制作表格整理数据的时候,常常要用到它的函数功能来自动统计处理表格中的数据。本专题整理了Excel中使用频率最高的函数的功能、使用方法,以及这些函数在实际...
  • Excel的功能在于对数据进行统计和计算,其自带了很多的函数,利用这些函数可以...1、Excel统计函数:普通求和Sum。功能:对指定的区域或数值进行求和。语法结构:=Sum(数值或区域1,数值或区域2,……数值或区域N)。...
  • 点击上方△蓝字可关注我们会计人2019职场充电站坚持阅读... Excel常用快捷键 8大类常用公式 一.数字处理1取绝对值=ABS(数字)2数字取整=INT(数字)3数字四舍五入=ROUND(数字,小数位数)二.判断公式4把公式返回的错误...
  • 平时工作中经常会用到各种求和,sum,sumif,sumifs,sumproduct,count,countif,countifs,counta,maxifs,minifs,averageifs是使用频率非常高的求和函数,虽然数据透视表汇总求和相当方便,但是必须更换字段,不能同时...
  • 本文总结在使用Excel进行数据分析时,最常用的功能和函数Excel的功能和函数非常多,用进废退,除了学习基本的函数和功能,最重要的是遇到问题可以快速的搜索并解决。 首先Excel可以处理的数据量有大多? 使用...
  • 文员常用的8个excel函数

    千次阅读 2019-08-27 17:00:15
    今天和大家分享一组常用函数公式的使用方法:职场人士必须掌握的8个Excel函数,用心掌握这些函数,工作效率就会有质的提升。 1、函数一:求和 Sum函数可以对数值型行或者列进行操作,计算一行或者一列的总和 2、...
  • EXCEL成绩统计分析常用函数[汇编].pdf

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 14,063
精华内容 5,625
关键字:

常用统计函数excel