【WPS表格技巧:对象数不确定的成绩统计】分享给互联网技能从业者学习和参考。
评比活动中为了更好地体现公正、公平、公开,组织者往往在现场设定多个评定内容、临时邀请多个评委专家,当场打分当场公布。利用WPS表格,能高效准确地完成这类统计对象数不确定的成绩统计任务。
基本思路
多个评委多个评定内容,属于评委数量不确定、评定内容数量不确定的命题,解决的基本思路是把评委数量等不确定的值转化为确定值来处理。
评分要求
多个评委对多项内容分别打分。评分中去掉一个最高分、一个最低分后求平均,所得结果作为得分;评定内容按相同权重进行汇总,所得结果为项目总得分。按总得分排出名次。以合唱比赛为例:现场确定的评委(数量不确定)对5个参赛节目的音色、处理、指挥、伴奏等现场确定的内容(数量不确定)进行评分,去掉一个最高分、一个最低分后求平均为某内容得分,各内容得分之和为该节目总得分。
处理过程
(一)设计固定对象数统计表格
打开WPS表格,新建空白文档,在“节目1”工作表中录入数据,计算各评委对某节目各内容的评分,假定5位评委、评定4个内容,如图1所示。
图1:5位评委对节目1的评分记录表
在图1G2格中输入:“=(SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)”(SUM是求和公式,MAX是求最大值公式,MIN是求最小值公式,COUNT是计数公式,这个公式的意思是:求总得,减去最高分,减去高低分,除以参加计分的评委个数)。并将此公式复制给G3、G4、G5(拖动G2格句柄,向下至G5格即可),同理分别计算音色、处理、指挥、伴奏的得分。见图2。
图2:拖动G2格句柄向下,复制公式给G3、G4、G5.
在工作表“Sheet2”中建立表格,计算各节目的总得分,具体方法同上,如图3所示。
图3:计算节目总分
把“节目1”工作表中音色、处理、指挥、伴奏等得分引用到工作表“Sheet2”中:在B2格中输入“=Sheet1!G2”,在B3格中输入 “=Sheet1!G3”,B4格输入“=Sheet1!G4”,B5格输入“=Sheet1!G5”。将标签1重命名为“节目1”。这样,评委对节目1 的评分自动被取用到标签2中,并按设定的计算公式自动计算出节目1的得分。如图4所示。
图4:取用“节目1”表中G2数据到标签2表B2中
(二)变化统计对象数
保存此设计,带到使用现场。根据现场使用要求,进行相应增减。如:评委人数增加为9位。此时,只要在图1中的得分列前增加4列即可。具体操作如下:打开标签1选中表中列标F(图1中的评委5这列的列标)右键选“插入”。继续这个操作,直到增加了4列,如图5所示:
图5:增加评委人数
打开“节目1”标签表,复制表格,按照步骤(一)中标签1的设计思路,新增标签粘贴设计出节目2、节目3、节目4、节目5表格,按上述方法将结果取用到标签2的对应单元格中。如图6所示。
图6:“节目3”表中K4数据取用到标签2表D4中
刚才在“节目1”标签表中插入了多列,得分栏的计算公式并没有作手工修改,我们看一下插入多列后的得分计算公式(图7):
图7:K2格中的总分计算公式
细心的读者一定发现了得分计算公式已经自动作了相应变化,由原来的“= (SUM(B2:F2)-MAX(B2:F2)-MIN(B2:F2))/(COUNT(B2:F2)-2)”变成了“= (SUM(B2:J2)-MAX(B2:J2)-MIN(B2:J2))/(COUNT(B2:J2)-2)”。利用WPS表格的这个特性,我们实现了统计对象数量的任意变化。注意,插入位置必须选在中间的某列,以确保计算公式的正确变化。
(三)按得分对节目排名
在标签2表格的“名次”列的“G2”单元格输入“=RANK(F2,$F$2:$F$7)”,然后按下回车键,第一个节目的名次就出来了。见图8所示。
图8:按总得分计算排名
公式中“$F$2:$F$7”是“总分”记录区域位置,实际中可以根据表格中的实际情况来填写。点中G2格句柄,向下拖动到G6,所有节目的名次立即显现。如图9所示。
图9:G2格中名次计算公式复制到G3、G4、G5、G6格中