PHP程序员小白到大牛集训(12期免息)
作者信息
视频教程分类
推荐视频教程
  • php程序员小白到大牛三个月集训php程序员小白到大牛三个月集训
  • Laravel 9 学习正当时—保姆级教程,想学不会都难!Laravel 9 学习正当时—保姆级教程,想学不会都难!
  • 千万级数据并发解决方案(理论+实战)千万级数据并发解决方案(理论+实战)
  • Laravel基础与实战(模块化)Laravel基础与实战(模块化)
  • 首页 >专题 >excel > 正文

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

    转载2022-06-14 10:17:131266 关注公众号:每天精选资源文章推送
    在之前的文章《实用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技巧分享:学习怎么做多因素排名统计表?的详细内容,更多请关注php中文网其它相关文章!

    手机表格制作

    声明:本文转载于:部落窝教育,如有侵犯,请联系admin@php.cn删除

  • 相关标签:Excel
  • 推荐:Excel视频教程零基础入门到精通高级教学视频

    相关文章

    相关视频


    专题推荐