Оптимизация макроса

[Intension]

Добрый день господа!
Столкнулся с такой проблемой что по работе появилась необходимость воссоздавать "партионный" учет по реализациям / платежам. Иными словами есть потребность по расчету ФАКТИЧЕСКОГО дня оплаты каждой отгрузки.
Я написал макрос который эмитирует как раз тот самый процесс - находит в списке первую по очереди платежку (или возврат, корректировку, встречную поставку - иными словами сумму которая уменьшая общую сумму долга клиента), а как следующий шаг находит первую по очереди сумму реализации и уменьшает ее на сумму платежа. Если сумму платежки достаточно что бы полностью обнулить реализация, то напротив реализации проставляется дата когда была произведена оплата (дата платежки). Если суммы платежа не достаточно что бы полностью обнулить реализацию, то создается "клон", т.е. релиз дробится на 2 штуки, первый из которых равен сумму платежки, а второй (остаточный) равен остатку от реализации.
Над этим макросам я помучился, но в итоге написал, и он даже работает
Но в работе этого макроса есть два существенных недостатка:
1. не знаю по какой причине он он не всегда полностью прорабатывает список с первого раза, т.е. он останавливается в какой-то момент и пишет что он готов, а по факту нет - приходится запускать повторно. Это не самая критичная проблема. Просто не получается оставлять файл на просчет на ночь.
2. А вот это уже проблема серьезная. Файл который приходится просчитывать это набор данных из 500к+ строк. И просчет этого файла занимает ОЧЕНЬ много времени. По примерным замерам на каждые 10к строк уходит ~~ 10 - 20 минут, все зависит от разношерстности данных.

В приложенном файле небольшой пример как раз такого файла (простите на сумбурность "названий" внутри - все подменил). Макрос состоит из двух штук:
1. ПроработкаИдентичности - находит и определяет те записи которые стоит считать как "единые" - один договор, одна организация и тд)
2. РаспределениеПлатежей - это и сам макрос который уже и обрабатывает все данные

Был бы очень признателен за идеи по ускорению работы данной процедуры.

Всем заранее большое спасибо!

https://www.box.com/s/ty47uoxij5m6dcjoy4nu

p.s. прошу прощения, файл пришлось залить на BoxSync, а то он слишком большой, не дался залиться на форум.

[Skif-F]

Попробуйте. Пока чисто механически, в Ваших действиях не разобрался, но длительность Kolvo * iRow * iRow повторений цикла, при iRow=500000 требует пересмотра алгоритма

Теперь по вопросу о неполном выполнении макроса. Макрос отрабатывает полностью, но у Вас происходит добавление строк в ходе работы макроса, а количество строк в верхней границе циклов не меняется (переменная iRow в прмере равна 10793, а число строк после окончания - почти 16000).
Здесь рекомендую уйти от цикла For...Next в сторону Do...Loop, поскольку For...Next после инициализации не пересчитывает верхнюю границу, и увеличивать iRow после каждого добавления строки на лист.

По алгоритму:
- могу порекомендовать разделить платёжки и договора в разные таблицы, тогда можно прийти к ситуации Kolvo * iRow1 * iRow2, где iRow1 + iRow2 = iRow, т.е. почти 4-хкратное снижение времени выполнения;
- после этого можно платёжки брать в массив:
Dim Ar As Variant
Ar = Range(Cells(..., ...), Cells(..., ...)).Value
поскольку работа с массивом проходит быстрее, чем с ячейками, правда будет высокий расход памяти (примерно 1МБ на 1000 строк Вашего примера);
- ну и продумать как не просматривать повторно обработанные ячейки...

[Intension]

Сообщение от Skif-F

Попробуйте. Пока чисто механически, в Ваших действиях не разобрался, но длительность Kolvo * iRow * iRow повторений цикла, при iRow=500000 требует пересмотра алгоритма

Теперь по вопросу о неполном выполнении макроса. Макрос отрабатывает полностью, но у Вас происходит добавление строк в ходе работы макроса, а количество строк в верхней границе циклов не меняется (переменная iRow в прмере равна 10793, а число строк после окончания - почти 16000).
Здесь рекомендую уйти от цикла For...Next в сторону Do...Loop, поскольку For...Next после инициализации не пересчитывает верхнюю границу, и увеличивать iRow после каждого добавления строки на лист.

