Excel - 列到行

Question

我有以下数据列表：

A1
1
2
3
4
5
6
7
8
9
10

我需要将数据转换为：

A1 B1 C1 D1 E1 
1   2  3  4  5

A2 B2 C2 D2 E2
 6  7  8  9 10

Answer 1

在 A1 中输入公式：
=INDEX($J$1:$J$8000,(ROW()-1)*8+COLUMN())

将公式复制到 A1:H1 的所有单元格

在地址栏中输入“A1:H8000”，然后按 CTRL+D 将公式填充到所有行。

Answer 2

这是 VBA 代码。请不要忘记更新源范围，目标单元格和列值。

Sub GenerateMatrix()
Dim rows As Integer, columns As Integer
Dim sourceRange As Range
Dim targetCell As Range
Dim cellCount As Integer, i As Integer, j As Integer
Dim arr As Variant
Set sourceRange = Range("Sheet2!A1:A10")
Set targetCell = Range("Sheet2!A1")
cellCount = sourceRange.Count
ReDim arr(1 To cellCount)
For i = 1 To cellCount
    arr(i) = sourceRange.Cells(i, 1)
Next i
columns = 5
rows = cellCount / 5
For j = 1 To rows
    For i = 1 To columns
        targetCell.Offset(j - 1, i - 1) = arr((j - 1) * columns + i)
    Next i
Next j
End Sub