實(shí)用Excel技巧分享：學(xué)習(xí)怎么做多因素排名統(tǒng)計(jì)表？（excel多項(xiàng)綜合排名怎么做）

手機(jī)如何做表格：點(diǎn)擊查看

四年一度的世界杯激戰(zhàn)正酣，每一名球迷都為自己喜歡的球隊(duì)加油助威，更加關(guān)心這支球隊(duì)所在小組的戰(zhàn)況。隨著第一輪比賽的結(jié)束，小組賽達(dá)到一個(gè)高潮，同組隊(duì)伍的每一個(gè)進(jìn)球都可以影響全局的結(jié)果。雖說現(xiàn)在的媒體信息很發(fā)達(dá)了，時(shí)事新聞，發(fā)展預(yù)測(cè)隨處可見，不過如果能夠有一個(gè)自己輸入數(shù)據(jù)后就能計(jì)算、預(yù)測(cè)出小組排名結(jié)果的電子表格，無(wú)疑是非常方便的，今天就和大家分享一個(gè)這樣的比賽成績(jī)統(tǒng)計(jì)表。

表格的最終效果如下圖所示：

功能：只要在黃色區(qū)域中輸入比賽結(jié)果，就會(huì)自動(dòng)計(jì)算出各項(xiàng)指標(biāo)以及最終排名。

價(jià)值：

①戰(zhàn)況數(shù)據(jù)實(shí)時(shí)統(tǒng)計(jì)

②預(yù)測(cè)排名

當(dāng)然，更加重要的意義在于：通過這個(gè)表格的設(shè)計(jì)思路學(xué)到很多細(xì)節(jié)的知識(shí)點(diǎn)，如果自己工作中遇到計(jì)算綜合排名的問題都可以借鑒。下面就來(lái)看看具體的制作過程。

因?yàn)楦餍〗M的結(jié)構(gòu)完全一致，我們僅以A組數(shù)據(jù)為例進(jìn)行說明。整個(gè)表格的制作分為三個(gè)個(gè)階段：確定整體思路、排版設(shè)計(jì)和公式設(shè)計(jì)。

一、確定整體思路

在進(jìn)行表格設(shè)計(jì)之前，要對(duì)相關(guān)的規(guī)則和需求進(jìn)行整理，明確可以有哪些基礎(chǔ)數(shù)據(jù)，最終需要得到什么結(jié)果，然后再去設(shè)計(jì)中間的環(huán)節(jié)。

在這個(gè)成績(jī)統(tǒng)計(jì)表中，我們的基礎(chǔ)數(shù)據(jù)就是每場(chǎng)結(jié)束后的兩個(gè)數(shù)字（雙方的進(jìn)球數(shù)）；最終需要的結(jié)果就是小組排名。

如何根據(jù)每場(chǎng)的結(jié)果得到排名，還需要對(duì)排名規(guī)則進(jìn)行了解：

上圖中是官方的解釋，算是一個(gè)比較復(fù)雜的計(jì)算規(guī)則了，對(duì)于這個(gè)規(guī)則我們可以歸納為以下幾點(diǎn)：

①根據(jù)每場(chǎng)比賽需要判斷各隊(duì)的勝負(fù)情況，勝記3分，平記1分，負(fù)記0分；

②積分相同時(shí)按凈勝球多少排名（凈勝球就是進(jìn)球數(shù)-失球數(shù)）；

③凈勝球還相同時(shí)按進(jìn)球數(shù)多少排名。

④對(duì)于進(jìn)球數(shù)還相同的情況，要按比賽的犯規(guī)情況再去判斷。

因?yàn)槲覀兊幕A(chǔ)數(shù)據(jù)僅僅是雙方每場(chǎng)的進(jìn)球數(shù)，所以第1~3點(diǎn)可以實(shí)現(xiàn)，而第4點(diǎn)無(wú)法實(shí)現(xiàn)。

接下來(lái)就需要根據(jù)這些信息去設(shè)計(jì)表格。

二、排版設(shè)計(jì)

1、數(shù)字單獨(dú)存放

排版設(shè)計(jì)要掌握的第一個(gè)原則是一個(gè)單元格只存放一種類型的數(shù)據(jù)。例如5：2這個(gè)比分，如果放在一個(gè)單元格，在后期進(jìn)行統(tǒng)計(jì)分析時(shí)就需要把兩個(gè)數(shù)字分別提取出來(lái)再進(jìn)行計(jì)算，非常不方便。因此，在設(shè)計(jì)表格的時(shí)候需要重點(diǎn)考慮這個(gè)問題。下面來(lái)看看是如何處理的：

