利用Excel制作競賽評分自動計算系統(tǒng)

1、利用Excel制作競賽評分自動計算系統(tǒng)摘要：利用Excel函數(shù)，函數(shù)數(shù)組設(shè)計一個能自動統(tǒng)計、自動排名、自動顯示獲獎名單的小程序。適合各種競賽活動的當(dāng)眾打分、評分，效率高。無論學(xué)校還是企事業(yè)單位，都會有不同規(guī)模的比賽。為了使比賽成績更公平、公開，都要當(dāng)眾打分、評分，為了提高效率，可編制一些小程序來完成計算過程。這些小程序可以用專門的編程語言來寫，但專業(yè)性太強。筆者采用人們最常用的Excel編寫了評委打分自動處理系統(tǒng)，易學(xué)好用，靈活性強，自動化程度較高。此系統(tǒng)共有三個組成部分：初賽區(qū)，決賽區(qū)，名次表。為了方便，我們假設(shè)有15個選手比賽，初賽后選出10個進(jìn)行決賽，最后勝出6個（根據(jù)需要可自行修改）。

2、下面是詳細(xì)的編制過程。新建空白Excel工作簿，將自動建立的Sheet1、Sheet2和Sheet3工作表分別重命名為初賽區(qū)、決賽區(qū)和名次表（如圖1）。初賽區(qū)：主要功能是針對評委給出的分?jǐn)?shù)，去掉兩個最高分，兩個最低分，根據(jù)最后得分排出名次。圖1根據(jù)上表，依次輸入評委（1-10）和選手（1-15）的姓名，最終得分、排名。評委1-評委10共10列中填入評委給每個選手的分?jǐn)?shù)。在打分的過程中，最終得分和排名自動計算得出。最高分：在L3單元格中輸入公式“=MAX（B3:K3）"。然后選中L3單元格，向下拖動該單元格右下角的小“十”字圖標(biāo)至最后一行（這種操作稱為自動填充）。最低分：在M3單元格中

3、輸入公式“=MIN（B3:K3）"。然后選中M3單元格，按上面的方法自動填充。最終得分:在N3單元格中輸入“=TRIMMEANB3:K3,0.2）”。然后選中N3單元格，向下自動填充。該函數(shù)的功能是求平均值，在這里主要是指在B3：K3區(qū)域內(nèi)，去掉兩個數(shù)據(jù)后的平均值。公式中的“0.2”表示10個數(shù)據(jù)中去掉2個（10X0.2）,即一個最高分和一個最低分。如果改為“0.4”，表示10個數(shù)據(jù)中去掉4個（10X0.4）,即兩個最高分和兩個最低分。排名：在O3單元格中輸入”=RANK（N3,N$3:N$17）”。再按上述方法向下自動填充。根據(jù)最終得分，按從大到小順序排名。如果有并列的名次，則下一

4、個名次將按并列數(shù)量順延，如：有兩個并列第2名，則沒有第3名，而是排到第4名。在許多情況下，這種排列方式不符合中國人的日常排名習(xí)慣。在中國人的習(xí)慣中，無論有幾個并列第2名，之后的排名仍應(yīng)該是第3名，即并列排名不占用名次。按中國式排名則需將上面公式改為：“=SUMPRODU（CTN$3：N$17>N3）*（1/（COUNTIF（N$3：N$17，N$3：N$17）+1”決賽區(qū)：根據(jù)初賽成績，本系統(tǒng)自動從15個選手中選出前10名參加決賽。名單自動顯示在決賽區(qū)的第一列。其它列同初賽區(qū)。為了獲得決賽區(qū)名單，需做兩個輔助列。在工作表“初賽區(qū)”中，在Q2單元格中輸入字段“拼合”，在R2單元格中

5、輸入字段“按大小排序”。在Q3單元格中輸入”=INT（CONCATENATENT（N3*100）,200-ROW（A1）"。公式中ROWA1）為A1單元格所在的行數(shù)即為1（該公式下拉時依次為2、3、4），用200來減是為了CONCATENATED中的第2個參數(shù)保持3位數(shù)，CONCATENATE函數(shù)是一個拼合函數(shù)，這里把N列的數(shù)據(jù)和它所在的行數(shù)拼合成一個數(shù)據(jù)。這樣在對它進(jìn)行排序后該數(shù)據(jù)包含了它所在行數(shù)的信息。CONCATENATED和INT函數(shù)套用是為了把原來的文本變?yōu)閿?shù)字。在R3單元格中輸入”=LARGE（Q:QROWA1）”,即對Q列數(shù)值（包含所在行的信息）按大小排列。在工作表“決賽區(qū)”中，A3單元格中輸入“=INDEX（初賽區(qū)！$A$3:初賽區(qū)！$A$17,200-RIGHT（初賽區(qū)！R3,3）,COLUMN（初賽區(qū)！A3）”，即獲初賽區(qū)中前10名的名單。INDEX函數(shù)為引用函數(shù)，即根據(jù)“200-RIGHT（初賽區(qū)！R3,3）”標(biāo)明的行數(shù)，在“初賽區(qū)”工作表中$A$3:$A$17單元格矩陣中引用姓名。名次表：根據(jù)決賽成績，前6名名單自動生成。其中，一等獎1名，二等獎2名，三等獎3名。在B2單元格中輸入“=INDEX（決賽區(qū)！$A$3:決賽區(qū)！A12,200-RIG