![在 Excel 中 - 将一行拆分为多行](https://linux22.com/image/1619471/%E5%9C%A8%20Excel%20%E4%B8%AD%20-%20%E5%B0%86%E4%B8%80%E8%A1%8C%E6%8B%86%E5%88%86%E4%B8%BA%E5%A4%9A%E8%A1%8C.png)
我想要一个 Excel 公式,它可以帮助我将一行中的条目拆分为多行,如图中的第二个表所示。如能得到任何帮助,我将不胜感激:
尝试了在如何使用 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 Query
Excel 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
单击一个单元格,或选择要拆分的多个单元格。在“表格工具”下的“布局”选项卡上的“合并”组中,单击“拆分单元格”。输入要将选定单元格拆分成的列数或行数。