煤礦材料單耗統(tǒng)計中VBA程序的應(yīng)用

摘要：針對材料單耗統(tǒng)計中，數(shù)據(jù)龐大、過程繁瑣、費(fèi)時費(fèi)力的問題，利用vba程序?qū)崿F(xiàn)快速準(zhǔn)確統(tǒng)計分類的目的。

關(guān)鍵詞：VBA EXCEL 單耗統(tǒng)計

在信息處理技術(shù)高速發(fā)展的今天，美國MicroSoft公司的Office系列辦公自動化軟件以其功能強(qiáng)大、使用方便成為幾乎所有辦公電腦中必備的軟件。Office軟件提供的圖文混排、文字編輯、電子表格處理等在工作中發(fā)揮了巨大的作用，成為數(shù)據(jù)統(tǒng)計、分析不可缺少的工具。只要和數(shù)據(jù)打交道的工作，Excel幾乎是必然的選擇。

1 問題的提出

為加強(qiáng)礦井精細(xì)化管理，提高經(jīng)濟(jì)效益，降低成本消耗，監(jiān)督經(jīng)濟(jì)運(yùn)行情況，兗礦集團(tuán)煤業(yè)公司制定了100種材料單耗指標(biāo)。100種材料單耗指標(biāo)涵蓋從一類到二十七類19955種材料，單耗指標(biāo)統(tǒng)計分為月度、季度、半年度和年度四類。以2010年年度指標(biāo)為例，2010年全年我礦erp領(lǐng)料記錄為33499條。要從這33499條記錄里找到這19955種材料，單純借助excel中的查找、篩選、匯總的方法是非常繁瑣的，即使是一個熟練操作excel的人員也需要兩個工作日的工作量。但是當(dāng)引入VBA程序，就會大大減少工作量，提高工作效率和準(zhǔn)確度，實(shí)現(xiàn)自動化辦公。

2 Excel中的VBA

VBA是Visual Basic for Applications（應(yīng)用軟件的可視Basic編程語言）的簡稱，是兼容BASIC編程語言的命令和語法的Excel應(yīng)用軟件的超級宏語言。微軟公司成功推出的統(tǒng)一的VBA應(yīng)用，擁有對象的連接和嵌入技術(shù)，能夠使用戶組合和協(xié)調(diào)多種主流應(yīng)用軟件的功能和特點(diǎn)進(jìn)行應(yīng)用開發(fā)，從而減少了開發(fā)用戶自定義命令和用戶程序的麻煩。[1]主要能用來擴(kuò)展Windows的應(yīng)用程式功能，特別是office軟件各應(yīng)用程序例如Word、Excel、Access、Powerpoint。VBA可以用EXCEL的宏語言來使EXCEL自動化，更確切地講，它是一種自動化語言，可以使常用的程序自動化，同時創(chuàng)建自定義的解決方案。

Excel對象、屬性和方法。對象在Excel VBA的結(jié)構(gòu)中起了重要的作用，Excel VBA 利用對象的概念把一類對象規(guī)定成一種特定的數(shù)據(jù)代表，在Excel VBA這樣高度結(jié)構(gòu)化的程序中，對象被組織在一起稱為集合。一個集合就是一組同類對象形成的一個更大的對象。Excel中各個對象之間存在著包含與被包含的關(guān)系，把能夠包含其它對象的對象稱為容器(Container)。容器可以包含其它對象，也可以被其它容器所包含。對象(Object)實(shí)際上就是一類實(shí)體的集合，而一個對象又擁有不同的屬性。比如，人，就是對象的例子，而人，具有姓名、性別、民族、出生日期等屬性。屬性是封裝在對象內(nèi)部的數(shù)據(jù)，它反應(yīng)了對象的特征，對對象的操作實(shí)際上是通過改變其屬性的方式來實(shí)現(xiàn)的。訪問對象屬性的方法表示為:

對象名稱.屬性= 屬性值

對象的方法是封裝在對象中用來操作對象屬性的代碼，這些代碼對用戶來說是不可見和不可改寫的。使用對象的方法采用如下語法格式:

對象.方法參數(shù)1，參數(shù)2，. . .，參數(shù)n[2]

3 材料單耗統(tǒng)計應(yīng)用

在excel表中，如圖1，Sheet1是全礦領(lǐng)料明細(xì)表，簡稱“l(fā)lmx”，加上標(biāo)題行一共33500行，A:H共8列，其中A2:A33500是領(lǐng)料明細(xì)物料號碼，再加上第9列I列，標(biāo)題取為“單耗分類”。

Sheet2指標(biāo)范圍表，簡稱“zbfw”，加上標(biāo)題行一共19956行，A:E共5列，其中B2:B19956是100類單耗包含的19955種材料的具體物料編碼，E列是每一種材料在100類單耗中的分類。

把這Sheet1、Sheet2兩張表放在一個工作簿里，取名“單耗統(tǒng)計.xls”

在excel中調(diào)用vb編輯器，編程如下：

……

Dim aa As Range

Dim bb As Range

Dim cc As String

Dim r As Single

