怎么快速将多列数据转换成多行数据 _知识经验

在我的工作表中，同一行是某学生测试项目和课外兴趣班的全部信息，如下图1所示。

文章插图
图1：在工作表“InputData”列A至列C中存储学生一般信息，列D至列列W存储着学生测试项目信息。测试项目信息分为“测试项目、测试日期、分数、等级”列，共重复5次（即有5个测试项目）。

文章插图
图2：在工作表“InputData”列X至列AI存储着学生课外兴趣班信息。课外兴趣班信息分为“课外兴趣班、频次、持续时间、效果”列，共重复了3次。
现在，要将工作表“InputData”同一行中的数据转换成多行数据，并存储到工作表“OutputData”中，如下图3所示。

文章插图
图3：转换成多行数据的结果。每名学生的“测试项目、测试日期、分数、等级”和“课外兴趣班、频次、持续时间、效果”信息放置在不同的行中。
可以使用VBA代码来快速完成数据的转换：
‘设置数组下标从1开始
Option Base 1
‘自定义类型
Type student
info() As Variant
exam(5) As Variant
interest(5) As Variant
End Type
Sub MainOutput()
Dim wks As Worksheet
Dim i As Long
Dim rngInputData As Range
‘获取要处理的数据区域
Set rngInputData =https://www.i8ku.com/2021/Worksheets(“InputData”).Range(“A1”).CurrentRegion
Set rngInputData = https://www.i8ku.com/2021/rngInputData.Offset(1,0). _
Resize(rngInputData.Rows.Count – 1,rngInputData.Columns.Count)
‘调用OutputData过程处理数据
OutputData rngInputData,Worksheets(“OutputData”).Range(“A2”)
‘自动调整列宽
Worksheets(“OutputData”).Range(“A1”).CurrentRegion.Columns.AutoFit
End Sub
Sub OutputData(rngSource AsRange, rngTarget As Range)
Dim titles() As Variant
Dim inputRows As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim stu() As student
‘输出表标题
titles = Array(“编号”, “姓名”, “性别”, _
“测试项目”, “测试日期”, “分数”, “等级”, _
“课外兴趣班”, “频次”, “持续时间”, “效果”)
‘获取输入数据行数并定义数组大小
inputRows = rngSource.Rows.Count
ReDim stu(inputRows)
‘遍历输入数据
For i = 1 To inputRows
‘依次将相应数据存放在数组中
With stu(i)
k = 4
‘存储前3列数据
.info = Range(rngSource.Item(i, 1),rngSource.Item(i, 3))
‘存储后面重复标题相应的数据
For j = 1 To 5
.exam(j) =Range(rngSource.Item(i, k), rngSource.Item(i, k + 3)).Value
.interest(j) =Range(rngSource.Item(i, k + 20), rngSource.Item(i, k + 23)).Value
k = k + 4
Next j
End With
Next i
‘清空放置数据的工作表
rngTarget.CurrentRegion.ClearContents
‘输入标题
rngTarget.Offset(-1, 0).Resize(1, 11).Value= https://www.i8ku.com/2021/titles
k = -1
‘输入数据
For i = 1 To inputRows
For j = 1 To 5
With stu(i)
If .exam(j)(1, 1) <>”” Or .interest(j)(1, 1) <> “” Then
k = k + 1
rngTarget.Offset(k,0).Resize(1, 3).Value = https://www.i8ku.com/2021/.info
rngTarget.Offset(k,3).Resize(1, 4).Value = https://www.i8ku.com/2021/.exam(j)
rngTarget.Offset(k, 7).Resize(1,4).Value = https://www.i8ku.com/2021/.interest(j)