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

Вход

Регистрация

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

 

= Мир MS Excel/Сводный отчет с использованием метода collection - Мир MS Excel

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

Excel 2016
Доброго времени суток, прошу оказать посильную помощь в формировании данных на листе "отчет" с помощью коллекций, сут вопроса на листе данные есть некий список, в реале он занимает порядка 300 000 строк, необходимо на лист "отчет в столбец Код вытащить уникальные и к ним суммировать количество по годам и суммы, рассчитать цены. Пробовал делать формулами очень долго такое количество считает, пробовал "допилить" сторонний макрос но "ума" хватает только-лишь что-бы заполнить либо количество, либо суммы. Заранее спасибо.
К сообщению приложен файл: 5903680.xlsx(9.9 Kb)
 
Ответить
СообщениеДоброго времени суток, прошу оказать посильную помощь в формировании данных на листе "отчет" с помощью коллекций, сут вопроса на листе данные есть некий список, в реале он занимает порядка 300 000 строк, необходимо на лист "отчет в столбец Код вытащить уникальные и к ним суммировать количество по годам и суммы, рассчитать цены. Пробовал делать формулами очень долго такое количество считает, пробовал "допилить" сторонний макрос но "ума" хватает только-лишь что-бы заполнить либо количество, либо суммы. Заранее спасибо.

Автор - ssm
Дата добавления - 11.06.2020 в 16:23
Pelena Дата: Четверг, 11.06.2020, 17:36 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16812
Репутация: 3623 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Здравствуйте.
Обязательно макросы?
Может, сводная подойдёт
К сообщению приложен файл: 6014931.xlsx(124.3 Kb)


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

Автор - Pelena
Дата добавления - 11.06.2020 в 17:36
Hugo Дата: Четверг, 11.06.2020, 17:41 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Доброго.
Тут на словаре нужно делать - сперва всё собрать в словаре по ключам код|год|сумма, код|год|количество (с ценой по примеру непонятно. но вероятно нужно просто делить итоговую сумму на итоговое количество уже в финале при выгрузке в сводную).
Затем циклом по сводной разложить собранное из словаря.
Если тот сторонний макрос на словаре - то нет проблем добавить либо количество, либо суммы :)


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеДоброго.
Тут на словаре нужно делать - сперва всё собрать в словаре по ключам код|год|сумма, код|год|количество (с ценой по примеру непонятно. но вероятно нужно просто делить итоговую сумму на итоговое количество уже в финале при выгрузке в сводную).
Затем циклом по сводной разложить собранное из словаря.
Если тот сторонний макрос на словаре - то нет проблем добавить либо количество, либо суммы :)

