当前位置 : 主页 > 软件教程 > excel >

实用Excel技巧分享:学习怎么做多因素排名统计表?

来源:互联网 收集:自由互联 发布时间:2022-06-23
在之前的文章《实用Excel技巧分享:两个为表格添加背景图的方法》中,我们学习了表格背景图的方法。今天我们来聊聊统计表,看世界杯学做多因素排名统计表,快来看看! 手机如何
在之前的文章《实用Excel技巧分享:两个为表格添加背景图的方法》中,我们学习了表格背景图的方法。今天我们来聊聊统计表,看世界杯学做多因素排名统计表,快来看看!

手机如何做表格:点击查看

四年一度的世界杯激战正酣,每一名球迷都为自己喜欢的球队加油助威,更加关心这支球队所在小组的战况。随着第一轮比赛的结束,小组赛达到一个高潮,同组队伍的每一个进球都可以影响全局的结果。虽说现在的媒体信息很发达了,时事新闻,发展预测随处可见,不过如果能够有一个自己输入数据后就能计算、预测出小组排名结果的电子表格,无疑是非常方便的,今天就和大家分享一个这样的比赛成绩统计表。

表格的最终效果如下图所示:

1.png

功能:只要在黄色区域中输入比赛结果,就会自动计算出各项指标以及最终排名。

价值:

①战况数据实时统计

②预测排名

当然,更加重要的意义在于:通过这个表格的设计思路学到很多细节的知识点,如果自己工作中遇到计算综合排名的问题都可以借鉴。下面就来看看具体的制作过程。

因为各小组的结构完全一致,我们仅以A组数据为例进行说明。整个表格的制作分为三个个阶段:确定整体思路、排版设计和公式设计。

一、确定整体思路

在进行表格设计之前,要对相关的规则和需求进行整理,明确可以有哪些基础数据,最终需要得到什么结果,然后再去设计中间的环节。

在这个成绩统计表中,我们的基础数据就是每场结束后的两个数字(双方的进球数);最终需要的结果就是小组排名。

如何根据每场的结果得到排名,还需要对排名规则进行了解:

2.png

上图中是官方的解释,算是一个比较复杂的计算规则了,对于这个规则我们可以归纳为以下几点:

①根据每场比赛需要判断各队的胜负情况,胜记3分,平记1分,负记0分;

②积分相同时按净胜球多少排名(净胜球就是进球数-失球数);

③净胜球还相同时按进球数多少排名。

④对于进球数还相同的情况,要按比赛的犯规情况再去判断。

因为我们的基础数据仅仅是双方每场的进球数,所以第1~3点可以实现,而第4点无法实现。

接下来就需要根据这些信息去设计表格。

二、排版设计

1、数字单独存放

排版设计要掌握的第一个原则是一个单元格只存放一种类型的数据。例如5:2这个比分,如果放在一个单元格,在后期进行统计分析时就需要把两个数字分别提取出来再进行计算,非常不方便。因此,在设计表格的时候需要重点考虑这个问题。下面来看看是如何处理的:

3.png

为了便于大家理解,我们将单元格边框显示出来,可以清楚地看到,实际上每个得分都是用了三个单元格,将两队的成绩与冒号分开存放,中间斜线的四个单元格,以及第四行中球队名称都是使用了合并单元格进行处理。

4.png

通过合理地使用合并单元格,既满足了将数字单独存放的要求,又符合视觉需求,很直观地就能看到比赛结果。

2、合理利用辅助列

排版设计的第二个原则就是要简化输入步骤以及简化计算过程。这点可以借助辅助列和公式来实现,例如在这个表格中,需要填写斜线下方黄色区域的比分,斜线上方的比分是用公式进行引用的:

5.png

为了统计每队的胜场、平场和负场数,用到了一些辅助列:

6.png

关于辅助列,有些朋友可能觉得过于麻烦,总是希望直接用公式得到结果,其实这是一个误解。在一些逻辑关系比较复杂或者计算步骤较多的模板表格中,善于利用辅助列可以简化公式的难度,降低运算量,同时便于使用过程中对表格的维护,因为计算过程都是通过辅助列实现的,如果需要调整某一处的计算方法,只需要改对应的地方即可。

以上是对这个成绩统计表设计的说明,看起来很多,其实都不难,接下来就看看这个表格里都用了哪些公式和函数。

三、公式设计

1、胜场的判断:=IF(F5>H5,1,0)

7.png

利用IF函数直接比较F5和H5,当F5大于H5为胜,得到1,反之得到0。

胜场次数计算:=SUM(O5:Q5)

8.jpg

直接对前面的数据求和。

2、平场的判断:=IF(AND(F5=H5,F5<>" "),1,0)

9.png

