Three methods of data transpose commonly used in Excel tables

Three methods of data transpose commonly used in Excel tables

As shown in the following illustration, you need to change the contents of the A2:B9 to D columns.

Think about it, how can there be several ways to achieve it?

This topic at first glance is a little difficult to tall, isn't it?

1. VBA code method

Press CTRL+F11 to open the VBE editor and write the following code in the Code window:

Sub Run ()

For i = 1 to 8

Cells (2 + (i-1) * 2, 4) = Cells (i + 1, 1)

Cells (3 + (i-1) * 2, 4) = Cells (i + 1, 2)

Next I

End Sub

Use a For loop in your code to perform cell assignment operations. You can get the results you want by running the macro run.

2. Function Formula method

D2 Cell Enter the following formula and copy down.

=offset (A$2, (Row (A1)-1)/2,mod (Row (A1) -1,2))

The offset function is based on the A2 cell, and its row and column offsets use the regular sequence values generated by the formula. On the use of the second and third parameters, can be in the micro-letter Content < We learn the serial number > have introduced, here is no longer to repeat, interested students can click to view.

3. Skill Operation method

This is a collection of techniques, a lot of ways, let's look at several representative:

1. Selective pasting method

2. Alignment Filling method

3, Word replacement method

Note that the ^t used in Word substitution represents a tab character, and ^p represents a paragraph mark. That is, all tabs are replaced with paragraph marks.