編程實現電子表格自動匯總

摘 要：隨著科技的發展，信息技術自動化，應用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