与胜场有点区别,平场判断时还需要加一个条件,也就是进球数不为空的时候才去比较,因此加了AND函数去做判断。同时满足进球数相等并且不为空这两个条件时才算打平,记为1,反之为0。

平场次数计算:

10.jpg

同样是直接求和。

3、负场的判断:=IF(F5

11.png

与胜场的判断方法一样,只是公式中把大于符号改成小于符号。

负场次数计算:

12.jpg

直接求和即可。

以上这部分内容是将每场比赛结果变成具体的数字,看起来有点麻烦,但是没什么难度。接下来将辅助列隐藏,仅显示胜场、平场和负场的统计次数即可。

13.jpg

4、进球数、失球数和净胜球数的统计:

进球数:=SUM(F5,I5,L5)

失球数:=SUM(H5,K5,N5)

净胜球数:=AA5-AB5

这部分公式非常简单,都是加减运算。

14.jpg

5、积分的计算

按照规则,胜场记3分,平场记1分,负场记0分,积分的公式就是:=R5*3+V5*1

15.jpg

至此,计算排名的各项指标我们都算出来了,分别是:积分、净胜球和进球数。

在根据这三项指标计算排名之前,还需要做一个过渡,将三项指标根据各自的优先级(权重)进行量化,变成可以比较的数字,可以在AF列使用这样的一个公式来实现:=AD5+AC5%+AA5%%,结果如下。

是不是感觉这个公式挺奇怪的,百分号是什么意思呢?

我们重点来解释一下这个公式,这里用到三个单元格:AD5(积分)、AC5(净胜球)和AA5(进球数)

16.png

在排名规则中明确说到,先根据积分,积分相同时判断净胜球数,再相同才判断进球数。那么如何将这种按顺序比较多数据转化为只比较一个数据呢?可以参考利用权重计算多因素排名的方法。例如指标A权重是50%,指标B权重30%,指标C权重20%,那么综合得分就是A*0.5+B*0.3+C*0.2,如此虽然有A、B、C三个排名因素,但只需要比较综合得分即可获得排名。

这时这就需要人为的指定权重,将需要先后比较的数据转化为一个数据。

公式=AD5+AC5%+AA5%%原本的写法应该是:=AD5*100+AC5*10+AA5,在AG列输入公式,结果如下。

17.png

也就是将每个指标按照优先级分别扩大100倍、10倍、1倍后相加,使每个指标不会在同一个数位上,而是分别位于百位、十位和个位,如此既保证了各指标的优先等级,又将多个指标化成了一个数据。(注:当前倍数设置只适合积分、净胜球、进球数据都是小于10的情况。)

在足球比赛中,数字都比较小(一般都不满10),如果要计算的数据量级比较大的话,仅仅差10倍是不够的,经常会遇到相差100倍或者10000倍的情况。在这种这种情形下,公式里就会有很多个0,此时可以变个思路,将扩大改为缩小,%的作用就是将数据缩小100倍,%%的作用就是将数据缩小10000倍,这就是=AD5+AC5%+AA5%%的由来。

经过了这么多步骤,终于到了计算最终排名的时候,一起来看看排名公式吧。

6、排名的计算

公式非常简单,就是rank函数的标准用法:=RANK(AF5,$AF$5:$AF$8)

18.png

再来复习一下rank函数的含义:=rank(要计算名次的数据,参与排名的区域,升序或降序),其中第三参数省略时按照降序排名,得分最高者为第一名。

最后隐藏所有的辅助列,一个成绩统计表就完成了:

19.png

四、小结

大家可能会有个感觉,设计一个这样的模板真的好麻烦,用这么多的辅助列,而且有些地方的公式还不能下拉,要一个一个填写,有没有不用辅助列可以直接下拉的方法呢?

肯定是有的,不过使用起来未必方便,那会用到大量的数组公式和复杂的函数。当然就这个表格来说,肯定还有继续优化的可能。老菜鸟曾经花了一周时间改进一个计算销售排名的报表模板,过程中反复的测试,修改。但是用户的体验非常好,因为只需要填写基础数据,就可以看到最想要的结果,中间的计算过程都是辅助列(单独存放在一个sheet里的),都用的是比较简单的函数,即便是有需要修改的时候,用户也能自己解决。

对于报表模板来说,设计时间的长短不是衡量好坏的标准,而是使用的方便程度和维护的难易度。希望大家能够从今天这个例子领悟到一些设计模板的思路,可以让自己的工作变得更加高效,让自己的表格变得更加智能。

相关学习推荐:excel教程

以上就是实用Excel技巧分享:学习怎么做多因素排名统计表?的详细内容,更多请关注自由互联其它相关文章!

【文章转自:扬州机房 http://www.558idc.com/yz.html欢迎留下您的宝贵建议】
上一篇:实例解析Excel怎么用条件格式制作旋风图
下一篇:没有了
网友评论