☰

在 Excel 中 - 将一行拆分为多行

tag-icon • tag-icon microsoft-excel
在 Excel 中 - 将一行拆分为多行

我想要一个 Excel 公式，它可以帮助我将一行中的条目拆分为多行，如图中的第二个表所示。如能得到任何帮助，我将不胜感激：

标签 第2页

在此处输入图片描述

尝试了在如何使用 Excel 将一行拆分为多行？然而，如果名称列条目重复，则它不起作用。

   =IF(COUNTIF($A$7:A7,A7)=COUNTA(OFFSET('Sheet2'!$B$1:$D$1,MATCH(A7,'Sheet2'!$A$2:$A,0),0)),INDEX('Sheet2'$A$2:$A,MATCH(A7,'Sheet2'$A$2:$A,0)+1),A7)

答案1

要将您的 转换Table1为Table2，如您的第一个屏幕截图所示，您可以使用Power QueryExcel 2010+ 中提供的 MS 免费插件或更高版本中的内置插件。

虽然可以从 GUI 进行转换，但如果您扩展数据中的列数，则会返回错误。因此您需要修改生成的实际 M 代码。

  • 我假设，如果添加列，将始终以 4 为一组 ( Task Name | Start Time | End Time | Consumed Time)。
  • 我还假设该Consumed Time列包含格式化的整数。如果它确实包含文本，则需要在查询中进行一些更改。

脚步：

  • 导入表格（PQ将某个范围转换为表格，或者您可以自己执行此操作）
  • 选择Project Name并取消透视其他列
  • 因为这已被转换成表，所以原始范围内的重复列名将通过附加序列号进行转换。
    • 要在逆透视后删除该数字，请选择新Attribute列
      • 分裂从字母到数字的转换列
      • 删除带有数字的列。
  • 将数据分组到适当的行中：
    • 添加索引列（基数为零）
    • 在 Index 列的基础上，添加一个Integer/Divide除以 4 的列
    • 删除索引列
  • 现在我们通过...分组和Integer-Division列Project Name（Operation:= All Rows无聚合）
  • 我们添加一个自定义列，将结果表转换为列表，然后
  • 使用分号分隔符（或其他不太可能出现在数据中的标记）从列表中提取值。
  • 然后我们使用分号作为分隔符将这些值拆分成单独的列
  • 删除多余的列
  • 重命名列，使其与原始表中的前五个名称相同。
  • 根据要求将表格列的数据类型更改为文本、时间和整数
  • Project Name按和对行进行排序Task Name
  • 加载回工作表。

如果您更改任何内容，或者添加行或列组，则可以刷新此查询。

如果您只是将代码粘贴到空白查询的高级编辑器中，则需要将Table3第 2 行更改为工作表上表格的名称。

M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Project Name"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Attribute.2"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Integer-Division", "Project Name"}, {{"Grouped", each _, type table [Project Name=text, Attribute.1=text, Value=anynonnull, #"Integer-Division"=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped],"Value")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Split Column by Delimiter",{"Integer-Division", "Grouped"}),

        colNames = List.FirstN(Table.ColumnNames(Source),5),
        
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",List.Zip({Table.ColumnNames(#"Removed Columns2"),colNames})),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Project Name", type text}, {"Start Time", type number}, {"End Time", type number}, {"Consumed Time", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start Time", type time}, {"End Time", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Project Name", Order.Ascending}, {"Task Name", Order.Ascending}})
in
    #"Sorted Rows"

在此处输入图片描述

答案2

单击一个单元格，或选择要拆分的多个单元格。在“表格工具”下的“布局”选项卡上的“合并”组中，单击“拆分单元格”。输入要将选定单元格拆分成的列数或行数。

相关内容