bzdww

Get answers and suggestions for various questions from here

Excel data splitting: several ways to split a single column into multiple columns

cms


Sometimes there is only one column of data in our Excel spreadsheet. If you need to print it, it looks like this:



It’s ugly to waste paper, so print the form, do you really dare to show it to the boss?

For such data, you need to evenly allocate a column to multiple columns. There are usually two ways to do this. Let's take a look at the operation:

First, the operation method turns a column of data into multiple columns

Step 1: First design it needs to be split into several columns, then manually complete the title;



In this example, it is planned to split 8 columns.

Step 2: Enter a2 in cell c2 and pull it to the right. Excel will automatically fill in to a9 for us.



Step 3: Enter a10 in cell c3 and pull it to the right; because the first row has reached a9, this row starts with a10 and is filled to a17.



Step 4: Select two rows to pull down together, assuming there are 160 data, 8 per row, then pull down 20 rows is enough;



Step 5: Press Ctrl + h to open the search and replace. The search content is a and the replacement content is = a, as shown in the following figure.



Click Replace All to confirm and complete the operation.



Step 6: Paste as a value to delete the formula, the data is centered and the border is set, and the print preview is set. The effect is exactly what we need;



in conclusion:

1. If it is not 8 per line, just adjust the number of the first right pull;

2, the advantage of the operation method is easy to use, more suitable for novice use;

3, but the operation method also has shortcomings. If the data continues to increase, then it is very troublesome to do this every time, so you still need to understand the formula.

Second, the formula method turns a column of data into multiple columns

Still use this example to illustrate that usually turning a column into multiple columns will use the OFFSET function. Let's first look at what the formula looks like:

=OFFSET($A$1,MOD(COLUMN(A1)+7,8)+ROW(A1)*8-7,)



Just pull this formula down and pull it down.

The OFFSET function gets a new reference (cell or region) based on the offset. There are five parameters in the format:

OFFSET (start position, line offset, column offset, height, width).

In this case, the starting position is A1 cell. Because the data sources are all in one column and they are all reference to the cell, you only need to determine the row offset. You can see that only two parameters are used in the formula. The second parameter is

MOD(COLUMN(A1)+7,8)+ROW(A1)*8-7.

The effect of showing this part separately is this:



To put it plainly, it is the cell reference of A1 down 1 line, 2 lines...etc.

The basics of this part need a little bit of structure. If you don't understand, you can remember the routine. You can leave a comment on the constructor of the series. We will explain it separately how to construct a series of columns in the formula.

in conclusion:

1. If it is not 8 per line, only need to adjust the numbers 7 and 8 in the second parameter. For example, if each line is 6 data, the formula is modified to: =OFFSET($A$1, MOD(COLUMN(A1) )+5,6)+ROW(A1)*6-5,)



2. The formula method requires the user to have certain experience. When the data source changes, it is very flexible to adjust; the expansion is strong.

Seeing this, it is estimated that some partners will think: What if the data source is not a column but two or three columns?

In fact, two or three columns of data sources are more common in normal work, such as the following:



The data source has three columns, and we want to become nine columns (three groups), which makes it very reasonable to print.

For this kind of problem, it is more difficult to implement the technique described in Method 1, or use the OFFSET function to deal with it.

Third, the processing method for multi-column data sources

The formula is:

=OFFSET($A$1,INT(COLUMN(C1)/3)+ROW(A1)*3-3,MOD(COLUMN(C1),3))



Because the data source is not in the same column, the row offset and the column offset must be considered, so three parameters are used, and the first parameter starting position is still A1;

The second parameter INT(COLUMN(C1)/3)+ROW(A1)*3-3 represents the row offset of the reference data relative to the starting position A1. If viewed alone, the effect is:



The law of change can be seen very clearly;

The third parameter MOD(COLUMN(C1), 3) represents the column offset of the reference data relative to the starting position A1. The effect of looking at it separately is:



The ability to control this rule determines the level of use of the OFFSET function. It is for this reason that the OFFSET function makes many beginners puzzled and never learns.

Today our main purpose is not to learn the OFFSET function, but to learn an application of this function. For this example, it is enough to write down the formula's routines and solve the problem after modifying the formula according to your actual problem.

The key to learning OFFSET is the construction of the second and third parameters.

For example, the above problem, the data source is three columns, hope to become four groups (twelve columns), the second parameter needs to become INT (COLUMN (C1) / 3) + ROW (A1) * 4-4



Modify the two places indicated by the arrow.

The third parameter = MOD (COLUMN (C1), 3) does not need to be adjusted; and so on, as long as the number of columns of the data source is unchanged, only need to adjust the two positions mentioned above.

When the data source becomes 2 columns, the second parameter needs to be modified to INT(COLUMN(B1)/2)+ROW(A1)*3-3



Or modify the two positions, C is changed to B, and 3 is changed to 2.

At the same time, the third parameter column offset should also be modified:

=MOD(COLUMN(B1), 2)



Also modify two places, C is changed to B, and 3 is changed to 2.

By analogy, if the data source becomes 4 columns, then B is changed to D and 2 is 4.

Through the above explanation, it is basically understandable how to adjust the corresponding content of the formula according to actual needs. In order to thoroughly understand the mystery here, the construction method of the series is a hurdle that cannot be circumvented.

Let's work together to learn the fun and practical skills of Excel!

**** tribal nest education - excel single column split into multiple columns ****

Original: Old rookie / tribal nest education (do not reprint without consent)

More tutorials: Tribal Nest Education ( itblw.com )

WeChat public number: exceljiaocheng