楊忠烽

（陜西陜煤黃陵礦業(yè)有限公司 一號煤礦　黃陵　727307）

Excel VBA在考核統(tǒng)計中的應用

楊忠烽

（陜西陜煤黃陵礦業(yè)有限公司 一號煤礦黃陵727307）

本文借助Excel VBA編程工具，采用化整為零和逆向編程的思路，將員工考核統(tǒng)計問題逐步簡單化，并設計出合理的操作窗體，使得執(zhí)行程序更加便捷，最終實現(xiàn)了核算工作的高效性和可靠性。

Excel VBA考核獎罰模塊過程函數(shù)

一、引言

工資核算是每個單位的日常要務，該工作要求及時、準確，然而，考核太多卻成為眾多核算工作中的攔路虎。比如黃陵一號煤礦選煤廠，每月僅員工考核就達300多項，如果只靠手工整理，往往要忙上幾天，不但效率低，還容易出錯。而VBA作為Office應用程序內(nèi)嵌的編程工具，為提高辦公效率開辟了簡單易行的途徑。

二、功能需求

一號煤礦選煤廠核算表運用了較多的VBA模塊，其中，考核統(tǒng)計功能主要是用來動態(tài)更新員工的考核分數(shù)和考核明細，從而提高考核統(tǒng)計效率和可靠性。

職工考核表分可為“大班組”和“小班組”兩種；考核對象可分為“職工”和“班長”兩類。大班組考核表樣式如圖1所示。

表中，上部分為職工區(qū)域，下部分為班長區(qū)域。職工區(qū)域的左邊為每日得分，右邊包含“返還獎罰”和“獎罰”列；班長區(qū)域僅包含“獎罰”列。小班組考核表與班長區(qū)域類似，也僅包含“獎罰”列。

統(tǒng)計考核時，先將考核信息統(tǒng)一錄入“獎罰”表中，再通過VBA功能塊，自動將考核分數(shù)填入考核表對應人員的“獎罰”列或“返還獎罰”列中。然后，在“獎罰明細”表中，將考核明細按照班組分類并編號顯示。功能需求詳情見表1，其中的“+”代表有需求。

表1　考核統(tǒng)計功能需求表

三、編程思路

本文采用化整為零的編程思路，將功能需求逐步分解。編程思路分析如圖2所示。

由圖可見，本程序首先將主程序分解為“更新分數(shù)”和“更新明細”兩個模塊。在“更新分數(shù)”模塊中，又通過調(diào)用對應的子過程，再從子過程中調(diào)用對應的函數(shù)，最終將問題歸結(jié)為編寫函數(shù)。編程時，可采用逆向思維，從最簡單的函數(shù)編起，從而簡化問題。

四、代碼簡析

該程序主要針對獎罰表進行操作，其段見表2。

?

1、更新分數(shù)模塊

（1）編寫函數(shù)

該程序具有2個函數(shù)，分別為獎罰函數(shù)和返還獎罰函數(shù)，現(xiàn)以返還獎罰函數(shù)為例進行說明。程序代碼如下所示。

Function FAN（name As String，banzu As String）

Dim intFen As Integer，i As Integer，k As Integer

intFen=0

With Sheets（"獎罰"）

k=.［a65536］.End（xlUp）.Row

For i=2 To k

If.Cells（i，3）=name And.Cells（i，1）=banzu And. Cells（i，6）="是"Then intFen=intFen+.Cells（i，5）

Next

End With

FAN=-intFen

End Function

該函數(shù)以員工姓名和班組為參數(shù)，通過遍歷有效獎罰記錄，將指定員工已落實的獎罰分進行累加并取負值，然后返回該分數(shù)。

（2）編寫過程

和函數(shù)對應，子過程也有兩個，現(xiàn)以返還獎罰過程為例予以說明。程序代碼如下所示。

Sub Fanhuan（r1 As Integer，r2 As Integer，c As Integer，ByVal name As String）

Dim j As Integer

With Sheets（name）

For j=0 To r2-r1-1

.Cells（r1+j，c）=FAN（.Cells（r1+j，2），name）

Next

End With

End Sub

該過程是對指定班組中的一列相鄰員工進行操作，需要員工起始行、終止行、填分列和班組名稱共4個參數(shù)。過程執(zhí)行后，會將一列員工的獎罰分或返還獎罰分填入對應的單元格，實現(xiàn)獎罰分數(shù)更新功能。

（3）編寫模塊

