基于EXCEL的體育運動會秩序編排與成績統(tǒng)計模型設(shè)計

摘要：作為一名從事體育教學的教師，每年學校都要舉辦運動會，在以往都是通過手工分組匯總的方法進行運動會秩序編排與成績統(tǒng)計，這是一項基本的、繁雜的工作。為了提高成績統(tǒng)計的準確性以及減輕教師的工作負擔，本文介紹了如何使用微軟的EXCEL電子表格來進行體育運動會秩序編排與成績統(tǒng)計模型設(shè)計，以供教師借鑒。

關(guān)鍵詞：EXCEL；體育運動會；秩序編排；成績；模型設(shè)計

⒈相關(guān)函數(shù)介紹

⑴VLOOKUP（）函數(shù)

VLOOKUP（）函數(shù)是重要的查找函數(shù)之一，其含義是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組中該數(shù)值所在行中指定列處的數(shù)值。

函數(shù)語法：VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

lookup_value：為需要在數(shù)組第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字符串。

table_array：數(shù)組所在的區(qū)域，如“B2：E10”，也可以使用對區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫或數(shù)據(jù)清單。

col_index_num：即希望區(qū)域（數(shù)組）中待返回的匹配值的列序號，為1時，返回第一列中的數(shù)值，為2時，返回第二列中的數(shù)值，以此類推；若列序號小于1，函數(shù)VLOOKUP 返回錯誤值 #VALUE?。蝗绻笥趨^(qū)域的列數(shù)，函數(shù)VLOOKUP返回錯誤值 #REF！。 “

range_lookup：為TRUE或FALSE。它指明函數(shù) VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值”的最大數(shù)值；如果“邏輯值”為FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。如果“查找值”為文本時，“邏輯值”一般應(yīng)為 FALSE 。另外： ·如果“查找值”小于“區(qū)域”第一列中的最小數(shù)值，函數(shù) VLOOKUP 返回錯誤值 #N/A。 ·如果函數(shù) VLOOKUP 找不到“查找值” 且“邏輯值”為 FALSE，函數(shù) VLOOKUP 返回錯誤值 #N/A。

函數(shù)說明：

1.VLOOKUP（） 函數(shù)中括號里有四個參數(shù)，都是必需的。最后一個參數(shù)range_lookup是個邏輯值，我們常常輸入一個0字，或者False；其實也可以輸入一個1字，或者true。

兩者的區(qū)別是前者表示的是完整尋找，找不到就傳回錯誤值#N/A；后者先是找一模一樣的，找不到再去找很接近的值，還找不到也只好傳回錯誤值#N/A。

2.Lookup_value是一個很重要的參數(shù)，它可以是數(shù)值、文字字符串、或參照地址。我們常常用的是參照地址。用這個參數(shù)時，要特別注意： A）參照地址的單元格格式類別與去搜尋的單元格格式的類別要一致； B）在使用參照地址時，有時需要將lookup_value的值固定在一個格子內(nèi)，而又要使用下拉方式（或復(fù)制）將函數(shù)添加到新的單元格中去，這里就用到“$”這個符號所代表的絕對引用。

3.Table_array是搜尋的范圍，col_index_num是范圍內(nèi)的欄數(shù)。Col_index_num 不能小于1，如果出現(xiàn)一個這樣的錯誤的值#REF！，則可能是col_index_num的值超過范圍的總字段數(shù)。

⑵ MID（）函數(shù)

MID（）函數(shù)是字符串函數(shù)。其含義是從字符串中返回指定數(shù)目的字符。

函數(shù)語法：MID（text，start_num，num_chars）

Text：字符串表達式，從中返回字符。如果 text 包含 Null，則返回 Null。

start_num：text 中被提取的字符部分的開始位置。如果 start 超過了 text 中字符的數(shù)目，Mid 將返回零長度字符串 （\"\"）。

num_chars：要返回的字符數(shù)。如果省略或num_chars 超過或等于文本的字符數(shù)（包括 start 處的字符），將返回字符串中從 start_num到字符串結(jié)束的所有字符。

