excel 宏用于收集数据

Question

我正在使用 ms-excel 2016，在 windows-7 sp1 下。

我通常会得到文件工作簿，每个工作簿有 1 个或多个工作表

我想将这些文件/工作表中的数据收集到一张工作表中的主工作簿中

将复制大部分数据，但某些列的顺序不同。

工作表包含如下数据：

id, name, address, telephone,branch,area, ...

主表将包含：

id, name,adress,telephone-1, telephone-2,area,branch

大多数情况下，如果客户有 2 部电话，数据输入系统会将其输入到 2 行中，如下所示：

id, name, address, telephone,branch,area
g13, jake, 7 st., 23456343, ,
k12,johne,ca st., 142635, ,
k12,johne,ca st.,150979, ,

在主表中将是这样的：

id, name, address, telephone-1,telephone-2,area,branch,verified_id
g13, jake, 7 st.,456343, , ,
k12,johne,ca st.,142635,150979, , ,k12

只需在末尾添加 id 来验证该数据是从同一个客户 id 复制而来的。

希望我能解释清楚，因为视力受损的人无法显示截图。

Answer 1

以下是使用 Power Query 的示例：

这假设在重复的列中，如您在示例中所示，除电话外，其他列都相同。如果不是这种情况，您可能需要在多个列上执行此过程

带注释 M 代码

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"name", type text}, {"address", type text}, {"telephone", Int64.Type}, {"branch", type any}, {"area", type any}}),
    
    //Group by all the rows EXCEPT "telephone"
    //Do not aggregate (select all rows)
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id", "name", "address", "branch", "area"}, {{"Grouped", each _, type table [id=nullable text, name=nullable text, address=nullable text, telephone=nullable number, branch=text, area=text]}}),
   
   //extract the phone numbers as a "List"
   #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Telephone", each Table.Column([Grouped],"telephone")),

   //remove unwanted column and put the phone number column where it belongs
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Grouped"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"id", "name", "address", "Telephone", "branch", "area"}),

    //add the verified_id column
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "verified_id", each if List.Count([Telephone]) = 1 then null else [id]),

    //extract the individual phone numbers from the list, and split the columnt
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Telephone", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Telephone", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Telephone.1", "Telephone.2"}),
    
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Telephone.1", Int64.Type}, {"Telephone.2", Int64.Type}})
in
    #"Changed Type1"

Answer 2

宏（至少对于不太高级的用户/程序员来说）仅当您对数据执行完全相同的操作并且数据具有完全相同的布局时才会对您有所帮助。

我认为可以为此制作一个宏，但这不是必要的。

我只需转到第一行包含数据的最后一个单元格（假设它是 B8）

输入“B8=B1”它将显示来自B1的ID。

然后复制 B8 单元格 -> 选择 B 列中所有剩余有数据的单元格并粘贴公式。

现在您将在两端拥有匹配的 ID。

谢谢，

麦克风

Answer 3

半手动解决方案：

1. 手动将所有内容合并到主表中。
2. 按 ID 排序
3. 在 E2 行输入： =IF(A2=A3, D2, "") *
4. 现在复制并粘贴 E 列（电话 2）作为值
5. 点击数据功能区上的“删除重复项”，仅选择 ID 列即可完成

• 不幸的是，第 3 步存在一个小问题 - 如果您在 E 列/电话 2 中已经有来自之前合并的数据。要解决此问题，请在标题行上设置过滤器并仅过滤空白。然后将公式复制并粘贴到空白单元格中。或者在 F 中创建一个新的电话 2 列并在 F2 中输入 =IF(A2=A3, D3, E2)