ExcelHome技术论坛

标题: 世界杯将近,外国小哥赠我一份“吓死人”的Excel模板 [打印本页]

作者: ExcelHome    时间: 2018-6-12 19:18
标题: 世界杯将近,外国小哥赠我一份“吓死人”的Excel模板

1.
俄罗斯世界杯即将火热来袭,大洋彼岸的外国小哥赠给我们一份Excel模版,还附送了一句话:
中国足球队世界杯加油啊!!
我呸——这家伙大概不晓得老毛子那块地儿,俺们除了足球队没去,其它都去了,还加油个毛线啊,再说了油价可刚涨没多久啊……
外国小哥表格制作的相当精美,不禁使我再次感慨,只要想象力足够,Excel就是一款没有上限的神器。
不信?先给在座的诸位抖个Splash封面瞧瞧。
(, 下载次数: 96)

从长相上,真没看出来外国小哥是个贴心的人呐,居然知道俺看不懂洋文,在封面的右上角设置了语言下拉框,我默默的选择了中文……
(, 下载次数: 32)

鼠标点击封面图,Excel自动跳转进入另外一张工作表。
然后……我需要冷静一下……你说大家都是玩Excel的,为啥外国小哥能把Excel养成了女儿,打扮的不像话!而我却只能把Excel惯成混小子,打拌的不像话呢?男女娃娃之间的差距真的就这么大吗?
看——外国小哥的表是不是打扮的好看的不像话:
(, 下载次数: 36)

更关键的是,人家不只是看起来很秀,内蕴也很秀,各种功能相当有料有趣啊。
上面一排高大上的导航图,每个图形内贴心的显示了国家名,还各自指向A~H八个小组的工作表,点击图形,就可以自动跳转到相关工作表。
(, 下载次数: 27)

有意思吧……
导航图下面的区域,左侧是比赛的时间和城市;中间是各支球队的比分;右侧是小组数据统计。
我本着从灭霸同志那里学习来的绝对公平公正原则,给每场比赛都输入了1:0的比分……
然后………
(, 下载次数: 24)

小组数据统计表很快计算出了各支球队场次、胜、平、负的次数;还有进球数、失球数、净胜球数以及积分……
更有趣的是,统计表的国旗和国名是按名次进行自动排列的。
BUT……
What Are You弄啥嘞?不是绝对公平的1:0吗?为什么积分不一……哦哦哦~这句话当我没说,俺承认是伪球迷你怎么着吧……
……
当小组所有的比赛尘埃落定后,表格下方区域,还会自动计算出小组第一名和第二名的国名和国旗。
(, 下载次数: 13)

有趣吧?
……我动动鼠标,溜进淘汰赛表格,发现小组赛出线的国家,名字已经自动写入相应的单元格。
此时,一股来自宇宙深处的神秘力量,促使我支配波斯铁骑干掉了北极熊………
(, 下载次数: 17)

更有趣的事,我们可以很认真的告诉在坐的诸位熊迪姐妹们,这些功能……你们……几乎……都可以……自己……来实现!
所有的功能均未涉及VBA编程,都是通过Excel函数完成的,而且大都是最基础的那部分函数哦,比如VLOOKUP、INDEX、SUMIF……。

2.
EH电视台,EH电视台,现在插播一条重要新闻,下载本模板请点击:https://www.spreadsheet1.com/fifa-world-cup-2018-russia-excel-download.html,若是手机端打开会比较慢。
友情提示,下载请选择中文版。 (, 下载次数: 51)

3.
打个响指,咱们一起聊聊模版里的几个主要功能是如何实现的?
看我眼神,再次给大家港,模版里涉及到的所有技巧都是ExcelHome公众号过去分享过的。关注不会迷路,如果你还没关注我们——自个看着办吧。

3.1语言切换。
模版的封面处有一个【数据验证】制作的语言选择下拉框,看起来很拉风,但实现起来其实很简单。
(, 下载次数: 14)
首先建立一张参数表,提供各个国家名字的各种语言版本,然后使用VLOOKUP等函数根据所选语种查询引用即可(模版里隐藏起来的参数表名为Games)。
摊手,忒社会,没想到VLOOKUP还可以这么用?
当然,如果你想来个更高大上的,还可以使用网络函数,通过有道翻译实现各语种自动切换。

3.2导航图
相当炫酷的导航图是有多个图形组合而成的。通过菜单【插入】→【形状】,保持耐心,选择合适的图形并填充颜色组合即可。
(, 下载次数: 18)
右键点击组合完成的图形,在快捷菜单中选择【链接】。在弹出的【超链接对话框】中,设置目标单元格引用地址。搞定。
(, 下载次数: 26)


