美文网首页
应用技巧十五:条件均值类应用

应用技巧十五:条件均值类应用

作者: 陶泽昱 | 来源:发表于2018-07-30 23:28 被阅读0次

统计类函数是Excel中使用频率最高的一类工作表函数,用户的绝大多数报表都离不开它们,从简单的计数与求和,到多区域多种条件下的计数与求和,此类函数总是能够帮助用户解决他们的问题。本文主要介绍此类别函数中最常用和具有一定代表性中的统计平均值函数,并通过一些示例来展现如何使用它们来处理那些最经典和普遍的问题。

在实际工作中,计算数值平均数的应用非常广泛,如计算某个时期内某商品的平均价格、某学科的学员平均成绩等应用,都离不开平均值函数的运用。在没有特殊条件限制的时候,计算平均值使用SUM函数和COUNT函数进行组合或者直接使用AVERAGE函数就能够实现,但是当我们所要求的计算的平均值带有特殊条件时,如去掉极值后计算平均值、计算高于平均数之间的平均值等。在这种状况下使用AVERAGE就有点力不从心了。本文将针对这些情形做出一一解说,且这些都是在日常工作中经常遇到的,希望能够给大家带来便利。

一、统计指定条件的平均值

统计包含特定条件的平均值有很多种方法,通常用户会使用SUMIF函数结合COUNTIF函数来计算,其实借助Excel

2010的新增AVERAGEIF函数实现则相对简单。

示例1 统计部门员工平均工资

如图1左侧所示,展现了一份某公司的员工工资明细表,如果人力资源部门希望尽快地掌握各个部门的平均工资额度,则可以利用下面的公式来进行统计。

例如:在P3单元格计算“企划”部门的平均公司的公式如下:

=AVERAGEIF($D$3:$D$17,$M3,$K$3:$K$17)

该函数做为Excel

2010新增函数,结构与之间的SUNIF函数完全一致,主要按照指定的条件,按指定的工资求得平均数,具体结果如图1右侧所示。

示例2 统计各科成绩大于等于平均分的总平均分

在如图2所示的学员期末考试成绩表中,下面的公式将统计各科成绩中大于等于平均分的总平均分。

(1)利用SUMIF函数和COUNTIF函数的普通公式解法,如C17单元格公式如下:

=SUMIF(C$3:C$13,”>=”&AVERAGE(C$3:C$13))/COUNTIF(C$3C$13,”>=”&AVERAGE(C$3:C$13))

(2)利用条件均值AVERAGEIF函数的公式如下:

=AVERAGEIF(C$3:C$13,”>=”&AVERAGE(C$3:C$13))

通过上述公式可以看出,虽然可以将SUMIF函数和COUNTIF函数组合起来运用,但公式比较冗长,使用新增的AVERAGEIF函数公式则简单得多,运算效率也更高。

二、去除最高最低分的平均分

在统计工作中,经常希望将一组数据两端的极值去掉一部分之后再求平均值,即计算内部平均值。例如竞技比赛中常用的评分规则:去掉一个最高分和一个最低分取平均值为最后得分。

示例3 计算跳水比赛的选手得分

如图3所示,展示了一份2010年青奥会跳水比赛的评分明细表,8位裁判分别岁6位比赛选手进行评分,评分结果为所有评分中去掉两个最高分和两个最低分后取平均值为选手的最终得分。

对于已知评委数量的内部平均分的计算,利用LARGE函数或者SMALL函数也可以计算,如公式如下:

=AVERAGE(LARGE($B3:$I3,{3,4,5,6}))

如果使用Excel提供的内部平均值函数,则会更加简单,如J3单元格公式如下:

=TRIMMEAN($B3:$I3,4/COUNT($B3:$I3))

三、多条件平均值引用

当用户希望根据多个条件进行平均值的统计时,可以使用Excel

2010新增的另外一个函数—AVERAGEIFS函数来协助处理。

示例4 计算指定多条件下的平均工资

如图4所示,展示了一份某公司2010年第8、9月份的员工工资明细表,该明细表中包括了这两个月内各个部门员工的所有工资明细数据,下面的公式将针对用户所选择的统计条件计算出平均工资额。

具体条件如下。

(1)用户在M3单元格指定统计的月份数,如9月份。

(2)在M4单元格指定统计的部门名称,如设计部门。

(3)并在M5单元格中指定需要统计平均值的费用类别,如引发工资。

按照用户指定的统计条件,月份和部门名称都可以直接使用单元格引用取得,而工资类别将是一个动态的数据引用,结合OFFSET引用函数可以实现数据的动态引用,例如在M6单元格的统计公式如下:

=AVERAGEIFS(OFFSET($d$3:$d$17,0,MATCH($M$5,$E$2:$J$2,0)),$B$3:$B$17,$M$3,$D$3:$D$17,$M$4)

该公式利用OFFSET函数介个MATCH函数来动态引用“费用类别”的数据列,将其作为AVERAGEIFS函数的统计列,在分别根据月份列(B列)和部门名称列(D列)分别进行条件查找,最后由AVERAGEIFS函数得出记过为920。通过截图中可以看出,统计结果,与Excel状态栏下的统计结果完全一致。

�u�"'�

相关文章

  • 应用技巧十五:条件均值类应用

    统计类函数是Excel中使用频率最高的一类工作表函数,用户的绝大多数报表都离不开它们,从简单的计数与求和,到多区域...

  • 聚类分析的原理和应用(二)

    本文主要讲述聚类分析在SPSS中的实现,和对结果的解读 主要应用K均值聚类、系统聚类、两步聚类法 案例一 K...

  • 移动应用的性能指标

    内存:内存涉及运行应用所需要的RAM最小值,以及应用消耗的内存平均值和峰值。最小内存值会限制硬件。更高的内存平均值...

  • 优雅的获取数组最值、平均值(iOS)

    应用场景(快速的获取数组最值、平均值)

  • 001-20180101-Excel技巧学习(一)

    O 今天开始学习Excel应用的小技巧,主要学习了多条件筛选、批量加汉字、处理重复值等小技巧,这些技巧都可以慢慢积...

  • EXCEL之筛选后的求和2020-09-30

    应用EXCEL做分析时,经常用到条件筛选功能,筛选后有求和的需求,除了连续选中表格,在表格下出现和值、均值,手动记...

  • 第39条:必要时进行保护性拷贝

    @(EffecitiveJava)[类设计|JAVA基础] 应用场景:当我们准备实现一个具有特殊约束条件类的时候,...

  • 力学

    基础、技巧、应用、现实

  • 应用技巧

    职场中的学习是一种典型的功利性学习,它有两个要求:① 为用而学,而且要求一学就能用;② 要能越用越好、越用越深。 ...

  • MeanShift聚类算法及代码实现

    MeanShift 该算法也叫做均值漂移,在目标追踪中应用广泛。本身其实是一种基于密度的聚类算法。主要思路是:计算...

网友评论

      本文标题:应用技巧十五:条件均值类应用

      本文链接:https://www.haomeiwen.com/subject/angqvftx.html