excel中自定义函数实例剖析

1、excel中自定义函数实例剖析 2008-04-04 21:17Excel内置的函数并不一定总是能满足我们的需求，这时，就可以通过定义自己的函数来解决问题 稍有Excel使用经验的朋友，都知道Excel内置函数的快捷与方便，它大大增强了Excel数据计算与分析的能力。不过内置的函数并不一定总是能满足我们的需求，这时，就可以通过定义自己的函数来解决问题。文章末尾提供.xls文件供大家下载参考。一、认识VBA 在介绍自定义函数的具体使用之前，不得不先介绍一下VBA，原因很简单，自定义函数就是用它创建的。VBA的全称是Visual Basic for Application，它是微软最好的通用应用程

2、序脚本编程语言，它的特点是容易上手，而且功能非常强大。在微软所有的Office组件中，如Word、Access、Powerpoint等等都包含VBA，如果你能在一种Office组件中熟练使用VBA，那么在其它组件中使用VBA的原理是相通的。Excel中VBA主要有两个用途，一是使电子表格的任务自动化；二是可以用它创建用于工作表公式的自定义函数。由此可见，使用Excel自定义函数的一个前提条件是对VBA基础知识有所了解，如果读者朋友有使用Visual Basic编程语言的经验，那么使用VBA时会感觉有很多相似之处。如果读者朋友完全是一个新手，也不必太担心，因为实际的操作和运用是很简单的。二、什么

3、时候使用自定义函数？ 有些初学Excel的朋友可能有这样疑问：Excel已经内置了这么多函数，我还有必要创建自己的函数吗？回答是肯定的。原因有两个，它们也正好可以解释什么时候使用Excel自定义函数的问题。第一，自定义函数可以简化我们的工作。有些工作，我们的确可以在公式中组合使用Excel内置的函数来完成任务，但是这样做的一个明显缺点是，我们的公式可能太冗长、繁琐，可读性很差，不易于管理，除了自己之外别人可能很难理解。这时，我们可以通过使用自定义函数来简化自己的工作。第二，自定义函数可以满足我们个性化的需要，可以使我们的公式具有更强大和灵活的功能。 实际工作的要求千变万化，仅使用Excel内置

4、函数常常不能圆满地解决问题，这时，我们就可以使用自定义函数来满足实际工作中的个性化需求。上面的讲述比较抽象，我们还是把重点放在实际例子的剖析上，请大家在实际例子中进一步体会，进而学会在Excel中创建和使用自定义函数。三、自定义函数实例剖析 下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的VBA基础。假如你完全没有VBA基础也不要紧，当学习完实例后，若觉得自定义函数在自己以后的工作中可能用到，那么再去补充相应的VBA基础也不迟。(一) 计算个人调节税的自定义函数 任务假设个人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800元以上至1500元

5、的超过部分按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-2000)*0.2)”，然后通过填充柄复制公式到C列的其余单元格

6、。既然公式能够解决问题，为什么还要使用自定义函数的方法呢？正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那么公式可能就无能为力了。使用自定义函数 下面就通过此例介绍使用自定义函数的全过程，即使是初学Excel的朋友，也会感觉其操作实际上是非常简单的。1. 为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具宏Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)，打开Visual Basic窗口，我们将在这里自定义函

7、数。2. 进入Visual Basic窗口后，选择菜单“插入模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)：Function TAX(salary)Const r1 As Double = 0.05Const r2 As Double = 0.08Const r3 As Double = 0.2Select Case salaryCase Is <= 800TAX = 0Case Is <= 1500TAX = (salary - 800) * r1Case Is <= 2000TAX = (1500 - 800) * r1 + (salary - 1

8、500) * r2Case Is > 2000TAX = (1500 - 800) * r1 + (2000 - 1500) * r2 + (salary - 2000) * r3End SelectEnd Function图 23. 函数自定义完成后，选择菜单“文件关闭并返回到Microsoft Excel”命令，返回到Excel工作表窗口，在C2单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图3)。图 3 4. 从自定义函数的代码中可以看出，用这种方式，自定义函数