更新分數(shù)模塊的核心代碼如下所示。

For i=0 To 11

With Sheets（arrName（i））

row1=4

row2=.Cells.Find（"以量計分"，，，xlWhole）.Row

row3=row2+2

row4=.Cells.Find（"班長得分"，，，xlWhole）.Row

col2=.Cells.Find（"獎罰"，，，xlWhole）.Column

If i〈8 Then

col1=.Cells.Find（"返還"，，，xlPart）.Column

Call Fanhuan（row1，row2，col1，arrName（i））

End If

Call Jiangfa（row1，row2，col2，arrName（i））

Call Jiangfa（row3，row4，col2，arrName（i））

End With

Next

其中，數(shù)組元素arrName（i）為班組名稱，row1和 row2分別為職工區(qū)域的起、止行，row3和row4分別為班長區(qū)域的起、止行。i〈8即為大班組的情況，需要執(zhí)行返還獎罰過程。

2、更新明細模塊

該程序首先將獎罰明細按照班組編號后賦值給數(shù)組，再利用循環(huán)將數(shù)組元素填入獎罰明細表中?？紤]到Excel單元格有字符串長度限制，設置每班占用2個單元格，明細超長部分自動顯示到第2個單元格。另外，如果明細單元格為空，則自動隱藏。程序核心代碼如下所示。

With Sheets（"獎罰"）

j=.［a65536］.End（xlUp）.Row

For k=0 To 11

For i=2 To j

If.Cells（i，1）=strName（k）Then

n（k）=n（k）+1

If（Len（strN（2*k））+Len（.Cells（i，7））+7）〈1024 Then

strN（2*k）=strN（2*k）&"【"&n（k）&"】"&CStr （.Cells（i，7））&"。"

Else

strN（2*k+1）=strN（2*k+1）&"【"&n（k）&"】"&CStr（.Cells（i，7））&"。"

End If

End If

Next

Next

End With

With Sheets（"獎罰明細"）

.Rows（"3：26"）.Hidden=False

For i=3 To 26

.Cells（i，2）=strN（i-3）

If.Cells（i，2）=""Then

.Cells（i，2）.EntireRow.Hidden=True

End If

Next

End With

其中，strName（k）為班組名稱，strN為保存明細的數(shù)組。

五、執(zhí)行程序

為方便操作，本程序設置了一個高級維護窗體（圖3所示）。

打開窗體后，只要選擇“生成獎罰”并點擊“執(zhí)行”按鈕，就會依次執(zhí)行更新分數(shù)和更新明細模塊，實現(xiàn)了員工獎罰考核分數(shù)和考核明細一鍵更新。該窗體兼具快速導出結(jié)果等功能，為協(xié)同上級核算中心可靠統(tǒng)計員工得分提供了方便。

六、結(jié)語

借助Excel VBA程序，不僅使得核算工作效率提高了數(shù)倍，而且有效地避免了人工操作引發(fā)的各種失誤，更為實現(xiàn)辦公自動化協(xié)同作業(yè)奠定了基礎。

［1］［英］洛邁克斯（Lomax，P.）編著；劉海明譯.VB與VBA技術手冊［M］.北京：中國電力出版社，2002.5.

［2］伍云輝等編著.Excel VBA辦公應用開發(fā)詳解［M］.北京：電子工業(yè)出版社，2008.1.

［3］趙雪慧，趙 瑋編著.Visual Basic程序開發(fā)完整實例教程［M］.北京：海洋出版社，2003.3.

［4］Microsoft Corporation著，微軟（中國）有限公司譯. Visual Basic 6.0中文版程序員指南［M］.北京：北京希望圖書創(chuàng)作室，1998.

楊忠烽（1982～），男，陜西榆林人，工學學士，主要從事機械、電氣、液壓、工業(yè)控制和辦公自動化方面的實踐工作。

Application of VBA Excel in assessment and statistics

Yang Zhongfeng
（No.1 Coal Mine，Huangling Mining Industry Co.，Ltd.，Shaanxi Coal and Chemical Industry Group Co.，Ltd.，Huangling727307）

AbstractThe paper by Excel VBA programming tool，using the idea of break up the whole into parts and reverse thinking，to simplify the assessment and statistical problems of employees，and the design of a reasonable form to operate，making the implementation of the program more convenient.In the end，the efficiency and reliability of the accounting work is realized.

Excel VBAAssessmentReward and penaltyModuleProcessFunction

TP317.3

A

160521-7299