看世界杯学做多因素Excel排名统计表

关注excel教程，回复1，领取全套免费教程

四年一度的世界杯激战正酣，每一名球迷都为自己喜欢的球队加油助威，更加关心这支球队所在小组的战况。随着第一轮比赛的结束，小组赛达到一个高潮，同组队伍的每一个进球都可以影响全局的结果。

虽说现在的媒体信息很发达了，时事新闻，发展预测随处可见，不过如果能够有一个自己输入数据后就能计算、预测出小组排名结果的Excel电子表格，无疑是非常方便的，今天就和大家分享一个这样的比赛成绩统计表Excel表格的最终效果如下图所示：。

功能：只要在黄色区域中输入比赛结果，就会自动计算出各项指标以及最终排名价值：①战况数据实时统计；②预测排名如果您想要本文配套的练习课件，请加入QQ群：316492581下载当然，更加重要的意义在于：通过这个表格的设计思路学到很多细节的知识点，如果自己工作中遇到计算综合排名的问题都可以借鉴。

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

确定整体思路在进行表格设计之前，要对相关的规则和需求进行整理，明确可以有哪些基础数据，最终需要得到什么结果，然后再去设计中间的环节在这个成绩统计表中，我们的基础数据就是每场结束后的两个数字（双方的进球数）；最终需要的结果就是小组排名。

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

上图中是官方的解释，算是一个比较复杂的计算规则了，对于这个规则我们可以归纳为以下几点：①根据每场比赛需要判断各队的胜负情况，胜记3分，平记1分，负记0分；②积分相同时按净胜球多少排名（净胜球就是进球数-失球数）；

③净胜球还相同时按进球数多少排名④对于进球数还相同的情况，要按比赛的犯规情况再去判断因为我们的基础数据仅仅是双方每场的进球数，所以第1~3点可以实现，而第4点无法实现接下来就需要根据这些信息去设计表格

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

下面来看看是如何处理的：

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

通过合理地使用合并单元格，既满足了将数字单独存放的要求，又符合视觉需求，很直观地就能看到比赛结果2.合理利用辅助列排版设计的第二个原则就是要简化输入步骤以及简化计算过程这点可以借助辅助列和公式来实现，例如在这个表格中，需要填写斜线下方黄色区域的比分，斜线上方的比分是用公式进行引用的：。

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

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

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

公式设计1.胜场的判断：=IF(F5>H5,1,0)

利用IF函数直接比较F5和H5，当F5大于H5为胜，得到1，反之得到0。胜场次数计算：=SUM(O5:Q5)

直接对前面的数据求和。2.平场的判断：=IF(AND(F5=H5,F5<>" "),1,0)

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

同样是直接求和。3.负场的判断：=IF(F5

与胜场的判断方法一样，只是公式中把大于符号改成小于符号。负场次数计算：

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

4.进球数、失球数和净胜球数的统计：进球数：=SUM(F5,I5,L5)失球数：=SUM(H5,K5,N5)净胜球数：=AA5-AB5这部分公式非常简单，都是加减运算。

5.积分的计算按照规则，胜场记3分，平场记1分，负场记0分，积分的公式就是：=R5*3+V5*1

至此，计算排名的各项指标我们都算出来了，分别是：积分、净胜球和进球数在根据这三项指标计算排名之前，还需要做一个过渡，将三项指标根据各自的优先级（权重）进行量化，变成可以比较的数字，可以在AF列使用这样的一个公式来实现：=AD5+AC5%+AA5%%，结果如下。

是不是感觉这个公式挺奇怪的，百分号是什么意思呢？我们重点来解释一下这个公式，这里用到三个单元格：AD5（积分）、AC5（净胜球）和AA5（进球数）

在排名规则中明确说到，先根据积分，积分相同时判断净胜球数，再相同才判断进球数那么如何将这种按顺序比较多数据转化为只比较一个数据呢？可以参考利用权重计算多因素排名的方法例如指标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列输入公式，结果如下。

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

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

经过了这么多步骤，终于到了计算最终排名的时候，一起来看看排名公式吧。6.排名的计算公式非常简单，就是rank函数的标准用法：=RANK(AF5,$AF$5:$AF$8)

再来复习一下rank函数的含义：=rank（要计算名次的数据，参与排名的区域，升序或降序），其中第三参数省略时按照降序排名，得分最高者为第一名。最后隐藏所有的辅助列，一个成绩统计表就完成了：

四、小结大家可能会有个感觉，设计一个这样的模板真的好麻烦，用这么多的辅助列，而且有些地方的公式还不能下拉，要一个一个填写，有没有不用辅助列可以直接下拉的方法呢？肯定是有的，不过使用起来未必方便，那会用到大量的数组公式和复杂的函数。

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

对于报表模板来说，设计时间的长短不是衡量好坏的标准，而是使用的方便程度和维护的难易度希望大家能够从今天这个例子领悟到一些设计模板的思路，可以让自己的工作变得更加高效，让自己的表格变得更加智能温馨提示：一年一度的618年中大促仅剩4天时间，需要学习软件技能的伙伴，抓紧时间报名哦。

咨询微信：603830039，或社群老师均可。活动详情请参考下面海报：

转载请联系授权！

哎呀！听说“阅读原文”可以学到更多知识可是我点不到啊。。。。。

亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。