⑶ IF（）函數(shù)

IF（）函數(shù)是較為常用的邏輯函數(shù)之一，它執(zhí)行真假值判斷，根據(jù)邏輯計算的真假值，返回不同結(jié)果?？梢允褂煤瘮?shù)IF對數(shù)值和公式進行條件檢測。

函數(shù)語法：

IF（logical_test，value_if_true，value_if_1）

Logical_test 表示計算結(jié)果為TRUE或FALSE的任意值或表達式。例如，A10=100就是一個邏輯表達式，如果單元格A10中的值等于100，表達式即為TRUE，否則為FALSE。本參數(shù)可使用任何比較運算符。

Value_if_true logical_test 為TRUE 時返回的值。例如，如果本參數(shù)為文本字符串“預(yù)算內(nèi)”而且logical_test參數(shù)值為TRUE，則IF函數(shù)將顯示文本“預(yù)算內(nèi)”。如果 logical_test 為TRUE而value_if_true為空，則本參數(shù)返回0（零）。如果要顯示TRUE，則應(yīng)為本參數(shù)使用邏輯值TRUE。Value_if_true也可以是其他公式。

Value_if_1 logical_test 為FALSE時返回的值。例如，如果本參數(shù)為文本字符串“超出預(yù)算”而且logical_test參數(shù)值為FALSE，則IF函數(shù)將顯示文本“超出預(yù)算”。如果 logical_test 為FALSE且忽略了Value_if_1（即 value_if_true 后沒有逗號），則會返回邏輯值FALSE。如果logical_test 為 FALSE且Value_if_1 為空（即 value_if_true 后有逗號，并緊跟著右括號），則本參數(shù)返回0（零）。Value_if_1 也可以是其他公式。

函數(shù)說明：

函數(shù)IF可以嵌套七層，用value_if_1及value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測條件。

在計算參數(shù)value_if_true和value_if_1后，函數(shù)I 返回相應(yīng)語句執(zhí)行后的返回值。

如果函數(shù)IF的參數(shù)包含數(shù)組 （數(shù)組：用于建立可生成多個結(jié)果或可對在行和列中排列的一組參數(shù)進行運算的單個公式。數(shù)組區(qū)域共用一個公式；數(shù)組常量是用作參數(shù)的一組常量。），則在執(zhí)行IF語句時，數(shù)組中的每一個元素都將計算。

⑷LEFT（）函數(shù)

LEFT（）函數(shù)是常用的字符串函數(shù)，其含義是得到字符串左部指定個數(shù)的字符。

函數(shù)語法：

LEFT（ string， n ）

string 指定要提取子串的字符串。

n 指定子串長度返回值String。

函數(shù)說明：

函數(shù)執(zhí)行成功時返回string字符串左邊n個字符，發(fā)生錯誤時返回空字符串（\"\"）。如果任何參數(shù)的值為NULL，Left（）函數(shù)返回NULL。如果n的值大于string字符串的長度，那么Left（）函數(shù)返回整個string字符串，但并不增加其它字符。

⒉新建表

新建一工作表，將編號、姓名、參賽項目、成績輸入到相關(guān)單元格中，

其中，編號：運動員號碼設(shè)6位。

第一位第二、三位第四第五六位

年級班級性別序號

第一位年級號：用數(shù)字1、2、3來分別區(qū)分。

第二、三位班級號：用這個班的班號來填，例如：2班，填“02”

第四位性別號，男生用“01”女生用“1”

第五、六位是序號，由各班自己編排，只要不重復(fù)即可。

整個編號如“102101”就代表，1年級、2班、女子、班內(nèi)第1個編號的運動員。

編號、姓名、田賽、徑賽、組別字段為文本，田賽成績、徑賽成績字段為數(shù)值。其中田賽成績“30”代表30米，徑賽成績“0.135”代表13秒5、“1.96”代表1分96秒。

⒊根據(jù)編號判斷“男”、“女”