Автор - Hugo
Дата добавления - 11.06.2020 в 17:41
ssm Дата: Четверг, 11.06.2020, 17:41 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
К сожалению сводная не подойдет, пробовал через массивы также занимает очень много времени, а вот коллекции быстро, но не получается одновременно сумму и количество сделать:(
 
Ответить
СообщениеК сожалению сводная не подойдет, пробовал через массивы также занимает очень много времени, а вот коллекции быстро, но не получается одновременно сумму и количество сделать:(

Автор - ssm
Дата добавления - 11.06.2020 в 17:41
ssm Дата: Четверг, 11.06.2020, 17:50 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Вот с чужим кодом, там еще проблема, поле Код должен быть в текстовом формате что бы работал, а можно переделать на числовой?
К сообщению приложен файл: 9155770.xlsm(20.2 Kb)
 
Ответить
СообщениеВот с чужим кодом, там еще проблема, поле Код должен быть в текстовом формате что бы работал, а можно переделать на числовой?

Автор - ssm
Дата добавления - 11.06.2020 в 17:50
Hugo Дата: Четверг, 11.06.2020, 17:54 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Код должен быть текстом, это согласно логики его существа!


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеКод должен быть текстом, это согласно логики его существа!

Автор - Hugo
Дата добавления - 11.06.2020 в 17:54
ssm Дата: Четверг, 11.06.2020, 17:56 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Сорри,[vba]
Код
Sub êîë()
    Dim a(), i&, ii&, t$, col As New Collection
    On Error Resume Next

    With CreateObject("scripting.dictionary"): .comparemode = 1
    
        a = Sheets("äàííûå").Range("A1").CurrentRegion.Value
        For i = 2 To UBound(a)
            col.Add a(i, 1), a(i, 1)
            t = a(i, 1) & a(i, 4)
            .Item(t) = .Item(t) + a(i, 3)
        Next

        a = Sheets("îò÷åò").Range("A2").CurrentRegion.Rows(2).Value
        ReDim b(1 To .Count, 1 To 5)
        For i = 1 To .Count
            b(i, 1) = col(i)
            For ii = 2 To UBound(a, 2)
                t = b(i, 1) & a(1, ii)
                b(i, ii) = .Item(t)
            Next
        Next

    End With

    Sheets("Îò÷åò").Range("A3").Resize(UBound(b), UBound(b, 2)) = b
End Sub
[/vba]
 
Ответить
СообщениеСорри,[vba]
Код
Sub êîë()
    Dim a(), i&, ii&, t$, col As New Collection
    On Error Resume Next

    With CreateObject("scripting.dictionary"): .comparemode = 1
    
        a = Sheets("äàííûå").Range("A1").CurrentRegion.Value
        For i = 2 To UBound(a)
            col.Add a(i, 1), a(i, 1)
            t = a(i, 1) & a(i, 4)
            .Item(t) = .Item(t) + a(i, 3)
        Next

        a = Sheets("îò÷åò").Range("A2").CurrentRegion.Rows(2).Value
        ReDim b(1 To .Count, 1 To 5)
        For i = 1 To .Count
            b(i, 1) = col(i)
            For ii = 2 To UBound(a, 2)
                t = b(i, 1) & a(1, ii)
                b(i, ii) = .Item(t)
            Next
        Next

    End With

    Sheets("Îò÷åò").Range("A3").Resize(UBound(b), UBound(b, 2)) = b
End Sub
[/vba]

Автор - ssm
Дата добавления - 11.06.2020 в 17:56
ssm Дата: Четверг, 11.06.2020, 17:57 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
[vba]
Код
Sub кол()
    Dim a(), i&, ii&, t$, col As New Collection
    On Error Resume Next

    With CreateObject("scripting.dictionary"): .comparemode = 1
    
        a = Sheets("данные").Range("A1").CurrentRegion.Value
        For i = 2 To UBound(a)
            col.Add a(i, 1), a(i, 1)
            t = a(i, 1) & a(i, 4)
            .Item(t) = .Item(t) + a(i, 3)
        Next

        a = Sheets("отчет").Range("A2").CurrentRegion.Rows(2).Value
        ReDim b(1 To .Count, 1 To 5)
        For i = 1 To .Count
            b(i, 1) = col(i)
            For ii = 2 To UBound(a, 2)
                t = b(i, 1) & a(1, ii)
                b(i, ii) = .Item(t)
            Next
        Next

    End With

    Sheets("Отчет").Range("A3").Resize(UBound(b), UBound(b, 2)) = b
End Sub
[/vba]
 
Ответить
Сообщение[vba]
Код
Sub кол()
    Dim a(), i&, ii&, t$, col As New Collection
    On Error Resume Next

    With CreateObject("scripting.dictionary"): .comparemode = 1
    
        a = Sheets("данные").Range("A1").CurrentRegion.Value
        For i = 2 To UBound(a)
            col.Add a(i, 1), a(i, 1)
            t = a(i, 1) & a(i, 4)
            .Item(t) = .Item(t) + a(i, 3)
        Next

        a = Sheets("отчет").Range("A2").CurrentRegion.Rows(2).Value
        ReDim b(1 To .Count, 1 To 5)
        For i = 1 To .Count
            b(i, 1) = col(i)
            For ii = 2 To UBound(a, 2)
                t = b(i, 1) & a(1, ii)
                b(i, ii) = .Item(t)
            Next
        Next

    End With

    Sheets("Отчет").Range("A3").Resize(UBound(b), UBound(b, 2)) = b
End Sub
[/vba]

Автор - ssm
Дата добавления - 11.06.2020 в 17:57
ssm Дата: Четверг, 11.06.2020, 17:59 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Прошу прощения, т.е. не получиться переделать что-бы подхватывал в коллекцию числа, а можно как-нибудь что бы на листе были числа а в коллекцию записывались в тексте?
 
Ответить
СообщениеПрошу прощения, т.е. не получиться переделать что-бы подхватывал в коллекцию числа, а можно как-нибудь что бы на листе были числа а в коллекцию записывались в тексте?

Автор - ssm
Дата добавления - 11.06.2020 в 17:59
Hugo Дата: Четверг, 11.06.2020, 18:07 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
В коллекции как ключ должен быть текст, хотя коллекция тут не особо и нужна, вполне можно её выкинуть, ну да ладно.
Чем вызвано волнение по поводу числа/текст?
А добавить количество без проблем - просто ключ делайте не
a(i, 1) & a(i, 4)
а
a(i, 1) & a(i, 4) & названиепараметра
, ну и ещё разделитель этих "слагаемых" будет не лишним, а вернее необходимым.
И при извлечении это названиепараметра нужно или учесть в коде, или брать с листа, но там эти объединения (что зло!) лично у меня отбивают охоту возиться...


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеВ коллекции как ключ должен быть текст, хотя коллекция тут не особо и нужна, вполне можно её выкинуть, ну да ладно.
Чем вызвано волнение по поводу числа/текст?
А добавить количество без проблем - просто ключ делайте не
a(i, 1) & a(i, 4)
а
a(i, 1) & a(i, 4) & названиепараметра
, ну и ещё разделитель этих "слагаемых" будет не лишним, а вернее необходимым.
И при извлечении это названиепараметра нужно или учесть в коде, или брать с листа, но там эти объединения (что зло!) лично у меня отбивают охоту возиться...

Автор - Hugo
Дата добавления - 11.06.2020 в 18:07
ssm Дата: Четверг, 11.06.2020, 18:11 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Суетнулся под "тесаком" из-за кода, подумал его отдельно надо показать:), а вот за совет спасибо, а можно как-то это в коде показать. Я честно только изучаю вопрос работы коллекций:(, пожалуйста, для меня каждый пример на вес золота
 
Ответить
СообщениеСуетнулся под "тесаком" из-за кода, подумал его отдельно надо показать:), а вот за совет спасибо, а можно как-то это в коде показать. Я честно только изучаю вопрос работы коллекций:(, пожалуйста, для меня каждый пример на вес золота

Автор - ssm
Дата добавления - 11.06.2020 в 18:11
ssm Дата: Четверг, 11.06.2020, 18:14 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
А из-за числа/текст, просто огромный прайс и поле Код там в числовом формате. И у меня проблемы с выгрузкой на лист что получилось, в общем полный "алес":)
 
Ответить
СообщениеА из-за числа/текст, просто огромный прайс и поле Код там в числовом формате. И у меня проблемы с выгрузкой на лист что получилось, в общем полный "алес":)

