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