9、的功能非常易于理解，同时如果税率改变，相应地变化r1、r2、r3的值即可。通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件另存为”命令，打开“另存为”对话框，选择保存类型为“Mircosoft Excel加载宏”，然后输入一个文件名，如“TAX”单击“确定”后文件就被保存为加载宏(图4)。然后选择菜单“工具加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后(图5)，就可以在本机上的所有工作薄中使用该自定义函数了。图 4 图 5 如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它电

10、脑上加载宏的默认保存位置即可。说明：Windows XP系统下加载宏文件的默认保存位置为：C:Documents and Settingszunyue(用户帐户)Application DataMicrosoftAddIns文件夹。(二) 计算奖金的自定义函数 任务为了促进销售人员的工作积极性，销售部门经理制定了销售业绩奖金制度，奖金发放的标准奖金率如下：月销售额小于等于2800元的奖金率为4，月销售额为2800元至7900元的奖金率为7，月销售额为7900元至15000元的奖金率为10，月销售额为15000元至30000元的奖金率为13，月销售额为30000元至50000元的奖金率为16，月

11、销售额大于50000元的奖金率为19。同时，为了鼓励员工持续地为公司工作，工龄越长对奖金越有利，具体规定为：参与计算的奖金率等于标准奖金率加上工龄一半的百分数。比如一个工龄为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,

12、B2*13%,IF(B2<=50000,B2*16%,B2*19%)”可以进行计算。但是，该公式的一些弊端很明显：一是公式看起来太繁琐、不容易理解，而且IF函数最多只能嵌套7层，万一奖金率超过7个，那么这个方法就无能为力了。另一方面，由于没有考虑工龄，所以该方法不能算是解决问题了，如果我们把工龄融入到上述公式中，这样公式就会显得更加冗长繁琐，以后的管理与调整都很不方便。使用自定义函数 下面我们看看利用Excel自定义函数进行计算的全过程，有了实例一的基础，相信大家理解起来更容易了。不过这里与实例一有一个明显的差别是，该自定义函数使用了2个参数，请大家注意体会。1. 在上述Excel工作表

13、中，选择菜单“工具宏Visual Basic编辑器”命令，打开Visual Basic窗口，然后选择菜单“插入模块”命令，插入一个名为“模块1”的模块。2. 接着在模块编辑窗口中输入自定义函数的代码如下(图 7)：Function REWARD(sales, years) As DoubleConst r1 As Double = 0.04Const r2 As Double = 0.07Const r3 As Double = 0.1Const r4 As Double = 0.13Const r5 As Double = 0.16Const r6 As Double = 0.19Selec

14、t Case salesCase Is <= 2800REWARD = sales * (r1 + years / 200)Case Is <= 7900REWARD = sales * (r2 + years / 200)Case Is <= 15000REWARD = sales * (r3 + years / 200)Case Is <= 30000REWARD = sales * (r4 + years / 200)Case Is <= 50000REWARD = sales * (r5 + years / 200)Case Is > 50000RE

15、WARD = sales * (r6 + years / 200)End SelectEnd Function图 7 3. 从代码可以看出，我们自定义了一个名为REWARD的函数，它包含两个参数：销售额sales和工龄years。常量r1至r6分别存放着各个等级的奖金率，这样处理的好处是当奖金率调整时，修改非常方便。同时，函数的层次结构比前面的公式清晰，让人容易理解函数的功能。此外，当奖金率超过7个时，用自定义函数的方法仍然可以轻松处理。4. 接下来用该自定义函数进行具体的计算。选择菜单“文件关闭并返回到Microsoft Excel”命令，关闭Visual Basic窗口，返回Excel工作

16、表。选中D2单元格，在其中输入“=reward(B2,C2)”，回车后就算出了第一个员工的奖金，然后利用公式填充柄复制该公式到后面的单元格，即可完成对其它员工奖金的计算(图 8)。图 8如果该自定义函数需要在其它工作薄或其它机器上使用，仿照实例一的操作方法进行即可。四、 总结 我们通过两个典型的实例讲述了Excel中自定义函数使用的全过程，相信大家都已经会到，其操作过程还是相当简单的。如果你觉得自己的工作可能需要自定义函数，想进一步学好提高使用自定义函数的水平，笔者想给出如下几点建议。第一点、尽力全面熟练地掌握Excel内置的函数。能用内置函数妥善解决的问题，就不必使用自定义函数。实际上，自定

