首页 > 化工知识 > Excel函数公式:计算“最终得分”的2个超级技巧,高效准确

Excel函数公式:计算“最终得分”的2个超级技巧,高效准确

时间:2020-12-09 来源:Excel函数公式 浏览:

 


在实际的生活中,已经遇到过很多的“最终得分”,如演讲比赛活动、或公考活动……那么,如果快速有效的计算“最终得分”呢?


一、Excel计算“最终得分”:Trimmean函数法。

功能:返回一组数据的修剪平均值,也可以理解为剔除“异常”数据后再计算平均值。

语法结构:=Trimmean(数据区域,修剪分数)。“修剪分数”其实就是需要去除的个数除以总个数。当去除的数为奇数时,将去除的数向下舍入为最接近2的倍数。

目的:计算参赛人员的“最终得分”。

 

方法:

在目标单元格中输入公式:=TRIMMEAN(C3:J3,2/COUNT(C3:L3))。

解读:

1、公式中C3:J3为需要计算的数据范围,2为需要去掉的1个“最高分”和1个“最低分”。Count(C3:L3)计算的为实际参与评分的评委数。

2、语法结构中的“当去除的数为奇数时,将去除的数向下舍入为最接近2的倍数。”解读,例如,将公式中的“2”替换为3或5,则在实际的计算中,按2或4来执行。暨“最高分”和“最低分”必须是“成对”出现的。

3、如果计算的“最终得分”小数位数不符合要求,只需在【设置单元格格式】中设置小数位数即可。


二、Excel计算“最终得分”:Sumproduct函数法。

功能:返回相应的数组或区域乘积的和。

语法结构:=Sumproduct(数组1,[数组2],……,[数组N])。当只有一个数组时,对数组内的数据进行求和运算;多个数组时,数组的维数必须相同。

目的:计算参考人员的“最终得分”。

 

方法:

在目标单元格中输入公式:=SUMPRODUCT(C$3:E$3,C4:E4)。

解读:

1、从示例中可以看出,每个项目(体能,笔试,面试)所占的比例不同,所以不同项目的成绩要分别乘以对应的比例,然后再计算总和。

2、以“王东”为例计算“最终得分”:=C3*C4+D3*D4+E3*E4。由于公式中的C3:E3中的行时绝对引用,所以在计算“小西”的“最终得分”时,公式为=C3*C5+D3*D5+E5,以此类推。


三、对“最终得分”排名:Rank函数法。

功能:返回指定的值在指定范围内的排名,如果多个值排名相同,则返回平均值。

语法结构:=Rank(排序值,数值范围,[排序方式]),其中排序方式有两种,“0”和“1”,“0”为降序,“1”为升序。省略[排序方式]时,默认为降序。

目的:对“最终得分”排名。

 

方法:

在目标单元格中输入公式:=RANK(M3,M$3:M$9,0)。

解读:

在Rank函数的功能中已经说明,“如果多个值排名相同,则返回平均值”其实就是当出现值相同时,“名次”会出现“跳跃”的情况。如75分出现了2次且排名为3,则下一分数的排名为5,而不是4。


四、对“最终得分”排名:Sumproduct函数法。

目的:对“最终得分”排名。

 

方法:

在目标单元格中输入公式:=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))+1。

解读:

1、利用Sumproduct函数进行排名,不会出现“跳跃”的情况,如75分出现了2次且排名为3,则下一分数的排名为4,而不是5。

2、公式中F$4:F$10>F4比较形成一个以1和0为值的数组,COUNTIF(F$4:F$10,F$4:F$10)统计出每个值出现的次数;然后对应的值进行除法运算,形成一个以1和0为值的一维数组,最后进行求和,公式末尾的“1”为辅助值,因为排名是从“1”开始计算的,也很好理解,因为第1名的值不能“自己大于自己”,所以公式=SUMPRODUCT((F$4:F$10>F4)/COUNTIF(F$4:F$10,F$4:F$10))的计算结果为0。


结束语:

本文中利用两个比较常用的Excel函数Trimmean、Sumproduct,巧妙的解决了实际工作中经常遇到的“最终得分”问题,并对“最终得分”进行了排名,可谓是一个完整的思路。如果亲在学习的过程中有不明白或对计算“最终得分”有独到的见解,可以在留言区留言讨论哦!


 

版权:如无特殊注明,文章转载自网络,侵权请联系cnmhg168#163.com删除!文件均为网友上传,仅供研究和学习使用,务必24小时内删除。
相关推荐