3.3小组赛数据统计
场次、胜、平、负可以用SUMPRODUCT函数。
场次:
=SUMPRODUCT(((E$7:E$17=O9)+(K$7:K$17=O9))*(G$7:G$17<>""))
胜:
=SUMPRODUCT((E$7:E$17=O9)*(G$7:G$17-I$7:I$17>0)+(K$7:K$17=O9)*(I$7:I$17-G$7:G$17>0))
平/负和胜的公式几乎无差,只是修改了一个小地方。
平:
=SUMPRODUCT((E$7:E$17=O9)*(G$7:G$17-I$7:I$17=0)+(K$7:K$17=O9)*(I$7:I$17-G$7:G$17=0))
负:
=SUMPRODUCT((E$7:E$17=O9)*(G$7:G$17-I$7:I$17<0)+(K$7:K$17=O9)*(I$7:I$17-G$7:G$17<0))
进球数和失球数可以使用SUMIF函数。
进球数:
=SUMIF(E$7:E$17,O9,G$7:G$17)+SUMIF(K$7:K$17,O9,I$7:I$17)
失球数:
=SUMIF(E$7:E$17,O9,I$7:I$17)+SUMIF(K$7:K$17,O9,G$7:G$17)
积分就是加减乘除了,胜的场次*3+平的场次。
机密吐槽:模版里外国小哥的公式写的是化简为繁了,隔着宽阔的太平洋,瞪他一眼,随便看看就好……
另外还有两个知识点……
一个是根据国家名称自动引用国旗图标。
另外一个是根据球队积分、相互间的胜负关系、净胜球数目排列名次。
这是一个多条件排序的问题,一般通过加权方式处理,我们过去也分享过类似的函数算法。当然,如果你不能通过一条函数公式来完成,使用辅助列或定义名称是最恰当不过的了。既好理解,运算效率也有保障。制作模版时,我们通常更推荐使用后者。
……

4.
鲁迅先生说过,世界杯来了,世界杯终究还是要走的。
但作为一枚货真价实的伪球迷,我们这次打算玩点有趣的……
本期分享的模版只是一个开始哦,随着比赛的进行,我们会陆续推出各种有趣的教程。比如,用VBA爬取各队球员的数据,用透视表或函数分析球员的属性(俱乐部、位置、身价)以及各种图表可视化等等……


作者: 川贝枇杷    时间: 2018-6-12 19:41
本菜比大开眼界。
作者: lmylmy224    时间: 2018-6-12 19:55
大开眼界.......
作者: WYLLZQ    时间: 2018-6-12 20:03
看了这个帖子只能说,叹为观止
作者: skyzxh    时间: 2018-6-12 20:53
一个表格差距就那么大!更何况芯片?!
作者: 同游人    时间: 2018-6-12 23:18
这个要收藏了!厉害啊!
作者: ashleyfeng    时间: 2018-6-13 08:20
是否可免責下載?
作者: yylucke    时间: 2018-6-13 10:16
谢谢分享,慢慢研究。
作者: miceqi    时间: 2018-6-13 10:30
膜拜大神,真是一个神作啊!
作者: shaowu459    时间: 2018-6-13 10:53
ashleyfeng 发表于 2018-6-13 08:20
是否可免責下載?

公众号文章下面评论区有下载链接
作者: fanteng520    时间: 2018-6-13 11:18
图表保护了,有没有密码呢
作者: darlingxx    时间: 2018-6-13 11:42
大神啊,这样也可以啊,收藏了
作者: 风色剑仙    时间: 2018-6-13 12:00
想拆开研究下 问下有没有密码
作者: kuangben8    时间: 2018-6-13 12:28
这是谁做的,然后放在外国网站的吧?上面居然有咱EH的连接
作者: 努牛    时间: 2018-6-13 13:53
厉害了,这都可以,这才是大大大大表哥!
作者: 2479028489    时间: 2018-6-13 14:22
工作簿保护密码是什么?
作者: d9896986    时间: 2018-6-13 15:09
我新来的,请大家多多指教,谢谢~
作者: skiss    时间: 2018-6-13 20:12
确实有点“吓人”,膜拜大神。
作者: ttyong    时间: 2018-6-13 21:53
球迷中的高手!
作者: 同游人    时间: 2018-6-14 02:20
打开网页好慢!今晚开始看球了。
作者: dingmurch    时间: 2018-6-14 08:12
路过,膜拜一下
作者: 02761752696    时间: 2018-6-14 08:23
那个大神下载了   分享下啊    学学大神的思维   哈哈哈    顶顶顶
作者: 许栩    时间: 2018-6-14 08:33
印证了一句话:高手,不在于懂的绝招多,而在于使用招数的能力!
作者: qqyyh    时间: 2018-6-14 10:02
好牛的作品。
作者: YYB111    时间: 2018-6-14 10:11
牛人啊,学习真的是无止境的,受教了。
作者: snacas    时间: 2018-6-14 10:29
信息技术不愧是当代最先进的技术了。想学好也不容易啊。
作者: toperyan    时间: 2018-6-14 10:54
谢谢分享。
作者: 踏雪访梅    时间: 2018-6-14 11:46
想看隐藏数据,却被密码挡住。
作者: imraku    时间: 2018-6-14 12:11
这个NB了,下载今晚看球用!
作者: wshcw    时间: 2018-6-14 15:01
漂亮得亮瞎眼了。


