Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сопоставление и группировка двух диапазонов данных - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сопоставление и группировка двух диапазонов данных (Макросы/Sub)
Сопоставление и группировка двух диапазонов данных
WERDART Дата: Среда, 24.07.2019, 09:05 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!

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

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

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

Больно не бейте и спасибо заранее.
 
Ответить
СообщениеДобрый день!

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

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

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

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

Автор - WERDART
Дата добавления - 24.07.2019 в 09:05
китин Дата: Среда, 24.07.2019, 09:38 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 5730
Репутация: 903 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
а файл то где?


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениеа файл то где?

Автор - китин
Дата добавления - 24.07.2019 в 09:38
WERDART Дата: Среда, 24.07.2019, 10:34 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
китин,
простите
К сообщению приложен файл: 0777434.xlsx(18.6 Kb)
 
Ответить
Сообщениекитин,
простите

Автор - WERDART
Дата добавления - 24.07.2019 в 10:34
WERDART Дата: Среда, 24.07.2019, 17:34 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
китин,
поможите,пожалуйста..
 
Ответить
Сообщениекитин,
поможите,пожалуйста..

Автор - WERDART
Дата добавления - 24.07.2019 в 17:34
krosav4ig Дата: Среда, 24.07.2019, 23:17 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2140
Репутация: 892 ±
Замечаний: 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
 
Ответить
СообщениеВариант в 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]

Автор - krosav4ig
Дата добавления - 24.07.2019 в 23:17
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
 
Ответить
Сообщениеkrosav4ig,
Спасибо, но возникло еще одно условие (столбец 8) - дело в том, что наименование по столбцу 8 в двух таблицах (ресурс 1 и ресурс 2) не совсем коррелируется, но при этом при сравнении и создании на него (столбец) необходимо опираться.

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

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

Автор - WERDART
Дата добавления - 25.07.2019 в 18:04
WERDART Дата: Суббота, 27.07.2019, 11:16 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
krosav4ig,
надо новую тему создать?
 
Ответить
Сообщениеkrosav4ig,
надо новую тему создать?

Автор - WERDART
Дата добавления - 27.07.2019 в 11:16
krosav4ig Дата: Суббота, 27.07.2019, 14:51 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2140
Репутация: 892 ±
Замечаний: 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
 
Ответить
Сообщение1. в запросе в двух местах заменить [vba]
Код
{"Столбец1","Столбец4"}
[/vba] на [vba]
Код
{"Столбец1","Столбец4","Столбец8"}
[/vba]
2. ПКМ по ячейке итоговой таблицы > Обновить. Или Данные > Обновить все (Ctrl+Alt+F5)

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

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

спасибо

Автор - WERDART
Дата добавления - 28.07.2019 в 01:29
krosav4ig Дата: Среда, 31.07.2019, 00:12 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2140
Репутация: 892 ±
Замечаний: 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]
К сообщению приложен файл: 5678766.xlsx(37.7 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
Сообщение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]

Автор - krosav4ig
Дата добавления - 31.07.2019 в 00:12
WERDART Дата: Четверг, 01.08.2019, 10:27 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
krosav4ig,
Спасибо Вам!
 
Ответить
Сообщениеkrosav4ig,
Спасибо Вам!

Автор - WERDART
Дата добавления - 01.08.2019 в 10:27
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сопоставление и группировка двух диапазонов данных (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!