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

楊忠烽

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

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

楊忠烽

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

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

Excel VBA考核獎(jiǎng)罰模塊過(guò)程函數(shù)

一、引言

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

二、功能需求

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

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

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

統(tǒng)計(jì)考核時(shí)，先將考核信息統(tǒng)一錄入“獎(jiǎng)罰”表中，再通過(guò)VBA功能塊，自動(dòng)將考核分?jǐn)?shù)填入考核表對(duì)應(yīng)人員的“獎(jiǎng)罰”列或“返還獎(jiǎng)罰”列中。然后，在“獎(jiǎng)罰明細(xì)”表中，將考核明細(xì)按照班組分類并編號(hào)顯示。功能需求詳情見(jiàn)表1，其中的“+”代表有需求。

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

三、編程思路

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

由圖可見(jiàn)，本程序首先將主程序分解為“更新分?jǐn)?shù)”和“更新明細(xì)”兩個(gè)模塊。在“更新分?jǐn)?shù)”模塊中，又通過(guò)調(diào)用對(duì)應(yīng)的子過(guò)程，再?gòu)淖舆^(guò)程中調(diào)用對(duì)應(yīng)的函數(shù)，最終將問(wèn)題歸結(jié)為編寫(xiě)函數(shù)。編程時(shí)，可采用逆向思維，從最簡(jiǎn)單的函數(shù)編起，從而簡(jiǎn)化問(wèn)題。

四、代碼簡(jiǎn)析

該程序主要針對(duì)獎(jiǎng)罰表進(jìn)行操作，其段見(jiàn)表2。

?

1、更新分?jǐn)?shù)模塊

（1）編寫(xiě)函數(shù)

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

Function FAN（name As String，banzu As String）

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

intFen=0

With Sheets（"獎(jiǎng)罰"）

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ù)，通過(guò)遍歷有效獎(jiǎng)罰記錄，將指定員工已落實(shí)的獎(jiǎng)罰分進(jìn)行累加并取負(fù)值，然后返回該分?jǐn)?shù)。

（2）編寫(xiě)過(guò)程

和函數(shù)對(duì)應(yīng)，子過(guò)程也有兩個(gè)，現(xiàn)以返還獎(jiǎng)罰過(guò)程為例予以說(shuō)明。程序代碼如下所示。

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

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

（3）編寫(xiě)模塊

更新分?jǐn)?shù)模塊的核心代碼如下所示。

For i=0 To 11

With Sheets（arrName（i））

row1=4

row2=.Cells.Find（"以量計(jì)分"，，，xlWhole）.Row

row3=row2+2

row4=.Cells.Find（"班長(zhǎng)得分"，，，xlWhole）.Row

col2=.Cells.Find（"獎(jiǎng)罰"，，，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分別為班長(zhǎng)區(qū)域的起、止行。i〈8即為大班組的情況，需要執(zhí)行返還獎(jiǎng)罰過(guò)程。

2、更新明細(xì)模塊

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

With Sheets（"獎(jiǎng)罰"）

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（"獎(jiǎng)罰明細(xì)"）

.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為保存明細(xì)的數(shù)組。

五、執(zhí)行程序

為方便操作，本程序設(shè)置了一個(gè)高級(jí)維護(hù)窗體（圖3所示）。

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

六、結(jié)語(yǔ)

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

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

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

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

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

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

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