世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

2018年俄罗斯世界杯,昨天正式开始了!32支球队即将粉墨登场,这是球迷们一个盛大的节日,虽然中国队没有出线,但一点也不妨碍中国球迷的热情,据世界杯官媒报道,大约有4万多中国球迷购买了本届世界杯的门票,在各国球迷买票数量中排第9位。白岩松曾戏称,本届世界杯,除了中国队没去,该去的人都去了。

世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

我除了早早地打印好比赛日程,今天还用EXCEL做了个小组赛的自动积分表,只要录入比分,各队、各小组的相关数据,就可以自动生成,一目了然,现在和大家分享一下。

世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

先了解一下,本次世界杯比赛的积分规则:

32支球队将遵循以下条例,来决定哪支球队晋级16强。

一、每队获胜一场得3分、平局一场得1分、输球不得分。

二、小组排名依次依照下列规则从多到少排列:

1. 小组赛总积分;

2. 小组赛总净胜球数;

3. 小组赛总进球数;

如果两支或以上多支球队依照前述规则未分出排名先后,他们的排名将依照下列规则从多到少排列:

4. 涉及球队间相互比赛的总积分;

5. 涉及球队间相互比赛的总净胜球;

6. 涉及球队间相互比赛的总进球;

7. 公平竞赛分数

第一张黄牌:减1分

间接红牌(第二张黄牌):减3分

直接红牌:减4分

黄牌和直接红牌:减5分

8. 由FIFA组委会抽签决定。

世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

根据积分规则的第一条,和第二条前3小条,制作小组赛的自动积分表。

设计好表格,稍作美化后,如上图。录入A组比赛的模拟比分,现将表中的关键单元格数据的生成公式和思路,分享如下:

世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

1、用数组公式,生成不重复球队名

N6单元格:=INDEX(E:E,3 MATCH(,COUNTIF(N$5:N5,$E$4:$E$9),)),按“ctrl shift enter”,向下复制,产生4个国家名称

2、在J、K列建辅助列,用于IF函数,计算比赛“胜”“平”“负”

J4单元格:=IF(OR(F4="",H4=""),"",IF(F4>H4,E4&"胜",IF(F4<H4,E4&"负",E4&"平")))

K4单元格:=IF(OR(F4="",H4=""),"",IF(F4>H4,I4&"负",IF(F4<H4,I4&"胜",I4&"平")))

3、通过J、K辅助列,用countif函数,计算O、P、Q列的“胜”“平”“负”场次

O6单元格:=COUNTIF($J$4:$K$9,$N6&"胜")

P6单元格:=COUNTIF($J$4:$K$9,$N6&"平")

Q6单元格:=COUNTIF($J$4:$K$9,$N6&"负")

4、用sumproduct函数,计算总进球数、失球数

R6单元格:=SUMPRODUCT(($E$4:$E$9=$N6)*($F$4:$F$9)) SUMPRODUCT(($I$4:$I$9=$N6)*($H$4:$H$9))

S6单元格:=SUMPRODUCT(($E$4:$E$9=$N6)*($H$4:$H$9)) SUMPRODUCT(($I$4:$I$9=$N6)*($F$4:$F$9))

5、根据胜、平、负场数,计算积分

U6单元格:=O6*3 P6*1 Q6*0

6、小组名次,考虑三个方面因素,并按顺序执行:1)总积分;2)净胜球数;3)总进球数

V6单元格:=SUMPRODUCT(($U$6:$U$9>=U6)*1)-SUMPRODUCT(($U$6:$U$9=U6)*($T$6:$T$9<=T6)*($R$6:$R$9<=R6)) 1

7、每小组排前2名的球队出线,用if函数计算

W6单元格:=IF(OR(V6=1,V6=2),N6,"")

最后,向下复制公式,即可。其他各小组的计算公式类似,只要修改相关绝对引用的单元格区间就行了。

世界杯小组赛公式(世界杯来了,我的小组赛EXCEL自动积分表也做好了)

有兴趣研究和需要的朋友,可在文章最后通过百度网盘的链接下载电子表格,公式全部开放,里面还有本届世界杯比赛的电子日程表(网上日程表大都是图片版的噢)。

每天学习一点点,每天进步一点点,积硅步,必能致千里,得大成。