基于Excel 的《學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》模型設(shè)計(jì)與查詢

林彬

［摘要］ 現(xiàn)代教育信息技術(shù)的迅速發(fā)展，尤其是計(jì)算機(jī)應(yīng)用技術(shù)的出現(xiàn)、網(wǎng)絡(luò)技術(shù)的運(yùn)用，已給現(xiàn)代教學(xué)帶來了深刻的變革，沖擊著傳統(tǒng)的教學(xué)模式，教學(xué)觀念，引起教學(xué)方法和教學(xué)手段的變革。在學(xué)校體育教學(xué)管理中，如何運(yùn)用信息技術(shù)來提高工作效率和管理質(zhì)量，是我們應(yīng)該重視和深入研究的。本文利用Excel設(shè)計(jì)了一個標(biāo)準(zhǔn)的《學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》模型，以便更方便、快捷地進(jìn)行錄入與查詢。

［關(guān)鍵詞］ Excel； 學(xué)生體質(zhì)健康標(biāo)準(zhǔn)； 模型； 設(shè)計(jì)； 查詢

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2012 . 10. 073

［中圖分類號］TP317.3［文獻(xiàn)標(biāo)識碼］A［文章編號］1673 - 0194（2012）10- 0124- 04

1前言

隨著ＩＴ技術(shù)的不斷發(fā)展及普及，對我們的工作、學(xué)習(xí)和社會生活等方面產(chǎn)生了巨大影響。各種應(yīng)用軟件的不斷完善和提高，為我們帶來了更多的快捷與方便。特別是Ｍｉｃｒｏｓｏｆｔ Ｅｘｃｅｌ，它是辦公室自動化中非常重要的一款軟件，很多企事業(yè)單位都是依靠Ｅｘｃｅｌ進(jìn)行數(shù)據(jù)管理。它不僅能方便地處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對數(shù)據(jù)的自動化處理和計(jì)算上?！秾W(xué)生體質(zhì)健康標(biāo)準(zhǔn)》已經(jīng)實(shí)施了好幾年，體育教師在工作中，通過測試所得的學(xué)生原始成績數(shù)據(jù)量非常大。在錄入《學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》成績時(shí)，對體育教師來說是件繁瑣、枯燥的工作，利用Ｅｘｃｅｌ函數(shù)與相關(guān)功能，不僅可以達(dá)到事半功倍的效果，而且還可以避免人工查詢引起的錯誤。

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

（1） ＬＯＯＫＵＰ（）函數(shù)

ＬＯＯＫＵＰ（） 函數(shù)從單行或單列區(qū)域或數(shù)組返回值。

ＬＯＯＫＵＰ 函數(shù)具有兩種語法形式：向量形式和數(shù)組形式。其中向量形式：

向量是只含一行或一列的區(qū)域。ＬＯＯＫＵＰ 的向量形式在單行區(qū)域或單列區(qū)域中查找值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。

函數(shù)語法：

ＬＯＯＫＵＰ（ｌｏｏｋｕｐ＿ｖａｌｕｅ，ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ，［ｒｅｓｕｌｔ＿ｖｅｃｔｏｒ］）

ＬＯＯＫＵＰ 函數(shù)向量形式語法具有以下參數(shù) （參數(shù)：為操作、事件、方法、屬性、函數(shù)或過程提供信息的值。）：

· ｌｏｏｋｕｐ＿ｖａｌｕｅ 必需。ＬＯＯＫＵＰ 在第一個向量中搜索的值。Ｌｏｏｋｕｐ＿ｖａｌｕｅ 可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊谩?/p>

· ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 必需。只包含一行或一列的區(qū)域。ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中的值可以是文本、數(shù)字或邏輯值。

函數(shù)說明：

· 如果 ＬＯＯＫＵＰ 函數(shù)找不到 ｌｏｏｋｕｐ＿ｖａｌｕｅ，則該函數(shù)會與 ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中小于或等于 ｌｏｏｋｕｐ＿ｖａｌｕｅ 的最大值進(jìn)行匹配。

· 如果 ｌｏｏｋｕｐ＿ｖａｌｕｅ 小于 ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中的最小值，則 ＬＯＯＫＵＰ 會返回 ＃Ｎ／Ａ 錯誤值。

（2） ＩＦ（）函數(shù)

功能：它執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。可以使用函數(shù)ＩＦ對數(shù)值和公式進(jìn)行條件檢測。

函數(shù)語法：

ＩＦ（ｌｏｇｉｃａｌ＿ｔｅｓｔ，ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ，ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ）

