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

在之前的文章《实用excel技巧分享：两个为表格添加背景图的方法》中，我们学习了表格背景图的方法。今天我们来聊聊统计表，看世界杯学做多因素排名统计表，快来看看！

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

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

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

价值：

①战况数据实时统计

②预测排名

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

因为各小组的结构完全一致，我们仅以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里的），都用的是比较简单的函数，即便是有需要修改的时候，用户也能自己解决。

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

相关学习推荐：excel教程

以上就是实用Excel技巧分享：学习怎么做多因素排名统计表？的详细内容，更多请关注知识资源分享宝库其它相关文章！