我有一张这样的桌子:
X Y Z
1 4 9
2 7 2
3 4 8
3 4 9
...
我想制作下表:
1 4 9 2 7 2 3 4 8 3 4 9
甚至更好的是:
X Y Z X Y Z X Y Z X Y Z
1 4 9 2 7 2 3 4 8 3 4 9
答案1
和X在 A1 中,您可以抓取延伸到数组中的 CurrentRegion(数据岛)。其余的只是循环和一些数学运算。
Option Explicit
Sub buildXYZ()
Dim i As Long, j As Long, arr1 As Variant
With Worksheets("sheet4")
'collect source values
arr1 = .Cells(1, "A").CurrentRegion.Value
'create the target array
ReDim arr2(1 To 2, 1 To (UBound(arr1, 1) - 1) * UBound(arr1, 2))
'populate target from source using two loops
For i = LBound(arr1, 1) + 1 To UBound(arr1, 1)
For j = LBound(arr1, 2) To UBound(arr1, 2)
arr2(1, j + (i - 2) * UBound(arr1, 2)) = arr1(1, j)
arr2(2, j + (i - 2) * UBound(arr1, 2)) = arr1(i, j)
Next j
Next i
'put target values back into worksheet
.Cells(1, "E").Resize(UBound(arr2, 1), UBound(arr2, 2)) = arr2
End With
End Sub
如果您需要“就地”执行操作,则请直接使用工作表单元格并避免使用过渡数组。
Option Explicit
Sub buildXYZ2()
Dim i As Long, lr As Long
With Worksheets("sheet4")
'collect last data row
lr = .Cells(.Rows.Count, "A").End(xlUp).Row
'loop through the rows backwards, shifting the values up and right
For i = lr To 3 Step -1
With .Range(.Cells(i, "A"), .Cells(i, .Columns.Count).End(xlToLeft))
.Parent.Cells(i - 1, .Parent.Columns.Count).End(xlToLeft).Offset(0, 1).Resize(1, .Columns.Count) = .Value
.Clear
End With
Next i
'AutoFill the headers across in a pattern
With .Range(.Cells(1, "A"), .Cells(1, .Columns.Count).End(xlToLeft))
.AutoFill Destination:=.Resize(1, .Columns.Count * (lr - 1)), Type:=xlFillCopy
End With
End With
End Sub
答案2
您可以使用公式来做到这一点。
对于列标题,E1 中的公式为:
=OFFSET($A$1,0,MOD(COLUMN()-2,3))
根据需要将其复制过来。 COLUMN()-2
调整输出的起始列。您希望将第一个输出列调整为值 3(我的从第 5 列开始),以便 MOD 函数返回0
相对于第一个数据列的偏移量。如果您的数据不是从 A1 开始,则需要进行调整。
输出值行使用相同的公式,但替换了0
行偏移量来增加数据行。E2 中的公式:
=OFFSET($A$1,CEILING((COLUMN()-4)/3),MOD(COLUMN()-2,3))
对于行增量,COLUMN()-4
调整列以使第一个结果位置为1
(我的从第 5 列开始)。同样,如果源数据不是从 A1 开始,请调整公式。
我尽量保持简单。只需根据需要复制即可。当您通过源数据的末尾时,结果将开始显示空白源单元格的零。