Ｌｏｇｉｃａｌ＿ｔｅｓｔ表示計(jì)算結(jié)果為ＴＲＵＥ或ＦＡＬＳＥ的任意值或表達(dá)式。

Ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅｌｏｇｉｃａｌ＿ｔｅｓｔ 為ＴＲＵＥ 時(shí)返回的值。

Ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅｌｏｇｉｃａｌ＿ｔｅｓｔ 為ＦＡＬＳＥ時(shí)返回的值。

如果指定條件的計(jì)算結(jié)果為 ＴＲＵＥ，ＩＦ 函數(shù)將返回某個值；如果該條件的計(jì)算結(jié)果為 ＦＡＬＳＥ，則返回另一個值。例如，如果 Ａ１ 大于 １０，公式 ＝ＩＦ（Ａ１＞１０，＂大于 １０＂，＂不大于 １０＂） 將返回“大于 １０”，如果 Ａ１ 小于等于 １０，則返回“不大于 １０”。

函數(shù)說明：

最多可以使用 ６４ 個 ＩＦ 函數(shù)作為 ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ 和 ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ 參數(shù)進(jìn)行嵌套以構(gòu)造更詳盡的測試。

如果 ＩＦ 的任意參數(shù)為數(shù)組，則在執(zhí)行 ＩＦ 語句時(shí)，將計(jì)算數(shù)組的每一個元素。

Ｅｘｃｅｌ 還提供了其他一些函數(shù)，可使用這些函數(shù)根據(jù)條件來分析數(shù)據(jù)。例如，若要計(jì)算某單元格區(qū)域內(nèi)某個文本字符串或數(shù)字出現(xiàn)的次數(shù)，可使用 ＣＯＵＮＴＩＦ 或 ＣＯＵＮＴＩＦＳ 工作表函數(shù)。若要計(jì)算基于某區(qū)域內(nèi)一個文本字符串或一個數(shù)值的總和，可使用 ＳＵＭＩＦ 或 ＳＵＭＩＦＳ 工作表函數(shù)。

（3） ＶＬＯＯＫＵＰ（）函數(shù)

功能：在表格數(shù)組的首列查找值，并由此返回表格數(shù)組當(dāng)前行中其他列的值。

ＶＬＯＯＫＵＰ 中的 Ｖ 表示垂直方向。當(dāng)比較值位于需要查找的數(shù)據(jù)左邊的一列時(shí)，可以使用 ＶＬＯＯＫＵＰ，而不用 ＨＬＯＯＫＵＰ。

函數(shù)語法：

ＶＬＯＯＫＵＰ（ｌｏｏｋｕｐ＿ｖａｌｕｅ，ｔａｂｌｅ＿ａｒｒａｙ，ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ，ｒａｎｇｅ＿ｌｏｏｋｕｐ）

Ｌｏｏｋｕｐ＿ｖａｌｕｅ為需要在表格數(shù)組第一列中查找的數(shù)值。

Ｌｏｏｋｕｐ＿ｖａｌｕｅ 可以為數(shù)值或引用。若 ｌｏｏｋｕｐ＿ｖａｌｕｅ 小于 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的最小值，ＶＬＯＯＫＵＰ 將返回錯誤值 ＃Ｎ／Ａ。

Ｔａｂｌｅ＿ａｒｒａｙ為兩列或多列數(shù)據(jù)。需使用對區(qū)域的引用或區(qū)域名稱。

ｔａｂｌｅ＿ａｒｒａｙ 第一列中的值是由 ｌｏｏｋｕｐ＿ｖａｌｕｅ 搜索的值。這些值可以是文本、數(shù)字或邏輯值。不區(qū)分大小寫。

Ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ為 ｔａｂｌｅ＿ａｒｒａｙ 中待返回的匹配值的列序號。

Ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ 為 １ 時(shí)，返回 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數(shù)值；ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ 為 ２，返回 ｔａｂｌｅ＿ａｒｒａｙ 第二列中的數(shù)值，以此類推。如果 ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ ：

小于 １，ＶＬＯＯＫＵＰ 返回錯誤值 ＃ＶＡＬＵＥ！。

大于 ｔａｂｌｅ＿ａｒｒａｙ 的列數(shù)，ＶＬＯＯＫＵＰ 返回錯誤值 ＃ＲＥＦ！。

Ｒａｎｇｅ＿ｌｏｏｋｕｐ為邏輯值，指定希望 ＶＬＯＯＫＵＰ 查找精確的匹配值還是近似匹配值：

