How Do I Create a Payroll Number?

The payroll, also called the payroll statement, is a form used to calculate the salary of employees based on units and departments, one for each month. Under normal circumstances, the payroll will be issued to employees 1-3 days before the salary is officially issued, and employees can reflect to the superiors on issues that appear in the payroll.

payroll

In actual work, the payment of wages and settlement of wages by enterprises is carried out by compiling a "salary statement".

Wage statements should generally be prepared in triplicate. One is filed by the labor and wage department;

1. Create a new Excel file and store the original salary table in sheet1.

Making pay slips for employees is a complicated and time-consuming task, but there are several ways to make your payroll automatically generate pay slips, saving you time and effort.

With the increasing number of employees in the enterprise, it is becoming more and more complicated to make each person's salary slips individually. Now, using the EXCEL function, you can automatically generate a salary slip for each person: Method 1:

This payroll contains two tables.

The first table is the salary table. Its first line includes the title line, name, and salary item.

The second table is a table for printing, called the "salary slip". It should be set as a group of three lines. The first line of each group is the title, the second line is the serial number, name, and each data, and the third line is a blank line. That is to say, the behavior that can be divided by 3 is an empty line, the entire table is divided by 3 and the remainder is the header line, the title line is referenced, and the remainder is divided by 3 and the rows are the rows of various data. Using the INDEX (range, row, column) function reference, the formula is: = IF (MOD (ROW (), 3) = 0, "", IF (MOD (ROW (), 3) = 1, salary table! A $ 1 , INDEX (salary! $ A: $ M, (ROW () + 4) / 3, COLUMN ())))

Syntax explanation: If the remainder is divided by 3, the remainder is 0; if the remainder is divided by 1, the payroll table is taken from columns A to M, line 1 (not quoted); if the remainder is divided by 2, the remainder is 2 , Return the salary table columns A to M, corresponding columns; row +4/3

Explanation: Line +4/3: Line is line 2, 5, 8, 11 of the payroll. Line +4/3 is the corresponding line 2, 3, and 4 of the returned salary table. For example, line 2 of the payroll returns (2 + 4) / 3 = 2 in the payroll, line 5 of the payroll returns (5 + 4) / 3 = 3 in the payroll, and line 8 of the payroll returns the payroll Medium (8 + 4) / 3 = 4.

Method 2: OFFSET (reference, rows, cols, height, width)

OFFSET parameter usage (take a cell (parameter 1referenc) as the reference point (starting point), the lower offset (parameter 2 rows) rows, the right offset (parameter 3 cols) column, and the offset cell as the starting point An area with the number of rows selected (parameter 4 height) and the right (parameter 5 width) columns)

= IF (MOD (ROW (), 3) = 0, "", IF (MOD (ROW (), 3) = 1, salary table! C $ 1, OFFSET (salary table! $ A $ 1, ((ROW () +1) / 3), COLUMN ()-1)))

Method three:

= IF (MOD (ROW (), 3)> 0, OFFSET (Payroll! $ A $ 1, (MOD (ROW ()-1,3)) * ((ROW () + 1) / 3), COLUMN ( )-1),"")

Syntax explanation: This formula is to automatically generate a circular ordinal number. To understand from the 7/8/9 line of the payroll:

If A7 is taken as an example: (MOD (ROW (), 3) means that the remainder of line 7 divided by 3 is 1> 0, then the salary table! $ A $ 1 is used as the reference frame (the remainder of (7-1) ÷ 3) × ((7 + 1) ÷ 3) = 0, column 0;

If A8 is taken as an example: the remainder of 8 divided by 3 is 2> 0, then the salary table! $ A $ 1 is used as the reference frame to quote ((8-1) ÷ 3 remainder) × ((8 + 1) ÷ 3) = 3 Row, column 0;

If A9 is taken as an example: the remainder of 9 divided by 3 is 0 and the line is blank.

Its practical Excel print payroll, in the final analysis is to let Excel make a payroll table, a line of salary breakdown data, a row of employee records. If we insert a line of salary breakdown data on top of each employee's data, obviously our requirements will be completed. Of course, the manual insertion of line by line is obviously an "impossible task". A little trick is needed here.

Step 1: In the two columns on the right side of the salary line, enter any number crossly (mainly for the "vacant" null value, so the number can be entered arbitrarily), then select the four cells that cross, and double-click the lower right corner. "Fill handle" to fill this format all the way to the end of the payroll.

Step 2: Execute the "Edit" "Positioning" command, click the "Positioning Condition" button in the "Positioning" dialog box that opens, select "Null Value" in the "Positioning Conditional" dialog box, and then click Click the "OK" button.

Step 3: Execute the "Insert" "Line" command. At this time, it will start from the third line, and a blank line is inserted in front of each line.

Step 4: Copy the salary breakdown data in the header, select the data area in column A of the salary table, and execute the "Edit" "Positioning" command. In the "Positioning" dialog box that opens, click the "Positioning Condition" button. In the "Positioning Conditions" dialog box, select "null value", and then click the "OK" button. At this time, the blank line just inserted is selected, and the "Edit" "Paste" command is executed. At this time, the payroll is completed. Already.