Excel巨集來收集數據

Question

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

我通常會收到文件工作簿，每個工作簿有 1 張或多張紙

我想將這些文件/工作表中的資料收集到一張工作表中的主工作簿中

將主要複製所有數據，但在某些列中按不同的順序複製。

工作表有這樣的數據：

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

主表將具有：

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

在大多數情況下，如果客戶有 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)