答案1
答案2
您可以使用Microsoft 插件 Power Query以便将多个文件合并在一起。合并来自多个数据源的数据是 Power Query 的核心功能。更多信息请访问 MS 网站这里。
在您的示例中,我假设前三列中有相同的值,而“新”列中只有两个文件中的一个有值。
您在 Power Query 中有两个查询。这是您可以使用的 M 代码。
在第一个查询中读取 file2(仅作为连接):
let
Source = Excel.Workbook(File.Contents("C:\tmp\example\file2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Define Header" = Table.PromoteHeaders(Sheet1_Sheet),
#"Change Type" = Table.TransformColumnTypes(#"Define Header",{{"First", type text}, {"Last", type text}, {"Old", Int64.Type}, {"New", Int64.Type}})
in
#"Change Type"
读取 file1 并将其与第一个查询合并:
let
Source = Excel.Workbook(File.Contents("C:\tmp\example\file1.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Define Header" = Table.PromoteHeaders(Sheet1_Sheet),
#"Change Type" = Table.TransformColumnTypes(#"Define Header",{{"First", type text}, {"Last", type text}, {"Old", Int64.Type}, {"New", Int64.Type}}),
#"Merge tables" = Table.NestedJoin(#"Change Type",{"First", "Last", "Old"},File2,{"First", "Last", "Old"},"NewColumn",JoinKind.FullOuter),
#"Expand new column" = Table.ExpandTableColumn(#"Merge tables", "NewColumn", {"New"}, {"NewColumn.New"}),
#"Merge columns ""New""" = Table.CombineColumns(Table.TransformColumnTypes(#"Expand new column", {{"New", type text}, {"NewColumn.New", type text}}, "de-CH"),{"New", "NewColumn.New"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"New merged")
in
#"Merge columns ""New"""