макрос 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

просто добавлю идентификатор в конец, чтобы убедиться, что эти данные скопированы с того же идентификатора клиента.

Надеюсь, я понятно объяснил, так как я слабовидящий и не могу показать скриншот.

Answer 1

Вот пример использования Power Query:

Это предполагает, что в дублированных столбцах, как вы показываете в вашем примере, все идентичны, за исключением телефона. Если это не так, вам может потребоваться выполнить эту процедуру для нескольких столбцов

Аннотированный М-код

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.

Затем скопируйте ячейку B8 -> выберите все оставшиеся ячейки в столбце B с данными и вставьте формулу.

Теперь у вас будет совпадающий идентификатор на обоих концах.

Спасибо,

Майк

Answer 3

Полуавтоматическое решение:

1. Объедините все вручную в основные листы.
2. Сортировать по ID
3. В строку E2 поместите: =ЕСЛИ(A2=A3, D2, "") *
4. Теперь скопируйте и вставьте как значения столбца E (telephone2)
5. Нажмите «Удалить дубликаты» на ленте «Данные», выберите только столбец ID и все готово.

• К сожалению, есть небольшая проблема с шагом 3 — если у вас уже есть данные в столбце E / телефон 2 от предыдущего слияния. Чтобы решить эту проблему, установите фильтр на строку заголовка и фильтр только для пробелов. Затем скопируйте и вставьте формулу вниз только в пустые ячейки. Альтернативно создайте новый столбец телефон 2 в F и введите F2 =IF(A2=A3, D3, E2)