如果為 ＴＲＵＥ 或省略，則返回精確匹配值或近似匹配值。也就是說，如果找不到精確匹配值，則返回小于 ｌｏｏｋｕｐ＿ｖａｌｕｅ 的最大數(shù)值。

ｔａｂｌｅ＿ａｒｒａｙ 第一列中的值必須以升序排序；否則 ＶＬＯＯＫＵＰ 可能無法返回正確的值。

函數(shù)說明：

在 ｔａｂｌｅ＿ａｒｒａｙ 第一列中搜索文本值時(shí)，確保 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數(shù)據(jù)沒有前導(dǎo)空格、尾隨空格、不一致的直引號（＇ 或 ＂）、彎引號（‘或“）或非打印字符。

在搜索數(shù)字或日期值時(shí)，確保 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數(shù)據(jù)未保存為文本值。否則，ＶＬＯＯＫＵＰ 可能返回不正確或意外的值。

如果 ｒａｎｇｅ＿ｌｏｏｋｕｐ 為 ＦＡＬＳＥ 且 ｌｏｏｋｕｐ＿ｖａｌｕｅ 為文本，則可以在 ｌｏｏｋｕｐ＿ｖａｌｕｅ 中使用通配符、問號 （？） 和星號 （*）。問號匹配任意單個字符；星號匹配任意字符序列。如果您要查找實(shí)際的問號或星號本身，請?jiān)谠撟址版I入波形符 （ ）。

（4） ＩＮＤＥＸ（）函數(shù)

功能：返回表格或區(qū)域中的值或值的引用。

函數(shù) ＩＮＤＥＸ 有兩種形式：數(shù)組形式和引用形式。

函數(shù)語法：

ＩＮＤＥＸ（ａｒｒａｙ， ｒｏｗ＿ｎｕｍ， ［ｃｏｌｕｍｎ＿ｎｕｍ］）

Ａｒｒａｙ 必需。單元格區(qū)域或數(shù)組常量。

如果數(shù)組只包含一行或一列，則相對應(yīng)的參數(shù) ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ 為可選參數(shù)。

如果數(shù)組有多行和多列，但只使用 ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ，函數(shù) ＩＮＤＥＸ 返回?cái)?shù)組中的整行或整列，且返回值也為數(shù)組。

Ｒｏｗ＿ｎｕｍ 必需。選擇數(shù)組中的某行，函數(shù)從該行返回?cái)?shù)值。如果省略 ｒｏｗ＿ｎｕｍ，則必須有 ｃｏｌｕｍｎ＿ｎｕｍ。

Ｃｏｌｕｍｎ＿ｎｕｍ 可選。選擇數(shù)組中的某列，函數(shù)從該列返回?cái)?shù)值。如果省略 ｃｏｌｕｍｎ＿ｎｕｍ，則必須有 ｒｏｗ＿ｎｕｍ。

函數(shù)說明：

如果同時(shí)使用參數(shù) ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ，函數(shù) ＩＮＤＥＸ 返回 ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ 交叉處的單元格中的值。

如果將 ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ 設(shè)置為 ０（零），函數(shù) ＩＮＤＥＸ 則分別返回整個列或行的數(shù)組數(shù)值。

Ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ 必須指向數(shù)組中的一個單元格；否則，函數(shù) ＩＮＤＥＸ 返回錯誤值 ＃ＲＥＦ！。

3模型建立

3.1新建工作表

在Ｅｘｃｅｌ工作簿中新建一工作表，命名為“學(xué)生體質(zhì)健康標(biāo)準(zhǔn)登記表”，輸入相關(guān)成績，如圖１所示。

3.2函數(shù)設(shè)置

根據(jù)我國教育部頒布的大學(xué)一到四年級學(xué)生體質(zhì)健康標(biāo)準(zhǔn)，進(jìn)行評分。以男生為例：

（1） 計(jì)算身高體重得分

身高體重的標(biāo)準(zhǔn)簡化為如表１所示。

根據(jù)表１標(biāo)準(zhǔn)，首先：設(shè)置得分取值表，如圖２所示。

其中Ａ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，４４．７，５０．６，５７．５，５９．８｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）

Ｂ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，５１．６，５８．２，６６，６８．６｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）

Ｃ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，５６．７，６３．８，７２．８，７５．８｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）

Ｄ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，６１．８，６９．８，７９．９，８３．２｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）

其含義為：根據(jù)男１的體重，在4個不同層次的身高中查找其得分。其余復(fù)制公式即可。