為了便于大家理解，我們將單元格邊框顯示出來(lái)，可以清楚地看到，實(shí)際上每個(gè)得分都是用了三個(gè)單元格，將兩隊(duì)的成績(jī)與冒號(hào)分開存放，中間斜線的四個(gè)單元格，以及第四行中球隊(duì)名稱都是使用了合并單元格進(jìn)行處理。

通過合理地使用合并單元格，既滿足了將數(shù)字單獨(dú)存放的要求，又符合視覺需求，很直觀地就能看到比賽結(jié)果。

2、合理利用輔助列

排版設(shè)計(jì)的第二個(gè)原則就是要簡(jiǎn)化輸入步驟以及簡(jiǎn)化計(jì)算過程。這點(diǎn)可以借助輔助列和公式來(lái)實(shí)現(xiàn)，例如在這個(gè)表格中，需要填寫斜線下方黃色區(qū)域的比分，斜線上方的比分是用公式進(jìn)行引用的：

為了統(tǒng)計(jì)每隊(duì)的勝場(chǎng)、平場(chǎng)和負(fù)場(chǎng)數(shù)，用到了一些輔助列：

關(guān)于輔助列，有些朋友可能覺得過于麻煩，總是希望直接用公式得到結(jié)果，其實(shí)這是一個(gè)誤解。在一些邏輯關(guān)系比較復(fù)雜或者計(jì)算步驟較多的模板表格中，善于利用輔助列可以簡(jiǎn)化公式的難度，降低運(yùn)算量，同時(shí)便于使用過程中對(duì)表格的維護(hù)，因?yàn)橛?jì)算過程都是通過輔助列實(shí)現(xiàn)的，如果需要調(diào)整某一處的計(jì)算方法，只需要改對(duì)應(yīng)的地方即可。

以上是對(duì)這個(gè)成績(jī)統(tǒng)計(jì)表設(shè)計(jì)的說明，看起來(lái)很多，其實(shí)都不難，接下來(lái)就看看這個(gè)表格里都用了哪些公式和函數(shù)。

三、公式設(shè)計(jì)

1、勝場(chǎng)的判斷：=IF(F5>H5,1,0)

利用IF函數(shù)直接比較F5和H5，當(dāng)F5大于H5為勝，得到1，反之得到0。

勝場(chǎng)次數(shù)計(jì)算：=SUM(O5:Q5)

直接對(duì)前面的數(shù)據(jù)求和。

2、平場(chǎng)的判斷：=IF(AND(F5=H5,F5<>" "),1,0)

與勝場(chǎng)有點(diǎn)區(qū)別，平場(chǎng)判斷時(shí)還需要加一個(gè)條件，也就是進(jìn)球數(shù)不為空的時(shí)候才去比較，因此加了AND函數(shù)去做判斷。同時(shí)滿足進(jìn)球數(shù)相等并且不為空這兩個(gè)條件時(shí)才算打平，記為1，反之為0。

平場(chǎng)次數(shù)計(jì)算：

同樣是直接求和。

