Simple payroll template

Generally, the payroll should be made in triplicate. A copy shall be kept by the labor and wage department; One copy is cut into "salary slip" according to the first employee and sent to the employee together with the salary; One is signed by the employee when paying the salary, and then handed over to the accounting department as a voucher for salary accounting, which is used to replace the detailed accounting of the salary. Because the wage statement is compiled by each workshop and department, it can only reflect the wage settlement and payment of each workshop and department.

Related reading:

Making payroll for employees in an enterprise is a complicated and time-consuming job, but there are several ways to automatically generate payroll from your payroll, which will save you time and effort.

With the increasing number of employees in enterprises, it becomes more and more complicated to independently make everyone's salary slip. Now, using the EXCEL function, you can automatically generate everyone's payroll from the payroll:

Method 1: This payroll contains two tables.

1 table is payroll. Its first header line includes serial number, name and salary item.

The second table is used for printing and is called "payslip". It should be set as a group of three lines, the first line is title, the second line is serial number, name and data, and the third line is blank. That is to say, the behavior divisible by 3 is an empty row, the whole table is divisible by 3, the behavior title row of 1 refers to the title row, and the behavior data row is divisible by 3. Use the INDEX (area, row, column) function for reference, and the formula is: = if (mod (row (), 3) = 0, "",if (mod (row (), 3) = 1, salary! A$ 1, index (payroll! $A:$M，(ROW()+4)/3，COLUMN()))

Syntax explanation: if the number of lines divided by 3 is 0, it is empty; If the number of rows divided by 3 is 1, then take column A to column M of the payroll, 1 row (cannot be referenced); If the number of rows divided by 3 is 2, columns A to M of the payroll are returned, and the corresponding columns are; Line +4/3

Note: Line +4/3: Lines 2, 5, 8, 1 1 ... Line +4/3 is the corresponding line 2, 3 and 4 of the returned payroll. For example, the second line of the payroll returns to (2+4)/3=2, the fifth line returns to (5+4)/3=3, and the eighth line returns to (8+4)/3=4.

Method 2: Offset (reference, row, column, height, width)

Parameter usage of OFFSET (take a cell (parameter1reference) as the reference point (starting point), offset the next row (parameter 2 row) and the right column (parameter 3 column), and select the area with the next row number (parameter 4 height) and the right column number (parameter 5 width) from the offset cell).

= if (mod (row (), 3) = 0, "",if (mod (row (), 3) = 1, payroll! C$ 1, offset (salary! $A$ 1，((ROW()+ 1)/3)，COLUMN()- 1))

Method 3:

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

Syntax explanation: This formula automatically generates cyclic ordinal numbers. By referring to the payroll of line 7/8/9, we can know that:

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

Take A8 as an example: 8 divided by 3 gives a remainder of 2 >; 0, payroll is used! $A$ 1 is the remainder of the reference frame (8- 1)÷3) ×((8+ 1)÷3)=3 rows and 0 columns;

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

;