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

2014-10-21 19:57王峰
计算机光盘软件与应用 2014年24期

摘 要:随着科技的发展,信息技术自动化,应用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