1 前 言
隨著ＩＴ技術的不斷發(fā)展及普及，對我們的工作、學習和社會生活等方面產生了巨大影響。各種應用軟件的不斷完善和提高，為我們帶來了更多的快捷與方便。特別是Ｍｉｃｒｏｓｏｆｔ Ｅｘｃｅｌ，它是辦公室自動化中非常重要的一款軟件，很多企事業(yè)單位都是依靠Ｅｘｃｅｌ進行數據管理。它不僅能方便地處理表格和進行圖形分析，其更強大的功能體現在對數據的自動化處理和計算上?！秾W生體質健康標準》已經實施了好幾年，體育教師在工作中，通過測試所得的學生原始成績數據量非常大。在錄入《學生體質健康標準》成績時，對體育教師來說是件繁瑣、枯燥的工作，利用Ｅｘｃｅｌ函數與相關功能，不僅可以達到事半功倍的效果，而且還可以避免人工查詢引起的錯誤。
2 相關函數介紹
（1） ＬＯＯＫＵＰ（）函數
?。蹋希希耍眨校ǎ?函數從單行或單列區(qū)域或數組返回值。
?。蹋希希耍眨?函數具有兩種語法形式：向量形式和數組形式。其中向量形式：
向量是只含一行或一列的區(qū)域。ＬＯＯＫＵＰ 的向量形式在單行區(qū)域或單列區(qū)域中查找值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。
函數語法：
ＬＯＯＫＵＰ（ｌｏｏｋｕｐ＿ｖａｌｕｅ， ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ， ［ｒｅｓｕｌｔ＿ｖｅｃｔｏｒ］）
?。蹋希希耍眨?函數向量形式語法具有以下參數 （參數：為操作、事件、方法、屬性、函數或過程提供信息的值。）：
・ ｌｏｏｋｕｐ＿ｖａｌｕｅ 必需。ＬＯＯＫＵＰ 在第一個向量中搜索的值。Ｌｏｏｋｕｐ＿ｖａｌｕｅ 可以是數字、文本、邏輯值、名稱或對值的引用。
・ ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 必需。只包含一行或一列的區(qū)域。ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中的值可以是文本、數字或邏輯值。
函數說明：
・ 如果 ＬＯＯＫＵＰ 函數找不到 ｌｏｏｋｕｐ＿ｖａｌｕｅ，則該函數會與 ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中小于或等于 ｌｏｏｋｕｐ＿ｖａｌｕｅ 的最大值進行匹配。
・ 如果 ｌｏｏｋｕｐ＿ｖａｌｕｅ 小于 ｌｏｏｋｕｐ＿ｖｅｃｔｏｒ 中的最小值，則 ＬＯＯＫＵＰ 會返回 ＃Ｎ／Ａ 錯誤值。
?。?） ＩＦ（）函數
功能：它執(zhí)行真假值判斷，根據邏輯計算的真假值，返回不同結果?？梢允褂煤瘮担桑茖抵岛凸竭M行條件檢測。
函數語法：
ＩＦ（ｌｏｇｉｃａｌ＿ｔｅｓｔ，ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ，ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ）
?。蹋铮纾椋悖幔欤撸簦澹螅?表示計算結果為ＴＲＵＥ或ＦＡＬＳＥ的任意值或表達式。
Ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ ｌｏｇｉｃａｌ＿ｔｅｓｔ 為ＴＲＵＥ 時返回的值。
?。郑幔欤酰澹撸椋妫撸妫幔欤螅?ｌｏｇｉｃａｌ＿ｔｅｓｔ 為ＦＡＬＳＥ時返回的值。
如果指定條件的計算結果為 ＴＲＵＥ，ＩＦ 函數將返回某個值；如果該條件的計算結果為 ＦＡＬＳＥ，則返回另一個值。例如，如果 Ａ１ 大于 １０，公式 ＝ＩＦ（Ａ１＞１０，＂大于 １０＂，＂不大于 １０＂） 將返回“大于 １０”，如果 Ａ１ 小于等于 １０，則返回“不大于 １０”。
函數說明：
最多可以使用 ６４ 個 ＩＦ 函數作為 ｖａｌｕｅ＿ｉｆ＿ｔｒｕｅ 和 ｖａｌｕｅ＿ｉｆ＿ｆａｌｓｅ 參數進行嵌套以構造更詳盡的測試。
如果 ＩＦ 的任意參數為數組，則在執(zhí)行 ＩＦ 語句時，將計算數組的每一個元素。
?。牛悖澹?還提供了其他一些函數，可使用這些函數根據條件來分析數據。例如，若要計算某單元格區(qū)域內某個文本字符串或數字出現的次數，可使用 ＣＯＵＮＴＩＦ 或 ＣＯＵＮＴＩＦＳ 工作表函數。若要計算基于某區(qū)域內一個文本字符串或一個數值的總和，可使用 ＳＵＭＩＦ 或 ＳＵＭＩＦＳ 工作表函數。
?。?） ＶＬＯＯＫＵＰ（）函數
功能：在表格數組的首列查找值，并由此返回表格數組當前行中其他列的值。
?。郑蹋希希耍眨?中的 Ｖ 表示垂直方向。當比較值位于需要查找的數據左邊的一列時，可以使用 ＶＬＯＯＫＵＰ，而不用 ＨＬＯＯＫＵＰ。
函數語法：
ＶＬＯＯＫＵＰ（ｌｏｏｋｕｐ＿ｖａｌｕｅ，ｔａｂｌｅ＿ａｒｒａｙ，ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ，ｒａｎｇｅ＿ｌｏｏｋｕｐ）
?。蹋铮铮耄酰穑撸觯幔欤酰?為需要在表格數組第一列中查找的數值。
?。蹋铮铮耄酰穑撸觯幔欤酰?可以為數值或引用。若 ｌｏｏｋｕｐ＿ｖａｌｕｅ 小于 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的最小值，ＶＬＯＯＫＵＰ 將返回錯誤值 ＃Ｎ／Ａ。
?。裕幔猓欤澹撸幔颍颍幔?為兩列或多列數據。需使用對區(qū)域的引用或區(qū)域名稱。
?。簦幔猓欤澹撸幔颍颍幔?第一列中的值是由 ｌｏｏｋｕｐ＿ｖａｌｕｅ 搜索的值。這些值可以是文本、數字或邏輯值。不區(qū)分大小寫。
Ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ 為 ｔａｂｌｅ＿ａｒｒａｙ 中待返回的匹配值的列序號。
?。茫铮欤撸椋睿洌澹撸睿酰?為 １ 時，返回 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數值；ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ 為 ２，返回 ｔａｂｌｅ＿ａｒｒａｙ 第二列中的數值，以此類推。如果 ｃｏｌ＿ｉｎｄｅｘ＿ｎｕｍ ：
小于 １，ＶＬＯＯＫＵＰ 返回錯誤值 ＃ＶＡＬＵＥ！。
大于 ｔａｂｌｅ＿ａｒｒａｙ 的列數，ＶＬＯＯＫＵＰ 返回錯誤值 ＃ＲＥＦ！。
Ｒａｎｇｅ＿ｌｏｏｋｕｐ 為邏輯值，指定希望 ＶＬＯＯＫＵＰ 查找精確的匹配值還是近似匹配值：
如果為 ＴＲＵＥ 或省略，則返回精確匹配值或近似匹配值。也就是說，如果找不到精確匹配值，則返回小于 ｌｏｏｋｕｐ＿ｖａｌｕｅ 的最大數值。
?。簦幔猓欤澹撸幔颍颍幔?第一列中的值必須以升序排序；否則 ＶＬＯＯＫＵＰ 可能無法返回正確的值。
函數說明：
在 ｔａｂｌｅ＿ａｒｒａｙ 第一列中搜索文本值時，確保 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數據沒有前導空格、尾隨空格、不一致的直引號（＇ 或 ＂）、彎引號（‘或“）或非打印字符。
在搜索數字或日期值時，確保 ｔａｂｌｅ＿ａｒｒａｙ 第一列中的數據未保存為文本值。否則，ＶＬＯＯＫＵＰ 可能返回不正確或意外的值。
如果 ｒａｎｇｅ＿ｌｏｏｋｕｐ 為 ＦＡＬＳＥ 且 ｌｏｏｋｕｐ＿ｖａｌｕｅ 為文本，則可以在 ｌｏｏｋｕｐ＿ｖａｌｕｅ 中使用通配符、問號 （？） 和星號 （*）。問號匹配任意單個字符；星號匹配任意字符序列。如果您要查找實際的問號或星號本身，請在該字符前鍵入波形符 （ ）。
（4） ＩＮＤＥＸ（）函數
功能：返回表格或區(qū)域中的值或值的引用。
函數 ＩＮＤＥＸ 有兩種形式：數組形式和引用形式。
函數語法：
ＩＮＤＥＸ（ａｒｒａｙ， ｒｏｗ＿ｎｕｍ， ［ｃｏｌｕｍｎ＿ｎｕｍ］）
Ａｒｒａｙ 必需。單元格區(qū)域或數組常量。
如果數組只包含一行或一列，則相對應的參數 ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ 為可選參數。
如果數組有多行和多列，但只使用 ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ，函數 ＩＮＤＥＸ 返回數組中的整行或整列，且返回值也為數組。
?。遥铮鳎撸睿酰?必需。選擇數組中的某行，函數從該行返回數值。如果省略 ｒｏｗ＿ｎｕｍ，則必須有 ｃｏｌｕｍｎ＿ｎｕｍ。
?。茫铮欤酰恚睿撸睿酰?可選。選擇數組中的某列，函數從該列返回數值。如果省略 ｃｏｌｕｍｎ＿ｎｕｍ，則必須有 ｒｏｗ＿ｎｕｍ。
函數說明：
如果同時使用參數 ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ，函數 ＩＮＤＥＸ 返回 ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ 交叉處的單元格中的值。
如果將 ｒｏｗ＿ｎｕｍ 或 ｃｏｌｕｍｎ＿ｎｕｍ 設置為 ０（零），函數 ＩＮＤＥＸ 則分別返回整個列或行的數組數值。
Ｒｏｗ＿ｎｕｍ 和 ｃｏｌｕｍｎ＿ｎｕｍ 必須指向數組中的一個單元格；否則，函數 ＩＮＤＥＸ 返回錯誤值 ＃ＲＥＦ！。
3 模型建立
3.1 新建工作表
在Ｅｘｃｅｌ工作簿中新建一工作表，命名為“學生體質健康標準登記表”，輸入相關成績，如圖１所示。
3.2 函數設置
根據我國教育部頒布的大學一到四年級學生體質健康標準，進行評分。以男生為例：
?。?） 計算身高體重得分
身高體重的標準簡化為如表１所示。
根據表１標準，首先：設置得分取值表，如圖２所示。
其中Ａ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，４４．７，５０．６，５７．５，５９．８｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）
?。拢保罚剑蹋希希耍眨校ǎ模?，｛０，５１．６，５８．２，６６，６８．６｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）
Ｃ１７＝ＬＯＯＫＵＰ（Ｄ３，｛０，５６．７，６３．８，７２．８，７５．８｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）
?。模保罚剑蹋希希耍眨校ǎ模?，｛０，６１．８，６９．８，７９．９，８３．２｝，｛＂５０＂，＂６０＂，＂１００＂，＂６０＂，＂５０＂｝）
其含義為：根據男１的體重，在4個不同層次的身高中查找其得分。其余復制公式即可。
其次：設置登記表中的得分公式
單擊Ｅ３單元格，輸入公式＝ＩＦ（Ｃ３＜＝１６０，Ａ１７，ＩＦ（Ｃ３＜＝１７０，Ｂ１７，ＩＦ（Ｃ３＜＝１８０，Ｃ１７，Ｄ１７））），其余復制公式即可。
其含義為：如果男１的身高小于１６０，則?。粒保返闹?，如果在１６０~１７０之間，則?。拢保返闹?，如果在１７０~１８０之間，則于Ｃ１７的值，否則?。模保返闹怠Ｆ浣Y果如圖３所示。
（2） 計算肺活量得分：肺活量指數部分得分表，如表２所示。
單擊Ｅ３單元格，輸入公式＝ＩＦ（Ｃ３＜＝１６０，Ａ１７，ＩＦ（Ｃ３＜＝１７０，Ｂ１７，ＩＦ（Ｃ３＜＝１８０，Ｃ１７，Ｄ１７））），其余復制公式即可。
單擊Ｇ３單元格，輸入公式＝Ｆ３／Ｄ３計算指數。單擊Ｈ３單元格，輸入公式，其余復制公式即可。
＝ＬＯＯＫＵＰ（Ｇ３，｛８４，８３，８２，８１，８０，７８，７７，７５，７３，７１，６８，６６，６４，６１，５８，５５｝，｛＂１００＂，＂９８＂，＂９６＂，＂９４＂，＂９２＂，＂９０＂，＂８７＂，＂８４＂，＂８１＂，＂７８＂，＂７５＂，＂７２＂，＂６９＂，＂６６＂，＂６３＂，＂６０＂｝）
其含義如上所述。結果如圖４所示。
?。?） 其余各項目的計算以此類推。
最終計算結果如圖５所示。
其中：總分Ｏ３＝ ＡＶＥＲＡＧＥ（Ｅ３，Ｈ３，Ｊ３，Ｌ３，Ｎ３），其余復制公式即可。
等級Ｐ３＝ＩＦ（Ｏ３＜６０，＂不及格＂，ＩＦ（Ｏ３＜７５，＂及格＂，ＩＦ（Ｏ３＜９０，＂良好＂，＂優(yōu)秀＂））），其余復制公式即可。
4 動態(tài)查詢
4.1 設置自動查詢卡
設置如圖６所示的動態(tài)查詢卡片，只要在Ｅ２５單元格中輸入學生的姓名，其余各項指指標自動依次出現在相應的位置。
單擊Ｅ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，２）
其含義是在Ｅ２５，Ｂ２：Ｐ５單元格區(qū)域第一列查找Ｅ２５單元格的值，找到后返加相同行第２列的值。其余含義以此類推。
單擊Ｇ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，３）
單擊Ｉ２６單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，４）
單擊Ｅ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，５）
單擊Ｇ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，６）
單擊Ｉ２７單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，７）
單擊Ｅ２８單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，８）
單擊Ｉ２８單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，９）
單擊Ｅ２９單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１０）
單擊Ｉ２９單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１１）
單擊Ｅ３０單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１２）
單擊Ｉ３０單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１３）
單擊Ｇ３１單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１４）
單擊Ｉ３１單元格，輸入公式＝ＶＬＯＯＫＵＰ（Ｅ２５，Ｂ２：Ｐ５，１５）
4.2 填加控件按鈕
單擊Ｅ２５單元格，輸入公式＝ＩＮＤＥＸ（Ｂ３：Ｂ５，Ｃ２５），其含義是在姓名這一列中查找Ｃ２５單元格中的值，如果為１，返回男１，其余類推。
在菜單的空白處，右擊，彈出的快捷菜單中，選擇“窗體”，在打開的窗體工具箱中，選擇“列表框”， 如圖７所示。右擊“列表框”控件，在彈出的對話框中選擇“設置控件格式”命令，如圖８所示。在彈出的“設置控件格式”對話框中，作如圖９的設置。