作者: bl041322    时间: 2018-6-14 15:12
太神奇了啊
作者: liugq0125    时间: 2018-6-14 15:47
这是大开眼界呀
作者: 闻声悟道    时间: 2018-6-14 16:14
楼主哦,我电脑端还是打不开?
作者: 苏南    时间: 2018-6-14 16:45
看到有密码,就觉得只能看看热闹了,,
作者: 踏雪访梅    时间: 2018-6-14 19:07
想看隐藏工作表,保护密码猜不到。
作者: redguardyu    时间: 2018-6-14 22:09
相当强大!厉害厉害!
作者: redguardyu    时间: 2018-6-14 22:19
楼主知道xls文件的保护密码吗?保护的部分打开来可以仔细研究啊!
作者: CNGDHYJ    时间: 2018-6-15 09:18
这个厉害,好好学习学习,谢楼主!
作者: 同游人    时间: 2018-6-15 09:32
一个是工作簿保护,还有一个工作表保护,该如何去掉,我想看看是怎么做的。
作者: 一碧清蓝    时间: 2018-6-15 10:54
牛逼神作,谢谢分享
作者: 13581977234    时间: 2018-6-15 11:21
神,就是神
作者: atonwang    时间: 2018-6-15 15:29
大开眼界.......
作者: atonwang    时间: 2018-6-15 15:30

这个要收藏了!厉害啊!
作者: through2006    时间: 2018-6-15 16:53
历害,大神!
作者: ygsh168    时间: 2018-6-15 21:44
这是把Excel当成的编程软件来玩了,太厉害了
作者: 月之饺子    时间: 2018-6-15 23:03
取消工作表保护的密码是什么?初级菜鸟表示看不懂
作者: cbq666666    时间: 2018-6-16 08:55
很好很强大,美中不足的是,好像赛程日期与实际不符,应该是组委会调整了赛程,模板里面没有来得及更新吧,要是能更新一下,就更完美了
作者: 修身养性大小    时间: 2018-6-16 13:42
楼主,此表功能太强大了,谢谢分享!但务必尽快修改为盼:第一“东道主是俄罗斯你错误录为了俄国”;第二“赛程从第3场开始就完全错误了--比赛球队、比赛时间均出错”。企盼你能在原表的基础上加以纠正,再发出来。谢谢!
作者: ray_che    时间: 2018-6-16 14:30
厉害了,我等膜拜
作者: zijun6143    时间: 2018-6-16 16:33
大神作品,太棒了
作者: 骑着毛驴逛逛    时间: 2018-6-16 21:14
膜拜大神,神作
作者: micch    时间: 2018-6-17 12:07
嗯,在我眼里Excel是数据,别人眼里Excel是不一样的,可以是美术
作者: 消逝的骑兵MK2    时间: 2018-6-17 17:45
本彩笔大开眼界
作者: zhang3618    时间: 2018-6-18 01:55
细节看差距
作者: zhang3618    时间: 2018-6-18 03:12
细节决定成败,真用心!
作者: zqxhuaxin    时间: 2018-6-20 09:24
优秀作品,但是比赛时间有很多错误
作者: 健影    时间: 2018-6-20 09:31
本帖最后由 健影 于 2018-6-20 09:35 编辑

果断收藏了,但是下载不了
作者: xiaobing1226    时间: 2018-6-20 10:16
确实太强大了
作者: CNGDHYJ    时间: 2018-6-20 12:17
奇怪,这还有咱们的官方微信号二维码供扫描,这外国小哥可真是咱们的铁杆朋友,马上下来学习学习。谢楼主!
作者: cam333    时间: 2018-6-20 16:05
下载地址在那里???
作者: Nick_Chen0622    时间: 2018-6-20 21:03
版主大大,还是想解开密码自己研究,能告知密码吗
作者: ws2013    时间: 2018-6-21 11:00
高手啊,努力学习。
作者: zengwei1986    时间: 2018-6-21 13:52
简直太厉害了
作者: zhang325632    时间: 2018-6-21 15:51
佩服 佩服,大开眼界
作者: myboy888    时间: 2018-6-22 17:00
厉害了,不关注世界杯也得关注这个。函数公式实用示例
作者: al32456    时间: 2018-6-23 08:11
谢谢分享,下载看看
作者: HBABCD    时间: 2018-6-23 10:11
真是太厉害了。
作者: lzc135    时间: 2018-6-23 10:41
没办法下载了

