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

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

關鍵詞：Excel；田徑成績；計算；查詢；模型

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

引言

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

一、相關函數介紹

1．IF函數。IF函數是較為常用的邏輯函數之一，它執行真假值判斷，根據邏輯計算的真假值，返回不同結果。可以使用函數IF對數值和公式進行條件檢測。

函數語法：

IF（logical_test，value_if_true，value_if_1）

Logical_test表示計算結果為TRUE或FALSE的任意值或表達式。

Value_if_truelogical_test為TRUE 時返回的值。

Value_if_1logical_test為FALSE時返回的值。

函數說明：

函數IF可以嵌套七層，用value_if_1及value_if_true 參數可以構造復雜的檢測條件。

在計算參數value_if_true和value_if_1后，函數I 返回相應語句執行后的返回值。

如果函數IF的參數包含數組，則在執行IF語句時，數組中的每一個元素都將計算。

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

函數語法：

LEFT（text，num_chars）

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

Num_chars 指定要由 LEFT 所提取的字符數。

Num_chars 必須大于或等于 0。

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

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

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

函數語法：

INT（number）

Number 需要進行向下舍入取整的實數。

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

函數語法：

VLOOKUP （lookup_value，table_array，col_index_num，range

lookup）

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

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

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

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

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

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

函數說明：

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

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

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

二、計算模型設計

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

圖1成績計算表

2.計算立定跳遠得分。單擊E4單元格→在編輯欄中輸入“=IF（C4=\"男\"，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）））”，按[回車]鍵確認，復制公式至D19單元格。

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

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

注意：D4是相對引用。

3.計算鉛球得分。單擊G4單元格→在編輯欄中輸入“=IF（C4=\"男\"，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）））”，按[回車]鍵確認，復制公式至G19單元格。

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

注意：F4是相對引用。

4.計算仰臥得分。單擊I4單元格→在編輯欄中輸入“=IF（C4=\"男\"，IF（ISTEXT（H4），IF（LEFT（H4，FIND（\"個\"，H4）-1）<\"04\"，0，IF（LEFT（H4，FIND（\"個\"，H4）-1）>\"79\"，100，INT（（LEFT（H4，FIND（\"個\"，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））），按[回車]鍵確認，復制公式至I19單元格。

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

三、查詢模型設計

設置查詢卡樣式（如圖2所示）。

圖2查詢卡

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

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

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

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

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

圖3查詢結果

小結

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

參考文獻：

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