編程實(shí)現(xiàn)電子表格自動(dòng)匯總

摘 要：隨著科技的發(fā)展，信息技術(shù)自動(dòng)化，應(yīng)用EXCEL制作一個(gè)實(shí)用的工資管理系統(tǒng)是非常有必要的，可以利用EXCEL中的VBA來(lái)編程實(shí)現(xiàn)對(duì)大量數(shù)據(jù)的輸入和匯總。這篇論文主要是對(duì)電子表格的工作表復(fù)制、字段的排序以及對(duì)一些數(shù)據(jù)的匯總編寫一些程序。

關(guān)鍵詞：EXCEL；VBA；分類匯總

中圖分類號(hào)：TP311.52

VBA是一種自動(dòng)化語(yǔ)言，它可以使常用的程序自動(dòng)化，可以創(chuàng)建自定義的解決方案，是非常流行的應(yīng)用程序開發(fā)語(yǔ)言VASUAL BASIC的子集。實(shí)際上VBA是“寄生于”VB應(yīng)用程序的版本，它可以稱作EXCEL的“遙控器”，使用VBA可以實(shí)現(xiàn)的功能包括：（1）使重復(fù)的任務(wù)自動(dòng)化；（2）自定義EXCEL工具欄，菜單和界面；（3）簡(jiǎn)化模板的使用；（4）自定義EXCEL，使其成為開發(fā)平臺(tái)；（5）創(chuàng)建報(bào)表；（6）對(duì)數(shù)據(jù)進(jìn)行復(fù)雜的操作和分析。

在此用EXCEL作為開發(fā)平臺(tái)實(shí)現(xiàn)VBA的用應(yīng)程序。

1 完成此向任務(wù)的流程圖

2 作數(shù)據(jù)工作表及匯總工作

2.1 啟動(dòng)Microsoft Office Excel2003，將sheet1重命名“總表”，將sheet2重命名為“部門匯總”，sheet3重命名為“工資表”，然后將工作簿保存為“工資管理.XLS”。

2.2 打開工作簿，選擇工資表，在A1至D1單元格中依次輸入職工編號(hào)、基本工資、工齡、合計(jì)工資。然后選擇總表，將職工編號(hào)、基本工資、工齡等項(xiàng)目數(shù)據(jù)復(fù)制到工資表中，利用VBA編程，打開VBA界面。

（1）選擇“工具—宏—visual basic編輯器”命令，出現(xiàn)Microsoft visual basic編輯界面。

（2）選擇“插入—模塊”命令，出現(xiàn)模塊1代碼窗口，如圖2所示，然后在代碼窗口中輸入如下代碼：

2.3 計(jì)算工資表的合計(jì)工資，選中D2，在編輯欄中輸入“=2*C2+B2”，按回車鍵。即可完成D2單元格的數(shù)據(jù)，再用填充的方法完成其他數(shù)據(jù)的填充。

2.4 再完成總表中的合計(jì)工資。利用列查找函數(shù)VLOOKUP（）完成。選擇總表，在I2單元格中輸入公式“=VLOOKUP（A2，工資表！$A$2：$D$31，7，0）”

2.5 對(duì)總表按部門進(jìn)行匯總，完成這一部之前可先用VBA編輯一個(gè)自動(dòng)“部門匯總”工具欄，以后單擊此按扭就可自動(dòng)完成匯總工作。

（1）選擇“工具—宏—visual basic編輯器”命令，出現(xiàn)Microsoft visual basic 編輯界面（即VBA編輯器界面）。

（2）選擇“插入—模塊”命令，然后在代碼窗口中輸入如下代碼：

private sub auto_open（）

‘自動(dòng)創(chuàng)建部門匯總工具欄

menubars（xlworksheet）.reset

‘關(guān)閉其他EXCEL自定義菜單

set my‘單擊對(duì)象時(shí)運(yùn)行collect宏commandbar=commandbars（“standard”）

‘創(chuàng)建commandbars集合對(duì)象

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

‘用ADD方法可在集合中添加一個(gè)新的工具欄

mybutton.caption=“部門匯總”

mybutton.enabled=true

‘打開對(duì)象的enabled屬性

mybutton.onaction=“collect”

end sub

2.6 完成了自動(dòng)“部門匯總”工具欄，現(xiàn)在我們可將總表數(shù)據(jù)全部復(fù)制到部門匯總表，用EXCEL 中的subtotal（）分類匯總函數(shù)對(duì)部門進(jìn)行匯總，但在做匯總之前，數(shù)據(jù)一定是按某種順序排列的，我們可用EXCEL中的排序函數(shù)sort（）來(lái)對(duì)部門升序排序。

（1）選擇“工具—宏—visual basic編輯器”命令，出現(xiàn)Microsoft visual basic編輯界面。

（2）選擇“插入—模塊”命令，然后在代碼窗口中輸入如下代碼：

sub copysheet（） ‘復(fù)制工作表

sheets（“部門匯總”）.select

cells.select ‘選擇整個(gè)工作表

range（“E5”）.active

selection.delete shift：=xlup

sheets（“總表”）.select‘執(zhí)行刪除命令

range（“A2：A31”）.select

range（“A15”）.active

select.copy

sheets（“部門匯總”）.select

range（“A1”）.select

Activesheet.paste

End sub

（3）再選擇“工具—宏—visual basic編輯器”命令，出現(xiàn)Microsoft visual basic 編輯界面。

（4）選擇“插入—模塊”命令，然后在代碼窗口中輸入如下代碼：

sub sort（） ‘對(duì)部門進(jìn)行排序

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）自動(dòng)匯總的編程，再選擇“工具—宏—visual basic編輯器”命令，出現(xiàn)Microsoft visual basic 編輯界面。

（6）選擇“插入—模塊”命令，然后在代碼窗口中輸入如下代碼：

sub collect（） ‘定義一個(gè)名為collect的宏

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

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

end sub

2.7 輸入一組數(shù)據(jù)進(jìn)行測(cè)試，保存并退出EXCEL 2003 。

2.8 重新打開“工資管理”工作簿，單擊“部門匯總”按扭，即可查看部門匯總結(jié)果，如圖3、圖4所示。

3 結(jié)束語(yǔ)

在EXCEL中利用VBA編程實(shí)現(xiàn)電子表格的自動(dòng)匯總，可進(jìn)一步增強(qiáng)電子表格的功能。

參考文獻(xiàn)：

[1]劉小偉，陳德榮.EXCEL 高效辦公應(yīng)用范例[M].北京：機(jī)械工業(yè)出版社，2006.

[2]晶辰工作室.Excel 2000中文版VBA開發(fā)實(shí)例指南[M].北京：電子工業(yè)出版社，2008.

作者簡(jiǎn)介：王峰（1985-），男，安徽阜陽(yáng)人，助理工程師，本科，研究方向：計(jì)算機(jī)科學(xué)與技術(shù)。

作者單位：潁東區(qū)信息化工作辦公室，安徽阜陽(yáng) 236000