Доброго времени суток, прошу оказать посильную помощь в формировании данных на листе "отчет" с помощью коллекций, сут вопроса на листе данные есть некий список, в реале он занимает порядка 300 000 строк, необходимо на лист "отчет в столбец Код вытащить уникальные и к ним суммировать количество по годам и суммы, рассчитать цены. Пробовал делать формулами очень долго такое количество считает, пробовал "допилить" сторонний макрос но "ума" хватает только-лишь что-бы заполнить либо количество, либо суммы. Заранее спасибо.
Доброго времени суток, прошу оказать посильную помощь в формировании данных на листе "отчет" с помощью коллекций, сут вопроса на листе данные есть некий список, в реале он занимает порядка 300 000 строк, необходимо на лист "отчет в столбец Код вытащить уникальные и к ним суммировать количество по годам и суммы, рассчитать цены. Пробовал делать формулами очень долго такое количество считает, пробовал "допилить" сторонний макрос но "ума" хватает только-лишь что-бы заполнить либо количество, либо суммы. Заранее спасибо.ssm
Доброго. Тут на словаре нужно делать - сперва всё собрать в словаре по ключам код|год|сумма, код|год|количество (с ценой по примеру непонятно. но вероятно нужно просто делить итоговую сумму на итоговое количество уже в финале при выгрузке в сводную). Затем циклом по сводной разложить собранное из словаря. Если тот сторонний макрос на словаре - то нет проблем добавить либо количество, либо суммы
Доброго. Тут на словаре нужно делать - сперва всё собрать в словаре по ключам код|год|сумма, код|год|количество (с ценой по примеру непонятно. но вероятно нужно просто делить итоговую сумму на итоговое количество уже в финале при выгрузке в сводную). Затем циклом по сводной разложить собранное из словаря. Если тот сторонний макрос на словаре - то нет проблем добавить либо количество, либо суммы Hugo
К сожалению сводная не подойдет, пробовал через массивы также занимает очень много времени, а вот коллекции быстро, но не получается одновременно сумму и количество сделать:(
К сожалению сводная не подойдет, пробовал через массивы также занимает очень много времени, а вот коллекции быстро, но не получается одновременно сумму и количество сделать:(ssm
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
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
Прошу прощения, т.е. не получиться переделать что-бы подхватывал в коллекцию числа, а можно как-нибудь что бы на листе были числа а в коллекцию записывались в тексте?
Прошу прощения, т.е. не получиться переделать что-бы подхватывал в коллекцию числа, а можно как-нибудь что бы на листе были числа а в коллекцию записывались в тексте?ssm
В коллекции как ключ должен быть текст, хотя коллекция тут не особо и нужна, вполне можно её выкинуть, ну да ладно. Чем вызвано волнение по поводу числа/текст? А добавить количество без проблем - просто ключ делайте не a(i, 1) & a(i, 4) а a(i, 1) & a(i, 4) & названиепараметра , ну и ещё разделитель этих "слагаемых" будет не лишним, а вернее необходимым. И при извлечении это названиепараметра нужно или учесть в коде, или брать с листа, но там эти объединения (что зло!) лично у меня отбивают охоту возиться...
В коллекции как ключ должен быть текст, хотя коллекция тут не особо и нужна, вполне можно её выкинуть, ну да ладно. Чем вызвано волнение по поводу числа/текст? А добавить количество без проблем - просто ключ делайте не a(i, 1) & a(i, 4) а a(i, 1) & a(i, 4) & названиепараметра , ну и ещё разделитель этих "слагаемых" будет не лишним, а вернее необходимым. И при извлечении это названиепараметра нужно или учесть в коде, или брать с листа, но там эти объединения (что зло!) лично у меня отбивают охоту возиться...Hugo
Суетнулся под "тесаком" из-за кода, подумал его отдельно надо показать:), а вот за совет спасибо, а можно как-то это в коде показать. Я честно только изучаю вопрос работы коллекций:(, пожалуйста, для меня каждый пример на вес золота
Суетнулся под "тесаком" из-за кода, подумал его отдельно надо показать:), а вот за совет спасибо, а можно как-то это в коде показать. Я честно только изучаю вопрос работы коллекций:(, пожалуйста, для меня каждый пример на вес золотаssm
А из-за числа/текст, просто огромный прайс и поле Код там в числовом формате. И у меня проблемы с выгрузкой на лист что получилось, в общем полный "алес":)
А из-за числа/текст, просто огромный прайс и поле Код там в числовом формате. И у меня проблемы с выгрузкой на лист что получилось, в общем полный "алес":)ssm
Так число в текст в коде легко преобразовать - просто берёте значение и Trim() его... на всякий, вдруг там текст с пробелами сидит... Вот добавил в существующий код второй параметр, с ценой не вникал, но можно тоже вывести среднее. Но объединение убрал, ибо лениво бороться в коде.
Так число в текст в коде легко преобразовать - просто берёте значение и Trim() его... на всякий, вдруг там текст с пробелами сидит... Вот добавил в существующий код второй параметр, с ценой не вникал, но можно тоже вывести среднее. Но объединение убрал, ибо лениво бороться в коде.Hugo
С ценой и бох с ним, работает на малой выборке что надо, можно вопросик, не понял куда в коде вписать что бы брал значением по Коду, а трим не нужен точно не будет пробелов, спасибо.
С ценой и бох с ним, работает на малой выборке что надо, можно вопросик, не понял куда в коде вписать что бы брал значением по Коду, а трим не нужен точно не будет пробелов, спасибо.ssm
Hugo, прошу прощения что не сразу ответил, тренировался на огромной выборке:), есть вопрос, а возможно ли данный макрос переделать что-бы на два листа раскидывал коллекцию, на первый лист суммы, а на второй количества?
Hugo, прошу прощения что не сразу ответил, тренировался на огромной выборке:), есть вопрос, а возможно ли данный макрос переделать что-бы на два листа раскидывал коллекцию, на первый лист суммы, а на второй количества?ssm
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