17、义函数的执行效率当然是比Excel内置函数的执行效率慢的。第二点、认真掌握好VBA的基础知识。这点很容易理解，如果连VBA的基本规则都不甚清楚，那么别说是写出精致的自定义函数，就是写出能解决问题的自定义函数也还大有疑问。第三点、具体写自定义函数代码之前，应该认真分析自己要处理的实际问题，如果这个问题有实际的数学函数模型，那么最好列出这个函数的解析式。以上只是笔者的一些浅薄认识，希望能为大家使用好Excel自定义函数带来帮助，也希望大家能够通过使用自定义函数提高自己的工作效率。 .xls文件下载Excel中用自动运行宏提高工作效率     

18、60; 在日常工作中，我们经常需要在每次打开同一个Excel文件时都进行一些例行的操作，如改变表格的格式、更新报表日期、打印文件、对工作表进行保护或取消保护等等。Excel的自动运行宏“AutoOpen”可在文件打开后立即完成这些例行的操作任务，既快速又准确。假设我们在Excel文件的工作表Sheet1中有一个销售日报表，下面我们在这个文件中建立一个AutoOpen宏，让它在文件打开后自动完成下面任务：       1. 取消工作表保护；2. 把“当日销售”列里的数据值复制到“上日销售”一列；3. 将日期增加一天；4. 恢复工作表保护

19、。具体做法如下：1. 在“工具”菜单上选择“宏”子菜单，打开“宏”对话框，在“宏名”一栏里键入“AutoOpen”再点击下面的“新建”钮，进入宏编辑状态。注意不要把宏名字输错了，否则宏不会自动执行。2. 在宏编辑状态下，把下面VBA Visual Basic for Application 语句输入到AutoOpen下面：Sub AutoOpen Sheets"Sheet1".Activate '取消工作表保护 ActiveSheet.Unprotect '将当日销售值拷贝到上日销售一栏 x = MsgBox"把当日销售值拷贝到上日销售栏吗？&qu

20、ot; vbYesNo If x = vbYes Then Range"B5B8".Copy Range"C5".Select Selection.PasteSpecial Paste=xlValues Application.CutCopyMode = False End If '将日期增加一天 x = MsgBox"把日期增加一天吗？" vbYesNo If x = vbYes Then Range"C2" = Range"C2" + 1 End If '重新保护工作表 Ac

21、tiveSheet.Protect End Sub        将文件保存并关闭。重新打开此文件，体验一下AutoOpen宏是如何为你工作的吧。如果你想用AutoOpen完成其它的操作而又不知道如何用VBA语句直接建立宏，Excel的录制宏的功能可以帮助你，但是别忘了把所录制的宏取名为AutoOpen。关于录制宏的方法请参阅一般的Excel功能手册。Excel 巧用名称框和输入平方、立方巧用名称框Excel中的名称框位于编辑栏左端的下拉列表框中，它主要用于指示当前选定的单元格、图表项或绘图对象。灵活运用名称框，对我们提高Excel的

22、使用效率有很大帮助。      一、快速命名单元格或单元格区域      选定需要命名的单元格或单元格区域，然后直接在“名称框”中键入名称，再按回车键即可快速命名选定的单元格或单元格区域。      二、快速移动至指定单元格      如果要将活动单元格移动到指定的单元格，可直接在“名称框”中键入需要移动至相应位置的单元格标志。例如用户要将活动单元格从A1单元格移动到X1000单元格时，如果使用移

23、动鼠标指针的方法将是一件非常麻烦的操作，但是你只要在“名称框”中键入“X1000”，按下回车键后活动单元格就会立即移动到X1000单元格中。      三、快速选定单元格区域      在“名称框”中直接键入需要选定的单元格区域标志，例如“B2:H200”，然后按下回车键，这时B2:H200单元格区域就被选中了。      如果需要选定工作表中不相邻的单元格或单元格区域，只要在“名称框”中使用逗号将各个单元格分隔开即可。例如在“名称框”中键入“A

