Excel中自定义函数实例剖析

1、xcel中自定义函数实例剖析2004-09-06 14:08作者：罗庆丰原创出处：天极网责任编辑：Shiny三、自定义函数实例剖析下面我们通过两个典型实例，学习自定义函数使用的全过程。这里实际上假设读者朋友都有一定的VBA基础。假如你完全没有VBA基础也不要紧，当学习完实例后，若觉得自定义函数在自己以后的工作中可能用到，那么再去补充相应的VBA基础也不迟。(一) 计算个人调节税的自定义函数任务假设个人调节税的收缴标准是：工资小于等于800元的免征调节税，工资800元以上至1500元的超过部分按5的税率征收，1500元以上至2000元的超过部分按8的税率征收，高于2000元的超过部分按20的税率

2、征收。分析假设Sheet1工作表的A、B、C、D列中分别存放“姓名”、“总工资”、“调节税”、“税后工资”字段数据，如图1所示。图 1平时使用较多的方法是借助嵌套使用IF函数计算，比如在C2单元格输入公式“=IF(B2=800,0,IF(B2& lt;=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列的其余单元格。既然公式能够解决问题，为什么还要使用自定义函数的方法呢？正如前面提到的两个方面的原因：一是公式看起来太繁琐，不便于理解和管理；

3、二是公式的处理能力在面对稍微复杂一些的问题时便失去效用，比如假设调节税的税率标准会根据年龄的不同而改变，那么公式可能就无能为力了。使用自定义函数下面就通过此例介绍使用自定义函数的全过程，即使是初学Excel的朋友，也会感觉其操作实际上是非常简单的。1. 为了便于测试自定义函数的计算效果，可以先把上面采用公式计算的结果删去。然后选择菜单“工具宏Visual Basic编辑器”命令(或按下键盘Alt+F11组合键)，打开Visual Basic窗口，我们将在这里自定义函数。2. 进入Visual Basic窗口后，选择菜单“插入模块”命令，于是得到“模块1”，在其中输入如下自定义函数的代码(图2)

4、：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 + (2000 - 1500) * r2 + (salary - 2000) * r3End SelectEnd Function图 23. 函数自定义完成后，选择菜单“文件关闭并返回

5、到Microsoft Excel”命令，返回到Excel工作表窗口，在C2单元格中输入公式“=TAX(B2)”回车后就计算出了第一个员工应付的个人调节税，然后用公式填充柄复制公式到其它后面的单元格，这样就利用自定义函数完成了个人调节税的计算(图3)。图 34. 从自定义函数的代码中可以看出，用这种方式，自定义函数的功能非常易于理解，同时如果税率改变，相应地变化r1、r2、r3的值即可。通常，自定义的函数只能在当前工作薄使用，如果该函数需要在其它工作薄中使用，则选择菜单“文件另存为”命令，打开“另存为”对话框，选择保存类型为“Mircosoft Excel加载宏”，然后输入一个文件名，如“TAX

6、”单击“确定”后文件就被保存为加载宏(图4)。然后选择菜单“工具加载宏”命令，打开“加载宏” 对话框，勾选“可用加载宏”列表框中的“Tax”复选框即可，单击“确定”按钮后(图5)，就可以在本机上的所有工作薄中使用该自定义函数了。图 4图 5如果想要在其它机器上使用该自定义函数，只要把上面的加载宏文件复制到其它 HYPERLINK / 电脑上加载宏的默认保存位置即可。说明：Windows XP系统下加载宏文件的默认保存位置为：C:Documents and Settingszunyue(用户帐户)Application DataMicrosoftAddIns文件夹。EXCEL中单元格的颜色统计其

7、实，这个问题要用到一个特别的函数：get.cell解决这个问题的思路是，你首先得让系统知道你每种颜色的代码是多少。得到这个代码就用到这个函数。设你要统计的单元格在A列，B列为空列，操作如下：1，点插入，名称，定义，弹出的窗口第一行名称名内写入一个自定义的名称名字，可以是中文也可以是英文，比如XX，最下边一行的引用位置一行内填入：=get.cell(63.A1) 确定。2，在B1中输入=XX，光标指向B1单元格右下角的小黑点，光标变成小黑实心十字时，双击左键。完成填充。这样操作以后，在B列中会出现各个单元格中底色的代码。3,用countif()函数对你要统计的单元进行统计，如：=countif(

8、$B$1:$B$100,5)这个统计公式意思是统计B1至B100中颜色代码是5的单元格有多少个。用辅助列可以做到假设列A为原数据列，选择列B为第一辅助列1.在插入-名称-定义里定义一个名称，X=GET.CELL(24,SHEET1!A1)2.在B1中输入=X这样你就会看到列B中有对应的列A颜色返回的数值，假定黑色格子的颜色数值为1再利用一辅助列C，在列C输入公式=IF(OR(B:B=1,B:B=57,B:B=0),A:A+3,A:A)，即可得所需变更后的全部数值列最后便是利用选择性拷贝的方法，将C列的值复制到A列，就可以完成全部操作了注意：1、GET.CELL(24,SHEET1!A1)其中2

9、4代表字体颜色，换成38代表背景颜色 HYPERLINK /blog/read.php?84 用EXCEL来根据单元格的颜色来计数和求和 HYPERLINK /s/blog_6971d9c30100o1el.html 大| HYPERLINK /s/blog_6971d9c30100o1el.html 中| HYPERLINK /s/blog_6971d9c30100o1el.html 小STEP 1 ：打开你的excel；STEP 2 ：菜单栏：工具宏Visual Basic 编辑器；STEP 3 ：Visual Basic 编辑器菜单栏： 插入模块STEP 4 ：贴入下面这段函数Funct

10、ion Countcolor(col As Range, countrange As Range)Dim icell As RangeApplication.VolatileFor Each icell In countrangeIf icell.Interior.ColorIndex = col.Interior.ColorIndex ThenCountcolor = Countcolor + 1End IfNext icellEnd FunctionFunction Sumcolor(col As Range, sumrange As Range)Dim icell As RangeApp

11、lication.VolatileFor Each icell In sumrangeIf icell.Interior.ColorIndex = col.Interior.ColorIndex ThenSumcolor = Application.Sum(icell) + SumcolorEnd IfNext icellEnd FunctionSTEP 5 ：保存并关闭Visual Basic 编辑器STEP 6 ：使用函数 countcolor（所要统计的颜色所在单元格，统计的区域）sumcolor（所要统计的颜色所在单元格，统计的区域）/若是字体颜色Function Sumfontcol

12、or(col As Range, sumrange As Range)Dim icell As RangeApplication.VolatileFor Each icell In sumrangeIf icell.Font.ColorIndex = col.Font.ColorIndex ThenSumfontcolor = Application.Sum(icell) + SumfontcolorEnd IfNext icellEnd Function1、 建立Excel对象setobjExcelApp=CreateObject(Excel.Application)objExcelApp.

13、DisplayAlerts=false不显示警告objExcelApp.Application.Visible=false不显示界面2、 新建Excel文件objExcelApp.WorkBooks.addsetobjExcelBook=objExcelApp.ActiveWorkBooksetobjExcelSheets=objExcelBook.WorksheetssetobjExcelSheet=objExcelBook.Sheets(1)3、 读取已有Excel文件strAddr=Server.MapPath(.)objExcelApp.WorkBooks.Open(strAddr&T

14、empletTable.xls)setobjExcelBook=objExcelApp.ActiveWorkBooksetobjExcelSheets=objExcelBook.WorksheetssetobjExcelSheet=objExcelBook.Sheets(1)4、 另存Excel文件objExcelBook.SaveAsstrAddr&TempTable.xls5、 保存Excel文件objExcelBook.Save（笔者测试时保存成功，页面报错。）6、 退出Excel操作objExcelApp.Quit一定要退出setobjExcelApp=Nothing三、 操作Exce

15、l生成数据表1、 在一个范围内插入数据objExcelSheet.Range(B3:k3).Value=Array(67,87,5,9,7,45,45,54,54,10)2、 在一个单元格内插入数据objExcelSheet.Cells(3,1).Value=InternetExplorer3、 选中一个范围4、 单元格左边画粗线条5、 单元格右边画粗线条6、 单元格上边画粗线条7、 单元格下边画粗线条8、 单元格设定背景色9、 合并单元格10、 插入行11、 插入列四、 操作Excel生成Chart图1、 创建Chart图objExcelApp.Charts.Add2、 设定Chart图种类

16、objExcelApp.ActiveChart.ChartType=97注：二维折线图，4；二维饼图，5；二维柱形图，513、 设定Chart图标题objExcelApp.ActiveChart.HasTitle=TrueobjExcelApp.ActiveChart.ChartTitle.Text=AtestChart4、 通过表格数据设定图形objExcelApp.ActiveChart.SetSourceDataobjExcelSheet.Range(A1:k5),15、 直接设定图形数据（推荐）objExcelApp.ActiveChart.SeriesCollection.NewSe

17、riesobjExcelApp.ActiveChart.SeriesCollection(1).Name=333objExcelApp.ActiveChart.SeriesCollection(1).Values=1,4,5,6,26、 绑定Chart图objExcelApp.ActiveChart.Location17、 显示数据表objExcelApp.ActiveChart.HasDataTable=True8、 显示图例objExcelApp.ActiveChart.DataTable.ShowLegendKey=True我假设你的工作表已经有很多设置好背景颜色的单元格.如你上面讲的红

18、.黄.蓝视图,工具栏,窗体打上勾.在窗体工具条上点击按钮在工作表上拉出一个按钮.在弹出来的指定宏对话框中点新建这时会出现VBA窗口在自动生出来的Sub按钮1_单击()这是自动生出来的DimaAsRangeForEachaInSheets(sheet1).UsedRange在使用过的单元格内搜索Ifa.Interior.ColorIndex=6Thena=1000:a.Font.ColorIndex=3如果条件为真,在这个单元格内输入数值.并改变字体颜色ElseIfa.Interior.ColorIndex=5Thena=500:a.Font.ColorIndex=6ElseIfa.Interi

19、or.ColorIndex=3Thena=100:a.Font.ColorIndex=5EndIfNextaEndSub这也是自动生出来的.请注意:复制此段代码时,不要将第一句Sub按钮1_单击()和最后一句EndSub这两句复制.选中中间的代码将它粘贴进去就可以了.如果想判断颜色后,不想再要这些背景颜色的话,将它修改一下就可以了.如下面:Sub按钮1_单击()DimaAsRangeForEachaInSheets(sheet1).UsedRangeIfa.Interior.ColorIndex=6Thena.Interior.ColorIndex=0:a=1000:a.Font.ColorIndex=3ElseIfa.Interior.ColorIndex=5Thena.Interior.ColorIndex=0:a=500:a.Font.ColorIndex=6ElseIfa.Interior.ColorIndex=3Thena.Interior.ColorIndex=0:a=100:a.Font.ColorIndex=5EndIfNextaEndSub这样只要你点击一下工作表上你拉出来的这个按钮.就会完成你的要求我这个代码还是粗糙了一点.如果哪位高手有更好的方法也可以贴出来.权当是一次VBA编写练习.如果不想要单元格的字体颜色设