基于Excel的田徑成績計算與查詢模型設(shè)計

摘要：Excel電子表格以其強大的數(shù)據(jù)處理與數(shù)據(jù)分析功能，已應(yīng)用于社會經(jīng)濟生活的各個領(lǐng)域。體育比賽中的田徑項目，如跳遠、鉛球、引體、仰臥等，其計量單位各不相同，按照不同計算單位計算的單科成績最終要折合為統(tǒng)一的分數(shù)，才能和其他成績一并統(tǒng)計計算，如果利用手工計算勢必會費時、費力，且準確性又差。

關(guān)鍵詞：Excel；田徑成績；計算；查詢；模型

中圖分類號：G642文獻標志碼：A文章編號：1673-291X（2010）34-0317-02

引言

目前，在學(xué)校的田徑比賽、運動訓(xùn)練及全能項目的教學(xué)中，大多是利用人工的方式進行計算，統(tǒng)計、查詢，這項工作相對比較煩瑣，且容易出現(xiàn)查分或計算錯誤。因此，為了減輕工作強度和提高運算的準確性，筆者利用Excel的函數(shù)功能，設(shè)計了一個田徑成績計算與查詢模型，實現(xiàn)了田徑評分與查詢的自動化。

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

1．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的任意值或表達式。

Value_if_truelogical_test為TRUE 時返回的值。

Value_if_1logical_test為FALSE時返回的值。

函數(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ù)組，則在執(zhí)行IF語句時，數(shù)組中的每一個元素都將計算。

2．LEFT函數(shù)。LEFT函數(shù)是基于所指定的字符數(shù)返回文本字符串中的第一個或前幾個字符。

函數(shù)語法：

LEFT（text，num_chars）

Text是包含要提取字符的文本字符串。

Num_chars 指定要由 LEFT 所提取的字符數(shù)。

Num_chars 必須大于或等于 0。

如果 num_chars 大于文本長度，則 LEFT 返回所有文本。

如果省略 num_chars，則假定其為 1。

3．INT 函數(shù)。INT 函數(shù)是將數(shù)字向下舍入到最接近的整數(shù)。

函數(shù)語法：

INT（number）

Number 需要進行向下舍入取整的實數(shù)。

4．VLOOKUP 函數(shù)。VLOOKUP 函數(shù)是較為常用的查找函數(shù)之一，它是在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當前行中其他列的值。

函數(shù)語法：

VLOOKUP （lookup_value，table_array，col_index_num，range

lookup）

Lookup_value為需要在表格數(shù)組第一列中查找的數(shù)值。Lookup_value 可以為數(shù)值或引用。若 lookup_value 小于 table_array 第一列中的最小值，VLOOKUP 將返回錯誤值 #N/A。

Table_array為兩列或多列數(shù)據(jù)。table_array 第一列中的值是由 lookup_value 搜索的值。這些值可以是文本、數(shù)字或邏輯值。不區(qū)分大小寫。

Col_index_num為 table_array 中待返回的匹配值的列序號。Col_index_num 為 1 時，返回 table_array 第一列中的數(shù)值；col_index_num 為 2，返回 table_array 第二列中的數(shù)值，以此類推。

Range_lookup為邏輯值，指定希望 VLOOKUP 查找精確的匹配值還是近似匹配值：

如果為 TRUE 或省略，則返回精確匹配值或近似匹配值。也就是說，如果找不到精確匹配值，則返回小于 lookup_value 的最大數(shù)值。

如果為 FALSE，VLOOKUP 將只尋找精確匹配值。在此情況下，table_array 第一列的值不需要排序。如果 table_array 第一列中有兩個或多個值與 lookup_value 匹配，則使用第一個找到的值。如果找不到精確匹配值，則返回錯誤值 #N/A。

函數(shù)說明：

在 table_array 第一列中搜索文本值時，需確保 table_array 第一列中的數(shù)據(jù)沒有前導(dǎo)空格、尾隨空格、不一致的直引號（’ 或 \"）、彎引號（‘或“）或非打印字符。在上述情況下，VLOOKUP 可能返回不正確或意外的值。

在搜索數(shù)字或日期值時，需確保 table_array 第一列中的數(shù)據(jù)未保存為文本值。否則，VLOOKUP 可能返回不正確或意外的值。

如果 range_lookup 為 FALSE 且 lookup_value 為文本，則可以在 lookup_value 中使用通配符、問號 （?） 和星號 （*）。問號匹配任意單個字符；星號匹配任意字符序列。如果您要查找實際的問號或星號本身，需在該字符前鍵入波形符 （~）。

