實用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倍，%%的作用就是將資料縮小10,000倍，這就是=AD5 AC5% AA5%%的由來。

經過了這麼多步驟，終於到了計算最終排名的時候，一起來看看排名公式吧。

6、排名的計算

公式非常簡單，就是rank函數的標準用法：=RANK(AF5,$AF$5:$AF$8)

再來複習一下rank函數的意義：=rank（要計算名次的數據，參與排名的區域，升序或降序） ，其中第三參數省略時依降序排名，得分最高者為第一名。

最後隱藏所有的輔助列，一個成績統計表就完成了：

#、小結

######### #######大家可能會有個感覺，設計一個這樣的模板真的好麻煩，用這麼多的輔助列，而且有些地方的公式還不能下拉，要一個一個填寫，有沒有不用輔助列可以直接下拉的方法呢？ ######肯定是有的，不過使用起來未必方便，那會用到大量的陣列公式和複雜的函數。當然就這個表格來說，肯定還有繼續優化的可能。老菜鳥曾經花了一周時間改進一個計算銷售排名的報表模板，過程中反覆的測試，修改。但是使用者的體驗非常好，因為只需要填寫基礎數據，就可以看到最想要的結果，中間的計算過程都是輔助列（單獨存放在一個sheet裡的），都用的是比較簡單的函數，即便是需要修改的時候，使用者也能自己解決。 ######對於報表範本來說，設計時間的長短不是衡量好壞的標準，而是使用的方便程度和維護的難易度。希望大家能夠從今天這個例子領悟到一些設計模板的思路，可以讓自己的工作變得更有效率，讓自己的表格變得更聰明。 ######相關學習推薦：###excel教學######

以上是實用Excel技巧分享：學習怎麼做多因素排名統計表？的詳細內容。更多資訊請關注PHP中文網其他相關文章！