《自定义函数》由会员分享，可在线阅读，更多相关《自定义函数（15页珍藏版）》请在金锄头文库上搜索。
1、VBA 自定义函数一、什么是函数一个函数就是预定的一个计算公式，可以快速地完成一个特定的计算。如：sum，indexetc。二、自定义函数自定义函数是用户自己编写的函数；一个自定义函数就是一个function过程；编写了一个function过程就相当于编写了一个自定义函数。三、为什么要创建自定义函数1.简化工作，解决需要较多辅助列或使用较长公式才能完成的计算问题，比如后面的提取工作簿名称的应用。2.解决不能用现有的工作簿函数完成的计算问题，如统计指定背景颜色的单元格个数。下面通过一个例子来学习简单的编写自定义函数例：下面表格中需要计算一些三角形的面积B列是底边长，C列是高，要求在D列通过公式计
2、算三角形面积。（通常我们会在D3单元格用公式 =B3*C3/2 来计算，然后把这个公式向D列下方拖动复制，得到其他公式。这只是一个简单的例子，通过它来学习编写简单的自定义函数）1、打开VBA窗口按ALT+F11调出VBA窗口，插入一个用户模块。2、编写代码通常自定义函数是用function命令开始的，在这个命令后面给它指定一个名字和参数把下面这个自定义函数代码粘贴到刚插入的用户模块中就可以使用了。Function sjxmj(di, gao)sjxmj = di * gao / 2End Function这段代码非常简单只有三行，先看第一行，其中sjxmj是自己取的函数名字，括号中的是参数，也
3、就是变量，di表示“底边长”，gao表示“高”，两个参数用逗号隔开。再看第二行，这是计算过程，将di*gao/2这个公式赋值给sjxmj，即自定义函数的名字。再看第三行，它是与第一行成对出现的，当你手工输入第一行的时候，第三行的end function就会自动出现，表示自定义函数的结束。3、使用自定义函数回到EXCEL窗口，我们在D3单元格中输入公式 =sjxmj(b3,c3) ，就会得到这一行的三角形面积了，它的使用方法同内置函数完全一样。通过上面例子可以了解自定义函数的编写和使用方法，下面再介绍一个稍微复杂点的自定义函数。比如下面统计成绩的表格，需要根据A1:D7的成绩表，统计出两门功能都
4、在90分以上的学生人数。通常这种时候需要在H3单元格使用数组公式 =SUM(IF($B$2:$B$7=G3)*($D$2:$D$7=90)*($E$2:$E$7=90),1,0)大家可以看到在H3单元格中的公式比较长，理解起来也有一定难度。我们通过自定义函数也可以得到正确结果，函数代码如下：Function 统计(a, b, c, d, e)For i = 1 To a.Rows.CountIf b = a.Cells(i, 1) And a.Cells(i, c) = e And a.Cells(i, d) = e Then统计 = 统计 + 1End IfNextEnd Function这
5、个函数用了五个参数（因为涉及到一个区域和四个条件）参数a表示要统计的区域，在此例中为B2:E7参数b表示要统计的是哪一个班级，在此例中为G3单元格参数c表示数学成绩相对于区域第一列向右的列数，在此例中为3参数d表示数学成绩相对于区域第一列向右的列数，在此例中为4参数e表示分数，在此例中为90分提示：要注意参数c和d“相对”于“区域”的列数，并非是从A列开始向右的列数。把上面这段代码也粘贴到用户模块中就可以使用了回到EXCEL窗口，在H3单元格中输入公式 =统计($B$2:$E$7,G3,3,4,90) 就可以显示正确结果了。Function 统计2(a, b)For i = 1 To a.Ro
6、ws.CountIf b = a.Cells(i, 1) And a.Cells(i, 3) = 90 And a.Cells(i, 4) = 90 Then统计 = 统计 + 1End IfNextEnd Function在表格中的H3单元格中输入公式 =统计2（$B$2:$E$7,G3） 就可以了。从上面可以看出，自定义函数可以使用“汉字”做为函数的名字，方便记忆，也可以根据实际情况对参数进行简化。如果我们的成绩表格式是固定的，各科目成绩位置相对于区域也是固定的，而且要统计的分数也是固定的90分，就可以在自定义函数中将参数的数量减少到两个，如下：(一) 计算个人调节税的自定义函数任务假设个
7、人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800元以上至1500元的超过部分按5的税率征收，1500元以上至2000元的超过部分按8的税率征收，高于2000元的超过部分按20的税率征收。分析假设Sheet1工作表的A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图1所示。图 1平时使用较多的方法是借助嵌套使用IF函数计算，比如在C2单元格输入公式“=IF(B2=800,0,IF(B2=1500,(B2-800)*0.05,IF(B2=2000,700*0.05+(B2-1500)*0.08,700*0.05+500*0.08+(B2-200
8、0)*0.2)”，然后通过填充柄复制公式到C列的其余单元格。既然公式能够解决问题，为什么还要使用自定义函数的方法呢？正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那么公式可能就无能为力了。使用自定义函数下面就通过此例介绍使用自定义函数的全过程，即使是初学Excel的朋友，也会感觉其操作实际上是非常简单的。1. 为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具宏Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)
9、，打开Visual Basic窗口，我们将在这里自定义函数。2. 进入Visual Basic窗口后，选择菜单“插入模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)：Function TAX(salary)Const r1 As Double = 0.05Constr2As Double = 0.08Const r3 As Double = 0.2Select Case salaryCase Is = 800TAX = 0Case Is = 1500TAX = (salary - 800) * r1Case Is 2000TAX = (1500 - 800) * r1 +
10、 (2000 - 1500) * r2 + (salary - 2000) * r3End SelectEnd Function图 23. 函数自定义完成后，选择菜单“文件关闭并返回到MicrosoftExcel”命令，返回到Excel工作表窗口，在C2单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图3)。图 34. 从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化r1、r2、r3的值即可。通常，自定义的函数只能在当前工作
11、薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件另存为”命令，打开“另存为”对话框，选择保存类型为“Mircosoft Excel加载宏”，然后输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏(图4)。然后选择菜单“工具加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后(图5)，就可以在本机上的所有工作薄中使用该自定义函数了。图 4图 5如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它电脑上加载宏的默认保存位置即可。说明：Windows XP系统下加载宏文件的默认保存位置为：C:Document
12、s and Settingszunyue(用户帐户)Application DataMicrosoftAddIns文件夹。(二) 计算奖金的自定义函数任务为了促进销售人员的工作积极性，销售部门经理制定了销售业绩奖金制度，奖金发放的标准奖金率如下：月销售额小于等于2800元的奖金率为4，月销售额为2800元至7900元的奖金率为7，月销售额为7900元至15000元的奖金率为10，月销售额为15000元至30000元的奖金率为13，月销售额为30000元至50000元的奖金率为16，月销售额大于50000元的奖金率为19。同时，为了鼓励员工持续地为公司工作，工龄越长对奖金越有利，具体规定为：参与
13、计算的奖金率等于标准奖金率加上工龄一半的百分数。比如一个工龄为5年的员工，标准奖金率为7时，参与计算的奖金率则为9.5%=7%+(5/2)%。分析首先，我们在Excel2003中制作好如图6的Sheet1工作表，开始分析计算的方法。图 6如果不考虑工龄对奖金率的影响，那么可以利用嵌套使用IF函数，在D2单元格输入公式“=IF(B2=2800,B2*4%,IF(B2=7900,B2*7%,IF(B2=15000,B2*10%,IF(B2=30000,B2*13%,IF(B2=50000,B2*16%,B2*19%)”可以进行计算。但是，该公式的一些弊端很明显：一是公式看起来太繁琐、不容易理解，而
14、且IF函数最多只能嵌套7层，万一奖金率超过7个，那么这个方法就无能为力了。另一方面，由于没有考虑工龄，所以该方法不能算是解决问题了，如果我们把工龄融入到上述公式中，这样公式就会显得更加冗长繁琐，以后的管理与调整都很不方便。使用自定义函数下面我们看看利用Excel自定义函数进行计算的全过程，有了实例一的基础，相信大家理解起来更容易了。不过这里与实例一有一个明显的差别是，该自定义函数使用了2个参数，请大家注意体会。1. 在上述Excel工作表中，选择菜单“工具宏Visual Basic编辑器”命令，打开Visual Basic窗口，然后选择菜单“插入模块”命令，插入一个名为“模块1”的模块。2. 接着在模块编辑窗口中输入自定义函数的代码如下(图 7)：Function REWARD(sales, years) As DoubleConst r1 As Double = 0.04Constr2As Double = 0.07Const r3 As Double = 0.1Const r4 As Double = 0.13Const r5 As Double = 0.16Const r6 As Double = 0.19Select Case salesCase Is = 2800REWARD = sales * (r1 + years / 200)Case Is =
