CN112819309A - Employee performance scoring and payroll calculation system based on EXCEL software - Google Patents
Employee performance scoring and payroll calculation system based on EXCEL software Download PDFInfo
- Publication number
- CN112819309A CN112819309A CN202110094876.7A CN202110094876A CN112819309A CN 112819309 A CN112819309 A CN 112819309A CN 202110094876 A CN202110094876 A CN 202110094876A CN 112819309 A CN112819309 A CN 112819309A
- Authority
- CN
- China
- Prior art keywords
- department
- month
- performance
- file
- data
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
- 238000004364 calculation method Methods 0.000 title claims description 22
- 238000011156 evaluation Methods 0.000 claims abstract description 109
- 238000009826 distribution Methods 0.000 claims abstract description 40
- 230000008520 organization Effects 0.000 claims abstract description 6
- 238000000034 method Methods 0.000 claims description 15
- 238000012550 audit Methods 0.000 claims description 13
- 230000008676 import Effects 0.000 claims description 11
- 238000010276 construction Methods 0.000 claims description 6
- 230000001351 cycling effect Effects 0.000 claims description 6
- 238000013461 design Methods 0.000 claims description 6
- 238000012986 modification Methods 0.000 claims description 5
- 230000004048 modification Effects 0.000 claims description 5
- 238000012552 review Methods 0.000 claims description 5
- 241001522296 Erithacus rubecula Species 0.000 claims description 3
- 230000004075 alteration Effects 0.000 claims description 3
- 230000000903 blocking effect Effects 0.000 claims description 3
- 238000005266 casting Methods 0.000 claims description 3
- 238000012790 confirmation Methods 0.000 claims description 3
- 238000011017 operating method Methods 0.000 claims description 3
- 230000008569 process Effects 0.000 claims description 3
- 230000006978 adaptation Effects 0.000 abstract description 2
- 238000010586 diagram Methods 0.000 description 6
- 238000007726 management method Methods 0.000 description 3
- 238000012356 Product development Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 238000012854 evaluation process Methods 0.000 description 1
- 238000009434 installation Methods 0.000 description 1
- 238000004519 manufacturing process Methods 0.000 description 1
- 238000003825 pressing Methods 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 238000004080 punching Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/06—Resources, workflows, human or project management; Enterprise or organisation planning; Enterprise or organisation modelling
- G06Q10/063—Operations research, analysis or management
- G06Q10/0639—Performance analysis of employees; Performance analysis of enterprise or organisation operations
- G06Q10/06393—Score-carding, benchmarking or key performance indicator [KPI] analysis
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/23—Updating
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2428—Query predicate definition using graphical user interfaces, including menus and forms
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06Q—INFORMATION AND COMMUNICATION TECHNOLOGY [ICT] SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES; SYSTEMS OR METHODS SPECIALLY ADAPTED FOR ADMINISTRATIVE, COMMERCIAL, FINANCIAL, MANAGERIAL OR SUPERVISORY PURPOSES, NOT OTHERWISE PROVIDED FOR
- G06Q10/00—Administration; Management
- G06Q10/10—Office automation; Time management
- G06Q10/107—Computer-aided management of electronic mailing [e-mailing]
Landscapes
- Engineering & Computer Science (AREA)
- Business, Economics & Management (AREA)
- Theoretical Computer Science (AREA)
- Human Resources & Organizations (AREA)
- Physics & Mathematics (AREA)
- General Physics & Mathematics (AREA)
- Entrepreneurship & Innovation (AREA)
- Strategic Management (AREA)
- Economics (AREA)
- Data Mining & Analysis (AREA)
- General Engineering & Computer Science (AREA)
- Computational Linguistics (AREA)
- Quality & Reliability (AREA)
- Tourism & Hospitality (AREA)
- Marketing (AREA)
- General Business, Economics & Management (AREA)
- Educational Administration (AREA)
- Databases & Information Systems (AREA)
- Development Economics (AREA)
- Operations Research (AREA)
- Artificial Intelligence (AREA)
- Audiology, Speech & Language Pathology (AREA)
- General Health & Medical Sciences (AREA)
- Human Computer Interaction (AREA)
- Mathematical Physics (AREA)
- Health & Medical Sciences (AREA)
- Computer Hardware Design (AREA)
- Game Theory and Decision Science (AREA)
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
- Financial Or Insurance-Related Operations Such As Payment And Settlement (AREA)
Abstract
The invention discloses an employee performance scoring and wage calculating system based on EXCEL software, which comprises a department manager performance evaluation system and a performance wage distribution system, wherein the department manager uses the department manager performance evaluation system to complete the department performance evaluation and sends the department performance evaluation to a deputy; then, after collecting the performance evaluation of each department, the manager uses the performance wage distribution system to collect and adjust and generate a wage table and distributes the wage table to the personnel department. The product provided by the invention has the advantages of accurate adaptation to organization architecture and management operation mode of a company, low requirement on operation environment, simplicity in operation, strong error resistance, high automation degree and the like.
Description
Technical Field
The invention relates to the field of employee performance scoring and wage calculation, in particular to an employee performance scoring and wage calculation system based on EXCEL software.
Background
Staff performance evaluation is an important link of enterprise human resource management, and for finished automobile manufacturing enterprises, especially product development departments with the most accumulated engineering technical staff, staff performance evaluation cannot be realized only by attendance checking and card punching or other simple indexes, and all levels of leaders and cadres need to be comprehensively evaluated by combining multiple factors. The huge number of personnel, the complicated evaluation method and the complicated data processing work on the basis of the evaluation method make the staff performance evaluation become the 'difficulty' faced by the middle-level cadres every month.
The performance evaluation process has the following difficulties:
1) after the roster of the staff is updated, the evaluation table needs to be manually adjusted, which wastes time and labor;
2) personnel omission, personnel dislocation, evaluation omission, wage calculation error and the like occur due to negligence, no clear view, wrong key pressing and the like;
3) individual middle and basic cadres violate the regulations of the company, and the performance score of the employee is increased by utilizing vulnerability stealing, so that the proportion of the whole department exceeds the standard, and troubles are brought to the high level of the company.
Due to the particularity of the company management system, the general performance evaluation software on the market cannot be directly adopted, and the cost of customization and development is too high to be accepted by the company.
Disclosure of Invention
In order to solve the technical problems, the invention provides the following technical scheme:
the invention provides an employee performance scoring and payroll calculating system based on EXCEL software,
comprises a performance evaluation system of a department manager and a performance wage distribution system,
the department manager uses the department manager performance evaluation system to complete the department performance evaluation and sends the result to the captain; then, after collecting the performance evaluation of each department, the manager uses the performance wage distribution system to collect and adjust and generate a wage table and distributes the wage table to the personnel department.
Preferably, the department manager performance evaluation system main body is set as an xlsm file, the file comprises four worksheets of '1 month', 'employee roster', 'annual summary', 'annual terminal summary', and a new month worksheet is added in the using process; the document also contains 1 user form UserForm1 as the main operation interface.
Preferably, the department manager uses the department manager performance evaluation system to evaluate the employees in the department at the end of each month, in the middle of each year and at the end of each year, and sends the evaluation result to the manager,
the department manager performance evaluation system comprises the following steps:
2.1: the start-up procedure is carried out,
the xlsm file of the system main body is opened, and the operation interface can automatically pop up after the macro function is started;
2.2: updating roster
2.2.1 when the person changes and needs to update the information, click the button [ update the roster ] →
2.2.2 background program automatically input password in protection state [ employee roster ], remove protection, and delete original table →
2.2.3 popping up a file selection window, setting to be only singly selected, selecting the updated roster file and/or the workbook, clicking to determine, opening the file →
2.2.4 activate employee roster worksheet →
2.2.5 calculating maximum department people → using round robin
2.2.6 copying and pasting department, personnel and modification date → to alignment
2.2.7 use the loop statement to count the number of end (xldown) selected cells to get the number of people in each department, then update to the top of the name of each department, and then the loop calculation gets the total number of people →in the development center
2.2.8 fill the "Serial number" column → according to the calculated maximum number of departures
2.2.9 protecting the worksheet after formatting to prevent alteration;
2.3 borrowing historical data
2.3.1 when needing to borrow historical data, click [ upgrade data carrier ] button →
2.3.2 popping up a file selection window, setting that only single selection is carried out, selecting an evaluation list file, clicking to confirm →
2.3.3 to prevent operation errors caused by file renaming, the name of the currently-operating file is compared with the name of the selected file, if not, the file is opened directly, if the same file is used, the selected file is renamed to 'original name _ bak', and then the file is opened →
2.3.4 comparing the names of departments in the '1 month' worksheet of the opened file with the names of departments in the '1 month' worksheet of the current file, if the names are not equal, judging that the names are not matched, closing the file, and exiting the operation; if so, copying the data of the file to the current file;
2.4: selecting departments and months to be evaluated
2.4.1 in order to set a subroutine LB _ data _ ready for calling, an important parameter exists, where exists is 0, which indicates that the selected month does not exist, and a new worksheet named in the month is created; exist ═ 1, which indicates that the selected month exists, and data on the existing worksheet will be called and modified; exist ═ 2, which indicates that what is to be examined is the summary (middle of year/end of year)) module; exist ═ 3, which indicates that a new department is selected, and defines a variable m ═ 6, which indicates the number of columns to be displayed by the ListBox 1;
2.4.2 the user form1 is provided with a ComboBox1 combo box for selecting departments, see fig. 2, wherein options for grabbing departments from the roster as a ComboBox1 pull-down menu are set in the useform _ initiative, and the department of the worksheet "month 1" is set as a default department;
2.4.3 when the ComboBox1 is clicked to select a department, comparing the value in the ComboBox1 with the value of the department in the current worksheet, if equal, not changing; if they are not equal, the user decides that a new department → is selected
2.4.4 popping up a selection box, prompting that a new file is newly created when a new department is selected, canceling if no, and saving the current file → if yes
2.4.5exist becomes 3, deletes the worksheets for all months except "1 month", and renames the current workbook → according to the current date
2.4.6 next, Call LB _ data _ ready with Call, and since exist is 3, execute the corresponding procedure:
1) define a Range-type variable rngs to locate in the "employee roster" the cell area of all the people in the department selected by ComboBox1 →
2) Define a variable k for calculating the total number of lines rngs, i.e. → the number of departures
3) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
4) Filling the array by using double-loop sentences, filling the serial numbers, the names and the affiliated departments, setting the performance scores, the performance final scores and the remark columns to be empty to be filled →
5) Initialize the data Source of ListBox1 →
6) Import the array into a ListBox1, set text center alignment →
7) Column width → of ListBox1 calculated by the current working page table header width
8) Choose first row →
9) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.7 calling LB _ data _ ready program, extracting a series of information of selected department, year, month, etc. into the current worksheet (summary in x month, year, and year end), making header, and storing the file as the performance appraisal file of new department;
2.4.8 also has a ComboBox2 combo box for selecting month, automatic identification of system date gives drop-down menu options from month 1to the current month, and two options "summary in year" and "summary in end of year" are given at the bottom;
2.4.9 when exist is 3, when just creating a department file, the program of ComboBox2 is in silent state, and needs to click ComboBox2 to select the month to be reviewed before starting operation;
2.4.10 when "summarization in year" or "summarization end of year" is selected, let exist be 2, activate "summarization in year" or "summarization end of year" worksheet according to ComboBox2 option, and call LB _ data _ ready →
2.4.11 because exist is 2, the corresponding program is executed:
1) sort all months' worksheets using bubble →
2) Setting a parameter temp, when ComboBox2 selects "summarization in year", making temp equal to 6, otherwise, making temp equal to 12 →
3) Using a loop statement, for all months less than or equal to temp, grab the person names in the worksheet, enter into the Uniqueitem set, and use the On Error statement to prevent duplicate items →
4) The number of elements in the Uniqueitem set (i.e., the number of people aggregated) is assigned to the variable k →
5) Defining a two-dimensional array arr _ b (1To k,1To 4), storing name, company-entering month, company-exiting month and construction period (month) in four columns, and initializing the name, the company-entering month, the company-exiting month and the construction period (month)
6) Recording the time of employee entry/exit, the detailed flow is shown in figure 4 →
7) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
8) Filling the array by using double-circulation sentences, filling the serial numbers, the names and the departments to which the names belong, setting the performance scoring, the leadership adjustment and the performance final assessment to be empty to be filled →
9) Calculating the summary average score of the employees (in the middle of the year/at the end of the year), wherein the specific flow is shown in figure 5;
10) initialize the data Source of ListBox1 →
11) Import the array into a ListBox1, set text center alignment →
12) Column width → of ListBox1 calculated by the current working page table header width
13) Choose first row →
14) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.12 if selecting month in ComboBox2, there are two ways, one is clicking pull-down menu, the other is directly inputting month, the convenient design is made, only inputting number, then adding "month" after automatically, and making mistake-proof design, the redundant character after "month" will be deleted automatically;
2.4.13 selection of month → in ComboBox2
2.4.14 traverse all worksheets in the workbook looking for a worksheet that is synonymous with the month selected in ComboBox2 →
2.4.15 if the existing data is found, let exist be 1, activate the existing worksheet, call LB _ data _ ready, and import the existing data into ListBox1, which is not described again in the above;
2.4.16 if not found, let exist be 0 and call LB _ data _ ready, the procedure is 2.4.7;
2.5: score the points
2.5.1 there are two ways to score, one is to select a person in ListBox1 in the UserForm1 interface and then enter in TextBox4, 6; secondly, clicking a [ confirm ] key cmdOK on a UserForm1 interface and inputting the key cmdOK into a worksheet;
and 2.5.2, setting an error-proofing program during scoring, namely intercepting data outside the interval of [0,100] to prevent input, and blocking the authority of a department manager to score for the department manager, wherein the scoring displays a prompt in a remark column and prevents data input.
Preferably, the performance wage issuing system main body is configured as an xlsm file, and the file contains a worksheet of "software description", "monthly award parameter", "distribution detail", "history data", "performance audit", "mail data", and "department setting"; and the system comprises six windows such as a main selection menu, monthly award parameter setting, performance evaluation department summary, evaluation examination module, performance wage calculation and data release.
Preferably, the division gives a summary of the evaluation tables of each department at the end of each month/year, adjusts the evaluation scores, completes the payroll and bonus scheme, and issues to the company personnel department,
the performance wage issuing system comprises the following steps:
4.1: start-up procedure
Opening the file, starting the macro function, automatically popping up the main selection menu,
the 7-piece worksheet of the performance wage issuing system functions as follows:
the operating method of the software is briefly explained;
the month award parameter is used for storing month award parameters issued by departments and personal resources;
the allocation detail is used for storing a wage allocation detail table and is provided with a program starting button;
the historical data is used for storing the historical data of wage distribution;
the performance audit is used for storing a performance evaluation table summarized by departments;
the sending address and the personnel for storing the mail;
department setting is used for storing department organization architecture data;
4.2: updating monthly prize parameters
4.2.1 clicking the button for the monthly award parameter issued by the people to enter a monthly award parameter setting interface,
4.2.2 click on the ComboBox1, select department, and choose options from [ department settings ] worksheet →
4.2.3 enter the department parameter → issued by the human affairs department in the TextBox1-5
4.2.4 clicking (abandoning), abandoning the setting and returning to the main selection menu;
4.2.5 click [ confirm ], write back [ monthly award parameter ] worksheet with selected department and modified parameters, and return to main selection menu;
4.3 introduction into Each Chamber for evaluation
4.3.1 click [ obtain department performance evaluation ], enter a performance evaluation department summary interface,
4.3.2TextBox2 will map out the department name in the [ monthly prize parameter ] worksheet, which is not editable;
4.3.3ComboBox2 selects months to summarize;
4.3.4 clicks [ browse ] to select the position of the file, the selection result is presented in a TextBox1, and the default is the position of the current file;
4.3.5ComboBox1 may select a file suffix to be found;
4.3.6 a subprogram List _ Box _ Frash is set up to write the file List screened out by the limited path and suffix into the ListBox 1;
4.3.7 is provided with a sub-program light _ department for identifying and selecting department files of the current department and reporting departments which do not submit the evaluation form;
4.3.8 is provided with a subroutine arrow _ click for implementing
The function of the button;
4.3.9 after selecting the file position, calling List _ Box _ Frash to refresh the List in ListBox1, calling light _ department to select the department file of the current department, and reporting the department which does not submit the evaluation List;
4.3.10 click
Selected files in the ListBox1 may be moved into the ListBox 2; click on
Selected files in the ListBox2 may be moved into the ListBox 1;
4.3.11CheckBox1 is an additional mode switch, if not selected, the new establishment is represented, and the original data in the work table of performance audit is cleared when the department summary is clicked;
4.3.12 click [ department summary ], start traversing the files in ListBox2, check if the department matches the month →
4.3.13 will alarm and terminate operation if there is no match;
4.3.14 if matching, inputting the summarized data into the 'performance audit' worksheet;
4.4 evaluation review
4.4.1 the main structure and operation principle of the evaluation and examination module are similar to those of 2.4 and 2.5, so the description is omitted,
4.4.2 after clicking (finishing), transmitting the final evaluation data to a (distribution detail) worksheet;
4.5 computing Performance wages
4.5.1 clicking [ calculating performance wages ] in a main selection menu, and entering a performance wage calculation interface;
4.5.2 click ComboBox 1to select the month to be released, the default options are 1 month to summary in the current month and year and summary at end of year;
4.5.3 the program automatically projects the data of the pages [ monthly prize parameter ] to TextBox 1-6;
4.5.4TextBox7-11 assigns the performance wage ratio for each evaluation level, which can be adjusted manually, the program sets a set of default values, and by clicking ToggleButton1, it can be written into TextBox 7-11;
4.5 clicking [ bonus allocation ], calculating the performance wages of all the personnel according to the ratings in the [ allocation details ] worksheet and the percentages in the TextBox7-11, randomly allocating at zero, writing the allocation result back to the [ allocation details ] worksheet, and clicking [ bonus allocation ] again to randomly switch zero acquirers;
4.5.6 selecting CheckBox1, no bonus and odds are assigned to employees who rate as empty;
4.5.7 when the total value of the performance criteria of the department issued by the human resources (TextBox2) is not equal to the actual distribution result, popping up an error prompt box to prevent writing in the distribution result;
4.6 publishing
4.6.1 after completing the prize money distribution, distributing the distribution result to the personnel department, inputting password confirmation in TextBox13 and TextBox14 before distribution, and if the password is forgotten, clicking ToggleButton2 can automatically input a default password;
4.6.2 after the password is input, clicking (data publishing), creating a workbook in an xlsx format according to the current data, named as 'xxxx year xx month performance wage publishing-xx department', inputting and storing [ distribution detail ] table data into the workbook, and entering a data publishing interface;
4.6.3 topic stored in TextBox1, default to "xx year xx month performance payroll list" in xx division, determined by the previously selected division and month and year, and can not be edited;
4.6.4 mail content is stored in TextBox7, default empty, editable;
4.6.5 Attachments names are stored in TextBox2, default "xxxx year xx month Performance Pair-xx division" is determined by the previously selected division and month of year, and are not editable;
4.6.6, casting addresses and names of main sending and copy persons from a [ mail data ] worksheet into a TextBox3-6, defaulting to a non-editable state, and clicking ToggleButton1 and ToggleButton2 to convert the addresses and the names into an editable state;
4.6.7 clicking [ affirm release ], opening Outlook software to create a mail according to the above content, sending the workbook file created by 2.6.2 as an attachment; if the CheckBox1 is checked, the mail is not sent out, but is saved as a draft;
4.6.8 click (abandon), abandon the operation and close all operation interfaces.
The invention has the beneficial effects
(1) The product provided by the invention mainly comprises two macro-enabled workbook files, each size is no more than 500KB, installation is not needed, the product can be used by a computer provided with office2016, the operation burden on the computer is small, and the product is more flexible and more convenient than software which moves hundreds of megabytes.
(2) The product provided by the invention has the advantages of accurate adaptation to organization architecture and management operation mode of a company, low requirement on operation environment, simplicity in operation, strong error resistance, high automation degree and the like.
Drawings
FIG. 1 is a flow chart of the operation of the performance evaluation system of the department manager.
FIG. 2 is a schematic view of an operation interface of the performance evaluation system of a department manager according to the present invention.
Fig. 3 is a flow chart of the present invention for recording employee enrollment/retirement time.
FIG. 4 is a flowchart of the present invention for computing the aggregated average division of employees (middle/end of year).
FIG. 5 is a flowchart of the department long program work of the present invention.
FIG. 6 is a diagram of a main selection menu interface according to the present invention.
Fig. 7 is a prize parameter setting interface diagram according to the present invention.
Fig. 8 is a summary interface diagram of performance assessment departments in accordance with the present invention.
FIG. 9 is an evaluation review module interface diagram according to the present invention.
Fig. 10 is a diagram of a performance payroll calculation interface in accordance with the present invention.
FIG. 11 is a diagram of a data distribution interface according to the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be clearly and completely described below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present invention.
Example 1
As shown in fig. 1-11, the present invention provides an employee performance scoring and payroll computing system based on EXCEL software,
comprises a performance evaluation system of a department manager and a performance wage distribution system,
the department manager uses the department manager performance evaluation system to complete the department performance evaluation and sends the result to the captain; then, after collecting the performance evaluation of each department, the manager uses the performance wage distribution system to collect and adjust and generate a wage table to be distributed to the personnel department,
the department manager performance evaluation system main body is set as an xlsm file, the file comprises four worksheets of '1 month', 'employee roster', 'annual summary', 'annual terminal summary', and a new month worksheet can be added in the using process; the file also comprises 1 user window Userform1 which is used as a main operation interface, a department manager uses a department manager performance evaluation system to evaluate employees in the department at the end of each month, in the middle of each year and at the end of each year, and sends the evaluation result to a manager, and the employee roster can be automatically led into the employee roster without copying and pasting and manual checking; automatically updating the date and the number of people and visualizing;
the department manager performance evaluation system comprises the following steps,
2.1: the start-up procedure is carried out,
the xlsm file of the system main body is opened, and the operation interface can automatically pop up after the macro function is started;
2.2: updating roster
2.2.1 when the person changes and needs to update the information, click the button [ update the roster ] →
2.2.2 background program automatically input password in protection state [ employee roster ], remove protection, and delete original table →
2.2.3 popping up a file selection window, setting to be only singly selected, selecting the updated roster file and/or the workbook, clicking to determine, opening the file →
2.2.4 activate employee roster worksheet →
2.2.5 calculating maximum department people → using round robin
2.2.6 copying and pasting department, personnel and modification date → to alignment
2.2.7 use the loop statement to count the number of end (xldown) selected cells to get the number of people in each department, then update to the top of the name of each department, and then the loop calculation gets the total number of people →in the development center
2.2.8 fill the "Serial number" column → according to the calculated maximum number of departures
2.2.9 protecting the worksheet after formatting to prevent alteration;
2.3 borrowing historical data, automatically importing historical evaluation data, and improving efficiency
2.3.1 when needing to borrow historical data, click [ upgrade data carrier ] button →
2.3.2 popping up a file selection window, setting that only single selection is possible, selecting an evaluation table file, clicking to confirm →, automatically capturing roster data by the evaluation table file to generate an evaluation table, and automatically finishing corresponding operation according to different departments and months selected;
2.3.3 to prevent operation errors caused by file renaming, the name of the currently-operating file is compared with the name of the selected file, if not, the file is opened directly, if the same file is used, the selected file is renamed to 'original name _ bak', and then the file is opened →
2.3.4 comparing the names of departments in the '1 month' worksheet of the opened file with the names of departments in the '1 month' worksheet of the current file, if the names are not equal, judging that the names are not matched, closing the file, and exiting the operation; if so, copying the data of the file to the current file;
2.4: selecting departments and months to be evaluated
2.4.1 in order to set a subroutine LB _ data _ ready for calling, an important parameter exists, where exists is 0, which indicates that the selected month does not exist, and a new worksheet named in the month is created; exist ═ 1, which indicates that the selected month exists, and data on the existing worksheet will be called and modified; exist ═ 2, which indicates that what is to be examined is the summary (middle of year/end of year)) module; exist ═ 3, which indicates that a new department is selected, and defines a variable m ═ 6, which indicates the number of columns to be displayed by the ListBox 1;
2.4.2 the user form1 is provided with a ComboBox1 combo box for selecting departments, see fig. 2, wherein options for grabbing departments from the roster as a ComboBox1 pull-down menu are set in the useform _ initiative, and the department of the worksheet "month 1" is set as a default department;
2.4.3 when the ComboBox1 is clicked to select a department, comparing the value in the ComboBox1 with the value of the department in the current worksheet, if equal, not changing; if they are not equal, the user decides that a new department → is selected
2.4.4 popping up a selection box, prompting that a new file is newly created when a new department is selected, canceling if no, and saving the current file → if yes
2.4.5exist becomes 3, deletes the worksheets for all months except "1 month", and renames the current workbook → according to the current date
2.4.6 next, Call LB _ data _ ready with Call, and since exist is 3, execute the corresponding procedure:
1) define a Range-type variable rngs to locate in the "employee roster" the cell area of all the people in the department selected by ComboBox1 →
2) Define a variable k for calculating the total number of lines rngs, i.e. → the number of departures
3) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
4) Filling an array by using double-circulation sentences, filling sequence numbers, names and affiliated departments, setting performance grading, performance final evaluation and remark columns to be empty to be filled →, automatically marking evaluation levels according to the scores by the performance grading, counting the number of people at different levels, comparing the number of people with a set proportion standard, and displaying the people in an overproof manner to prevent the number of people from being overproof; the proportion standard can be adjusted; the annual mid/annual end scores are automatically aggregated, the manager can adjust the final scores,
5) initialize the data Source of ListBox1 →
6) Import the array into a ListBox1, set text center alignment →
7) Column width → of ListBox1 calculated by the current working page table header width
8) Choose first row →
9) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.7 calling LB _ data _ ready program, extracting a series of information of selected department, year, month, etc. into the current worksheet (summary in x month, year, and year end), making header, and storing the file as the performance appraisal file of new department;
2.4.8 also has a ComboBox2 combo box for selecting month, automatic identification of system date gives drop-down menu options from month 1to the current month, and two options "summary in year" and "summary in end of year" are given at the bottom;
2.4.9 when exist is 3, when just creating a department file, the program of ComboBox2 is in silent state, and needs to click ComboBox2 to select the month to be reviewed before starting operation;
2.4.10 when "summarization in year" or "summarization end of year" is selected, let exist be 2, activate "summarization in year" or "summarization end of year" worksheet according to ComboBox2 option, and call LB _ data _ ready →
2.4.11 because exist is 2, the corresponding program is executed:
1) sort all months' worksheets using bubble →
2) Setting a parameter temp, when ComboBox2 selects "summarization in year", making temp equal to 6, otherwise, making temp equal to 12 →
3) Using a loop statement, for all months less than or equal to temp, grab the person names in the worksheet, enter into the Uniqueitem set, and use the On Error statement to prevent duplicate items →
4) The number of elements in the Uniqueitem set (i.e., the number of people aggregated) is assigned to the variable k →
5) Defining a two-dimensional array arr _ b (1To k,1To 4), storing name, company-entering month, company-exiting month and construction period (month) in four columns, and initializing the name, the company-entering month, the company-exiting month and the construction period (month)
6) Recording the time of employee entry/exit, the detailed flow is shown in figure 4 →
7) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
8) Filling the array by using double-circulation sentences, filling the serial numbers, the names and the departments to which the names belong, setting the performance scoring, the leadership adjustment and the performance final assessment to be empty to be filled →
9) Calculating the summary average score of the employees (in the middle of the year/at the end of the year), wherein the specific flow is shown in figure 5;
10) initialize the data Source of ListBox1 →
11) Import the array into a ListBox1, set text center alignment →
12) Column width → of ListBox1 calculated by the current working page table header width
13) Choose first row →
14) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.12 if selecting month in ComboBox2, there are two ways, one is clicking pull-down menu, the other is directly inputting month, the convenient design is made, only inputting number, then adding "month" after automatically, and making mistake-proof design, the redundant character after "month" will be deleted automatically;
2.4.13 selection of month → in ComboBox2
2.4.14 traverse all worksheets in the workbook looking for a worksheet that is synonymous with the month selected in ComboBox2 →
2.4.15 if the existing data is found, let exist be 1, activate the existing worksheet, call LB _ data _ ready, and import the existing data into ListBox1, which is not described again in the above;
2.4.16 if not found, let exist be 0 and call LB _ data _ ready, the procedure is 2.4.7;
2.5: score the points
2.5.1 there are two ways to score, one is to select a person in ListBox1 in the UserForm1 interface and then enter in TextBox4, 6; secondly, clicking a [ confirm ] key cmdOK on a UserForm1 interface and inputting the key cmdOK into a worksheet;
2.5.2 setting error-proofing programs when scoring, namely intercepting data outside the interval [0,100] and preventing input, and blocking the authority of a department manager to score for the manager, wherein the scoring can display a prompt in a remark column and prevent data input, and the scoring error-proofing programs are provided with scoring error-proofing programs at scoring function modules of the manager and the department, and are used for intercepting illegal input and intercepting the manager to score for the manager;
the performance wage issuing system main body is set as an xlsm file, and the file comprises a software use description, monthly award parameters, distribution details, historical data, performance audit, mail data and department setting worksheet; and comprises six windows of a main selection menu, a monthly award parameter setting, a performance evaluation department summarizing, an evaluation examination module, a performance wage calculating and a data issuing, wherein the departments summarize the evaluation tables of all departments at the end of each month, the department evaluation table summarizing can automatically identify the evaluation table files of the departments to which the department belongs according to the organization structure setting and the selected month, the department summarizing evaluation table is generated by one-key importing, the evaluation score can be adjusted, wage and bonus issuing schemes are completed and issued to a company personnel department,
the performance wage issuing system comprises the following steps:
4.1: start-up procedure
Opening the file, starting the macro function, automatically popping up the main selection menu,
the 7-piece worksheet of the performance wage issuing system functions as follows:
the operating method of the software is briefly explained;
the month award parameter is used for storing month award parameters issued by departments and personal resources;
the allocation detail is used for storing a wage allocation detail table and is provided with a program starting button;
the historical data is used for storing the historical data of wage distribution;
the performance audit is used for storing a performance evaluation table summarized by departments;
the sending address and the personnel for storing the mail;
department setting is used for storing department organization architecture data;
4.2: updating monthly prize parameters
4.2.1 clicking the button for the monthly award parameter issued by the people to enter a monthly award parameter setting interface,
4.2.2 click on the ComboBox1, select department, and choose options from [ department settings ] worksheet →
4.2.3 enter the department parameter → issued by the human affairs department in the TextBox1-5
4.2.4 clicking (abandoning), abandoning the setting and returning to the main selection menu;
4.2.5 click [ confirm ], write back [ monthly award parameter ] worksheet with selected department and modified parameters, and return to main selection menu;
4.3 introduction into Each Chamber for evaluation
4.3.1 click [ obtain department performance evaluation ], enter a performance evaluation department summary interface,
4.3.2TextBox2 will map out the department name in the [ monthly prize parameter ] worksheet, which is not editable;
4.3.3ComboBox2 selects months to summarize;
4.3.4 clicks [ browse ] to select the position of the file, the selection result is presented in a TextBox1, and the default is the position of the current file;
4.3.5ComboBox1 may select a file suffix to be found;
4.3.6 a subprogram List _ Box _ Frash is set up to write the file List screened out by the limited path and suffix into the ListBox 1;
4.3.7 is provided with a sub-program light _ department for identifying and selecting department files of the current department and reporting departments which do not submit the evaluation form;
4.3.8 is provided with a subroutine arrow _ click for implementing
The function of the button;
4.3.9 after selecting the file position, calling List _ Box _ Frash to refresh the List in ListBox1, calling light _ department to select the department file of the current department, and reporting the department which does not submit the evaluation List, and an evaluation List review module, wherein the department manager can check all employee rating grades, can adjust the final rating, can check whether the rating proportion exceeds the standard, and can adjust the rating proportion standard; the payroll can enter a payroll module after being evaluated, so that the number of people is prevented from exceeding the standard;
4.3.10 click
Selected files in the ListBox1 may be moved into the ListBox 2; click on
Selected files in the ListBox2 may be moved into the ListBox 1;
4.3.11CheckBox1 is an additional mode switch, if not selected, the new establishment is represented, and the original data in the work table of performance audit is cleared when the department summary is clicked;
4.3.12 click [ department summary ], start traversing the files in ListBox2, check if the department matches the month →
4.3.13 will alarm and terminate operation if there is no match;
4.3.14 if matching, inputting the summarized data into the 'performance audit' worksheet;
4.4 evaluation review
4.4.1 the main structure and operation principle of the evaluation and examination module are similar to those of 2.4 and 2.5, so the description is omitted,
4.4.2 after clicking (finishing), transmitting the final evaluation data to a (distribution detail) worksheet;
4.5 computing Performance wages
4.5.1 click on the main selection menu [ calculate performance wage ], enter the performance wage calculation interface, the wage calculation can set the distribution proportion of different evaluation levels, and the adjustment can be flexibly carried out according to the department wage strategy; the password/default issuing function is set, so that the mistake can be prevented, and the work cannot be influenced by forgetting the password;
4.5.2 click ComboBox 1to select the month to be released, the default options are 1 month to summary in the current month and year and summary at end of year;
4.5.3 the program automatically projects the data of the pages [ monthly prize parameter ] to TextBox 1-6;
4.5.4TextBox7-11 assigns the performance wage ratio for each evaluation level, which can be adjusted manually, the program sets a set of default values, and by clicking ToggleButton1, it can be written into TextBox 7-11;
4.5 click [ bonus allocation ], the performance wages of all the personnel are calculated according to the rating in the [ allocation detail ] worksheet and the percentage in the TextBox7-11, the zero-head random allocation is carried out, the allocation result is written back to the [ allocation detail ] worksheet, the zero-head acquirers can be switched randomly by clicking [ bonus allocation ], departments can be selected by the department, and the setting and the modification of bonus parameters can be carried out according to the personal affair department in each month;
4.5.6 selecting CheckBox1, no bonus and odds are assigned to employees who rate as empty;
4.5.7 when the total value of the performance criteria of the department issued by the human resources (TextBox2) is not equal to the actual distribution result, popping up an error prompt box to prevent writing in the distribution result;
4.6 publishing
4.6.1 after completing the prize money distribution, distributing the distribution result to the personnel department, inputting password confirmation in TextBox13 and TextBox14 before distribution, and if the password is forgotten, clicking ToggleButton2 can automatically input a default password;
4.6.2 after the password is input, clicking data publishing, creating an xlsx-format workbook according to the current data, named as 'xxxx year xx month performance wage publishing department-xx', inputting the data of an allocation detail table into the workbook, storing the workbook, entering a data publishing interface, and automatically sending mails for data publishing, so that the operation steps are reduced, and the time and the energy are saved;
4.6.3 topic stored in TextBox1, default to "xx year xx month performance payroll list" in xx division, determined by the previously selected division and month and year, and can not be edited;
4.6.4 mail content is stored in TextBox7, default empty, editable;
4.6.5 Attachments names are stored in TextBox2, default "xxxx year xx month Performance Pair-xx division" is determined by the previously selected division and month of year, and are not editable;
4.6.6, casting addresses and names of main sending and copy persons from a [ mail data ] worksheet into a TextBox3-6, defaulting to a non-editable state, and clicking ToggleButton1 and ToggleButton2 to convert the addresses and the names into an editable state;
4.6.7 clicking [ affirm release ], opening Outlook software to create a mail according to the above content, sending the workbook file created by 2.6.2 as an attachment; if the CheckBox1 is checked, the mail is not sent out, but is saved as a draft;
4.6.8 clicking (abandoning), abandoning the operation and closing all operation interfaces;
example 2
As shown in fig. 1to fig. 11, the present embodiment is further optimized based on embodiment 1, specifically, for example,
the performance evaluation and payroll issue of x department (subordinate departments A, B and C) in 2020 and 5 months are carried out
Firstly, the personnel department sends an updated roster to managers in departments A, B and C, and sends the bonus parameters of 5 months to the department x for growth;
secondly, opening a department manager A/B/C (department manager performance evaluation system) and automatically popping up an operation interface;
clicking a button (update staff roster) to select an update roster sent by personnel department, clicking (confirm) to automatically import;
selecting 'A/B/C department', selecting '5 months', automatically generating an evaluation table named '5 months' and projecting the evaluation table to a main operation interface;
fifthly, scoring on the main operation interface or the worksheet;
sixthly, storing the current evaluation list file and sending the current evaluation list file to a manager;
seventhly, the x departments collect evaluation tables of departments A, B and C and place the evaluation tables at the positions of folders of a performance wage distribution system;
eighthly, opening a performance wage distribution system by x departments, and automatically popping up a main selection menu;
ninthly, clicking a monthly award parameter issued by the people resource button, entering a monthly award parameter setting interface, selecting x parts, and updating the 5-month bonus parameter;
tenthly, clicking (acquiring department performance evaluation), entering a (performance evaluation department summary) interface, and selecting a month to be summarized;
eleven, the system identifies the evaluation tables of departments A, B and C, automatically lights and clicks
Clicking a button (department summary) to automatically import the data of the target file for 5 months into a performance audit worksheet;
step twelve, clicking an evaluation examination button, entering an evaluation examination module interface, carrying out evaluation examination, clicking completion, and automatically inputting final evaluation data into an assignment detail worksheet;
thirteen, clicking the main selection menu (calculating performance wages), entering a performance wage calculation module, selecting a dispensing month, adjusting distribution percentages of all levels, clicking (bonus distribution), automatically completing the performance wage calculation of all employees and writing in an allocation detail working table;
fourteen, inputting a password, clicking data publishing, creating a workbook, copying an assignment detail worksheet, storing, entering a data publishing interface, editing contents and recipients, clicking data confirming publishing, automatically opening an Outlook file, generating a mail, and sending the mail.
Finally, it should be noted that: although the present invention has been described in detail with reference to the foregoing embodiments, it will be apparent to those skilled in the art that modifications may be made to the embodiments or portions thereof without departing from the spirit and scope of the invention.
Claims (5)
1. An employee performance scoring and payroll calculation system based on EXCEL software,
comprises a performance evaluation system of a department manager and a performance wage distribution system,
the department manager uses the department manager performance evaluation system to complete the department performance evaluation and sends the result to the captain; then, after collecting the performance evaluation of each department, the manager uses the performance wage distribution system to collect and adjust and generate a wage table and distributes the wage table to the personnel department.
2. The EXCEL software-based employee performance scoring and payroll calculation system according to claim 1,
the department manager performance evaluation system main body is set as an xlsm file, the file comprises four worksheets of '1 month', 'employee roster', 'annual summary', 'annual terminal summary', and a new month worksheet can be added in the using process; the document also contains 1 user form UserForm1 as the main operation interface.
3. The EXCEL software-based employee performance scoring and payroll calculation system of claim 2,
the department manager uses the department manager performance evaluation system to evaluate the employees in the department at the end of each month, in the middle of each year and at the end of each year, and sends the evaluation result to the manager,
the department manager performance evaluation system comprises the following steps:
2.1: the start-up procedure is carried out,
the xlsm file of the system main body is opened, and the operation interface can automatically pop up after the macro function is started;
2.2: updating roster
2.2.1 when the person changes and needs to update the information, click the button [ update the roster ] →
2.2.2 background program automatically input password in protection state [ employee roster ], remove protection, and delete original table →
2.2.3 popping up a file selection window, setting to be only singly selected, selecting the updated roster file and/or the workbook, clicking to determine, opening the file →
2.2.4 activate employee roster worksheet →
2.2.5 calculating maximum department people → using round robin
2.2.6 copying and pasting department, personnel and modification date → to alignment
2.2.7 use the loop statement to count the number of end (xldown) selected cells to get the number of people in each department, then update to the top of the name of each department, and then the loop calculation gets the total number of people →in the development center
2.2.8 fill the "Serial number" column → according to the calculated maximum number of departures
2.2.9 protecting the worksheet after formatting to prevent alteration;
2.3 borrowing historical data
2.3.1 when needing to borrow historical data, click [ upgrade data carrier ] button →
2.3.2 popping up a file selection window, setting that only single selection is carried out, selecting an evaluation list file, clicking to confirm →
2.3.3 to prevent operation errors caused by file renaming, the name of the currently-operating file is compared with the name of the selected file, if not, the file is opened directly, if the same file is used, the selected file is renamed to 'original name _ bak', and then the file is opened →
2.3.4 comparing the names of departments in the '1 month' worksheet of the opened file with the names of departments in the '1 month' worksheet of the current file, if the names are not equal, judging that the names are not matched, closing the file, and exiting the operation; if so, copying the data of the file to the current file;
2.4: selecting departments and months to be evaluated
2.4.1 in order to set a subroutine LB _ data _ ready for calling, an important parameter exists, where exists is 0, which indicates that the selected month does not exist, and a new worksheet named in the month is created; exist ═ 1, which indicates that the selected month exists, and data on the existing worksheet will be called and modified; exist ═ 2, which indicates that what is to be examined is the summary (middle of year/end of year)) module; exist ═ 3, which indicates that a new department is selected, and defines a variable m ═ 6, which indicates the number of columns to be displayed by the ListBox 1;
2.4.2 the user form1 is provided with a ComboBox1 combo box for selecting departments, see fig. 2, wherein options for grabbing departments from the roster as a ComboBox1 pull-down menu are set in the useform _ initiative, and the department of the worksheet "month 1" is set as a default department;
2.4.3 when the ComboBox1 is clicked to select a department, comparing the value in the ComboBox1 with the value of the department in the current worksheet, if equal, not changing; if they are not equal, the user decides that a new department → is selected
2.4.4 popping up a selection box, prompting that a new file is newly created when a new department is selected, canceling if no, and saving the current file → if yes
2.4.5exist becomes 3, deletes the worksheets for all months except "1 month", and renames the current workbook → according to the current date
2.4.6 next, Call LB _ data _ ready with Call, and since exist is 3, execute the corresponding procedure:
1) define a Range-type variable rngs to locate in the "employee roster" the cell area of all the people in the department selected by ComboBox1 →
2) Define a variable k for calculating the total number of lines rngs, i.e. → the number of departures
3) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
4) Filling the array by using double-loop sentences, filling the serial numbers, the names and the affiliated departments, setting the performance scores, the performance final scores and the remark columns to be empty to be filled →
5) Initialize the data Source of ListBox1 →
6) Import the array into a ListBox1, set text center alignment →
7) Column width → of ListBox1 calculated by the current working page table header width
8) Choose first row →
9) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.7 calling LB _ data _ ready program, extracting a series of information of selected department, year, month, etc. into the current worksheet (summary in x month, year, and year end), making header, and storing the file as the performance appraisal file of new department;
2.4.8 also has a ComboBox2 combo box for selecting month, automatic identification of system date gives drop-down menu options from month 1to the current month, and two options "summary in year" and "summary in end of year" are given at the bottom;
2.4.9 when exist is 3, when just creating a department file, the program of ComboBox2 is in silent state, and needs to click ComboBox2 to select the month to be reviewed before starting operation;
2.4.10 when "summarization in year" or "summarization end of year" is selected, let exist be 2, activate "summarization in year" or "summarization end of year" worksheet according to ComboBox2 option, and call LB _ data _ ready →
2.4.11 because exist is 2, the corresponding program is executed:
1) sort all months' worksheets using bubble →
2) Setting a parameter temp, when ComboBox2 selects "summarization in year", making temp equal to 6, otherwise, making temp equal to 12 →
3) Using a loop statement, for all months less than or equal to temp, grab the person names in the worksheet, enter into the Uniqueitem set, and use the On Error statement to prevent duplicate items →
4) The number of elements in the Uniqueitem set (i.e., the number of people aggregated) is assigned to the variable k →
5) Defining a two-dimensional array arr _ b (1To k,1To 4), storing name, company-entering month, company-exiting month and construction period (month) in four columns, and initializing the name, the company-entering month, the company-exiting month and the construction period (month)
6) Recording the time of employee entry/exit, the detailed flow is shown in figure 4 →
7) Define a two-dimensional array arr (k-1, m-1) for storing the data to be displayed by ListBox1 →
8) Filling the array by using double-circulation sentences, filling the serial numbers, the names and the departments to which the names belong, setting the performance scoring, the leadership adjustment and the performance final assessment to be empty to be filled →
9) Calculating the summary average score of the employees (in the middle of the year/at the end of the year), wherein the specific flow is shown in figure 5;
10) initialize the data Source of ListBox1 →
11) Import the array into a ListBox1, set text center alignment →
12) Column width → of ListBox1 calculated by the current working page table header width
13) Choose first row →
14) Refreshing the contents of the TextBox1 through 6 by cycling;
2.4.12 if selecting month in ComboBox2, there are two ways, one is clicking pull-down menu, the other is directly inputting month, the convenient design is made, only inputting number, then adding "month" after automatically, and making mistake-proof design, the redundant character after "month" will be deleted automatically;
2.4.13 selection of month → in ComboBox2
2.4.14 traverse all worksheets in the workbook looking for a worksheet that is synonymous with the month selected in ComboBox2 →
2.4.15 if the existing data is found, let exist be 1, activate the existing worksheet, call LB _ data _ ready, and import the existing data into ListBox1, which is not described again in the above;
2.4.16 if not found, let exist be 0 and call LB _ data _ ready, the procedure is 2.4.7;
2.5: score the points
2.5.1 there are two ways to score, one is to select a person in ListBox1 in the UserForm1 interface and then enter in TextBox4, 6; secondly, clicking a [ confirm ] key cmdOK on a UserForm1 interface and inputting the key cmdOK into a worksheet;
and 2.5.2, setting error-proofing program when scoring, firstly, intercepting data outside the interval of [0,100] and preventing input, and secondly, blocking the authority of a department manager to score for the department manager, and displaying a prompt in a remark column and preventing data input when scoring.
4. The EXCEL software-based employee performance scoring and payroll calculation system according to claim 1,
the performance wage issuing system main body is set as an xlsm file, and the file comprises a software use description, monthly award parameters, distribution details, historical data, performance audit, mail data and department setting worksheet; and the system comprises six windows such as a main selection menu, monthly award parameter setting, performance evaluation department summary, evaluation examination module, performance wage calculation and data release.
5. The EXCEL software-based employee performance scoring and payroll calculation system according to claim 4,
the department collects the evaluation tables of all departments at the end of each month, in the middle of each year and at the end of each year, can adjust the evaluation scores, complete the scheme of wage and bonus issuance, and releases the scheme to the personnel department of the company,
the performance wage issuing system comprises the following steps:
4.1: start-up procedure
Opening the file, starting the macro function, automatically popping up the main selection menu,
the 7-piece worksheet of the performance wage issuing system functions as follows:
the operating method of the software is briefly explained;
the month award parameter is used for storing month award parameters issued by departments and personal resources;
the allocation detail is used for storing a wage allocation detail table and is provided with a program starting button;
the historical data is used for storing the historical data of wage distribution;
the performance audit is used for storing a performance evaluation table summarized by departments;
the sending address and the personnel for storing the mail;
department setting is used for storing department organization architecture data;
4.2: updating monthly prize parameters
4.2.1 clicking the button for the monthly award parameter issued by the people to enter a monthly award parameter setting interface,
4.2.2 click on the ComboBox1, select department, and choose options from [ department settings ] worksheet →
4.2.3 enter the department parameter → issued by the human affairs department in the TextBox1-5
4.2.4 clicking (abandoning), abandoning the setting and returning to the main selection menu;
4.2.5 click [ confirm ], write back [ monthly award parameter ] worksheet with selected department and modified parameters, and return to main selection menu;
4.3 introduction into Each Chamber for evaluation
4.3.1 click [ obtain department performance evaluation ], enter a performance evaluation department summary interface,
4.3.2TextBox2 will map out the department name in the [ monthly prize parameter ] worksheet, which is not editable;
4.3.3ComboBox2 selects months to summarize;
4.3.4 clicks [ browse ] to select the position of the file, the selection result is presented in a TextBox1, and the default is the position of the current file;
4.3.5ComboBox1 may select a file suffix to be found;
4.3.6 a subprogram List _ Box _ Frash is set up to write the file List screened out by the limited path and suffix into the ListBox 1;
4.3.7 is provided with a sub-program light _ department for identifying and selecting department files of the current department and reporting departments which do not submit the evaluation form;
4.3.8 is provided with a subroutine arrow _ click for implementing
The function of the button;
4.3.9 after selecting the file position, calling List _ Box _ Frash to refresh the List in ListBox1, calling light _ department to select the department file of the current department, and reporting the department which does not submit the evaluation List;
4.3.10 click
Selected files in the ListBox1 may be moved into the ListBox 2; click on
Selected files in the ListBox2 may be moved into the ListBox 1;
4.3.11CheckBox1 is an additional mode switch, if not selected, the new establishment is represented, and the original data in the work table of performance audit is cleared when the department summary is clicked;
4.3.12 click [ department summary ], start traversing the files in ListBox2, check if the department matches the month →
4.3.13 will alarm and terminate operation if there is no match;
4.3.14 if matching, inputting the summarized data into the 'performance audit' worksheet;
4.4 evaluation review
4.4.1 the main structure and operation principle of the evaluation and examination module are similar to those of 2.4 and 2.5, so the description is omitted,
4.4.2 after clicking (finishing), transmitting the final evaluation data to a (distribution detail) worksheet;
4.5 computing Performance wages
4.5.1 clicking [ calculating performance wages ] in a main selection menu, and entering a performance wage calculation interface;
4.5.2 click ComboBox 1to select the month to be released, the default options are 1 month to summary in the current month and year and summary at end of year;
4.5.3 the program automatically projects the data of the pages [ monthly prize parameter ] to TextBox 1-6;
4.5.4TextBox7-11 assigns the performance wage ratio for each evaluation level, which can be adjusted manually, the program sets a set of default values, and by clicking ToggleButton1, it can be written into TextBox 7-11;
4.5 clicking [ bonus allocation ], calculating the performance wages of all the personnel according to the ratings in the [ allocation details ] worksheet and the percentages in the TextBox7-11, randomly allocating at zero, writing the allocation result back to the [ allocation details ] worksheet, and clicking [ bonus allocation ] again to randomly switch zero acquirers;
4.5.6 selecting CheckBox1, no bonus and odds are assigned to employees who rate as empty;
4.5.7 when the total value of the performance criteria of the department issued by the human resources (TextBox2) is not equal to the actual distribution result, popping up an error prompt box to prevent writing in the distribution result;
4.6 publishing
4.6.1 after completing the prize money distribution, distributing the distribution result to the personnel department, inputting password confirmation in TextBox13 and TextBox14 before distribution, and if the password is forgotten, clicking ToggleButton2 can automatically input a default password;
4.6.2 after the password is input, clicking (data publishing), creating a workbook in an xlsx format according to the current data, named as 'xxxx year xx month performance wage publishing-xx department', inputting and storing [ distribution detail ] table data into the workbook, and entering a data publishing interface;
4.6.3 topic stored in TextBox1, default to "xx year xx month performance payroll list" in xx division, determined by the previously selected division and month and year, and can not be edited;
4.6.4 mail content is stored in TextBox7, default empty, editable;
4.6.5 Attachments names are stored in TextBox2, default "xxxx year xx month Performance Pair-xx division" is determined by the previously selected division and month of year, and are not editable;
4.6.6, casting addresses and names of main sending and copy persons from a [ mail data ] worksheet into a TextBox3-6, defaulting to a non-editable state, and clicking ToggleButton1 and ToggleButton2 to convert the addresses and the names into an editable state;
4.6.7 clicking [ affirm release ], opening Outlook software to create a mail according to the above content, sending the workbook file created by 2.6.2 as an attachment; if the CheckBox1 is checked, the mail is not sent out, but is saved as a draft;
4.6.8 click (abandon), abandon the operation and close all operation interfaces.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110094876.7A CN112819309A (en) | 2021-01-25 | 2021-01-25 | Employee performance scoring and payroll calculation system based on EXCEL software |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110094876.7A CN112819309A (en) | 2021-01-25 | 2021-01-25 | Employee performance scoring and payroll calculation system based on EXCEL software |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN112819309A true CN112819309A (en) | 2021-05-18 |
Family
ID=75859049
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110094876.7A Pending CN112819309A (en) | 2021-01-25 | 2021-01-25 | Employee performance scoring and payroll calculation system based on EXCEL software |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN112819309A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115658144A (en) * | 2022-12-09 | 2023-01-31 | 西安葡萄城软件有限公司 | Spreadsheet formula on-demand calculation method and device |
| CN116167348A (en) * | 2023-04-20 | 2023-05-26 | 中瑞方胜人力资源科技(北京)有限公司 | Information processing method and device for generating payroll |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104866961A (en) * | 2015-05-15 | 2015-08-26 | 陕西华电蒲城发电有限责任公司 | Index evaluation management system for thermal power generating unit |
| CN106815182A (en) * | 2015-11-27 | 2017-06-09 | 国家电网公司 | One kind is based on being directed to power network financial statement statistical system under excel |
| CN108520389A (en) * | 2018-03-23 | 2018-09-11 | 杭州雅张科技有限公司 | human resource management system based on Excel |
| CN109492276A (en) * | 2018-10-24 | 2019-03-19 | 陕西泰德汽车空调有限公司 | The method for calculating air-conditioning duct processing technology based on VBA module in Excel |
| CN110457665A (en) * | 2019-08-14 | 2019-11-15 | 杭州豫毅信息科技有限公司 | A kind of Examination System without paper based on Excel VBA |
| CN111210203A (en) * | 2020-01-08 | 2020-05-29 | 北京思特奇信息技术股份有限公司 | E-mail distribution method, system, medium and equipment |
-
2021
- 2021-01-25 CN CN202110094876.7A patent/CN112819309A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104866961A (en) * | 2015-05-15 | 2015-08-26 | 陕西华电蒲城发电有限责任公司 | Index evaluation management system for thermal power generating unit |
| CN106815182A (en) * | 2015-11-27 | 2017-06-09 | 国家电网公司 | One kind is based on being directed to power network financial statement statistical system under excel |
| CN108520389A (en) * | 2018-03-23 | 2018-09-11 | 杭州雅张科技有限公司 | human resource management system based on Excel |
| CN109492276A (en) * | 2018-10-24 | 2019-03-19 | 陕西泰德汽车空调有限公司 | The method for calculating air-conditioning duct processing technology based on VBA module in Excel |
| CN110457665A (en) * | 2019-08-14 | 2019-11-15 | 杭州豫毅信息科技有限公司 | A kind of Examination System without paper based on Excel VBA |
| CN111210203A (en) * | 2020-01-08 | 2020-05-29 | 北京思特奇信息技术股份有限公司 | E-mail distribution method, system, medium and equipment |
Non-Patent Citations (1)
| Title |
|---|
| 袁浩、毛颖颖: "基于EXCEL VBA的绩效考核统计系统设计", 《科技视界》 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115658144A (en) * | 2022-12-09 | 2023-01-31 | 西安葡萄城软件有限公司 | Spreadsheet formula on-demand calculation method and device |
| CN116167348A (en) * | 2023-04-20 | 2023-05-26 | 中瑞方胜人力资源科技(北京)有限公司 | Information processing method and device for generating payroll |
| CN116167348B (en) * | 2023-04-20 | 2023-08-04 | 中瑞方胜人力资源科技(北京)有限公司 | Information processing method and device for generating payroll |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US5903873A (en) | System for registering insurance transactions and communicating with a home office | |
| USRE37730E1 (en) | Computer method for collecting on money judgments | |
| US8032400B2 (en) | Method and apparatus for performing assessments | |
| USRE43905E1 (en) | Flow designer for establishing and maintaining assignment and strategy process maps | |
| US6961708B1 (en) | External interface for requesting data from remote systems in a generic fashion | |
| US7050997B1 (en) | Personal financial management system, method and program using a graphical object-oriented programming methodology | |
| Winston | Microsoft Excel 2010 Data Analysis and Business Modeling | |
| US7966235B1 (en) | Method and apparatus providing automated control of spending plans | |
| US7113923B1 (en) | System and method of managing an office of programs | |
| US20100293108A1 (en) | Automated Practice Management System | |
| US20070055688A1 (en) | Automatic report generation | |
| US20020174006A1 (en) | Cash flow forecasting | |
| CN112819309A (en) | Employee performance scoring and payroll calculation system based on EXCEL software | |
| US20060004612A1 (en) | Systems and methods for configuring and processing insurance information | |
| WO2001061587A1 (en) | Financial processing system and method | |
| US20090171884A1 (en) | System and method for web-based case management | |
| US20020052791A1 (en) | Remote transaction tracking and financial package link | |
| CN110221833A (en) | A kind of wisdom enterprise platform based on no code development | |
| US20090119191A1 (en) | Record retention and post-issuance compliance system and method for municipal bonds | |
| CN114118936A (en) | Comprehensive salary management system | |
| US20070255730A1 (en) | Data requirements methodology | |
| US20060069685A1 (en) | Method and a process, provided through internet based software, for the development, management, and reporting of information regarding contingent liabilities | |
| US6999937B1 (en) | System for predefining via an activity scheduler first types of entered data that are processed by an activity processor in real time and second types of entered data that are queued for processing at another time | |
| Mitchell et al. | A reflection of changing priorities? The reallocative impact of priority‐based budgeting in US municipalities | |
| US20120072316A1 (en) | Virtual accounting ledger system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210518 |