主页 / computer / 问题 / 1736641
Accepted
xeaqyud
Asked: 2022-08-12 23:49:42 +0800 CST

在 Excel 中，如何将用逗号分隔的行中的数据转换为具有值的列？

  • 772

我在excel中有这样组织的信息。

姓名 类型 数据
托马斯 桌子，高度 红色,180
监狱 十字架，狗 年轻的牛

我如何将其转换为这个？

姓名 桌子 高度 叉 狗
托马斯 红色的 180
监狱 年轻的 奶牛

不知道如何解决这个问题，尝试使用宏。

microsoft-excel microsoft-excel-2010

1 个回答

  1. Best Answer

    在 Excel-2007 中 - 仅宏（因为 SPLIT(any, ",") 不存在于本机函数中）

    对于 Excel-2010 安装插件 PowerQuery，单击十几次并获得结果 - 脚本 M 语言如下：

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"ChangeType" = Table.TransformColumnTypes(Source,{{"name", type text}, {"type", type text}, {"data", type text}}),
    #"NewTable" = Table.AddColumn(#"ChangeType", "TypeTable", each Table.FromColumns({Text.SplitAny([type], ","), Text.SplitAny([data], ",")}, {"Type1", "Data1"}) ),
    #"ExpandTable" = Table.ExpandTableColumn(#"NewTable", "TypeTable", {"Type1", "Data1"}, {"Type1", "Data1"}),
    #"RemoveCSV" = Table.RemoveColumns(#"ExpandTable",{"type", "data"}),
    #"GetResult" = Table.Pivot(#"RemoveCSV", List.Distinct(#"RemoveCSV"[Type1]), "Type1", "Data1")
in
    #"GetResult"

    样本结果

    一个可以使相同的工作更长的宏：

    Function expandTable(aSourceRange As Range) As Variant
Dim aSource As Variant
Dim i As Long, j As Long, k As Long
Dim aTypes As Variant, aDatas As Variant
Dim aResult() As String
Dim allTypes As New Collection
Rem Extract cell values from source range:
    aSource = aSourceRange.Value2
Rem First collect all unique types:
    For i = LBound(aSource, 1) + 1 To UBound(aSource, 1)
        aTypes = Split(aSource(i, 2), ",")
        For j = LBound(aTypes) To UBound(aTypes)
            Call allTypes.Add(aTypes(j), aTypes(j))
        Next j
    Next i
    i = allTypes.Count + 1
Rem Result table will be same height, but width will be name + all types
    ReDim aResult(LBound(aSource, 1) To UBound(aSource, 1), 1 To i)
Rem Fill first row - header:
    aResult(1, 1) = aSource(1, 1)
    For i = 1 To allTypes.Count
        aResult(LBound(aSource, 1), i + 1) = allTypes.Item(i)
    Next i
Rem Transfer data from source array to result array:
    For i = LBound(aSource, 1) + 1 To UBound(aSource, 1)
        aResult(i, 1) = aSource(i, 1)
        aTypes = Split(aSource(i, 2), ",")
        aDatas = Split(aSource(i, 3), ",")
Rem Equalize the sizes of arrays of Types and Data -
Rem if they have a different number of elements, an error may occur.
        ReDim Preserve aDatas(LBound(aTypes) To UBound(aTypes))
        For j = LBound(aTypes) To UBound(aTypes)
            For k = 2 To UBound(aResult, 2)
                If aResult(1, k) = aTypes(j) Then
                    aResult(i, k) = aDatas(j)
                    Exit For
                End If
            Next k
        Next j
    Next i
    expandTable = aResult
End Function

    如您所见，使用现代化的更新工具可以节省大量时间和精力。因此，只需安装PowerQuery并学习如何使用它转换数据 - YouTube 上有很多视频展示了如何将数据从一种形式转换为另一种形式（简单而有趣）

    • 0

相关问题