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

基于Excel實(shí)現(xiàn)基層田徑運(yùn)動會徑賽成績自動化處理

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

一、方法與思路

1．要便于快速輸入

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

2．?dāng)?shù)據(jù)的“非零進(jìn)位”處理

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

圖1 設(shè)計(jì)徑賽成績處理界面

圖2 設(shè)置單元格的自定義格式

3．設(shè)計(jì)徑賽成績處理的界面

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

由于在編排的時(shí)候已經(jīng)有了基礎(chǔ)的數(shù)據(jù)，在比賽中僅需要在“輸入成績”列輸入成績即可。圖1中“男子乙組400米”項(xiàng)目共有24位運(yùn)動員，但圖中隱藏了部分的行。

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

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

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

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

同理，對“E”列公告成績的自定義格式設(shè)置方法大同小異，所不同的是數(shù)字自定義格式的內(nèi)容為：“[＜=600]0!．0；[＞600]0!：00!．0”。因?yàn)槌煽児娴臅r(shí)間是精確到十分之一秒，只有一位小數(shù)點(diǎn)。所以，小于1min的成績最大為“599”，顯示為“59．9”，而1min以上的最小成績“1000”，顯示為“1：00．0 ”。

5．設(shè)計(jì)公式轉(zhuǎn)換成績

因?yàn)椤拜斎氤煽儭笔前俜种幻?，而“公告成績”顯示為十分之一秒，所以，必須設(shè)計(jì)計(jì)算公式進(jìn)行成績轉(zhuǎn)換。

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

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

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

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

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

=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函數(shù)的三個參數(shù)，其中“公式2～1”是IF函數(shù)的第一參數(shù)，“公式2～2”是IF函數(shù)的第二參數(shù)，“公式2～3”則為IF函數(shù)的第三參數(shù)。當(dāng)“公式2～1”為“TRUE”，也就是成立，則執(zhí)行“公式2～2”的運(yùn)算，否則執(zhí)行“公式2～3”的運(yùn)算。

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

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

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

“公式2”已經(jīng)可以解決“百分之一秒”到“十分之一秒”的成績轉(zhuǎn)換，并執(zhí)行“非零進(jìn)位”的原則。但是，“公式2”還不夠強(qiáng)壯，因?yàn)?，?dāng)把“公式2”往下復(fù)制，在還沒有比賽時(shí)，“輸入成績”為空白，而“公告成績”列顯示“0”成績，不合理。因此，可以通過嵌套一個IF函數(shù)進(jìn)行“容錯處理”，也就是當(dāng)輸入成績?yōu)椤翱瞻住睍r(shí)，也顯示為“空白”，可以把“D2”單元格的公式修改為：

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

“公式3”的含義：

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

二、程序測試

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

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

三、結(jié)果

1．輸入成績速度快

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

2．實(shí)現(xiàn)百分之一秒自動轉(zhuǎn)換為十分之一秒

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

3．自動實(shí)現(xiàn)“分”、“秒”之間的“六十進(jìn)制”的運(yùn)算

本程序還能實(shí)現(xiàn)“分”、“秒”之間的“六十進(jìn)制”的進(jìn)位運(yùn)算。由于比賽項(xiàng)目沒有超過59min，所以，不需要考慮“分”進(jìn)位到“時(shí)”。

4．順利實(shí)現(xiàn)按成績排名次

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