Excel VBA在考核統計中的應用

楊忠烽

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

Excel VBA在考核統計中的應用

楊忠烽

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

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

Excel VBA考核獎罰模塊過程函數

一、引言

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

二、功能需求

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

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

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

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

表1　考核統計功能需求表

三、編程思路

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

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

四、代碼簡析

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

?

1、更新分數模塊

（1）編寫函數

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

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

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

（2）編寫過程

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

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個參數。過程執行后，會將一列員工的獎罰分或返還獎罰分填入對應的單元格，實現獎罰分數更新功能。

（3）編寫模塊

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

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

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

2、更新明細模塊

該程序首先將獎罰明細按照班組編號后賦值給數組，再利用循環將數組元素填入獎罰明細表中。考慮到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為保存明細的數組。

五、執行程序

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

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

六、結語

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

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

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

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

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

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

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