二、計算模型設(shè)計

1.新建工作表。打開Excel工作簿，在“Book1.xls”工作簿中新建一工作表，建立“田徑成績計算表”，輸入相關(guān)的各指標數(shù)值（如圖1所示）。

圖1成績計算表

2.計算立定跳遠得分。單擊E4單元格→在編輯欄中輸入“=IF（C4=\"男\(zhòng)"，IF（D4<1.59，0，IF（D4>2.35，100，（INT（（D4*100-159）/4）+1）*5）），IF（D4<1.21，0，IF（D4>1.96，100，（INT（（D4*100-121）/4）+1）*5）））”，按[回車]鍵確認，復(fù)制公式至D19單元格。

該公式的含義是首先由IF函數(shù)判斷性別，如果是男生，小于1.59米為0分，大于2.35米為100分，其余用公式（（D4*100

-159）/4）+1）*5）計算，計算結(jié)果取整。如果是女生同理。

注意：D4是相對引用。

3.計算鉛球得分。單擊G4單元格→在編輯欄中輸入“=IF（C4=\"男\(zhòng)"，IF（F4<4.2，0，IF（F4>9.89，100，（INT（（F4*10-42）/3）+1）*5）），IF（F4<3，0，IF（F4>6.8，100，（INT（（F4*10-30）/2）+1）*5）））”，按[回車]鍵確認，復(fù)制公式至G19單元格。

該公式的含義是首先由IF函數(shù)判斷性別，如果是男生，小于4.29米為0分，大于9.89米為100分，其余用公式（（F4*10-42）/3）+1）*5））計算，計算結(jié)果取整。如果是女生同理。

注意：F4是相對引用。

4.計算仰臥得分。單擊I4單元格→在編輯欄中輸入“=IF（C4=\"男\(zhòng)"，IF（ISTEXT（H4），IF（LEFT（H4，F(xiàn)IND（\"個\"，H4）-1）<\"04\"，0，IF（LEFT（H4，F(xiàn)IND（\"個\"，H4）-1）>\"79\"，100，INT（（LEFT（H4，F(xiàn)IND（\"個\"，H4）-1）-4）/4+1）*5）），IF（H4<1，0，IF（H4>10，100，IF（H4>5，40+（H4-5）*10，20+H4*5）））），IF（H4<6，0，IF（H4>43，100，（INT（（H4-6）/2）+1）*5））），按[回車]鍵確認，復(fù)制公式至I19單元格。

該公式的含義是首先由IF函數(shù)判斷性別，如果是男生，判斷H4單元格是否是文本，如果是且小于4個為0分，大于79個為100分，其余按照公式（LEFT（H4，F(xiàn)IND（\"個\"，H4）-1）-4）/4+1）*5））計算，計算結(jié)果取整。如果是女生同理。

三、查詢模型設(shè)計

設(shè)置查詢卡樣式（如圖2所示）。

圖2查詢卡

單擊E2單元格→在編輯欄中輸入“=VLOOKUP（B2，Sheet1!A3:I19，2，F(xiàn)ALSE）”→單擊[確定]按鈕。

單擊C4單元格→在編輯欄中輸入“=VLOOKUP（B2，Sheet1!A3:I19，5，F(xiàn)ALSE）”→單擊[確定]按鈕。

單擊C6單元格→在編輯欄中輸入“=VLOOKUP（B2，Sheet1!A3:I19，7，F(xiàn)ALSE）”→單擊[確定]按鈕。

單擊C8單元格→在編輯欄中輸入“=VLOOKUP（B2，Sheet1!A3:I19，9，F(xiàn)ALSE）”→單擊[確定]按鈕。

上述公式的含義是：在sheet1表的A3:I19單元格區(qū)域中查找B2單元格的數(shù)值，找到后分別返回該區(qū)域第2、5、7、9列相對位置的數(shù)值，“FALSE”為精確查找。這樣，如果在B2單元格中輸入1002，則返回的內(nèi)容（如圖3所示）。

圖3查詢結(jié)果

小結(jié)

總之，利用Excel提供的大量函數(shù)，可以很方便地進行各種計算或查詢模型的設(shè)計，作為一名體育工作者，掌握Excel的相關(guān)知識，對在教學(xué)中應(yīng)用Excel來解決各類問題，是會有很大幫助的。

參考文獻：

[1]鄭小玲，梁露，趙丹亞.Excel在信息管理中的應(yīng)用:第1版[M].北京:人民郵電出版社，2004:6.[責(zé)任編輯 陳鶴]