巧用VBA拓展EXCEL的求和函数功能

张秀虎

（江苏省海州高级中学，江苏连云港222023）

巧用VBA拓展EXCEL的求和函数功能

张秀虎

（江苏省海州高级中学，江苏连云港222023）

在中小学的日常管理工作中，Excel软件被大量应用，Excel最大的特点是简单易用，操作灵活，可大大地弥补管理软件的不足。采用Excel VBA编写自定义函数，可以拓展Excel的函数功能，使其更好地为大家服务。

VBA；求和函数；Excel

引言

随着教育信息化的逐步推进深入，学校的日常管理软件已经趋于普及，使得一般工作人员的工作效率大幅度提高，减轻了工作人员的工作强度。管理软件从数据输入、处理、输出方面实现了一体化，但在实时需要的细节方面还是满足不了管理工作的要求，导致这个现象的主要原因是软件的开发者不是教育管理工作一线人员，设计管理软件时不能够全面深入了解具体的工作需求，也有工作实时需求多变的原因。因此，在中小学的日常管理工作中，Excel软件还是被大量应用，Excel最大的特点是简单易用，操作灵活，可大大地弥补管理软件的不足。可惜的是，Excel软件也有自身的缺陷，如自带的函数也有解决不了的实际问题。那能否拓展Excel的函数功能，使其更好地为大家服务呢？采用Excel VBA编写自定义函数，就可以解决这个问题。在本文中，笔者介绍使用VBA拓展求和函数功能。

一、Excel自带的求和函数

在Excel中，为了统计一列或一行数据的总和，如图1所示，统计所有财产的金额，一般采用SUM (E2:E12)函数、自动求和∑命令以及手动编写表达式的方式实现。

图1

也可使用SUMIF函数统计满足条件的数据和，如统计“方凳(板式或钢式)”的金额总和，通过调用SUMIF(A2:A12,"方凳(板式或钢式)",E2:E12)实现。

假设想查找出“方凳(板式或钢式)”的存放地点或者保管使用人，SUMIF函数无法实现，原因是SUMIF只能统计数值型数据，无法统计文本型数据。根据这个想法，采用VBA编写函数SUMCIF拓展求和函数的功能。

二、VBA语言

VBA(Visual Basic For Applications)是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。VBA是Visual Basic的一个子集,VBA不同于VB，原因是VBA要求有一个宿主应用程序才能运行（需要在Excel等软件的运行下才能运行），而且不能用于创建独立应用程序。而VB可用于创建独立的应用程序。VBA可使常用的过程或者进程自动化，可以创建自定义的解决方案，最适用于定制已有的桌面应用程序。

在Excel主窗口中，选择“工具—>宏—>Visual Basic编辑器”命令，弹出VBA编写窗口，如图2所示，在VBA编写窗口中，选择“插入—>模块”命令，插入类1，在右侧的窗口中编写VBA代码。

三、SUMCIF函数的代码

Excel中的SUMIF(rang,criteria,sum_rang)函数有3个参数，其中criteria表示求和的条件，rang表示待比较的条件区域，sum_rang表示需要统计的数据区域。

SUMCIF函数模仿SUMIF函数，SUMCIF (col_head,col_end,sea_address,Ret_address)使用4个参数，col_head、col_end为待比较的条件区的行首、行尾，sea_address为求和的条件，Ret_address为需要统计的数据区的行首。

FunctionSUMCIF(col_head,col_end,sea_address, Ret_address As Range)

Dimd1,d2,d3,d4AsRange'声明4个单元格对象

Set d1=col_header'给单元格对象d1关联col_header

Set d2=col_end'给单元格对象d2关联col_end

Set d3=sea_address'给单元格对象d3关联sea_address

Set d4=Ret_address'给单元格对象d4关联ret_address

Dim i,j,k As Integer'定义两个循环变量，i表示比较数据区的行号，j表示比较数据区的列号,k表示求和数据区的列号

Dim s1As String's1记录满足条件的文本

s1=""

j=d1.column'取比较数据区的列号

k=d4.column'取求和数据区的列号

For i=d1.Row To d2.Row'遍历比较区域的数据与条件是否相符，相符则将对应的统计区数据相连接

temp=ActiveWorkbook.Sheets(d1.Parent.Name).Cells (i,j).Value

If temp=d3.Value Then

s1=s1+ActiveWorkbook.Sheets(d4.Parent.Name). Cells(i,k).Value+","

End If

Next i

s1=Mid(s1,1,Len(s1)-1)

SUMCIF=s1'通过函数名称带回结果

End Function

图2

四、SUMIF函数的运用

返回Excel窗口（图1），统计出方凳(板式或钢式)存放地点或者保管使用人。可以按下列步骤完成：第一步，在H2单元格中，输入要查询的条件——方凳(板式或钢式)。第二步，在I2单元格中输入=SUMCIF（）函数，（）内的参数为A2,A12,H2,F2，其中A2、A12为比较区域的行首、行尾，H2为条件区域地址，F2为求和数据的地址。第三步按“回车”键，I2单元格中立即得出“生物解剖室，生物观察室”信息。

五、SUMIF函数的移植

将SUMCIF移植至其他程序中，可以将其以“加载宏”类型保存，再在其他程序中加载宏，就可以将SUMCIF函数移植，避免函数代码的重复编写工作。具体步骤为：

保存宏，在Excel主窗口中，选择“文件—>另存为”命令，弹出的另存为窗口，选择保存类型“Microsoft Office Excel加载宏”，并任意命名为SUMC，单击“保存”按钮，将其保存在“C:Documents and SettingsAdministratorApplication DataMicrosoftAddIns”目录下。

加载宏，在需要使用SUMCIF函数的工作表中，选择“工具—>加载宏”命令，弹出加载宏窗口，如果没有出现SUMC宏选项，可以通过“浏览”命令浏览本地硬盘，找到保存的宏SUMC，将其添加至加载宏窗口中，然后选取SUMC，单击“确定”按钮就将SUMCIF函数加载到工作表中了。

要正常使用函数SUMCIF函数，必须在打开工作表时启用宏，并将宏的安全级别设置为“中”，函数SUNCIF才会起作用，不然SUMCIF不会生效。

结束语

以上代码在Excel2000和Excel2003中都调试成功，上述函数代码只要进行适当修改，可以继续拓展SUNCIF函数功能。

（编辑：王天鹏）

book=63,ebook=59

TP312 文献标识码：A 文章编号：1673-8454（2010）16-0063-02