3、負(fù)場(chǎng)的判斷：=IF(F5

與勝場(chǎng)的判斷方法一樣，只是公式中把大于符號(hào)改成小于符號(hào)。

負(fù)場(chǎng)次數(shù)計(jì)算：

直接求和即可。

以上這部分內(nèi)容是將每場(chǎng)比賽結(jié)果變成具體的數(shù)字，看起來(lái)有點(diǎn)麻煩，但是沒什么難度。接下來(lái)將輔助列隱藏，僅顯示勝場(chǎng)、平場(chǎng)和負(fù)場(chǎng)的統(tǒng)計(jì)次數(shù)即可。

4、進(jìn)球數(shù)、失球數(shù)和凈勝球數(shù)的統(tǒng)計(jì)：

進(jìn)球數(shù)：=SUM(F5,I5,L5)

失球數(shù)：=SUM(H5,K5,N5)

凈勝球數(shù)：=AA5-AB5

這部分公式非常簡(jiǎn)單，都是加減運(yùn)算。

5、積分的計(jì)算

按照規(guī)則，勝場(chǎng)記3分，平場(chǎng)記1分，負(fù)場(chǎng)記0分，積分的公式就是：=R5*3+V5*1

至此，計(jì)算排名的各項(xiàng)指標(biāo)我們都算出來(lái)了，分別是：積分、凈勝球和進(jìn)球數(shù)。

在根據(jù)這三項(xiàng)指標(biāo)計(jì)算排名之前，還需要做一個(gè)過渡，將三項(xiàng)指標(biāo)根據(jù)各自的優(yōu)先級(jí)（權(quán)重）進(jìn)行量化，變成可以比較的數(shù)字，可以在AF列使用這樣的一個(gè)公式來(lái)實(shí)現(xiàn)：=AD5+AC5%+AA5%%，結(jié)果如下。

是不是感覺這個(gè)公式挺奇怪的，百分號(hào)是什么意思呢？

我們重點(diǎn)來(lái)解釋一下這個(gè)公式，這里用到三個(gè)單元格：AD5（積分）、AC5（凈勝球）和AA5（進(jìn)球數(shù)）

在排名規(guī)則中明確說到，先根據(jù)積分，積分相同時(shí)判斷凈勝球數(shù)，再相同才判斷進(jìn)球數(shù)。那么如何將這種按順序比較多數(shù)據(jù)轉(zhuǎn)化為只比較一個(gè)數(shù)據(jù)呢？可以參考利用權(quán)重計(jì)算多因素排名的方法。例如指標(biāo)A權(quán)重是50%，指標(biāo)B權(quán)重30%，指標(biāo)C權(quán)重20%，那么綜合得分就是A*0.5+B*0.3+C*0.2，如此雖然有A、B、C三個(gè)排名因素，但只需要比較綜合得分即可獲得排名。

這時(shí)這就需要人為的指定權(quán)重，將需要先后比較的數(shù)據(jù)轉(zhuǎn)化為一個(gè)數(shù)據(jù)。

公式=AD5+AC5%+AA5%%原本的寫法應(yīng)該是：=AD5*100+AC5*10+AA5，在AG列輸入公式，結(jié)果如下。

也就是將每個(gè)指標(biāo)按照優(yōu)先級(jí)分別擴(kuò)大100倍、10倍、1倍后相加，使每個(gè)指標(biāo)不會(huì)在同一個(gè)數(shù)位上，而是分別位于百位、十位和個(gè)位，如此既保證了各指標(biāo)的優(yōu)先等級(jí)，又將多個(gè)指標(biāo)化成了一個(gè)數(shù)據(jù)。（注：當(dāng)前倍數(shù)設(shè)置只適合積分、凈勝球、進(jìn)球數(shù)據(jù)都是小于10的情況。）

在足球比賽中，數(shù)字都比較小（一般都不滿10），如果要計(jì)算的數(shù)據(jù)量級(jí)比較大的話，僅僅差10倍是不夠的，經(jīng)常會(huì)遇到相差100倍或者10000倍的情況。在這種這種情形下，公式里就會(huì)有很多個(gè)0，此時(shí)可以變個(gè)思路，將擴(kuò)大改為縮小，%的作用就是將數(shù)據(jù)縮小100倍，%%的作用就是將數(shù)據(jù)縮小10000倍，這就是=AD5+AC5%+AA5%%的由來(lái)。

經(jīng)過了這么多步驟，終于到了計(jì)算最終排名的時(shí)候，一起來(lái)看看排名公式吧。

6、排名的計(jì)算

公式非常簡(jiǎn)單，就是rank函數(shù)的標(biāo)準(zhǔn)用法：=RANK(AF5,$AF$5:$AF$8)

再來(lái)復(fù)習(xí)一下rank函數(shù)的含義：=rank（要計(jì)算名次的數(shù)據(jù)，參與排名的區(qū)域，升序或降序），其中第三參數(shù)省略時(shí)按照降序排名，得分最高者為第一名。

最后隱藏所有的輔助列，一個(gè)成績(jī)統(tǒng)計(jì)表就完成了：

四、小結(jié)

大家可能會(huì)有個(gè)感覺，設(shè)計(jì)一個(gè)這樣的模板真的好麻煩，用這么多的輔助列，而且有些地方的公式還不能下拉，要一個(gè)一個(gè)填寫，有沒有不用輔助列可以直接下拉的方法呢？

肯定是有的，不過使用起來(lái)未必方便，那會(huì)用到大量的數(shù)組公式和復(fù)雜的函數(shù)。當(dāng)然就這個(gè)表格來(lái)說，肯定還有繼續(xù)優(yōu)化的可能。老菜鳥曾經(jīng)花了一周時(shí)間改進(jìn)一個(gè)計(jì)算銷售排名的報(bào)表模板，過程中反復(fù)的測(cè)試，修改。但是用戶的體驗(yàn)非常好，因?yàn)橹恍枰顚懟A(chǔ)數(shù)據(jù)，就可以看到最想要的結(jié)果，中間的計(jì)算過程都是輔助列（單獨(dú)存放在一個(gè)sheet里的），都用的是比較簡(jiǎn)單的函數(shù)，即便是有需要修改的時(shí)候，用戶也能自己解決。

對(duì)于報(bào)表模板來(lái)說，設(shè)計(jì)時(shí)間的長(zhǎng)短不是衡量好壞的標(biāo)準(zhǔn)，而是使用的方便程度和維護(hù)的難易度。希望大家能夠從今天這個(gè)例子領(lǐng)悟到一些設(shè)計(jì)模板的思路，可以讓自己的工作變得更加高效，讓自己的表格變得更加智能。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。