По алгоритму:
- могу порекомендовать разделить платёжки и договора в разные таблицы, тогда можно прийти к ситуации Kolvo * iRow1 * iRow2, где iRow1 + iRow2 = iRow, т.е. почти 4-хкратное снижение времени выполнения;
- после этого можно платёжки брать в массив:
Dim Ar As Variant
Ar = Range(Cells(..., ...), Cells(..., ...)).Value
поскольку работа с массивом проходит быстрее, чем с ячейками, правда будет высокий расход памяти (примерно 1МБ на 1000 строк Вашего примера);
- ну и продумать как не просматривать повторно обработанные ячейки...

Skif-F,
спасибо что уделили время!
Не знал такой особенности с For Next ... значит перепишу под Do Loop, а в нем происходит пересчет верхней границы?
Спасибо за идею изменения алгоритма, вот только с массивами никогда не работал, но как говориться "есть к чему стремится"
Если появятся еще вопросы - обращусь!

[Hugo121]

По первому макросу - кроме применения массивов (не только когда анализируются данные, но и когда выводится результат!) есть ещё смысл использовать словарь.
Тогда не нужна сортировка данных, да и работать всё будет шустро.
Алгоритм такой - цикл по массиву, собираем ключ из полей строки, смотрим нет ли уже такого в словаре.
Если есть - берём из item'а словаря номер, пишем в выходной массив.
Если нет - увеличиваем последний номер на 1, пишем в словарь ключ и номер, в выходной массив номер.
В конце выгружаем выходной массив на лист (в этой задаче в первый столбец).
Но не вникал полностью в задачу (т.к. уже делается) - может там ещё какие мелочи анализируются...

[Skif-F]

Сообщение от Intension

Не знал такой особенности с For Next ... значит перепишу под Do Loop, а в нем происходит пересчет верхней границы?

Пересчёт верхней границы будете делать Вы, а в заголовке цикла будет условие:

b=2    'Начальное значение счётчика
Do While b<=iRow  'Цикл выполняется пока b не превысит iRow
    if надо вставить строку then
        Insert_a_line    'Вставили строку
        iRow=iRow+1    'Здесь происходит учёт изменения кол-ва строк
    end if
    b=b+1    'Увеличиваем счётчик
Loop

[Intension]

Сообщение от Skif-F

- ну и продумать как не просматривать повторно обработанные ячейки...

Вот за этот комментарий особое спасибо ... тут я, конечно, очень сильно ступил!
Переписал таким образом что бы ячейки не просматривались повторно, и время проработки на тестовых данных сократилось с 8 минут до 2 ...

Что касается массивов, я никогда их ранее не использовал (тут пожалуй, стоит уточнить что VBA я знаю достаточно посредственно, без f1 мне никуда), почитаю материал как правильно обрабатывать данные в массиве и тогда попробую переписать.

[Intension]

Сообщение от Skif-F

Пересчёт верхней границы будете делать Вы, а в заголовке цикла будет условие:

b=2    'Начальное значение счётчика
Do While b<=iRow  'Цикл выполняется пока b не превысит iRow
    if надо вставить строку then
        Insert_a_line    'Вставили строку
        iRow=iRow+1    'Здесь происходит учёт изменения кол-ва строк
    end if
    b=b+1    'Увеличиваем счётчик
Loop

Skif-F, это я понял, точно таким же образом я обрабатывал и в текущем варианте. Только я не знал нюанса что конструкция For Next не пересматривает предел границы после запуска, даже не смотря на изменение переменной.

[Intension]

Сообщение от Hugo121

По первому макросу - кроме применения массивов (не только когда анализируются данные, но и когда выводится результат!) есть ещё смысл использовать словарь.
Тогда не нужна сортировка данных, да и работать всё будет шустро.
Алгоритм такой - цикл по массиву, собираем ключ из полей строки, смотрим нет ли уже такого в словаре.
Если есть - берём из item'а словаря номер, пишем в выходной массив.
Если нет - увеличиваем последний номер на 1, пишем в словарь ключ и номер, в выходной массив номер.
В конце выгружаем выходной массив на лист (в этой задаче в первый столбец).
Но не вникал полностью в задачу (т.к. уже делается) - может там ещё какие мелочи анализируются...

Hugo121, спасибо большое что уделили время и предложи вариант, но боюсь что для моих познаний в VBA это сложновато... не смогу такое реализовать

[Hugo121]

Когда-то только заменой перебора ячеек на перебор массивов (словарь тогда ещё не применял) удалось человеку ускорить с 40 минут до 5-ти секунд! Со словарём было бы ещё быстрее!
Вот тут:
http://www.sql.ru/forum/actualthread...id=774191&pg=3