巧用vba突破Excel中If函數(shù)7層嵌套的限制——Excel在體育測試中的應(yīng)用

☆ 張秀虎

（海州高級中學(xué)，江蘇連云港 222023）

一、問題

學(xué)校每學(xué)期都要對學(xué)生進(jìn)行體能考查測試，體能考查測試項目繁多（如圖1），每個項目的評分標(biāo)準(zhǔn)都不相同，同項目的男女生的標(biāo)準(zhǔn)也不同。如50米項目測試的等級有15個（如圖2）。

圖1

圖2

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

表1 現(xiàn)場考試成績表

二、問題解決過程

1.建立評分標(biāo)準(zhǔn)

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

2.錄入現(xiàn)場考試成績

將sheet2工作薄的名稱改為“現(xiàn)場成績”，在此工作薄內(nèi)，按照表1的格式輸入每位學(xué)生的現(xiàn)場考試成績，其中，每位學(xué)生的性別、考試項目、考試成績?nèi)袛?shù)據(jù)不可缺少，其他信息可以根據(jù)需要進(jìn)行添加，例如：序號、班級、年級，等等。

3.設(shè)計自定義函數(shù)

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

函數(shù)參數(shù)說明：x,y,z,d1,d2都定義為單元格類型，x,y,z分別為性別、考試項目、考試成績的單元格地址，d1,d2分別為評分標(biāo)準(zhǔn)區(qū)間的左上角、右下角單元格地址。

函數(shù)功能：根據(jù)性別、考試項目、考試成績查詢評分標(biāo)準(zhǔn)，核算出相應(yīng)成績得分，函數(shù)返回值定義為雙精度類型。

代碼如下：

4.核算考試得分

打開“現(xiàn)場成績”工作薄，定位第一位學(xué)生的得分單元格，輸入核算成績得分函數(shù)Cjdf，并填入相應(yīng)參數(shù)。如表一中“杜知遠(yuǎn)”同學(xué)，性別為c3,考試項目為d3，考試成績?yōu)閑3，其得分f3單元格輸入：=Cjdf(C3,D3,E3,評分標(biāo)準(zhǔn)!A$3,評分標(biāo)準(zhǔn)!C$47)，回車確認(rèn)，核算其得分。其他學(xué)生項目得分，直接復(fù)制f3單元格公式，即可核算。

三、Cjdf函數(shù)的移植

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

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

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

四、后記

以上代碼在Excel2000和Excel2003中都調(diào)試成功，根據(jù)處理任務(wù)的不同，上述Cjdf函數(shù)代碼進(jìn)行適當(dāng)修改，就可應(yīng)用于不同事務(wù)要求。