Allowance on performance management in the college application Excel VBA

Abstract: According to the characteristics of university management performance grants and the actual needs, develop the use of Excel VBA program, grants management standards to improve performance and accuracy.

Keywords: VBA application performance benefits

Excel is an important management tool for university grants, often used to grant a summary of the distribution, statistics and so on. Excel VBA is to Excel as a development platform to develop applications, by recording a macro to generate the macro code directly to the line of the Excel worksheet control, in order to achieve an Excel worksheet data processing automation.

A needs analysis
College performance benefits mainly refers to schools in the personnel structure of the two units and full year to complete the teaching, Research, papers, patents, awards, the number of tasks such as introduction of talents, appropriated in accordance with the overall college program, the total accounting of the two units allowance, a monthly advance of the annual net of two units of school grants, the remaining part of the unit's performance as the two allowances performance benefits with incentive pay for performance properties, mainly used to reward outstanding contributions to colleges and school development faculty. performance benefits of the end of a one-time payment of commonly used way of payment due to the two units to complete the overall mission, there is mutual co-operation between different units, hence the release of the annual performance benefits there will be the same two employees in different level distribution unit also happen, the two different work units according to their corresponding performance benefits paid, which requires workers to find out the same results in different units to obtain grants, and the aggregate sum of Finance issued evacuation. Specifically as shown in Table 1 (Annual 2008 in a college subsidy payments table, then the need for the Institute of Communications Engineering Huang Fei Zhang and performance benefits enjoyed by the sum the aggregate, and only retain the total number of line can be.

The demand for more university staff, if you use manual mode, the data processing will become very complicated, Excel Vba in the language can be a good deal with the issue.

Second, the solution
For demand analysis, found that the data stored in an Excel spreadsheet, the function of the method can also be used to achieve the goals, functions and Sumif can Countif function, but after using these two functions need to manually filter and delete, and function data the case of large amount of running as fast as Vba program speed, so a program will be more efficient and fast. concrete steps are as follows:
① Open the table where the documents for the table named sheet1.

② Press the Alt + Fll shortcut, enter the VBA code writing window, click on the menu - Insert - Module, New Module 1, click on Module 1, in the code window, enter the following command to run:
Sub compare ()
Dim i As Integer, j As Integer, m As Integer 'define the variable
Dim row As Integer, row1 As Integer
row = 8 'the number taking the form of head office
row1 = row
For i = 1 To row - 1 'loop is used to find the same people
If Cells (i, "g") <> "DELETE" Then 'If you have to add remove the tag, not the following cycle
For j = i + 1 To row
If Cells (j, "e") = Cells (i, "e") and Cells (j, "d") = Cells (i, "d") Then 'determine the number and names of wage equality, equality is the following program
Cells (i, "f") = Cells (i, "f") + Cells (j, "f") 'the sum of the same performance benefits of workers
Cells (j, "g") = "DELETE" 'Add or Remove tag
End If, Next j, End If, Next i
For m = 1 To row1 'loop is used to remove records marked for deletion have been added
Cells (m, "g"). Select
If ActiveCell = "DELETE" Then
Selection.EntireRow.Delete
row1 = row1 - 1
m = m - 1
End If, Next m, End Sub
③ Remove Table 3 "release unit" column.

After these three steps to get the results in Table 2:
Third, the effectiveness analysis
Vba using EXCEL in the language, the ability to accurately and quickly resolve performance benefits management in the college of practical problems, compared to the function approach, with its unparalleled advantages for the college to provide a scientific and efficient compensation management ideas and methods.

References:
[1] Xiong Gaoying. Excel Vba in the calculation of personal income in the application [J]. China's management of information technology, 2008 (4:18-20
[2] Zhao Weixu etc. Using VBA to expand Excel [J]. China's management of information technology, 2007 (12:37-39
[3] Sun Haimei. University post allowance system in practice and thinking [J]. Heilongjiang Higher Education Research, 2008 (6:46-48 Links to free download http://www.hi138.com