在之前的文章《實用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的情況。）
在足球比賽中，數字都比較?。ㄒ话愣疾粷M10），如果要計算的數據量級比較大的話，僅僅差10倍是不夠的，經常會遇到相差100倍或者10000倍的情況。在這種這種情形下，公式里就會有很多個0，此時可以變個思路，將擴大改為縮小，%的作用就是將數據縮小100倍，%%的作用就是將數據縮小10000倍，這就是=AD5+AC5%+AA5%%的由來。
經過了這么多步驟，終于到了計算最終排名的時候，一起來看看排名公式吧。
6、排名的計算
公式非常簡單，就是rank函數的標準用法：=RANK(AF5,$AF$5:$AF$8)
再來復習一下rank函數的含義：=rank（要計算名次的數據，參與排名的區域，升序或降序），其中第三參數省略時按照降序排名，得分最高者為第一名。
最后隱藏所有的輔助列，一個成績統計表就完成了：
四、小結
大家可能會有個感覺，設計一個這樣的模板真的好麻煩，用這么多的輔助列，而且有些地方的公式還不能下拉，要一個一個填寫，有沒有不用輔助列可以直接下拉的方法呢？
肯定是有的，不過使用起來未必方便，那會用到大量的數組公式和復雜的函數。當然就這個表格來說，肯定還有繼續優化的可能。老菜鳥曾經花了一周時間改進一個計算銷售排名的報表模板，過程中反復的測試，修改。但是用戶的體驗非常好，因為只需要填寫基礎數據，就可以看到最想要的結果，中間的計算過程都是輔助列（單獨存放在一個sheet里的），都用的是比較簡單的函數，即便是有需要修改的時候，用戶也能自己解決。
對于報表模板來說，設計時間的長短不是衡量好壞的標準，而是使用的方便程度和維護的難易度。希望大家能夠從今天這個例子領悟到一些設計模板的思路，可以讓自己的工作變得更加高效，讓自己的表格變得更加智能。
相關學習推薦：實用excel技巧分享：兩個為表格添加背景圖的方法