24、1，C2:D10，H:H”，按下回车键后，即可将A1单元格、C2:D10单元格区域以及H行同时选中。 要注意一点的是,如果要用名称框选区的话,先要选中多个单元格. 第一步:选中多个单元格如(A1:D15) 第二步:打开插入菜单中选择"名称"菜单中选择"定义",弹出对话框,在定义名称对话框中输入名称,确定. 第三步:打开编辑栏中的名称框旁边的小三角,菜单中就可以选择你定义的名称了,单击,这样你定义的名称就选中了. 还要一个就是先不用选中单元格也可以: 第一步:插入菜单中选择"名称"中的"定义",对话框中输入名称. 第

25、二步,在"引用位置"下面的方框里面把引用的内容先删除 ,然后,再次把你要定义的单元格选中,确定. 第三步:打开编辑栏中的名称框旁边的小三角,菜单中就可以选择你定义的名称了,单击,这样你定义的名称就选中了. 输入平方、立方      如果要在Excel中输入平方、立方, 可采用如下方法解决这个问题：先在单元格中输入“X2”或“X3”，再在编辑栏中选定“2”或“3”，点击“格式单元格”，在“字体”标签页中，勾选“上标”前面的复选框，最后单击“确定”即可。直接按ALT，别松手再按小键盘的178，同时放手，就是平方了。179是立方。

26、 EXCEL中VBA应用举例 在以下所举例子中，均可以采用简洁的VBA代码来完成。如果你以前没有做过程序，看到那么多代码会以为很难，其实不然，这些代码有大部分都是可以录制下来的，然后稍做修改即可。 例一、 工作簿中SHEET1(工资表)的A列是姓名，SHEET2(员工E-MAIL列表)的A列是姓名，B列是对应的E-MAIL。现在你想将所有员工的E-MAIL填写到SHEET1中的B列， (注意:SHEET1中姓名“李三”在SHEET2中可能为“李三”，中间没有空格)。简答：可以利用双层循环，从SHEET1中找出每个人名字，然后到SHEET2中去匹配，匹配上的就把SHEET1相应的单元格赋值。Su

27、b fillCell()    Dim strTemp$, I%, J%    Worksheets("sheet1").Activate    For I = 1 To 3 '在之前可以用另一段程序判断出行数        strTemp = Trim(Replace(Worksheets("sheet1").Cells(I, 1).Value, " ", "

28、;") '把姓名赋值给变量strTemp，并去掉空格        For J = 1 To 3 '在之前可以用另一段程序判断出行数            If Trim(Replace(Worksheets("sheet2").Cells(J, 1).Value, " ", "") = strTemp Then 

29、0;              Worksheets("sheet1").Cells(I, 2).Value = Worksheets("sheet2").Cells(J, 2).Value                Exit For   

30、60;        End If        Next    NextEnd Sub例二、 在年度的工作计划中，老总要求把公司每部业务电话的通话时长的通话费用按日(月)做成柱状图，供部门经理和老总调阅。例三、 有一份公司所有员工工资表(或各代理处的存货表)，你想把这个总表按个人(代理处)分离成单个的EXCEL表，然后分发给对应的人(代理处)简答：需要应用到FSO对象，FSO控制文件的复制、命名等。首先利用Dim wkboo

31、k As Workbook和Set wkbook = Workbooks.Add新建一个工作簿，range("a:b").Copy 和Sheets("sheet1").Paste 可以把指定内容复制到新工作簿中，wkbook.SaveAs方法可以用变量给文件命名，变量可以是姓名或代理处名。然后调用JMAIL或其它控件把文件发到指定的邮箱。例四、 作为会务人员，你需要每个与会人员签到，你希望在A列每输入一个名字(或用户ID号)时，在B列就出现精确到分钟的时间简答：以下为基本代码，可以根据情况再做判断。输入完一个到会人员名字后再回到这个单元格，就可以在同行相

32、邻一列的单元格中写出系统时间。    Dim strtemp$, strAdd$    strtemp = Trim(ActiveCell.Value)    strAdd = ActiveCell.Address    If Len(strtemp) > 0 Then        ActiveCell.Offset(0, 1).Value = Format(Now, "yyyy-mm-d

33、d hh:mm:ss")    End If例五、 如果有一大批格式统一的TXT文档需要导入到EXCEL中去并作一些调整(或者一批EXCEL表需要导出为TXT文档或HTML文档，并为导出的文件命名)简答：用FSO把一个要倒入的的TXT文件的名字取出来，然后利用    Workbooks.OpenText Filename:="文件名", StartRow:=1, _        DataType:=xlDelimited, TextQ

34、ualifier:=xlDoubleQuote, ConsecutiveDelimiter _        :=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _        Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)打开TXT文件(一些属性可以实际情况修改)，然后再利用SAVE AS 保存就可以完成TXT