Автор - ssm
Дата добавления - 11.06.2020 в 18:14
Hugo Дата: Четверг, 11.06.2020, 18:17 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Так число в текст в коде легко преобразовать - просто берёте значение и Trim() его... на всякий, вдруг там текст с пробелами сидит...
Вот добавил в существующий код второй параметр, с ценой не вникал, но можно тоже вывести среднее.
Но объединение убрал, ибо лениво бороться в коде.
К сообщению приложен файл: 9155770H.xlsm(20.9 Kb)


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Четверг, 11.06.2020, 18:18
 
Ответить
СообщениеТак число в текст в коде легко преобразовать - просто берёте значение и Trim() его... на всякий, вдруг там текст с пробелами сидит...
Вот добавил в существующий код второй параметр, с ценой не вникал, но можно тоже вывести среднее.
Но объединение убрал, ибо лениво бороться в коде.

Автор - Hugo
Дата добавления - 11.06.2020 в 18:17
ssm Дата: Четверг, 11.06.2020, 18:26 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
С ценой и бох с ним, работает на малой выборке что надо, можно вопросик, не понял куда в коде вписать что бы брал значением по Коду, а трим не нужен точно не будет пробелов, спасибо.
 
Ответить
СообщениеС ценой и бох с ним, работает на малой выборке что надо, можно вопросик, не понял куда в коде вписать что бы брал значением по Коду, а трим не нужен точно не будет пробелов, спасибо.

Автор - ssm
Дата добавления - 11.06.2020 в 18:26
ssm Дата: Четверг, 11.06.2020, 18:30 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Разобрался со значением, спасибо большое
 
Ответить
СообщениеРазобрался со значением, спасибо большое

Автор - ssm
Дата добавления - 11.06.2020 в 18:30
Hugo Дата: Четверг, 11.06.2020, 18:43 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Добавил трим и цену. А трим нужен, т.к. коды теперь могут быть и числом.
К сообщению приложен файл: 9252450.xlsm(21.8 Kb)


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Четверг, 11.06.2020, 18:44
 
Ответить
СообщениеДобавил трим и цену. А трим нужен, т.к. коды теперь могут быть и числом.

Автор - Hugo
Дата добавления - 11.06.2020 в 18:43
Hugo Дата: Четверг, 11.06.2020, 18:44 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3137
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
не понял куда в коде вписать что бы брал значением по Коду
- а я не понял что спросили :)


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
Сообщение
не понял куда в коде вписать что бы брал значением по Коду
- а я не понял что спросили :)

Автор - Hugo
Дата добавления - 11.06.2020 в 18:44
ssm Дата: Пятница, 12.06.2020, 21:21 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Hugo, прошу прощения что не сразу ответил, тренировался на огромной выборке:), есть вопрос, а возможно ли данный макрос переделать что-бы на два листа раскидывал коллекцию, на первый лист суммы, а на второй количества?
 
Ответить
СообщениеHugo, прошу прощения что не сразу ответил, тренировался на огромной выборке:), есть вопрос, а возможно ли данный макрос переделать что-бы на два листа раскидывал коллекцию, на первый лист суммы, а на второй количества?

Автор - ssm
Дата добавления - 12.06.2020 в 21:21
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Сводный отчет с использованием метода collection (Макросы/Sub)
  • Страница 1 из 1
  • 1
Поиск:

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