Set aa=Workbooks(\"單耗統(tǒng)計.xls\").Sheets(\"zbfw\").Range(\"b2:b19956\")

For j = 2 To 33500

cc = Workbooks(\"單耗統(tǒng)計.xls\").Sheets(\"llmx\").Cells(j，1).Value

Workbooks(\"單耗統(tǒng)計.xls\").Sheets(\"llmx\").Cells(j，1).Select

DoEvents

Set bb = aa.Find(cc)

If Not bb Is Nothing Then

r = bb.Row

Workbooks(\"單耗統(tǒng)計.xls\").Sheets(\"llmx\").Cells(j，9).Value = Workbooks(\"單耗統(tǒng)計.xls\").Sheets(\"zbfw\").Cells(r，5).Value

End If

Next j

……

程序的主要思想是從zbfw表中取物料編碼，到llmx表中去找，如果找到了，就把物料的分類賦予llmx表中該物料。使用VBA在工作表或單元格區(qū)域中查找某項(xiàng)數(shù)據(jù)時，我們通常使用For…Next循環(huán)，這在小范圍中使用還可以，但應(yīng)用在大量數(shù)據(jù)中查找時，會耗費(fèi)較多時間。所以我們采用程序主要核心是Find方法，其最主要的原因是查找的速度。例如，區(qū)域查找，在區(qū)域中查找特定信息，并返回 Range 對象，該對象代表用于查找信息的第一個單元格。如果未發(fā)現(xiàn)匹配單元格，就返回Nothing，該方法不影響選定區(qū)域或活動單元格。

……

Dim MRG As Range，AAA As String

Set MRG =Range(\"A1:F16\").Find(\"A\")

AAA =MRG.Address

Do

Set MRG=Range(\"A1:F16\").FindNext(MRG)

MsgBox MRG.Address

Loop Until MRG.Address=AAA

……

若要進(jìn)行更為復(fù)雜的模式匹配查找，要用For Each...Next 語句和Like運(yùn)算符。

生成新的報表命名為“統(tǒng)計”，Worksheets.Add(After:= llmx).Name=\"統(tǒng)計\"，復(fù)制表頭到各新工作表，\"With llmx.Rows(1)；.Copy Sheets(\"統(tǒng)計\").Rows(1)，如果數(shù)據(jù)存在則復(fù)制到新目標(biāo)工作表，語句為“If Not統(tǒng)計Is Nothing Then，.EntireRow.Copy\"統(tǒng)計\".Rows(統(tǒng)計.UsedRange.Rows.Count+1)。

生成的“統(tǒng)計”新表Worksheets(\"統(tǒng)計\").Range(\"A1:I120\").Sort Key1:=Worksheets(\"統(tǒng)計\").Range(\"I1\")，.Subtotal GroupBy:=9， Function:=xlSum，TotalList:=Array(6，7)等語句，就可以實(shí)現(xiàn)“單耗分類”索引排序、匯總，得到約100種單耗的指標(biāo)。如圖2。

在讀取成千上萬行數(shù)據(jù)時需要一個進(jìn)度顯示，為了進(jìn)一步完善該程序，編寫進(jìn)度表。

進(jìn)入Visual Basic編輯器，選擇“插入”、“用戶窗體”，即生成一個userform1，從右下方的屬性欄中（右鍵單擊可以選擇彈出屬性欄），將caption設(shè)置為“單耗統(tǒng)計進(jìn)度……”，width設(shè)置為350，Height設(shè)置為50。緊接著需要插入1個Label標(biāo)簽，Label1放置在UserForm1的中間偏右一點(diǎn)，caption設(shè)置為空值，BackColor設(shè)置為紅色，SpecialEffect設(shè)置為2-fmspecialeffectsunken，Width=335，Height=15，然后在選擇插入-模塊，在這里面編寫一個宏。如：

……

UserForm1.Label1.Width = Int(j / 33500 * 335)

UserForm1.Label1.Caption = Int(j / 33500 * 100) \"%\"

DoEvents

……

為了操作宏方便，在Exce中加命令按鈕，其屬性指定宏“單耗統(tǒng)計”。完成后自動彈出，MsgBox“數(shù)據(jù)統(tǒng)計完畢”對話框。

4 結(jié)語

通過Excel Vba語言編寫宏完成材料單耗統(tǒng)計只用7分鐘， 大大節(jié)約大量人力物力，進(jìn)一步提高辦公自動化程度。當(dāng)然在實(shí)際的應(yīng)用中，還會有許多個性化的問題出現(xiàn)，但基于VBA 的應(yīng)用可以幫助我們解決許多看似復(fù)雜的應(yīng)用問題。只要能熟練的掌握方法，就可以有許多的函數(shù)、宏來解決實(shí)際應(yīng)用問題。

參考文獻(xiàn)：

[1]解保紅.基于VBA的EXCEL管理應(yīng)用[J].科技創(chuàng)新與生產(chǎn)力，2010，(12)

[2]沐士光.Excel中的VBA應(yīng)用探討[J].玉溪師范高等專科學(xué)校學(xué)報，2000，(03).