基于Excel實現基層田徑運動會徑賽成績自動化處理

謝雄峙陳華福（.廣東省廣州市鐵二中學，5000；.廣州大學體育學院，50006）

基于Excel實現基層田徑運動會徑賽成績自動化處理

謝雄峙1陳華福2
（1.廣東省廣州市鐵二中學，510010；2.廣州大學體育學院，510006）

一、方法與思路

1．要便于快速輸入

由于田徑比賽的徑賽成績是時間，而時間的表示格式有多種，國內常用“12"23”來表示“12秒23”，而“1'02"34”則表示“1分02秒34”。但國際上通用的方式卻不是這樣，而是采用“12．23”來表示“12秒23”，用“1：02．34”表示“1分02秒34”。為了在比賽中快速地輸入成績，可以考慮只輸入數字，利用Excel的自定義格式顯示為所需的數據格式。由于在輸入數據時，不必輸入各種標點符號，將會大大提高輸入成績的速度。

2．數據的“非零進位”處理

由于輸入的成績是精確到百分之一秒的，而成績公告，判斷是否破紀錄等都是按照十分之一秒。因此，必須設計一個公式對輸入成績進行計算，按“非零進位”的原則轉換成為十分之一秒的成績。

圖1 設計徑賽成績處理界面

圖2 設置單元格的自定義格式

3．設計徑賽成績處理的界面

如圖1所示，在“A”、“B”、“C”、“D”、“E”、“F”、“G”列輸入“項目”、“姓名”、“單位”、“輸入成績”、“公告成績”、“名次”、“備注”。

由于在編排的時候已經有了基礎的數據，在比賽中僅需要在“輸入成績”列輸入成績即可。圖1中“男子乙組400米”項目共有24位運動員，但圖中隱藏了部分的行。

4．設置“輸入成績”和“公告成績”的自定義格式

選擇“D”列，右擊該列的列標“D”，在彈出的“快捷菜單”中選擇“設置單元格格式”命令，出現如圖2所示的對話框。單擊“數字”標簽，在“分類”列表框中單擊“自定義”，在“類型(T)：”下面的文本框中輸入“[＜=6000]0!．00；[＞6000]0!：00!．00”，如圖2所示。

“[＜=6000]0!．00；[＞6000]0!：00!．00”是一個數字的條件格式，中括號里面的內容是條件，隨后是數字的顯示格式，多個條件以分號“；”分隔。需要注意的是，自定義格式設置，所有的標點符號都必須是英文標點，否則出錯。

設置該格式的意義是：當輸入的成績小于6000，顯示的格式為“0?。?0”，表示從最右邊的數字開始，往左邊數到第二位數字，在它的左邊插入(顯示)一個小數點，也就是保留兩位小數點，因為小于1min的時間在輸入時，最大也就是5999，這個數字當然小于6000；當輸入的成績大于 6000，顯示的格式為“1！：00?。?0”，表示從最右邊的數字開始，往左邊數到第二位數字，在它左邊插入（顯示）一個小數點，同理，第四位數字的左邊插入（顯示）“：”（冒號），表示分與秒的分隔符，因為大于1分鐘的成績都是5位數字，最小的10000，也會比6000要大，符合該條件。

同理，對“E”列公告成績的自定義格式設置方法大同小異，所不同的是數字自定義格式的內容為：“[＜=600]0!．0；[＞600]0!：00!．0”。因為成績公告的時間是精確到十分之一秒，只有一位小數點。所以，小于1min的成績最大為“599”，顯示為“59．9”，而1min以上的最小成績“1000”，顯示為“1：00．0 ”。

5．設計公式轉換成績

因為“輸入成績”是百分之一秒，而“公告成績”顯示為十分之一秒，所以，必須設計計算公式進行成績轉換。

Excel的函數非常豐富，ROUNDUP函數就可以實現“非零進位”。因此，可以在圖1中的“E2”單元格中輸入：“=ROUNDUP(D2/10，0)公式1”。

“公式1”的含義：對“D2”單元格的值除以10，并對所得的結果取0位小數，也就是保留到整數位，在保留整數位時，進行“非零進位”的操作。這樣就巧妙地實現百分之一秒的成績轉換為十分之一秒的成績。

但是，“公式1”還存在缺憾，因為，如果輸入成績是“5991”～“5999”之間，經過“非零進位”的計算，都會轉換為“600”，顯示為“60．0”；同理，如果是“15991”～“15999”這種成績，經過轉換后成為“1600”，顯示為“1：60．0”。眾所周知，“時”、“分”、“秒”是“六十進制”，逢60進1。

所以，必須對“公式1”做進一步的修改，不僅讓公式能做到“非零進位”精確到十分之一秒，還能判斷如果由于進位的秒的時間是“60”，就進位到“分”，自動實現“時分秒”的“六十進制”的運算。

因此，可以對“E2”單元格的公式進行修改：

=IF(MOD(D2，10000)＞5990，(INT(D2/100 00)+1)*1000，ROUNDUP(D2/10，0))公式2

“公式2”的含義：可以把該公式分解為如下三部分：

MOD(D2，10000)＞5990公式2～1

(INT(D2/10000)+1)*1000公式2～2

ROUNDUP(D2/10，0)公式2～3

