Excel 2007中自定义函数实例剖析.docx

《Excel 2007中自定义函数实例剖析.docx》由会员分享，可在线阅读，更多相关《Excel 2007中自定义函数实例剖析.docx（9页珍藏版）》请在第一文库网上搜索。

1、Exce12007中自定义函数实例析一、认识VBA在介绍自定义函数的具体使用之前，不得不先介绍一下VBA,原因很简单，自定义函数就是用它创建的。VBA的全称是ViSUaIBaSiCfOrAPPIiCation,它是微软最好的通用应用程序脚本编程语言，它的特点是容易上手，而且功能非常强大。在微软所有的Office组件中，如Word、AccessPoWerPoint等等都包含VBA,如果你能在一种OffiCe组件中熟练使用VBA,那么在其它组件中使用VBA的原理是相通的。EXCe1中VBA主要有两个用途，一是使电子表格的任务自动化；二是可以用它创建用于工作表公式的自定义函数。由此可见，使用Exce

2、1自定义函数的一个前提条件是对VBA基础知识有所了解，如果读者朋友有使用ViSUa1BaSiC编程语言的经验，那么使用VBA时会感觉有很多相似之处。如果读者朋友完全是一个新手，也不必太担心，因为实际的操作和运用是很简单的。二、什么时候使用自定义函数？有些初学Exce1的朋友可能有这样疑问：Exce1已经内置了这么多函数，我还有必要创建自己的函数吗？回答是肯定的。原因有两个,它们也正好可以解释什么时候使用Exce1自定义函数的问题。第一，自定义函数可以简化我们的工作。有些工作，我们的确可以在公式中组合使用EXCe1内置的函数来完成任务，但是这样做的一个明显缺点是，我们的公式可能太冗长、繁琐，可读

3、性很差，不易于管理，除了自己之外别人可能很难理解。这时，我们可以通过使用自定义函数来简化自己的工作。第二，自定义函数可以满足我们个性化的需要，可以使我们的公式具有更强大和灵活的功能。实际工作的要求千变万化，仅使用Exce1内置函数常常不能圆满地解决问题，这时，我们就可以使用自定义函数来满足实际工作中的个性化需求。上面的讲述比较抽象，我们还是把重点放在实际例子的剖析上，请大家在实际例子中进一步体会，进而学会在Exce1中创建和使用自定义函数。下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的VBA基础。假如你完全没有VBA基础也不要紧，当学习完实例后，若觉得自

4、定义函数在自己以后的工作中可能用至J,那么再去补充相应的VBA基础也不迟。(一)计算个人调节税的自定义函数任务假设个人调节税的收缴标准是:工资小于等于800元的免征调节税，工资800元以上至1500元的超过部分按5%的税率征收，1500元以上至2000元的超过部分按8%的税率征收，高于2000元的超过部分按20%的税率征收。分析名勇梅莉佳洁红姓王李钟刘朱罗假设Sheet1工作表的A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图1所示。调节税税后工资总工资750.001,250.001,750.002,250.003,000.001,300.00图1平时使用

5、较多的方法是借助嵌套使用IF函数计算，比如在C2单元格输入公式u=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-2000)*0.2),然后通过填充柄复制公式到C列的其余单元格。既然公式能够解决问题，为什么还要使用自定义函数的方法呢？正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那么公式可能就无能为力了。使用自定义函数下面就通过此

6、例介绍使用自定义函数的全过程，即使是初学Exce1的朋友，也会感觉其操作实际上是非常简单的。1 .为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具一宏一ViSUa1BaSiC编辑器”命令(或按下键盘A1t+F11组合键)，打开ViSUa1BaSiC窗口，我们将在这里自定义函数。2 .进入ViSUaIBaSiC窗口后，选择菜单“插入T模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)：FunctionTAX(sa1ary)Constr1AsDoub1e=0.05Constr2AsDoub1e=0.08Constr3AsDoub1e=0.

7、2Se1ectCasesa1aryCase 15 =800TAX=OCase 16 =1500TAX=(sa1ary-800)*r1Case 17 2000TAX=(1500-800)*r1+(2000-1500)*r2+(sa1ary-2000)*r3EndSe1ect图23 .函数自定义完成后，选择菜单“文件T关闭并返回到MiCroSoftExce1”命令，返回到EXCe1工作表窗口，在C2单元格中输入公式“=TAX（B2）”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算（图3）oC2笈jtax(B2)4

8、-AIBCD5 .从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化门、2、r3的值即可。通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件一另存为”命令，打开“另存为”对话框，选择保存类型为“MircosoftExce1加载宏”，然后输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏（图4）。然后选择菜单“工具一加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后（图5）,就可以在本机上的所有工作薄中使用该自定义函数了。图5如果想要在其它机

