巧用vba突破Excel中If函數7層嵌套的限制——Excel在體育測試中的應用

☆ 張秀虎

（海州高級中學，江蘇連云港 222023）

一、問題

學校每學期都要對學生進行體能考查測試，體能考查測試項目繁多（如圖1），每個項目的評分標準都不相同，同項目的男女生的標準也不同。如50米項目測試的等級有15個（如圖2）。

圖1

圖2

假設現有一個班級的學生測試了50米跑，現場測試的成績如表1。由于篇幅限制，表1中只列出了8位學生的信息。依據50米的評分標準，如何核算出每位學生的50米考試成績得分呢？這常讓人想到的是運用If函數來核算考試成績得分。可是50米的評分標準有15個等級，大致需要15個If函數，外加性別的判斷，以及多種考試項目的判斷，共需要17個If函數。可惜的是Excel 2003及以下版本支持使用If函數的個數最多達7個，所以，沒有辦法解決這個問題。不過目前Excel 2007版本允許最多嵌套64個IF函數。可惜的是即使允許超7層，7層以上的嵌套對一般用戶來說編寫起來非常麻煩，容易出錯。能否利用Vba來設計一個函數，只需要輸入少量的參數，就能輕易地實現呢？接下來，為大家介紹如何運用vba技術解決多層判斷的問題。

表1 現場考試成績表

二、問題解決過程

1.建立評分標準

使用Excel 2003或Excel 2000軟件，新建一個xls電子表格，將sheet1工作薄的名稱改為“評分標準”，在此工作薄內，輸入圖2的評分標準詳細信息，如果有其他考試項目，也將其評分標準詳細信息錄入此工作薄。工作薄中的信息按照“項目”、“分值”兩個關鍵字從大到小的順序排列，注意必須給每個項目添加分值為0的評分標準。

2.錄入現場考試成績

將sheet2工作薄的名稱改為“現場成績”，在此工作薄內，按照表1的格式輸入每位學生的現場考試成績，其中，每位學生的性別、考試項目、考試成績三列數據不可缺少，其他信息可以根據需要進行添加，例如：序號、班級、年級，等等。

3.設計自定義函數

依次選擇菜單中的工具→宏→Visual Basic編輯器命令，打開Vba代碼編輯窗口，選擇菜單中的插入→模塊命令，設計一個核算考試成績得分的函數Cjdf。其函數格式為：

函數參數說明：x,y,z,d1,d2都定義為單元格類型，x,y,z分別為性別、考試項目、考試成績的單元格地址，d1,d2分別為評分標準區間的左上角、右下角單元格地址。

函數功能：根據性別、考試項目、考試成績查詢評分標準，核算出相應成績得分，函數返回值定義為雙精度類型。

代碼如下：

4.核算考試得分

打開“現場成績”工作薄，定位第一位學生的得分單元格，輸入核算成績得分函數Cjdf，并填入相應參數。如表一中“杜知遠”同學，性別為c3,考試項目為d3，考試成績為e3，其得分f3單元格輸入：=Cjdf(C3,D3,E3,評分標準!A$3,評分標準!C$47)，回車確認，核算其得分。其他學生項目得分，直接復制f3單元格公式，即可核算。

三、Cjdf函數的移植

將Cjdf移植至其它程序中，可以將其以“加載宏”類型保存，再在其他程序中加載宏，就可以將Cjdf函數移植，避免函數代碼的重復編寫工作。主要通過Excel中“文件菜單→另存為”命令，選擇保存類型“Microsoft Of?fice Excel加載宏”，保存宏，并將其保存在“C:Docu?mentsand SettingsAdministratorApplication DataMicro?softAddIns”目錄下。

在需要使用Cjdf函數的工作表中，選擇“工具菜單→加載宏”命令，彈出加載宏窗口，通過“瀏覽”命令瀏覽本地硬盤，找到保存的宏，將其加載至宏窗口中，然后選擇宏，將Cjdf函數加載到工作表中。

要正常使用函數Cjdf函數，在打開工作表時必須啟用宏，并將宏的安全級別設置為“中”，函數Cjdf才會生效。

四、后記

以上代碼在Excel2000和Excel2003中都調試成功，根據處理任務的不同，上述Cjdf函數代碼進行適當修改，就可應用于不同事務要求。