Excel教程下載和軟件下載中心,Microsoft技術社區聯盟成員,全球極具影響力的Excel門戶,Office技術培訓社區

世界杯將近,外國小哥贈我一份“嚇死人”的Excel模板

2018-06-13  作者:ExcelHome  閱讀:
1.
俄羅斯世界杯即將火熱來襲,大洋彼岸的外國小哥贈給我們一份Excel模版,還附送了一句話:
中國足球隊世界杯加油啊!!
我呸——這家伙大概不曉得老毛子那塊地兒,俺們除了足球隊沒去,其它都去了,還加油個毛線啊,再說了油價可剛漲沒多久啊……
外國小哥表格制作的相當精美,不禁使我再次感慨,只要想象力足夠,Excel就是一款沒有上限的神器
不信?先給在座的諸位抖個Splash封面瞧瞧。


從長相上,真沒看出來外國小哥是個貼心的人吶,居然知道俺看不懂洋文,在封面的右上角設置了語言下拉框,我默默的選擇了中文……


鼠標點擊封面圖,Excel自動跳轉進入另外一張工作表。
然后……我需要冷靜一下……你說大家都是玩Excel的,為啥外國小哥能把Excel養成了女兒,打扮的不像話!而我卻只能把Excel慣成混小子,打拌的不像話呢?男女娃娃之間的差距真的就這么大嗎?
看——外國小哥的表是不是打扮的好看的不像話:


更關鍵的是,人家不只是看起來很秀,內蘊也很秀,各種功能相當有料有趣啊。
上面一排高大上的導航圖,每個圖形內貼心的顯示了國家名,還各自指向A~H八個小組的工作表,點擊圖形,就可以自動跳轉到相關工作表。


有意思吧……
導航圖下面的區域,左側是比賽的時間和城市;中間是各支球隊的比分;右側是小組數據統計。
我本著從滅霸同志那里學習來的絕對公平公正原則,給每場比賽都輸入了1:0的比分……
然后………


小組數據統計表很快計算出了各支球隊場次、勝、平、負的次數;還有進球數、失球數、凈勝球數以及積分……
更有趣的是,統計表的國旗和國名是按名次進行自動排列的。
BUT……
What Are You弄啥嘞?不是絕對公平的1:0嗎?為什么積分不一……哦哦哦~這句話當我沒說,俺承認是偽球迷你怎么著吧……
……
當小組所有的比賽塵埃落定后,表格下方區域,還會自動計算出小組第一名和第二名的國名和國旗。


有趣吧?
……我動動鼠標,溜進淘汰賽表格,發現小組賽出線的國家,名字已經自動寫入相應的單元格。
此時,一股來自宇宙深處的神秘力量,促使我支配波斯鐵騎干掉了北極熊………


更有趣的事,我們可以很認真的告訴在坐的諸位熊迪姐妹們,這些功能……你們……幾乎……都可以……自己……來實現!
所有的功能均未涉及VBA編程,都是通過Excel函數完成的,而且大都是最基礎的那部分函數哦,比如VLOOKUP、INDEX、SUMIF……。

2.
EH電視臺,EH電視臺,現在插播一條重要新聞,下載本文模版請點擊:https://www.spreadsheet1.com/fifa-world-cup-2018-russia-excel-download.html
友情提示,下載請選擇中文版。


3.
打個響指,咱們一起聊聊模版里的幾個主要功能是如何實現的?
看我眼神,再次給大家港,模版里涉及到的所有技巧都是我們ExcelHome公眾號過去分享過的。關注不會迷路,如果你還沒關注我們——自個看著辦吧。

3.1語言切換。
模版的封面處有一個【數據驗證】制作的語言選擇下拉框,看起來很拉風,但實現起來其實很簡單。

首先建立一張參數表,提供各個國家名字的各種語言版本,然后使用VLOOKUP等函數根據所選語種查詢引用即可(模版里隱藏起來的參數表名為Games)。
攤手,忒社會,沒想到VLOOKUP還可以這么用?
當然,如果你想來個更高大上的,還可以使用網絡函數,通過有道翻譯實現各語種自動切換。
參考我們過去的教程:【Excel數據獲取】你會用函數實現網頁數據抓取嗎?

3.2導航圖
相當炫酷的導航圖是有多個圖形組合而成的。通過菜單【插入】→【形狀】,保持耐心,選擇合適的圖形并填充顏色組合即可。

右鍵點擊組合完成的圖形,在快捷菜單中選擇【鏈接】。在彈出的【超鏈接對話框】中,設置目標單元格引用地址。搞定。



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爬取各隊球員的數據,用透視表或函數分析球員的屬性(俱樂部、位置、身價)以及各種圖表可視化等等……

轉載請注明來源"ExcelHome"并保留原文鏈接。
固定鏈接:http://www.fvtgnn.tw/lesson/animation/excel/1902.html
分享到:
Copyright 1999 - 2017 Excel Home.All Rights Reserved.
本站特聘法律顧問:徐懷玉律師 李志群律師   滬ICP備11019229號

滬公網安備 31011702000001號

征信       
35选7摇奖器