Консолидация 2 файлов Excel - функция консолидации

Question

У меня проблемы с консолидацией данных из двух файлов Excel ...

file1.xlsx

file2.xlsx

Я хотел бы получить что-то вроде этого ....

Как бы я это сделал с консолидирующей функцией. Я пытался следовать этому руководству, но, похоже, оно не обновляется.

Answer 1

Что вы хотите сделать, это объединить данные из разных файлов, а не объединить данные из разных листов. Команда «Консолидировать» с ленты всегда агрегирует числовые данные, а не просто их вставляет.

Нет встроенной простой функции одним щелчком мыши, которая делает то, что вы описываете. Вот возможный подход для ручного слияния:

Скопируйте столбец "Новый" из одного файла и вставьте его рядом с существующим столбцом "Новый" в другом файле. Затем используйте формулу в следующем столбце, как

=IF(ISNUMBER(D2),D2,IF(ISNUMBER(E2),E2,""))

Скопируйте формулу до конца, и лист будет выглядеть так:

Теперь скопируйте данные из столбца F в столбец D и удалите столбцы E и F.

Answer 2

Вы можете использовать Microsoft Power Add-In Query , чтобы объединить несколько файлов. Объединение данных из нескольких источников данных является основной функцией Power Query. Больше на сайте MS здесь.

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

У вас есть два запроса в Power Query. Здесь М код, который вы можете использовать.

Считайте файл 2 в первом запросе (только как соединение):

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"

Прочитайте файл1 и объедините его с первым запросом:

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"""

вывод выглядит так: