巧用vba突破Excel中If函数7层嵌套的限制.docVIP

巧用vba突破Excel中If函数7层嵌套的限制 　　摘 要：大家都知道Excel 2003及以下版本只支持使用If函数7层嵌套的特性。在日常事务处理过程中，常常遇到需要使用If函数7层以上嵌套的情况，这让许多事务变得棘手，不得已有时只能放弃使用Excel软件，改用其他数据处理软件。这给习惯使用Excel软件的人们带来很多不便。本文将通过一个实例讲解如何运用VBA技术解决超7个If语句的问题。 　　关键词：VBA ；函数 　　中图分类号：G434 文献标识码：A 文章编号：1671-7503（2013）21-0078-03 　　一、问题 　　假设现有一个班级的学生测试了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 现场考试成绩表 　　[建档号＼姓名＼性别＼考试项目＼考试成绩＼得分＼132001210＼杜知远＼男＼50米＼5.6＼＼132001210＼胡佳仪＼女＼50米＼10.6＼＼132001211＼李晓婧＼女＼50米＼9＼＼132001211＼左梓民＼男＼50米＼10.5＼＼132001212＼宋靖童＼女＼50米＼3.4＼＼132001212＼王璇＼女＼50米＼3.8＼＼132001213＼徐振华＼男＼50米＼9.6＼＼132001214＼赵帅＼男＼50米＼7.4＼＼] 　　二、问题解决过程 　　1.建立评分标准 　　使用Excel 2003或Excel 2000软件，新建一个xls电子表格，将sheet1工作薄的名称改为“评分标准”，在此工作薄内，输入图2的评分标准详细信息，如果有其他考试项目，也将其评分标准详细信息录入此工作薄。工作薄中的信息按照“项目”、“分值”两个关键字从大到小的顺序排列，注意必须给每个项目添加分值为0的评分标准。 　　2.录入现场考试成绩 　　将sheet2工作薄的名称改为“现场成绩”，在此工作薄内，按照表1的格式输入每位学生的现场考试成绩，其中，每位学生的性别、考试项目、考试成绩三列数据不可缺少，其他信息可以根据需要进行添加，例如：序号、班级、年级，等等。 　　3.设计自定义函数 　　依次选择菜单中的工具→宏→Visual Basic编辑器命令，打开Vba代码编辑窗口，选择菜单中的插入→模块命令，设计一个核算考试成绩得分的函数Cjdf。其函数格式为： 　　Public Function Cjdf（x， y， z， d1， d2 As Range） As Double 　　函数代码 　　End Function 　　函数参数说明：x， y， z， d1， d2 都定义为单元格类型，x，y，z分别为性别、考试项目、考试成绩的单元格地址，d1，d2分别为评分标准区间的左上角、右下角单元格地址。 　　函数功能：根据性别、考试项目、考试成绩查询评分标准，核算出相应成绩得分，函数返回值定义为双精度类型。 　　代码如下： 　　定义三个单元格变量，用于存放学生性别、考试项目、考试成绩的单元格地址 　　Dim xb， xm， cj As Range 　　Set xb = x 将x、y、z单元格地址赋予xb、xm、cj 　　Set xm = y 　　Set cj = z 　　定义两个单元格变量，存放评分标准的左上角和右下角单元格地址 　　Dim r1， r2 As Range 　　Set r1 = d1 将d1、d2单元格地址赋予r1、 r2 　　Set r2 = d2 　　定义两个整型变量，存放评分标准的起始行和结尾行 　　Dim starh， endh As Integer 　　starh = r1.Row 将评分标准的起始行号、结尾行号赋予starh、endh 　　endh = r2.Row 　　Dim xiangmu As String 　　定义三个实型变量，分别用于记录前一标准、当前标准、下一标准 　　Dim front， Current， back As Double 　　Dim xmdf As Doub