编程实现电子表格自动汇总

摘 要：随着科技的发展，信息技术自动化，应用EXCEL制作一个实用的工资管理系统是非常有必要的，可以利用EXCEL中的VBA来编程实现对大量数据的输入和汇总。这篇论文主要是对电子表格的工作表复制、字段的排序以及对一些数据的汇总编写一些程序。

关键词：EXCEL；VBA；分类汇总

中图分类号：TP311.52

VBA是一种自动化语言，它可以使常用的程序自动化，可以创建自定义的解决方案，是非常流行的应用程序开发语言VASUAL BASIC的子集。实际上VBA是“寄生于”VB应用程序的版本，它可以称作EXCEL的“遥控器”，使用VBA可以实现的功能包括：（1）使重复的任务自动化；（2）自定义EXCEL工具栏，菜单和界面；（3）简化模板的使用；（4）自定义EXCEL，使其成为开发平台；（5）创建报表；（6）对数据进行复杂的操作和分析。

在此用EXCEL作为开发平台实现VBA的用应程序。

1 完成此向任务的流程图

2 作数据工作表及汇总工作

2.1 启动Microsoft Office Excel2003，将sheet1重命名“总表”，将sheet2重命名为“部门汇总”，sheet3重命名为“工资表”，然后将工作簿保存为“工资管理.XLS”。

2.2 打开工作簿，选择工资表，在A1至D1单元格中依次输入职工编号、基本工资、工龄、合计工资。然后选择总表，将职工编号、基本工资、工龄等项目数据复制到工资表中，利用VBA编程，打开VBA界面。

（1）选择“工具—宏—visual basic编辑器”命令，出现Microsoft visual basic编辑界面。

（2）选择“插入—模块”命令，出现模块1代码窗口，如图2所示，然后在代码窗口中输入如下代码：

2.3 计算工资表的合计工资，选中D2，在编辑栏中输入“=2*C2+B2”，按回车键。即可完成D2单元格的数据，再用填充的方法完成其他数据的填充。

2.4 再完成总表中的合计工资。利用列查找函数VLOOKUP（）完成。选择总表，在I2单元格中输入公式“=VLOOKUP（A2，工资表！$A$2：$D$31，7，0）”

2.5 对总表按部门进行汇总，完成这一部之前可先用VBA编辑一个自动“部门汇总”工具栏，以后单击此按扭就可自动完成汇总工作。

（1）选择“工具—宏—visual basic编辑器”命令，出现Microsoft visual basic 编辑界面（即VBA编辑器界面）。

（2）选择“插入—模块”命令，然后在代码窗口中输入如下代码：

private sub auto_open（）

‘自动创建部门汇总工具栏

menubars（xlworksheet）.reset

‘关闭其他EXCEL自定义菜单

set my‘单击对象时运行collect宏commandbar=commandbars（“standard”）

‘创建commandbars集合对象

set mybutton=mycommandbar.control.add（type：=msocontrolbutton）

‘用ADD方法可在集合中添加一个新的工具栏

mybutton.caption=“部门汇总”

mybutton.enabled=true

‘打开对象的enabled属性

mybutton.onaction=“collect”

end sub

2.6 完成了自动“部门汇总”工具栏，现在我们可将总表数据全部复制到部门汇总表，用EXCEL 中的subtotal（）分类汇总函数对部门进行汇总，但在做汇总之前，数据一定是按某种顺序排列的，我们可用EXCEL中的排序函数sort（）来对部门升序排序。

（1）选择“工具—宏—visual basic编辑器”命令，出现Microsoft visual basic编辑界面。

（2）选择“插入—模块”命令，然后在代码窗口中输入如下代码：

sub copysheet（） ‘复制工作表

sheets（“部门汇总”）.select

cells.select ‘选择整个工作表

range（“E5”）.active

selection.delete shift：=xlup

sheets（“总表”）.select‘执行删除命令

range（“A2：A31”）.select

range（“A15”）.active

select.copy

sheets（“部门汇总”）.select

range（“A1”）.select

Activesheet.paste

End sub

（3）再选择“工具—宏—visual basic编辑器”命令，出现Microsoft visual basic 编辑界面。

（4）选择“插入—模块”命令，然后在代码窗口中输入如下代码：

sub sort（） ‘对部门进行排序

select.sort key1：= range（“B2”） ， order1：=xlAsending ， _

header：=xlguess ， ordercustom：=1 ， matchcase：=false ， _

orientaction：=xl top to botton ， sortmethod：=xl pinyin ， _

dataoption：=xl sortnormal

end sub

（5）自动汇总的编程，再选择“工具—宏—visual basic编辑器”命令，出现Microsoft visual basic 编辑界面。

（6）选择“插入—模块”命令，然后在代码窗口中输入如下代码：

sub collect（） ‘定义一个名为collect的宏

selection.subtotal groupby：=2， unction：=xl sum ， totallist：=9 ，_

replace：=true ， pagebreak：=false ， summary belowdata：=true

end sub

2.7 輸入一组数据进行测试，保存并退出EXCEL 2003 。

2.8 重新打开“工资管理”工作簿，单击“部门汇总”按扭，即可查看部门汇总结果，如图3、图4所示。

3 结束语

在EXCEL中利用VBA编程实现电子表格的自动汇总，可进一步增强电子表格的功能。

参考文献：

[1]刘小伟，陈德荣.EXCEL 高效办公应用范例[M].北京：机械工业出版社，2006.

[2]晶辰工作室.Excel 2000中文版VBA开发实例指南[M].北京：电子工业出版社，2008.

作者简介：王峰（1985-），男，安徽阜阳人，助理工程师，本科，研究方向：计算机科学与技术。

作者单位：颍东区信息化工作办公室，安徽阜阳 236000