Practical Excel skills sharing: two quick ways to create salary slips

In the previous article "Sharing practical Excel skills: Making a smart attendance sheet", we learned about the method of making a smart attendance sheet. Today I will share two of the fastest ways to make salary slips, which can process 2,000 people in 10 seconds. Come and take a look!

First type: Add auxiliary column

The salary table as shown below, the bottle needs Add a header to each person's salary slip, and leave a blank line on the header for cutting.

We first manually enter 1 and 2 in cells H2 and H3.

Then select the H2-H3 cell range, place the mouse on the lower right corner of the H3 cell, and double-click to fill downward. You can see the following results.

Press ctrl c to copy the area, then click on cell H12, press ctrl V to paste, and get the following results.

Select the data area A1-H21 and click the "Sort" button under the "Data" tab.

Click the drop-down button behind "Main Keywords", select "(Column H)" in the drop-down menu, and click OK.

You can see the results as shown below.

Select the A1-G1 cell range and press ctrl C to copy.

Then select the cell range A2-H20.

Press the positioning shortcut key ctrl G, click "Positioning Conditions" in the pop-up dialog box, and then check "Null Value" in the dialog box shown below. Click OK.

At this point you can see that all blank lines are selected.

Press ctrl V to copy. You can see that the table header is copied to every empty line.

Then select column H and click "Clear All" under the "Home" tab.

Enter 1 in cells H2 and I3.

Select the H2-I3 cell range, then place the mouse on the lower right corner of the I3 cell and double-click to fill it.

You can see the results as shown below.

Select the data in column I, press the ctrl G shortcut key, and select "Constant". Click OK.

You can see that the data is selected.

Right-click and select "Insert".

Select "Entire Line" in the pop-up dialog box.

At this point you can see that there is a blank line in front of each header, which can be used for cropping after printing.

If you want to add a border to the table header and name, select the entire table area, click the border drop-down button under the "Home" tab, and select "All" in the drop-down menu frame line".

You can see that all cells are bordered.

If you want the blank rows to have no borders, you can follow the previous method of locating null values. After locating all blank rows, press the ctrl 1 shortcut key to pop up the setting unit. Format dialog box. In the dialog box, click on the left, middle, and right lines in sequence to remove the three lines on the blank row, and click OK.

The final effect is as follows.

The above method is more suitable for companies with a small number of people. If your company has two or three thousand people, then use the universal template given to you by the bottle below!

Second type: VBA

We will use two pieces of code to insert a trim row (blank row) before each row of employee salary data ), two-step operation to insert table header.

Press the ALT F11 shortcut key to pop up the VBA debugging window as shown below.

After clicking "Insert"-"Template", the page will look like this.

Enter the code in the blank: (note that all punctuation marks in the code are English punctuation marks)

Sub 选定范围隔行插入一行()
    Dim i As Integer
    For i = 11 To 3 Step -1
        Rows(i & ":" & i).Insert
      Next
End Sub

Click the "Run" button.

At this point you can see that the table has completed inserting blank rows.

#There are many words in the code that everyone does not know, but in fact, everyone only needs to understand the meaning of the following two rows.

For i = 11 To 3 Step -1

Rows(i & ":" & i).Insert

The meaning of the above code is: starting from line 11, insert a blank line upwards (the difference between the first i and the second i after Rows is 0. If you want to insert two blank lines, you can change the second one to i 1), then insert a blank row every other row (step -1) until row 3 (TO 3).

Next we will delete the original code and enter another set of codes: (note that all punctuation marks in the code are English punctuation marks)

Sub 隔2行插入表头()
For i = 20 To 4 Step -2
    Range("A1:G1").Copy
    Cells（I,1）.Insert
Next
End Sub

The result after clicking the run button is as follows. You can see that

# has a similar meaning to the above. We only need to understand the middle two lines.

For i = 20 To 4 Step -2

Range("A1:G1").Copy

cells(I,1).Insert

The meaning of the above code is: from cell A20 (cells(I,1), I is the number of rows, 1 is the number of columns, I is equal to 20 When, it is row 20, column 1 (i.e. cell A20) and start to insert (Insert) a table header that is copied (Copy) from the specified position (A1:G1), and then insert every 2 rows (step -2) 1 header, inserted until row 4 (To 4). If it is a two-line header, you only need to change A1:G1 to the corresponding header area.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: two quick ways to create salary slips. For more information, please follow other related articles on the PHP Chinese website!