用VBA编写Excel自定义的平均值函数及其应用《数字技术与应用》.doc

浅谈Excel中VBA自定义函数及应用 无锡市第六高级中学 丁家波 摘要： Excel自带的内置函数在数据统计分析中有着广泛的应用，但对于工作中某些特殊需求，内置函数无法满足。为减少重复操作，满足在教学数据统计工作中的特殊需要，本文着重讨论如何在Excel中自定义平均值函数，该函数与Excel内置函数average()函数的比较以及该函数在教学数据统计中的应用，引申出用VBA在Excel中自定义不同的函数来扩展Excel的功能，满足工作中各种特殊需要，提高工作效率。 关键词： Excel VBA，自定义函数，数据统计 一、 为什么要在Excel中自定义函数 电子表格Excel具有快捷方便的数据输入方式和强大的数据处理能力，是中学教务工作中常用的数据统计分析软件，尤其是Excel里自带的近300个内置函数，可以轻松方便地完成日常许多工作，但是Excel只是个通用的办公软件，对于工作中某些特殊需求，内置函数无法满足。例如：Excel中求平均值Average()函数，函数只能求指中范围内所有数据的平均值，返回值为单精度值小数。如果在实际工作中只求指定范围内符合指定条件的某一部分数据的平均值，返回值为用户指定的一位或者两位小数，average函数即无法满足，这时就需要定制Excel，可通过VBA编程对其进行全方位的扩展、定制，实现自定义功能，从而达到特殊的需求。下面就介绍在Excel中用VBA定制求平均值函数及其应用。 二、自定义平均值函数程序代码的实现 要实现用Excel VBA自定义函数，首先要在电脑里安装Visual Basic 程序设计软件。打开Excel，使用菜单“工具—宏—Visual Basic编辑器”或者直接使用快捷键【Alt+F11】组合键，打开Microsoft Visual Basic编辑器窗口； 在“工程”浏览器窗口中的树状目录的任一项目上单击右键，使用“插入—模块”命令；或者在工具栏插入用户窗体按钮处点击下拉列表，选“模块”即可。在这个模块编辑器中，即可以开始编辑自定义函数了，由于此函数的型式及其每个参数需要用户在属性设置中定义和声明，故叫自定义函数，因Excel中内置的求平均值函数为Average函数，我们不妨命名自定义的求平均值函数为Uaverage，下面是以Excel2003 为例，介绍Uaverage函数的代码实现过程。 ??平均值自定义函数 函数形式Uaverage(rng1,rng2,n),返回值为Single型 函数带3个参数，其中rng1,rng2为Range型，n为整型 此算法是计算数据表中某一范围内符合指定条件的数值平均值 Function Uaverage(rng1 As Range, rng2 As Range, n As Integer) Dim i%, irow%, icol%, ittl% Dim num As Integer, sum As Single irow1 = rng1.Rows.Count icol1 = rng1.Column irow2 = rng2.Rows.Count icol2 = rng2.Column For i = 1 To irow1 Step 1 If Cells(i, icol1).Value = n Then If Cells(i, icol2).Value 0 Then 将大于0的数值计入平均值 num = num + 1 sum = sum + Cells(i, icol2).Value End If End If Next i Uaverage = CSng(sum / num) 计算平均值，返回值为单精度小数 Uaverage = Format$(Uaverage, 0.00) 函数的返回值设置成保留2位小数 End Function 该函数的计算过程其实很简单，就是遍历整个Excel表内的所有单元格，将整个表中rng1字段值为n的记录中，rng2字段数值累加，再求其平均值。函数的返回值为两位小数的数值。比如现Excel中有全校各年级考试的成绩数据，需统计全年级各班级各学科的平均值，假设B列数据为班级号， C列数据为语文成绩， D列数据为数学成绩等。函数Uaverage(B:B,C:C,1)的返回值即为数据表中所有1班语文成绩大于0分学生的平均值,同样函数Uaverage(B:B,D:D,1)的返回值即为数据表中所有1班语文成绩大于0分学生的平均值。 三、该自定义函数与Excel内置平均值average函数的比较 自定