單擊G21單元格，在編輯欄中輸入“=IF（MID（A21，4，1）=\"1\"，\"女\"，\"男\(zhòng)"）”，復(fù)制公式至G33單元格。

其含義是在A21單元格中從第四個字符開始查找，找一個字符，如果等于“1”，填入“男”，否則填入“女”，這樣就可根據(jù)編號將原始數(shù)據(jù)分為2組。

⒋篩選兩組成績

利用菜單“數(shù)據(jù)”-“篩選”-“自動篩選”命令，分別取出男女兩組的成績，如圖1.3所示。將其分別復(fù)制到相關(guān)單元格中。

⒌個人成績統(tǒng)計（以鐵餅、標槍為例）

單擊F3單元格，在編輯欄中輸入=IF（C16=$C$3，VLOOKUP（C16，C15：$F$28，3，F(xiàn)ALSE），\"\"）

其含義是假如C16單元的值與$C$3單元格的值相等，即都是“鐵餅“，則在C15：$F$28單元格區(qū)域中查找與C16同行的第3列的值，查找的結(jié)果為”“30”，復(fù)制公式至F10單元格。

注意：在公式中C3、F28單元格中使用了絕對引用。

單擊H3單元格，在編輯欄中輸入IF（C16=$C$4，VLOOKUP（C16，C15：$F$28，3，F(xiàn)ALSE），\"\"）復(fù)制公式至H10單元格。

其含義同上。這樣就分別將這兩項成績從原始數(shù)據(jù)表中取出，

⒍成績排序

單擊G3單元格，在編輯欄中輸入“=IF（F3=\"\"，\"\"，RANK（F3，$F$3：$F$10，1））”，復(fù)其含義是假如F3單元格中值為空，則不排名，否則，根據(jù)$F$3：$F$10單元格區(qū)域的值按降序排名。

單擊I3單元格，在編輯欄中輸入“=IF（H3=\"\"，\"\"，RANK（H3，$H$3：$H$10，1））”，其含義同上。結(jié)果如圖1.6所示。這樣就排出了女子組的個人名次。

跳遠、跳高的成績計算統(tǒng)計同理。

⒎運動員分組

在大會秩序冊中運動員分組一般都是以行排列，而在EXCEL表中是以列排列的，這時可以進行行列轉(zhuǎn)換，轉(zhuǎn)換的方法是：選中復(fù)制后在粘貼到一個新的工作表時點擊右擊選擇性粘貼，并對話框轉(zhuǎn)置前打上鉤即可。例如將同一個班、相同項目的運動員分到不同的組，具體的操作方法為：分別對“男100米”“女100米”等工作表進行“排序”（按號碼）再用需要分成的組數(shù)填充就可以了。例如：男100米需要分成8組，就從上到下在分組號中填充“1、2、3、4、5、6、7、8”反復(fù)循環(huán)直到結(jié)束，這樣一個班的同一項目運動員就分到了不同的組里。（可以用自定義序列的方法完成）

⒏計算團體總分

在原始記錄表中填加兩列，分別為“班級”、“總成績”，如圖1.7所示。單擊H16單元格，在編輯欄中輸入“=LEFT（A16，1）\"年\"MID（A16，3，1）\"班\"”，“ ”是連接符，其含義是將文本與函數(shù)連接。這樣就可以按照編號分別進行班級分類。

根據(jù)不同的班級，可以利用“自動篩選”功能，取出不同的班級，根據(jù)計算總成績的標準分別計算出各班的成績。

參考文獻：EXCLE 在信息管理中的應(yīng)用 鄭小玲主編 人民郵電出版社

作者姓名： 鄶淑娥

作者一 性別： 女 出生年月：1963年10月 籍貫： 遼寧省大連市 學歷：本科 職稱：副教授 研究方向：體育運動學

樣刊郵寄的地址及作者要求：

）

地址： 遼寧 省 大連 市 沙河口區(qū)楊樹街35號 單位：東北財經(jīng)大學職業(yè)技術(shù)學院

收件人：鄶淑娥