作者: wzh72636    时间: 2018-6-23 15:49
牛人真的很多
作者: woodhead03    时间: 2018-6-23 21:42
太强大了!!
作者: (_灬回憶ゞ雨泽    时间: 2018-6-23 23:01
这一看就是人才
作者: yiran7810    时间: 2018-6-24 08:55
谢谢分享!学习了!
作者: 15061948193    时间: 2018-6-24 14:57
看着真是神了
作者: Renzo163    时间: 2018-6-24 17:10
膜拜,大神
作者: 1255740219    时间: 2018-6-25 11:16
AAABABAABBBN
作者: hacker85    时间: 2018-6-25 15:08
真牛BB    这样的球迷是值得肯定的   球迷界的E高手
作者: youyoukaka    时间: 2018-6-26 15:59
真是大开眼界!
作者: Amarry    时间: 2018-6-27 09:00
这个厉害了,要慢慢研究
作者: 娇云之上    时间: 2018-6-27 09:10
真牛逼啊我们不会的真是大开眼界
作者: -w_w-    时间: 2018-6-27 09:27
路漫漫兮其修远,我好努力学习excel
作者: zljanice    时间: 2018-6-27 13:56
学习了,真的是不错
作者: ruixizhao    时间: 2018-6-28 09:27
我打不开下载页面,看来要回家再看了
作者: summer.linn    时间: 2018-6-28 10:31
大开眼界!
作者: 大川川    时间: 2018-6-29 09:15
不得不佩服
作者: CNGDHYJ    时间: 2018-6-29 09:45
本届H组日本队与塞内加尔同得4分,日本队依靠黄牌数少幸运晋级16强,该如何将表中日本队改为小组第二呢?
作者: pagedown_up    时间: 2018-6-29 12:04
是的,日本和塞内加尔如何判断谁是小组第二啊?!为什么自动判断塞内加尔为第二呢?表格中判断的依据是怎样的?

作者: kdtf    时间: 2018-6-29 14:59
太厲害了!!
作者: szwlp    时间: 2018-6-29 15:00
H组未考虑红黄牌,应该日本晋级
作者: 花的风景    时间: 2018-6-29 16:06
真大开眼界!
作者: WYLLZQ    时间: 2018-6-29 19:29
这个不好判断了,塞内加尔和日本,最后以黄牌数量,日本晋级,可是显示的是塞内加尔晋级,想改都没有办法,求解?
作者: 于箱长    时间: 2018-6-29 20:46
WYLLZQ 发表于 2018-6-29 19:29
这个不好判断了,塞内加尔和日本,最后以黄牌数量,日本晋级,可是显示的是塞内加尔晋级,想改都没有办法, ...

给塞内减0.001分,就改过来了啊
作者: WYLLZQ    时间: 2018-6-29 22:39
于箱长 发表于 2018-6-29 20:46
给塞内减0.001分,就改过来了啊

波兰:日本,1:0.001
作者: 于箱长    时间: 2018-6-30 08:30
WYLLZQ 发表于 2018-6-29 22:39
波兰:日本,1:0.001

结果是一样一样地
作者: CNGDHYJ    时间: 2018-6-30 08:55
本帖最后由 CNGDHYJ 于 2018-6-30 09:07 编辑

原先是根据“日本和塞内加尔”比赛结果来定,只有将24日“日本和塞内加尔”的比赛结果稍微改一下,将日本队+0.001改为“2.001”,即日本队就会小组第二了。
作者: 于箱长    时间: 2018-6-30 09:08
下载地址里还有一个2014年的表
作者: Lowchi    时间: 2018-6-30 16:20
真是大开眼界。。。
作者: 于箱长    时间: 2018-6-30 18:40
1255740219 发表于 2018-6-25 11:16
AAABABAABBBN

你真厉害呀
作者: winnieliao    时间: 2018-7-1 07:39
很好很强大,也许以后用得着
作者: 阿岚    时间: 2018-7-1 10:08
真是大开眼界啊!牛!
作者: 1079293309    时间: 2018-7-2 13:12
H组日本和塞内加尔的比分逻辑算出来是错的




欢迎光临 ExcelHome技术论坛 (https://club.excelhome.net/) Powered by Discuz! X3.4