基于Excel 的《學生體質健康標準》模型設計與查詢

林彬

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

［關鍵詞］ Excel； 學生體質健康標準； 模型； 設計； 查詢

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

［中圖分類號］TP317.3［文獻標識碼］A［文章編號］1673 - 0194（2012）10- 0124- 04

1前言

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

2相關函數介紹

（1） ＬＯＯＫＵＰ（）函數

ＬＯＯＫＵＰ（） 函數從單行或單列區域或數組返回值。

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

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

函數語法：

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

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

· ｌｏｏｋｕｐ＿ｖａｌｕｅ 必需。ＬＯＯＫＵＰ 在第一個向量中搜索的值。Ｌｏｏｋｕｐ＿ｖａｌｕｅ 可以是數字、文本、邏輯值、名稱或對值的引用。

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

函數說明：

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

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

（2） ＩＦ（）函數

功能：它執行真假值判斷，根據邏輯計算的真假值，返回不同結果。可以使用函數ＩＦ對數值和公式進行條件檢測。

函數語法：

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

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

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

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

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

函數說明：

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

如果 ＩＦ 的任意參數為數組，則在執行 ＩＦ 語句時，將計算數組的每一個元素。

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

（3） ＶＬＯＯＫＵＰ（）函數

功能：在表格數組的首列查找值，并由此返回表格數組當前行中其他列的值。

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

函數語法：

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

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

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

Ｔａｂｌｅ＿ａｒｒａｙ為兩列或多列數據。需使用對區域的引用或區域名稱。

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

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

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

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

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

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

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

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

函數說明：

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

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

如果 ｒａｎｇｅ＿ｌｏｏｋｕｐ 為 ＦＡＬＳＥ 且 ｌｏｏｋｕｐ＿ｖａｌｕｅ 為文本，則可以在 ｌｏｏｋｕｐ＿ｖａｌｕｅ 中使用通配符、問號 （？） 和星號 （*）。問號匹配任意單個字符；星號匹配任意字符序列。如果您要查找實際的問號或星號本身，請在該字符前鍵入波形符 （ ）。

（4） ＩＮＤＥＸ（）函數

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

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

函數語法：

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

Ａｒｒａｙ 必需。單元格區域或數組常量。

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

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

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

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

函數說明：

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

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

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

3模型建立

3.1新建工作表

在Ｅｘｃｅｌ工作簿中新建一工作表，命名為“學生體質健康標準登記表”，輸入相關成績，如圖１所示。

3.2函數設置

根據我國教育部頒布的大學一到四年級學生體質健康標準，進行評分。以男生為例：

（1） 計算身高體重得分

身高體重的標準簡化為如表１所示。

根據表１標準，首先：設置得分取值表，如圖２所示。

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

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

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

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

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

其次：設置登記表中的得分公式

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

其含義為：如果男１的身高小于１６０，則取Ａ１７的值，如果在１６０~１７０之間，則取Ｂ１７的值，如果在１７０~１８０之間，則于Ｃ１７的值，否則取Ｄ１７的值。其結果如圖３所示。

（2） 計算肺活量得分：肺活量指數部分得分表，如表２所示。

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

單擊Ｇ３單元格，輸入公式＝Ｆ３／Ｄ３計算指數。單擊Ｈ３單元格，輸入公式，其余復制公式即可。

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

其含義如上所述。結果如圖４所示。

（3） 其余各項目的計算以此類推。

最終計算結果如圖５所示。

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

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

4動態查詢

4.1設置自動查詢卡

設置如圖６所示的動態查詢卡片，只要在Ｅ２５單元格中輸入學生的姓名，其余各項指指標自動依次出現在相應的位置。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.2填加控件按鈕

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

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

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

主要參考文獻

［1］ 劉繼偉，楊樺. Ｅxcel在財務管理中的應用［M］. 北京：清華大學出版社，２０１０.