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

Вход

Регистрация

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

 

= Мир MS Excel/Группировка строк с объединением и суммирование данных - Мир MS Excel

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

Excel 2016
Друзья, добрый вечер!
Прошу помочь.

Проблема:

Две (на самом деле около 30 тыс.) позиции. Наименования идентичны. Необходимо соединить две позиции (как в примере, во вложении) - что-то должно быть в результате через запятую, что-то суммироваться.

Спасибо заранее.
К сообщению приложен файл: 2854380.xlsx(9.6 Kb)


Сообщение отредактировал WERDART - Вторник, 23.07.2019, 08:34
 
Ответить
СообщениеДрузья, добрый вечер!
Прошу помочь.

Проблема:

Две (на самом деле около 30 тыс.) позиции. Наименования идентичны. Необходимо соединить две позиции (как в примере, во вложении) - что-то должно быть в результате через запятую, что-то суммироваться.

Спасибо заранее.

Автор - WERDART
Дата добавления - 22.07.2019 в 19:16
_Boroda_ Дата: Понедельник, 22.07.2019, 19:41 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15650
Репутация: 6115 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Надежды никакой. Но хочется это услышать от Вас.

Слушайте - "Надежды никакой".
С таким названием темы
Прочитайте Правила форума. Исправьте название согласно п.2 Правил


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Надежды никакой. Но хочется это услышать от Вас.

Слушайте - "Надежды никакой".
С таким названием темы
Прочитайте Правила форума. Исправьте название согласно п.2 Правил

Автор - _Boroda_
Дата добавления - 22.07.2019 в 19:41
WERDART Дата: Понедельник, 22.07.2019, 21:42 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
_Boroda_, исправился
Спасибо
 
Ответить
Сообщение_Boroda_, исправился
Спасибо

Автор - WERDART
Дата добавления - 22.07.2019 в 21:42
_Boroda_ Дата: Понедельник, 22.07.2019, 22:10 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 15650
Репутация: 6115 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
исправился

Нет


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
исправился

Нет

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

Excel 2016
Как по вашему должна называться тема?
 
Ответить
СообщениеКак по вашему должна называться тема?

Автор - WERDART
Дата добавления - 23.07.2019 в 07:17
Pelena Дата: Вторник, 23.07.2019, 07:24 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Например, Сгруппировать строки с объединением и суммированием данных

По теме: при группировке на что опираться: только на первый столбец или на сочетание каких-то столбцов? Маловато данных


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНапример, Сгруппировать строки с объединением и суммированием данных

По теме: при группировке на что опираться: только на первый столбец или на сочетание каких-то столбцов? Маловато данных

Автор - Pelena
Дата добавления - 23.07.2019 в 07:24
WERDART Дата: Вторник, 23.07.2019, 08:38 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena, Доброе утро, я поправил тему

При группировке необходимо ориентироваться на столбцы 1 и 4.
Спасибо
 
Ответить
СообщениеPelena, Доброе утро, я поправил тему

При группировке необходимо ориентироваться на столбцы 1 и 4.
Спасибо

Автор - WERDART
Дата добавления - 23.07.2019 в 08:38
Pelena Дата: Вторник, 23.07.2019, 09:46 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Такой вариант
[vba]
Код
Public Sub test()
    Dim arr, oDic As Object, lrow&, i&, j&, r0&, keytmp$, arRez(), k
    r0 = 3
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(r0, 1).Resize(lrow - r0 + 1, 7).Value
    Set oDic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr)
        keytmp = arr(i, 1) & "|" & arr(i, 4)
        If Not oDic.Exists(keytmp) Then
            oDic(keytmp) = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & arr(i, 6) & "|" & arr(i, 7)
        Else
            oDic(keytmp) = arr(i, 1) & "|" & Split(oDic(keytmp), "|")(1) & ", " & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & Split(oDic(keytmp), "|")(5) + arr(i, 6) & "|" & arr(i, 7)
        End If
    Next i
    ReDim arRez(1 To oDic.Count, 1 To 7)
    i = 1
    For Each k In oDic.Keys
        For j = 0 To 6
            arRez(i, j + 1) = Split(oDic(k), "|")(j)
        Next j
        i = i + 1
    Next k
    Sheets("Лист2").UsedRange.ClearContents
    Sheets("Лист2").Cells(1, 1).Resize(UBound(arRez), 7) = arRez