“公式2～1”、“公式2～2”、“公式2～3”是IF函數的三個參數，其中“公式2～1”是IF函數的第一參數，“公式2～2”是IF函數的第二參數，“公式2～3”則為IF函數的第三參數。當“公式2～1”為“TRUE”，也就是成立，則執行“公式2～2”的運算，否則執行“公式2～3”的運算。

“公式2～1”的含義：通過取余數函數MOD，把“D2”單元格的值除以10000，取余數，得到除去“分”的值，也就是得到“秒”的數值，再把該余數與5990相比，如果大于5990，就為“TRUE”，執行“公式2～2”的運算。

“公式2～2”的含義為：通過取整數函數，把“D2”單元格的值除以10000，取得商的整數，丟去所有的小數，也就是取得整數部分。當“D2”單元格的值小于10000，整數為0，當大于10000，恰好能取得所有的“分”的值。由于“公式2～1”為“TRUE”，也就是秒的數字是大于 5990，由于進位后的“秒”的數值為“60”，故應該“秒”進位為“分”，“秒”轉變為“00”。所以，把取得的“分”的值+1，就能進位為“分”了，然后再把“分”的值乘以1000，轉化為“十分之一秒”的“分”的值。由于中小學生運動會的項目，不存在成績超過59min的運動項目，因此不必做判斷由于進位而達到60分鐘而進位到小時運算，盡管實現起來也很容易。

“公式2～3”就是“公式1”，它的含義不再贅述。

“公式2”已經可以解決“百分之一秒”到“十分之一秒”的成績轉換，并執行“非零進位”的原則。但是，“公式2”還不夠強壯，因為，當把“公式2”往下復制，在還沒有比賽時，“輸入成績”為空白，而“公告成績”列顯示“0”成績，不合理。因此，可以通過嵌套一個IF函數進行“容錯處理”，也就是當輸入成績為“空白”時，也顯示為“空白”，可以把“D2”單元格的公式修改為：

=IF(D2＜＞""，IF(MOD(D2，10000)＞59 90，(INT(D2/10000)+1)*1000，ROUNDUP (D2/10，0))，"") 公式3

“公式3”的含義：

“D2＜＞""”是第一個IF函數的第一參數，作用是判斷“D2”單元格是否為空，當不為空時，執行第二參數“IF(MOD(D2，10 000)＞5990，(INT(D2/10000)+1)*1000，ROU NDUP(D2/10，0))”的運算，該部分也就是“公式2”，是“公式3”的核心部分，“""”是第一個IF函數的第三參數，表示第一參數結果為“FALSE”，也就是不成立，顯示為“空”。通過這樣的設計，就可以增強公式的強壯度，并實現容錯，沒有“輸入成績”也不會在“公告成績”顯示為0。

二、程序測試

程序設計好了，必須做各種各樣的數據的測試，尤其是一些臨界數據的輸入測試。

可以輸入60秒以下的成績進行測試，當在“輸入成績”列的“D2”單元格中輸入“1234”，在“D2”單元格中顯示為“12．34”，而在“公告成績”列的“E2”單元格顯示為：“12．4”，實現了百分之一秒轉換為十分之一秒，同時也實現“非零進位”，當在“D2”單元格中輸入“1230”，則“D2”單元格顯示為“12．30”，“E2”單元格顯示為“12．3”；當在“D2”單元格輸入“5991”，“D2”單元格顯示為“59．91”，而“E2”單元格顯示為“1：00．0”，實現了數據的轉換的同時，還實現了“分秒”的“六十進制”轉換，同樣，以“15991”輸入在“D”列，“E”列對應的單元格則顯示為“2：00．0”。實際上，用所有的小于595990（小于1小時）的數據輸入，均能得到正確的結果。

三、結果

1．輸入成績速度快

在運動會進行中，因參賽人數眾多，每組之間的時間間隔非常短，通常組與組之間在3min左右，但由于在輸入成績時，僅僅需要輸入數字，不需要輸入任何標點符號，特別是利用臺式機的“數字鍵盤”進行輸入，極大地提高了“輸入成績”的速度和效率。

2．實現百分之一秒自動轉換為十分之一秒

“手計時”成績只能精確到十分之一秒，但由于精確到十分之一秒，導致相同成績而無法排名次，所以，既需要百分之一秒成績作為排名次的，也需要把百分之一秒轉換為十分之一秒來公告成績。由于都是賽前設計好公式，百分之一秒的成績自動轉換為十分之一秒，不需要人工干預。

3．自動實現“分”、“秒”之間的“六十進制”的運算

本程序還能實現“分”、“秒”之間的“六十進制”的進位運算。由于比賽項目沒有超過59min，所以，不需要考慮“分”進位到“時”。

4．順利實現按成績排名次

雖然Excel能處理日期和時間的數據，但由于Excel對時間的處理是按照“小數”來進行處理。因此，在直接按照時間的格式來輸入成績，例如，“1：02．23”的成績是可以直接這樣輸入“1：02．23”，然后對它進行時間格式的設置：“m：ss．00”，但這種輸入的數據，被當成很小的小數（遠遠小于1），而小于1min 的成績又輸入為“56．98”。由于“1：02．23”是比1還小的多的小數，當然會比“56．98”小，在排名次時，是按照升序來排序，“1：02．23”要排在“56．98”的前面，意味著它的成績好，但實際上卻相反。而采用本文的方法，小于60min的成績，輸入的整數是最大是5999，為4位整數，而大于60min，最小的的整數為10000，因此就不會出現相反的排名。