Сразу хочу извиниться за "стену текста", но описать проблему в два предложения не смог. Хочется не только решить конкретную задачу, но и понять принцип работы экселя и взаимосвязь "дополнительное условие в проверке = дополнительное время на обработку данных"
У меня есть два файла (прикрепил их к сообщению). Из файла Свод в файл Итог макросом переносятся данные. Макрос найден на просторах интернета и немного переработан под мою задачу, есть вероятность, что он не совсем правильно работает. Проблема заключается в том, что для получения результата необходимо ждать очень много времени.
Как работает макрос и что нужно получить:
Скрытый текст
На примере ячейки ЕА2 (книга Итог):
1) если значение ячейки i2 = В, то 2) если значение ячейки EA1 (книга Итог) равно значению ячейки в столбце BE (книга Свод) и значение ячейки J2 переменная m (книга Итог) равно значению ячейки в столбце АТ (книга Свод) и значение ячейки L2 переменная gg (книга Итог) равно значению ячейки в столбец С (книга Свод), то 3) добавляем в коллекцию значение ячейки в столбце СС (книга Свод) 4) цикл по всем ячейкам 5) количество значений в коллекции по данной ячейке умножаем на значение ячейки G2 переменная mmm2 6) выгружаем результат в ячейку EA2
Надеюсь, что понятно объяснил, что требуется.
Ранее не сталкивался с формированием таких больших отчетов, не уверен, что правильно понимаю принцип работы экселя/макросов. В примере 17 строк по 153 ячейки в каждой, т.е. 2601 ячейка. Если я задаю проверку через строку (ниже), макрос завершает свою работу через 20 секунд
Код
If arr2(1, i) = arr(n, 57) And arr(n, 46) = m And arr(n, 3) = gg Then
Если я задаю проверку через строку (ниже), макрос завершает свою работу через 40 секунд
Код
If arr2(1, i) = arr(n, 57) And arr2(1, i) >= c1 And arr2(1, i) <= c2 And arr(n, 46) = m And arr(n, 3) = gg Then
Т.е. из-за дополнительной проверки двух переменных макрос начинает работать в 2 раза дольше
Код
arr2(1, i) >= c1 And arr2(1, i) <= c2
Рабочий файл должен содержать более 60 тыс обрабатываемых ячеек (30 листов с результатами). Грубый математический расчет дает понять, что 60 тыс ячеек будут обрабатываться: 1 вариант - 7,5 минут, 2 вариант - 15 минут Первый вариант близок к нормальному времени, но не выполняет свою задачу на 100%. Второй вариант занимает слишком много времени.
Вопросы: 1) Мой макрос написан плохо или это нормально, что на обработку 2600 ячеек уходит 20 секунд? 2) Как можно улучшить текущий макрос (или как должен выглядеть новый) для более быстрой обработки результатов?
нормальное время - это когда 60 тыс. ячеек обрабатываются за 0.01 сек нужно не описание макроса, а описание вашей задачи на примере ЕА2 - это отлично, но какая ячейка следующая и что с чем сравнивать
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Ігор Гончаренко написал: нормальное время - это когда 60 тыс. ячеек обрабатываются за 0.01 сек
Получается, что мой макрос действительно проблемный..
Цитата
Ігор Гончаренко написал: нужно не описание макроса, а описание вашей задачи
Согласен, не описал задачу полностью.
В книге Итог нужно занести результаты во все ячейки диапазона, начиная от ячейки EA2 до последней ячейки* Последняя ячейка* зависит от количества строк (проверка идет по столбцу i) и количества столбцов (проверка по строке 1) (В моем примере диапазон EA2:JW18, т.к. в столбце i последняя заполненная строка 18, а в строке 1 столбец JW)
1) Если цифра в текущей строке столбца А меньше, чем 2000000 переходим к пункту 2 (если цифра больше, то пропускаем строку) 2) Определяется значение ячейки текущей строки в столбце i (с ячейки i2 по последнюю заполненную). Значение может быть "П" или "В", в зависимости от результата выбираем какие данные сравнивать:
Если значение = "П": 1) Дату из строки 1 текущего столбца (книга Итог) сравниваем с датами из столбца АН (книга Свод) 2) Определяем, что дата из строки 1 текущего столбца (книга Итог) находится в диапазоне между датами (или равна им) из столбцов E и F текущей строки (книга Итог) 3) Цифру из ячейки J2 (книга Итог) сравниваем с цифрами из столбца АТ (книга Свод) 4) Цифру из ячейки L2 (книга Итог) сравниваем с цифрами из столбца C (книга Свод) 5) Суммируем значения всех ячеек из столбца СС (книга Свод), которые попадают под условия (пункт 1, 3, 4) - (в примере, в столбце СС, везде стоят 1, но фактически там может быть пусто или стоять 0, т.ч. просто сложить количество подходящих дат из столбца АН нельзя, будет считать с ошибкой) 6) Умножаем цифру из текущей строки столбца G (книга Итог) на сумму значений из пункта 5 7) Записываем результат в текущую ячейку (книга Итог) 8 ) Переходим к следующей ячейке
Если значение = "В": 1) Дату из строки 1 текущего столбца (книга Итог) сравниваем с датами из столбца ВЕ (книга Свод) 2) не меняется 3) не меняется 4) не меняется 5) не меняется 6) не меняется 7) не меняется 8 ) не меняется
На правах АПа все еще не теряю надежды на то, что мой макрос можно ускорить. Все-таки не могу понять, почему так долго идет расчет, проверка идет с использованием массивов, т.е. должно быть нормально.. Есть у кого-то идеи, в чем ошибаюсь?
Fylhtqq написал: Грубый математический расчет дает понять, что 60 тыс ячеек будут обрабатываться:
Из правил форма:
Цитата
2.6. Один вопрос - одна тема. Не следует в открываемой теме обозначать и задавать сразу несколько вопросов.
Замерить время выполнения каждого "вопроса", найти проблемный и с ним выйти на форум.
Навскидку, коротко: r, r1, r2, r3 As Range - тип Range только у r3, остальные - Variant Похоже, не все переменные объявлены. Option Explicit в начале модуля - не мода, а необходимость. Да и много их, переменных. Расчет UBound(arr2, 2) - LBound(arr2) + 1 на каждом шаге цикла. And mmm3 < 2000000 вынести выше If, в отдельное условие c.Offset(, -4) - все равно же с диапазоном работаете.
Option Explicit добавил, лишние переменные удалил/присвоил нужные типы данных. Перенес And mmm3 < 2000000 выше If, в отдельное условие. Заменил mmm3 с проверкой через c.Offset(, -4) на проверку через данные из массива
Цитата
vikttur написал: Замерить время выполнения каждого "вопроса", найти проблемный и с ним выйти на форум.
Понял!, тогда, исходя из ваших замечаний, хотел бы разобраться вот с этим:
Цитата
vikttur написал: Расчет UBound(arr2, 2) - LBound(arr2) + 1 на каждом шаге цикла.
Что это значит и как переработать в правильный вид? Данная часть была взята из чужого макроса, проверена на работоспособность и использована тут. Но, почему это написано именно так и, что "оно" делает, не очень понятно.
Оно пишет о то, что от горизонтальной размерности массива отнимаем "начало" вертикальной размерности. Получаем какое-то значение +1. Это можно посчитать один раз и присвоить переменной найденное значение.
Возможно, для Вашего случая это не имеет смысла. Но размерность массива может начинаться с нуля. Непонятно, почему для расчета последнего значения цикла берутся две размерности. Косяк какой-то...
vikttur написал: Возможно, для Вашего случая это не имеет смысла. Но размерность массива может начинаться с нуля.
Действительно, где-то я уже такое слышал. У меня везде как-то само от 1 считает. Спасибо!
Цитата
vikttur написал: Непонятно, почему для расчета последнего значения цикла берутся две размерности. Косяк какой-то...
Не уверен, что технически это правильно написано, но оно работает. Просто начало (1) берется из первой размерности (она от 1 до 1, т.е. получается 1), а конец от второй (от 1 до 153, т.е. 153) Опять же, в чем тут хитрость трудно понять.
Код
+ : arr2 : : Variant/Variant(1 to 1, 1 to 153)
Спасибо за помощь и ответы! Попробую улучшить остальные строки.
Например, массив 20 (по вертикали)*50(по горизонтали) For i = 1 To 50 Если в Вашем цикле проходить по массиву сверху вниз (от 1 до 50), будет ошибка - "строки" 21 и ниже в массиве нет. Если массив 50*20, то наоборот - цикл прокрутится только 20 шагов.
Видимо, в моем примере можно немного "сжульничать", т.к. массив arr2 формируется из данных в строке (от 1 до последней заполненной ячейки). т.е. i = 1 to
Цитата
Юрий М написал: А что Вы хотите сделать с таким массивом? Задача в чём?
Добрый вечер! Первоначальная задача стояла в оптимизации (ускорении работы макроса). Но мне подсказали, что нужно задавать более четкий вопрос, а не спрашивать про все подряд. Т.ч. вопрос свелся к разбору формирования цикла, исходя из размерности массива, и плавно перетек в разбор того, что вообще написано в коде (и почему написано именно так). На мой взгляд, в конкретном примере массив arr2 работает нормально. Да и в целом, считаю задачу решенной. Найдено множество недочетов, которые стоит устранить)