1、Excel 中自定义函数实例剖析稍有 Excel 使用经验的朋友，都知道 Excel 内置函数的快捷与方便，它大大增强了 Excel 数据计算与分析的能力。不过内置的函数并不一定总是能满足我们的需求，这时，就可以通过定义自己的函数来解决问题。 一、认识 VBA 在介绍自定义函数的具体使用之前，不得不先介绍一下 VBA，原因很简单，自定义函数就是用它创建的。VBA 的全称是 Visual Basic for Application，它是微软最好的通用应用程序脚本编程语言，它的特点是容易上手，而且功能非常强大。 在微软所有的 Office 组件中，如 Word、Access、Powerpoint
2、等等都包含 VBA，如果你能在一种 Office 组件中熟练使用 VBA，那么在其它组件中使用 VBA 的原理是相通的。 Excel 中 VBA 主要有两个用途，一是使电子表格的任务自动化；二是可以用它创建用于工作表公式的自定义函数。 由此可见，使用 Excel 自定义函数的一个前提条件是对 VBA 基础知识有所了解，如果读者朋友有使用 Visual Basic 编程语言的经验，那么使用 VBA时会感觉有很多相似之处。如果读者朋友完全是一个新手，也不必太担心，因为实际的操作和运用是很简单的。 二、什么时候使用自定义函数？ 有些初学 Excel 的朋友可能有这样疑问： Excel 已经内置了这么
3、多函数，我还有必要创建自己的函数吗？ 回答是肯定的。原因有两个，它们也正好可以解释什么时候使用 Excel 自定义函数的问题。 第一，自定义函数可以简化我们的工作。 有些工作，我们的确可以在公式中组合使用 Excel 内置的函数来完成任务，但是这样做的一个明显缺点是，我们的公式可能太冗长、繁琐，可读性很差，不易于管理，除了自己之外别人可能很难理解。这时，我们可以通过使用自定义函数来简化自己的工作。 第二，自定义函数可以满足我们个性化的需要，可以使我们的公式具有更强大和灵活的功能。 实际工作的要求千变万化，仅使用 Excel 内置函数常常不能圆满地解决问题，这时，我们就可以使用自定义函数来满足实
4、际工作中的个性化需求。 上面的讲述比较抽象，我们还是把重点放在实际例子的剖析上，请大家在实际例子中进一步体会，进而学会在 Excel 中创建和使用自定义函数。三、自定义函数实例剖析 下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的 VBA 基础。 假如你完全没有 VBA 基础也不要紧，当学习完实例后，若觉得自定义函数在自己以后的工作中可能用到，那么再去补充相应的 VBA 基础也不迟。 (一) 计算个人调节税的自定义函数 任务 假设个人调节税的收缴标准是：工资小于等于 800 元的免征调节税，工资 800 元以上至 1500 元的超过部分按 5的税率征收，1
5、500 元以上至 2000元的超过部分按 8的税率征收，高于 2000 元的超过部分按 20的税率征收。 分析 假设 Sheet1 工作表的 A、B、C、D 列中分别存放“姓名”、“总工资”、“ 调节税”、“税后工资”字段数据，如图 1 所示。 图 1 平时使用较多的方法是借助嵌套使用 IF 函数计算，比如在 C2 单元格输入公式“=IF(B2 2000 TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3 End Select End Function 图 23. 函数自定义完成后，选择菜单“文件关闭并返回到
6、Microsoft Excel”命令，返回到 Excel 工作表窗口，在 C2 单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图 3)。 图 3 4. 从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化 r1、r2、r3 的值即可。 通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件另存为 ”命令，打开“另存为” 对话框，选择保存类型为“Mircosoft Excel 加载宏 ”，然后
7、输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏(图 4)。然后选择菜单“ 工具加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后( 图 5)，就可以在本机上的所有工作薄中使用该自定义函数了。图 4 图 5 如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它电脑上加载宏的默认保存位置即可。 说明：Windows XP 系统下加载宏文件的默认保存位置为：C:Documents and Settingszunyue(用户帐户)Application DataMicrosoftAddIns 文件夹。 (二) 计算
8、奖金的自定义函数 任务 为了促进销售人员的工作积极性，销售部门经理制定了销售业绩奖金制度，奖金发放的标准奖金率如下：月销售额小于等于 2800 元的奖金率为4，月销售额为 2800 元至 7900 元的奖金率为 7，月销售额为 7900 元至 15000 元的奖金率为 10，月销售额为 15000 元至 30000 元的奖金率为13，月销售额为 30000 元至 50000 元的奖金率为 16，月销售额大于 50000 元的奖金率为 19。同时，为了鼓励员工持续地为公司工作，工龄越长对奖金越有利，具体规定为：参与计算的奖金率等于标准奖金率加上工龄一半的百分数。比如一个工龄为 5 年的员工，标准
9、奖金率为 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%)”可以进行计算。 但是，该公式的一些弊端很明显：一是公式看起来太繁琐、不容易理解，而且 IF 函数最多只能嵌套 7 层，万一奖金
10、率超过 7 个，那么这个方法就无能为力了。 另一方面，由于没有考虑工龄，所以该方法不能算是解决问题了，如果我们把工龄融入到上述公式中，这样公式就会显得更加冗长繁琐，以后的管理与调整都很不方便。 使用自定义函数 下面我们看看利用 Excel 自定义函数进行计算的全过程，有了实例一的基础，相信大家理解起来更容易了。不过这里与实例一有一个明显的差别是，该自定义函数使用了 2 个参数，请大家注意体会。 1. 在上述 Excel 工作表中，选择菜单 “工具宏Visual Basic 编辑器”命令，打开 Visual Basic 窗口，然后选择菜单“ 插入模块”命令，插入一个名为“模块 1”的模块。 2. 接着在模块编辑窗口中输入自定义函数的代码如下(图 7)： Function REWARD(sales, years) As Double Const r1 As Double = 0.04 Const r2 As Double = 0.07 Const r3 As Double = 0.1
