答案1
使用 Power Query 非常简单。只需添加一个包含列表所有非空条目 价值列;然后扩展到新行。最后按所需顺序排序。
阅读评论并探索Applied Steps
以了解算法
let
//change next line to reflect actual data source
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//set data types to text
#"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"value", type text}}),
//add index column so as to be able to sort back to desired output
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
//Add a column containing a List of all non-null Values
#"Added Custom" = Table.AddColumn(#"Added Index", "values", each List.RemoveNulls(#"Added Index"[value])),
//remove now unneeded value column
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"value"}),
//expand the List to new rows
#"Expanded values" = Table.ExpandListColumn(#"Removed Columns", "values"),
//Sort back to original order
#"Sorted Rows" = Table.Sort(#"Expanded values",{{"values", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Index" = Table.RemoveColumns(#"Sorted Rows",{"Index"}),
#"Type Values" = Table.TransformColumnTypes(#"Removed Index",{{"values", type text}})
in
#"Type Values"