Сопоставление и группировка двух диапазонов данных

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Сопоставление и группировка двух диапазонов данных (Макросы/Sub)

WERDART

Дата: Среда, 24.07.2019, 09:05 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Добрый день!

Прошу помочь.

Необходимо сделать из 2 таблиц (ресурс 1 и ресурс 2) сопоставительную ведомость (вкладка "желаемый результат"), опираясь на столбцы 1,4

При этом в сопоставительной должны оказаться все материалы из вкладок 1 и 2 (на то она и сопоставительная).

Больно не бейте и спасибо заранее.

Ответить

китин

Дата: Среда, 24.07.2019, 09:38 | Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 7029

Репутация: 1078 ±

Замечаний: 0% ±

Excel 2007;2010;2016

а файл то где?

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

WERDART

Дата: Среда, 24.07.2019, 10:34 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

китин,
простите

К сообщению приложен файл: 0777434.xlsx (18.6 Kb)

Ответить

WERDART

Дата: Среда, 24.07.2019, 17:34 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

китин,
поможите,пожалуйста..

Ответить

krosav4ig

Дата: Среда, 24.07.2019, 23:17 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Вариант в Power Query[vba]

Код

let
    fn=(t)=>let
        Col = Table.ColumnNames(t),
        GroupBy = List.RemoveMatchingItems(Col,{Col{1},Col{5}}),
        List    = {{Col{1}, each Text.Combine(List.Transform(Table.Column(_,Col{1}),Text.From),",")},
                   {Col{5}, each List.Sum(Table.Column(_,Col{5}))}}
    in Table.ReorderColumns(Table.Group(t,GroupBy,List),Col),
    Source    = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Source1   = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Merge     = Table.NestedJoin(fn(Source),{"Столбец1","Столбец4"},fn(Source1),{"Столбец1","Столбец4"},"2",3),
    Group     = Table.Group(Merge, {"2"}, {{"1", each Table.RemoveColumns(_,{"2"}), type table}})[[1],[2]],
    Transform = Table.FromRecords(Table.TransformRows(Group,(r)=>
                    Record.TransformFields(r,{
                        {"1",each Table.ReplaceValue(_,null,r[2]{0}[Столбец1],Replacer.ReplaceValue,{"Столбец1"})},
                        {"2",each Table.ReplaceValue(_,null,r[1]{0}[Столбец1],Replacer.ReplaceValue,{"Столбец1"})}
                    }))),
    ColN      = List.Zip(List.Transform(Table.ColumnNames(Source),each {_,"1."&_,"2."&_})),
    Result    = Table.ExpandTableColumn(Table.ExpandTableColumn(Transform, "1", ColN{0}, ColN{1}),"2", ColN{0}, ColN{2})
in
    Result

[/vba]

К сообщению приложен файл: 8954645.xlsx (34.8 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Ответить

WERDART

Дата: Четверг, 25.07.2019, 18:04 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

krosav4ig,
Спасибо, но возникло еще одно условие (столбец 8) - дело в том, что наименование по столбцу 8 в двух таблицах (ресурс 1 и ресурс 2) не совсем коррелируется, но при этом при сравнении и создании на него (столбец) необходимо опираться.

Вопрос: 1. Возможно ли опираясь на столбцы 1,4,8 - сделать сопоставление двух таблиц
2. Как протянуть вниз результаты итоговой таблицы (очень много позиций)

Заранее спасибо.

К сообщению приложен файл: c_Power_Quary-.xlsx (35.2 Kb)

Сообщение отредактировал WERDART - Пятница, 26.07.2019, 18:03

Ответить

WERDART

Дата: Суббота, 27.07.2019, 11:16 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

krosav4ig,
надо новую тему создать?

Ответить

krosav4ig

Дата: Суббота, 27.07.2019, 14:51 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

1. в запросе в двух местах заменить [vba]

Код

{"Столбец1","Столбец4"}

[/vba] на [vba]

Код

{"Столбец1","Столбец4","Столбец8"}

[/vba]
2. ПКМ по ячейке итоговой таблицы > Обновить. Или Данные > Обновить все (Ctrl+Alt+F5)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Суббота, 27.07.2019, 14:51

Ответить

WERDART

Дата: Воскресенье, 28.07.2019, 01:29 | Сообщение № 9

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

krosav4ig,
У меня expression.error: "Столбец8" таблицы не найден.
PQ ругается,видимо,на вкладку "ресурс2, столбец 8 - в редакторе везде гордый "NULL". Хотя по первой вкладке 8-ой столбец подтянул без проблем.

Прошу прростить, понять и помочь.

спасибо

К сообщению приложен файл: 8954645-7-.xlsx (38.4 Kb)

Сообщение отредактировал WERDART - Воскресенье, 28.07.2019, 01:32

Ответить

krosav4ig

Дата: Среда, 31.07.2019, 00:12 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

WERDART, у вас на листе ресурс 2 таблица не растянулась на столбец 8
[vba]

Код

let
    fn=(t)=>let
        Col     = Table.ColumnNames(t),
        GroupBy = List.RemoveMatchingItems(Col,{Col{1},Col{5}}),
        List    = {{Col{1}, each Text.Combine(List.Transform(Table.Column(_,Col{1}),Text.From),",")},
                   {Col{5}, each List.Sum(Table.Column(_,Col{5}))}}
    in Table.ReorderColumns(Table.Group(t,GroupBy,List),Col),
    Source    = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Source1   = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    Merge     = Table.NestedJoin(fn(Source),{"Столбец1","Столбец4","Столбец8"},fn(Source1),{"Столбец1","Столбец4","Столбец8"},"2",3),
    Group     = Table.Group(Merge, {"2"}, {{"1", each Table.RemoveColumns(_,{"2"}), type table}})[[1],[2]],
    Transform = Table.FromRecords(Table.TransformRows(Group,(r)=>
                    Record.TransformFields(r,{
                        {"1",each Table.ReplaceValue(_,null,r[2]{0}[Столбец1],Replacer.ReplaceValue,{"Столбец1"})},
                        {"2",each Table.ReplaceValue(_,null,r[1]{0}[Столбец1],Replacer.ReplaceValue,{"Столбец1"})}
                    }))),
    ColN      = List.Zip(List.Transform(Table.ColumnNames(Source),each {_,"1."&_,"2."&_})),
    Result    = Table.ExpandTableColumn(Table.ExpandTableColumn(Transform, "1", ColN{0}, ColN{1}),"2", ColN{0}, ColN{2})
in
    Result

[/vba]