我在 A 列中有 5000 多条记录。每条记录包含多行数据。包含数据的行数因记录而异。如何转置这些记录中的数据,以便每条记录仅占用一行?请参阅附带的图片。(使用 Microsoft 365 中包含的 Excel)
答案1
IFERROR & INDEX
命令和一些辅助值的组合解决了这个问题。
::警告::
- 显示的方法适用于任何版本的 Excel。
- 这不是一个聪明的方法,但确实解决了问题。
- 为了改进这个练习,读者可以建议一个有效的公式/宏。
怎么运行的:
1.I93:M93
根据每个记录集的最大元素数插入一系列数字( ),这里最大值为5
。
2.H94:H96
根据记录集输入另一系列数字( ),如下所示3
。
3.单元格中的公式I94
:
=IF($H94=1,IFERROR(INDEX($I$78:$I$82,I$93,1),""),IF($H94=2,IFERROR(INDEX($I$84:$I$86,I$93,1),""),IF($H94=3,IFERROR(INDEX($I$88:$I$92,I$93,1),""),"")))
4、将其填满。
根据需要调整公式中的单元格引用。
::已編輯::
由于 OP 正在处理庞大的数据集(大约 5000 条记录),因此 VBA Macro 可以以一种智能的方式处理该问题。
Sub TransposeWithBlanks()
Dim Data_Array
Dim OutPut_Array()
Dim LR As Long, Counter As Long, LR2 As Long
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
Data_Array = .Range("A1:A" & LR).Value2
End With
On Error Resume Next
For i = LBound(Data_Array, 1) To UBound(Data_Array, 1)
If Trim(Data_Array(i, 1)) <> vbNullString Then
Counter = Counter + 1
ReDim Preserve OutPut_Array(1 To 1, 1 To Counter)
OutPut_Array(1, Counter) = Data_Array(i, 1)
Else
With Sheets("Sheet1")
LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
.Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
End With
Counter = 0
End If
Next i
End Sub
注意:
- 年代将工作簿作为米acro 已启用。
- 在上述代码中,工作表名称和数据范围是可编辑的。
如果你想磷将转置的数据保存到另一个 Sheet 中,然后您需要编辑这部分代码。
With Sheets("Sheet1") LR2 = .Cells(Rows.Count, "C").End(xlUp).Row .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array End With
答案2
非常感谢您能接受这个挑战。虽然您的公式有效,但它需要被告知每个单独记录的单元格范围(记录 2 为 $I$84:$I$86...然后记录 3 为 $I$88:$I$92...等等。如果有 5000 条记录,则需要输入 5000 条单独记录中每条记录的范围 - 这实际上并不实用。
我并不是一名 Excel 专家,因此请原谅我以下的“外行”想法。
我认为可行的公式(或更可能是宏?)如下(使用上面的表格):1)指示宏在第一列向下搜索单词(字符串?)“Record”。2)每次宏找到单词“Record”时,指示它将紧接着下方的后续单元格中的数据水平复制/粘贴到相邻单元格(其右侧)中 - 并在到达下一个单元格时停止复制/粘贴,其中包含另一个单词“record”。
正如我所说,我不知道如何在 Excel 中“编程”公式或宏 - 所以我的建议可能完全不可行。
无论如何,您已经非常友善地查看了这个问题。我希望有人之前遇到过这个问题,并且有一个可行的答案。
敬礼,肖恩