其次：設(shè)置登記表中的得分公式

單擊Ｅ３單元格，輸入公式＝ＩＦ（Ｃ３＜＝１６０，Ａ１７，ＩＦ（Ｃ３＜＝１７０，Ｂ１７，ＩＦ（Ｃ３＜＝１８０，Ｃ１７，Ｄ１７））），其余復(fù)制公式即可。

其含義為：如果男１的身高小于１６０，則?。粒保返闹?，如果在１６０~１７０之間，則?。拢保返闹担绻冢保罚皛１８０之間，則于Ｃ１７的值，否則?。模保返闹怠Ｆ浣Y(jié)果如圖３所示。

（2） 計(jì)算肺活量得分：肺活量指數(shù)部分得分表，如表２所示。

單擊Ｅ３單元格，輸入公式＝ＩＦ（Ｃ３＜＝１６０，Ａ１７，ＩＦ（Ｃ３＜＝１７０，Ｂ１７，ＩＦ（Ｃ３＜＝１８０，Ｃ１７，Ｄ１７））），其余復(fù)制公式即可。

單擊Ｇ３單元格，輸入公式＝Ｆ３／Ｄ３計(jì)算指數(shù)。單擊Ｈ３單元格，輸入公式，其余復(fù)制公式即可。

＝ＬＯＯＫＵＰ（Ｇ３，｛８４，８３，８２，８１，８０，７８，７７，７５，７３，７１，６８，６６，６４，６１，５８，５５｝，｛＂１００＂，＂９８＂，＂９６＂，＂９４＂，＂９２＂，＂９０＂，＂８７＂，＂８４＂，＂８１＂，＂７８＂，＂７５＂，＂７２＂，＂６９＂，＂６６＂，＂６３＂，＂６０＂｝）

其含義如上所述。結(jié)果如圖４所示。

（3） 其余各項(xiàng)目的計(jì)算以此類推。

最終計(jì)算結(jié)果如圖５所示。

其中：總分Ｏ３＝ ＡＶＥＲＡＧＥ（Ｅ３，Ｈ３，Ｊ３，Ｌ３，Ｎ３），其余復(fù)制公式即可。

等級Ｐ３＝ＩＦ（Ｏ３＜６０，＂不及格＂，ＩＦ（Ｏ３＜７５，＂及格＂，ＩＦ（Ｏ３＜９０，＂良好＂，＂優(yōu)秀＂））），其余復(fù)制公式即可。

4動態(tài)查詢

4.1設(shè)置自動查詢卡

設(shè)置如圖６所示的動態(tài)查詢卡片，只要在Ｅ２５單元格中輸入學(xué)生的姓名，其余各項(xiàng)指指標(biāo)自動依次出現(xiàn)在相應(yīng)的位置。

單擊Ｅ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，２）

其含義是在Ｅ２５，Ｂ２：Ｐ５單元格區(qū)域第一列查找Ｅ２５單元格的值，找到后返加相同行第２列的值。其余含義以此類推。

單擊Ｇ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，３）

單擊Ｉ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，４）

單擊Ｅ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，５）

單擊Ｇ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，６）

單擊Ｉ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，７）

單擊Ｅ２８單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，８）

單擊Ｉ２８單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，９）

單擊Ｅ２９單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１０）

單擊Ｉ２９單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１１）

單擊Ｅ３０單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１２）

單擊Ｉ３０單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１３）

單擊Ｇ３１單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１４）

單擊Ｉ３１單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１５）

4.2填加控件按鈕

單擊Ｅ２５單元格，輸入公式＝ＩＮＤＥＸ（Ｂ３：Ｂ５，Ｃ２５），其含義是在姓名這一列中查找Ｃ２５單元格中的值，如果為１，返回男１，其余類推。

在菜單的空白處，右擊，彈出的快捷菜單中，選擇“窗體”，在打開的窗體工具箱中，選擇“列表框”， 如圖７所示。右擊“列表框”控件，在彈出的對話框中選擇“設(shè)置控件格式”命令，如圖８所示。在彈出的“設(shè)置控件格式”對話框中，作如圖９的設(shè)置。

這樣只要在列表框中選擇學(xué)生的姓名，其查詢卡片中的姓名就會隨之刷新。如圖１０所示。

主要參考文獻(xiàn)

［1］ 劉繼偉，楊樺. Ｅxcel在財(cái)務(wù)管理中的應(yīng)用［M］. 北京：清華大學(xué)出版社，２０１０.