9、器上使用该自定义函数，只要把上面的加载宏文件复制到其它电脑上加载宏的默认保存位置即可。说明：WindowsXP系统下加载宏文件的默认保存位置为：C:DocumentsandSettingSZUnyUe(用户帐户App1icationDataMicrosoftAddIns文件夹。任务为了促进销售人员的工作积极性，销售部门经理制定了销售业绩奖金制度,奖金发放的标准奖金率如下:月销售额小于等于2800元的奖金率为4%,月销售额为2800元至7900元的奖金率为7%,月销售额为7900元至15000元的奖金率为10%，月销售额为15000元至30000元的奖金率为13%,月销售额为30000元至500

10、00元的奖金率为16%,月销售额大于50000元的奖金率为19%。同时，为了鼓励员工持续地为公司工作，工龄越长对奖金越有利，具体规定为：参与计算的奖金率等于标准奖金率加上工龄一半的百分数。比如一个工龄为5年的员工，标准奖金率为7%时，参与计算的奖金率则为9.5%=7%+(52)%o分析首先，我们在Exce12003中制作好如图6的Sheet1工作表，开始分析计算的方法。AB!CiD然丽名涵辉佳一美销售业绩工龄奖金3,998.00.26,520.00.耳17,990.00.ZsT9,400.00耳75,000.00632,000.00HT8邓小明1,800.001图6如果不考虑工龄对奖金率的影响

11、，那么可以利用嵌套使用IF函数,在D2单元格输入公式m=IF(B2=2800,B2*4%,IF(B2=7900,B2*7%,IF(B2=15000,B2*10%,IF(B2v=30000,B2*13%,IF(B2v=50000,B2*16%,B2*19%)”可以进行计算。但是，该公式的一些弊端很明显：一是公式看起来太繁琐、不容易理解，而且IF函数最多只能嵌套7层，万一奖金率超过7个，那么这个方法就无能为力了。另一方面，由于没有考虑工龄，所以该方法不能算是解决问题了，如果我们把工龄融入到上述公式中，这样公式就会显得更加冗长繁琐，以后的管理与调整都很不方便。使用自定义函数下面我们看看利用Exce1

12、自定义函数进行计算的全过程，有了实例一的基础，相信大家理解起来更容易了。不过这里与实例一有一个明显的差别是，该自定义函数使用了2个参数，请大家注意体会。1 .在上述EXCe1工作表中，选择菜单“工具T宏TViSUa1BaSiC编辑器”命令，打开ViSUa1BaSiC窗口，然后选择菜单“插入一模块”命令，插入一个名为“模块1”的模块。2 .接着在模块编辑窗口中输入自定义函数的代码如下(图7)：FunctionREWARD(sa1es,years)AsDoub1eConstr1AsDoub1e=0.04Constr2AsDoub1e=0.07Constr3AsDoub1e=0.1Constr4As

13、Doub1e=0.13Constr5AsDoub1e=0.16Constr6AsDoub1e=0.19Se1ectCasesa1esCaseIs=2800REWARD=sa1es*(r1+years/200)CaseIs=7900REWARD=sa1es*(r2+years/200)CaseIs=15000REWARD=sa1es*(r3+years/200)CaseIs=30000REWARD=sa1es*(r4+years/200)CaseIs50000REWARD=sa1es*(r6+years/200)EndSe1ectEndFunction1图U1,：拾9，/包&行24,列图73 .

14、从代码可以看出，我们自定义了一个名为REWARD的函数，它包含两个参数：销售额SaIeS和工龄years。常量r1至r6分别存放着各个等级的奖金率，这样处理的好处是当奖金率调整时，修改非常方便。同时，函数的层次结构比前面的公式清晰，让人容易理解函数的功能。此外，当奖金率超过7个时，用自定义函数的方法仍然可以轻松处理。.接下来用该自定义函数进行具体的计算。选择菜单“文件一关闭并返回到MicrosoftExce1”命令，关闭Visua1BaSiC窗口,返回Exce1工作表。选中D2单元格，在其中输入“=reward(B2,C2)”,回车后就算出了第一个员工的奖金，然后利用公式填充柄复制该公式到后面

15、的单元格，即可完成对其它员工奖金的计算(图8)o然丽明名涵辉佳一美华小姓王张李朱蔡王邓销售业绩工龄,奖金I3,998.OO2|319.8416,520.002521.6017,990.0052,788.459,400.0031,081.0075,000.00616,500.0032,000.0025,440.00：亡的Kii1181.00图8如果该自定义函数需要在其它工作薄或其它机器上使用，仿照实例一的操作方法进行即可。四、总结我们通过两个典型的实例讲述了EXCe1中自定义函数使用的全过程,相信大家都已经会到，其操作过程还是相当简单的。如果你觉得自己的工作可能需要自定义函数，想进一步学好提高使用自定义函数的水平，笔者想给出如下几点建议。第一点、尽力全面熟练地掌握EXCe1内置的函数。能用内置函数妥善解决的问题，就不必使用自定义函