35、-EXCEL的倒入。反过来更容易些，可以用FSO新建TXT(html)文件，然后把EXCEL中符合条件的记录集写进TXT(html)文件即可。例六、 在公司的SQLSERVER或ACCESS数据库中集中存放一些员工档案或公司运营记录，需要把符合条件的记录导出到EXCEL表中，并做出折线图或柱状解答：本例应用到ADO对象，先在VBE编辑环境中菜单“工具/引用”添加对ADO的引用，然后在程序中建立跟SQLSERVER或ACCESS数据库的连接，用SQL语句把查找的结果集RECORDSET写进EXCEL的CELLS当中。然后再利用CHARTS对象添加图表就可以了。例七、 把EXCEL中的数据在WOR

36、D中按一定的顺序和条件显示出来解答：本例要在VBE编辑环境中菜单“工具/引用”添加对WORD的引用，Dim wd As Word.Application和Dim wddoc As Word.Document可以定义WORD中的对象，    Set wd = New Word.Application    wd.Visible = True       wddoc.Select        

37、60; wddoc.SaveAs "c:tt.doc"wd.Quit这样可以做到对WORD的控制。例八、 在EXCEL中有20个以前做的折线图，现在要在图标题末尾中增加“(2003年一季度)”这几个字    Dim wkBook As Excel.Workbook    Dim wkSheet As Excel.Worksheet    Dim I%       Set wkBook = ActiveWorkbook 

38、;   For Each wkSheet In wkBook.Worksheets '循环所有的表                   wkSheet.ChartObjects(I).Activate '将Shape对象转换成对CHART的控制           

39、0;           'activeChart.Shapes("图表 1").IncrementLeft            With ActiveChart            .HasTitle = True  

40、0;                    End With        Next    NextEnd Sub 运用Excel三大条件函数解决实际问题！       秋尽冬至，又到一年年终时。在工作中，数据的运算量和汇总的操作一下比平时多了不少。众所周知，

41、在Excel中可以对数据进行各种不同的运算和汇总，今天我向大家介绍与条件相关的函数三兄弟，分别是“COUNTIF”、“SUMIF”和“IF”函数。他们有一个共同的特点都姓字符“IF”。大兄弟：COUNTIF函数（计数求和）COUNT函数，顾名思义是用来计数的，统计所选择区域的数值型单元格个数。COUNTIF是COUNT函数的引伸与拓展，在计数时加上先前条件，只有符合计数的条件才进行统计计算。比如，从员工信息表中，计算出有多少人的年龄大于35岁。下面我们来看一个典型的分类计数汇总的例子。这里有一张销售流水记录表，每名销售人员累计做了多少“销售订单个数”呢？ 大兄弟COUNTIF正常工作需要两个参

42、数条件区域（本例为左侧表中“销售人员”一列）和计数条件（本例为右侧表中的人员姓名）。要计算第一位销售人员的“订单数”，很简单，输入函数公式 “=COUNTIF($C$2:$C$16,E2)”即可（见图1）。二兄弟：SUMIF函数（条件求和）SUM函数的作用是对数据求和，而SUMIF对它进行了引伸和拓展，比如计算“金额”在1元以上的数据总和、按照人员或产品分类计算数据总和等等。它有3个参数，分别是条件区域、判断条件、实际的求和区域（如果它与“条件区域”是一个区域，就可省略）。在上例中，计算每位“销售人员”的订单总金额，就要使用SUMIF函数来协助了。如果要计算每个人的销售订单总金额，把左侧表的“销售人员”一列当作“条件区域”，把右侧表的每个名单当作求和“条件”，把左侧表的每笔“订单金额”当作“实际求和区域”，在G2单元格中输入数据计算公式 “=SUMIF($C$2:$C$16,E2,$B$2:$B$16)