End Sub
[/vba]
К сообщению приложен файл: 2854380.xlsm(20.6 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеТакой вариант
[vba]
Код
Public Sub test()
    Dim arr, oDic As Object, lrow&, i&, j&, r0&, keytmp$, arRez(), k
    r0 = 3
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Cells(r0, 1).Resize(lrow - r0 + 1, 7).Value
    Set oDic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr)
        keytmp = arr(i, 1) & "|" & arr(i, 4)
        If Not oDic.Exists(keytmp) Then
            oDic(keytmp) = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & arr(i, 6) & "|" & arr(i, 7)
        Else
            oDic(keytmp) = arr(i, 1) & "|" & Split(oDic(keytmp), "|")(1) & ", " & arr(i, 2) & "|" & arr(i, 3) & "|" & arr(i, 4) & "|" & arr(i, 5) & "|" & Split(oDic(keytmp), "|")(5) + arr(i, 6) & "|" & arr(i, 7)
        End If
    Next i
    ReDim arRez(1 To oDic.Count, 1 To 7)
    i = 1
    For Each k In oDic.Keys
        For j = 0 To 6
            arRez(i, j + 1) = Split(oDic(k), "|")(j)
        Next j
        i = i + 1
    Next k
    Sheets("Лист2").UsedRange.ClearContents
    Sheets("Лист2").Cells(1, 1).Resize(UBound(arRez), 7) = arRez
End Sub
[/vba]

Автор - Pelena
Дата добавления - 23.07.2019 в 09:46
WERDART Дата: Вторник, 23.07.2019, 13:15 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena,
У меня физиономия Соломина сейчас. Гениально. Спасибо.

Но осталось (пожалуйста) два вопроса:

1.Как суммировать столбец 7 корректно - макрос как будто не видит запятых после целых чисел (пример во вложении)
2.Возможно ли добавить в макрос суммирование по столбцу 3 (подстановку) как в столбце 2?

Еще раз спасибо.
К сообщению приложен файл: _28543801.xlsm(21.0 Kb)


Сообщение отредактировал WERDART - Вторник, 23.07.2019, 13:17
 
Ответить
СообщениеPelena,
У меня физиономия Соломина сейчас. Гениально. Спасибо.

Но осталось (пожалуйста) два вопроса:

1.Как суммировать столбец 7 корректно - макрос как будто не видит запятых после целых чисел (пример во вложении)
2.Возможно ли добавить в макрос суммирование по столбцу 3 (подстановку) как в столбце 2?

Еще раз спасибо.

Автор - WERDART
Дата добавления - 23.07.2019 в 13:15
Pelena Дата: Вторник, 23.07.2019, 13:26 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
По столбцу 3 все объединять или только уникальные?


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПо столбцу 3 все объединять или только уникальные?

Автор - Pelena
Дата добавления - 23.07.2019 в 13:26
WERDART Дата: Вторник, 23.07.2019, 13:30 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena,

Уникальные, спасибо
 
Ответить
СообщениеPelena,

Уникальные, спасибо

