☰

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

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 microsoft-excel-2007
在 Excel 中，如何将用逗号分隔的行中的数据转换为具有值的列？

我在 Excel 中的信息是这样组织的。

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

我如何将其转换为这个？

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

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

答案1

在 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 上有很多视频展示如何将数据从一种形式转换为另一种形式（简单又有趣）

相关内容