如何旋转(转置)不同长度的数据集

如何旋转(转置)不同长度的数据集

我在 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 中“编程”公式或宏 - 所以我的建议可能完全不可行。

无论如何,您已经非常友善地查看了这个问题。我希望有人之前遇到过这个问题,并且有一个可行的答案。

敬礼,肖恩

相关内容