Автор - WERDART
Дата добавления - 23.07.2019 в 13:30
Pelena Дата: Вторник, 23.07.2019, 14:01 | Сообщение № 12
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Как-то так
К сообщению приложен файл: 5620105.xlsm(22.5 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеКак-то так

Автор - Pelena
Дата добавления - 23.07.2019 в 14:01
krosav4ig Дата: Вторник, 23.07.2019, 14:46 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2211
Репутация: 919 ±
Замечаний: 0% ±

Excel 2007,2010,2013
Вариант в Power Query
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Group = Table.Group(Source, {"Column1", "Column3", "Column4","Column5","Column7"}, {{"Column2", each Text.Combine(List.Transform([Column2],Text.From),","), type text},{"Column6", each List.Sum([Column6]), type number}})
in
    Table.ReorderColumns(Group,Table.ColumnNames(Source))
[/vba]
данные для запроса берутся из именованного диапазона data, для обновления ПКМ по таблице>Обновить или Данные>Обновить все (Ctrl+Alt+F5)
К сообщению приложен файл: 9309715.xlsx(18.6 Kb)


email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460
 
Ответить
СообщениеВариант в Power Query
[vba]
Код
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Group = Table.Group(Source, {"Column1", "Column3", "Column4","Column5","Column7"}, {{"Column2", each Text.Combine(List.Transform([Column2],Text.From),","), type text},{"Column6", each List.Sum([Column6]), type number}})
in
    Table.ReorderColumns(Group,Table.ColumnNames(Source))
[/vba]
данные для запроса берутся из именованного диапазона data, для обновления ПКМ по таблице>Обновить или Данные>Обновить все (Ctrl+Alt+F5)

Автор - krosav4ig
Дата добавления - 23.07.2019 в 14:46
WERDART Дата: Вторник, 23.07.2019, 15:06 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena,
к сожалению на всю таблицу выдает ошибку : "Run time error 13. Type mismatch
в одной таблице 17 000 позиций, во второй 33 000. Возможно из-за объема?


Сообщение отредактировал WERDART - Вторник, 23.07.2019, 15:25
 
Ответить
СообщениеPelena,
к сожалению на всю таблицу выдает ошибку : "Run time error 13. Type mismatch
в одной таблице 17 000 позиций, во второй 33 000. Возможно из-за объема?

Автор - WERDART
Дата добавления - 23.07.2019 в 15:06
Pelena Дата: Вторник, 23.07.2019, 16:31 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Не должно зависеть от объёма.
Проверьте данные, везде ли в 6-м столбце числа


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНе должно зависеть от объёма.
Проверьте данные, везде ли в 6-м столбце числа

Автор - Pelena
Дата добавления - 23.07.2019 в 16:31
WERDART Дата: Вторник, 23.07.2019, 16:49 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena,
проверил с формулой =ечисло - ИСТИНА по всем позициям.
Везде числа
 
Ответить
СообщениеPelena,
проверил с формулой =ечисло - ИСТИНА по всем позициям.
Везде числа

Автор - WERDART
Дата добавления - 23.07.2019 в 16:49
Pelena Дата: Вторник, 23.07.2019, 17:00 | Сообщение № 17
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
В сообщении об ошибке нажмите Debug, посмотрите, чему равна переменная i (номер строки данных). Остановите макрос, скопируйте несколько строк до и после i-ой в отдельный файл и покажите здесь. Подозреваю, что всё-же в данных ошибка


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВ сообщении об ошибке нажмите Debug, посмотрите, чему равна переменная i (номер строки данных). Остановите макрос, скопируйте несколько строк до и после i-ой в отдельный файл и покажите здесь. Подозреваю, что всё-же в данных ошибка

Автор - Pelena
Дата добавления - 23.07.2019 в 17:00
WERDART Дата: Вторник, 23.07.2019, 20:07 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Pelena,
спасибо огромное, нашел ошибку.
Хотел уточнить, возможно ли из файла во вложении сделать сопоставительную ведомость - привязать к ресурсу №1 позиции из ресурса №2. При этом необходимо задействовать все без исключения позиции обоих ресурсов основываясь на данных 1 и 4 столбцов. (приложил файл).

Еще раз большое спасибо!
К сообщению приложен файл: 4336301.xlsx(18.6 Kb)
 
Ответить
СообщениеPelena,
спасибо огромное, нашел ошибку.
Хотел уточнить, возможно ли из файла во вложении сделать сопоставительную ведомость - привязать к ресурсу №1 позиции из ресурса №2. При этом необходимо задействовать все без исключения позиции обоих ресурсов основываясь на данных 1 и 4 столбцов. (приложил файл).

Еще раз большое спасибо!

Автор - WERDART
Дата добавления - 23.07.2019 в 20:07
Pelena Дата: Вторник, 23.07.2019, 20:14 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 14874
Репутация: 3250 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Это уже другой вопрос, значит, другая тема


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЭто уже другой вопрос, значит, другая тема

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

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

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

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