Shulou(Shulou.com)11/24 Report--

I believe that all the boys have encountered the problem of inserting blank lines in their daily work.

In particular, inserting blank rows into a table that has already been made is a real headache.

Many friends should have learned how to insert a row every other row and two rows every other row.

Let's start with a brief review.

Insert equidistant blank lines ▋ insert every other row I believe this simple question we all know the steps, let's review ~

❶ first enters a sequence of arithmetic numbers with an interval of 1 in the auxiliary column, that is, 1pm 2pm 3.

❷ mouse over the last cell in the secondary column, select [start]-[Edit]-[fill]-[sequence].

❸ generates an isometric sequence with a step value of-1 and a termination value of 1 in the column direction.

❹ is listed as the primary keyword, sorted in ascending order, and adjusted appropriately.

This is done! (see the following figure for details).

▋ inserts two rows every other row in much the same way as inserting a row.

It's just that in the first step, when the arithmetic sequence is entered, the spacing becomes 2, that is, 1, 3, 5, 5, 7.

So have you thought of the way to insert three rows and four rows every other row?

Yes, that's right!

Just change the spacing of the arithmetic columns on the auxiliary column to the number of rows that need to be inserted.

▋ summarizes the following solutions to the problem of inserting N blank lines every other line:

❶ establishes an auxiliary column and sets a sequence equal to N starting from 1.

❷ mouse is positioned in the last cell of the secondary column, and select [start]-[Edit]-[fill]-[sequence]

❸ generates an isometric sequence with a step value of-1 and a termination value of 1 in the column direction.

❹ is sorted in ascending order with secondary as primary keyword.

The problem of inserting unevenly spaced blank lines at equal intervals is perfectly solved!

But! That's not what Xiao E is going to talk about.

Although the above methods can solve most of the blank line insertion problems, but when we encounter the following situation, the above methods appear powerless.

We can see that the number of blank lines that need to be inserted varies from department to department.

Some insert 3 rows, some insert 1 row, and some insert 2 rows. What should I do in this case?

Do you want to use VBA?

NO! It doesn't have to be so much trouble.

We can also do this by sorting.

First, list the number of rows to be inserted in each section.

Enter the number of rows that each department needs to insert in the E2:E8 cell (as shown in the following figure).

Then, we want to construct the auxiliary columns needed for sorting.

❶ enter 0 in F2 cell, then enter the formula: = E2+F2 in F3 cell and drop down to F8 cell.

❷ hovers the mouse over the F8 cell, under the start tab, click fill, and select sequence in the fill drop-down list.

❸ will pop up the sequence setting dialog box. The sequence is generated in the selection column and the type selection isometric column.

The step size value is-1 and the termination value is 0. After the setting is completed, as shown in the following figure, press the "OK" key to complete the operation.

At this point, our sorting auxiliary column is constructed!

Finally, we need to sort the data.

❶ mouse over any cell in the F2:F20 area, and click "data"-"sort".

The ❷ cell selection automatically expands and the sort dialog box pops up. We sort in ascending order with the primary keyword "sort Auxiliary column" (see figure below).

After the sorting is completed, the figure is as follows:

Well, we finally finished inserting rows!

But it doesn't look good.

We use the format brush, brush the following cells, unify the format, and we can get the final result.

At this point, the auxiliary column has completed its mission, and we can delete it to facilitate subsequent tabulation work.

Let's do it all over again:

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: eldest cousin, Editor: Yali Zi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.