基于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］. 北京：清华大学出版社，２０１０.