При обработке больших массивов данных зачастую ключевым является вопрос оптимизации кода таким образом, чтобы время его выполнения было минимальным.
Один из подходов решения позволяющий улучшить производительность кода рассмотрим сегодня в обзоре.
📢 Файл с примером размещен в конце статьи 🔽
▶️ Цели и задачи
В качестве простого примера демонстрирующего улучшение производительности кода рассмотрим задачу нумерации строк.
Цель: максимально эффективно выстроить соответствующую нумерацию в столбце А при условии наличия записи (по столбцу В) исключив наличие формул на листе.
▶️ Тривиальные подходы решения
Безусловно наиболее простым решением поставленной задачи является использование подхода: =СТРОКА()-СТРОКА($A$1)
Макрос в этом случае может иметь вид:
Но при данном подходе на листе будет добавлена формула, что в рамках решения задачи не является допустимым:
Принимая во внимание, что формулы на листе (даже сгенерированные макросом) не допустимы можно воспользоваться следующим кодом VBA для нумерации столбца:
В этом коде номер строки в столбце А рассчитывается как разница между номером текущей строки и номером базовой строки (в данном случае, А1).
Но является ли такой подход оптимальным решением? На наш взгляд нет!
▶️ Оптимизация производительности
Для улучшения производительности мы можем использовать массивы.
Массивы представляют собой структуры данных, позволяющие хранить множество значений в одной переменной. Они могут использоваться для обработки больших объемов данных более эффективно, чем обычные циклы, работающие с ячейками листа по одной.
Когда данные загружаются из листа в массив, происходит одна операция ввода/вывода, что быстрее, чем многократные операции, когда вы работаете непосредственно с ячейками листа.
После обработки данных в массиве результаты могут быть записаны обратно на лист.
Например:
Этот код использует массивы для ускорения операций и затем записывает результат обратно на лист.
▶️ Альтернативное решение
Рассмотренный выше макрос безусловно является примером наилучшей практики если данные должны обновляться по запросу пользователя.
Но что, если нужно реагировать на изменения данных в реальном времени?
Использование массивов в модуле листа (в обработчике события Worksheet_Change) может быть хорошим решением, если обработки данных связаны с изменениями на листе.
Например:
Этот код будет выполняться при каждом изменении данных в столбце B на листе.
⚙️ Краткое описание:
- Проверяем, что изменения произошли именно в столбце В,
- Выполняем операции над массивом и записываем результаты в столбец A.
- Обязательно отключаем и включаем событие Change, чтобы избежать бесконечной рекурсии.
📝 Примечание автора: для обработки удаления значений в столбце B и очистки соответствующих ячеек в столбце A мы также учли, что, при удалении значения (в столбце B), соответствующая ячейка (в столбце A) будет очищаться.
✔️ В ряде основных преимуществ такого подхода отметим следующие:
- Автоматически срабатывает при определенных событиях, таких как изменение ячеек.
- Подходит для непрерывного мониторинга и обработки изменений данных.
⚠️ Однако, есть несколько важных моментов, которые следует учесть:
- Событие Worksheet_Change срабатывает при любом изменении на листе. Если вы используете массивы в обработчике события Worksheet_Change, он будет срабатывать каждый раз при изменении любой ячейки на листе. Это может повлиять на производительность, особенно если изменения касаются большого количества ячеек.
- Внимательно следите за целями обработки. Если массив охватывает всю область листа, каждый раз при изменении данных сработает событие. Это может привести к нежелательному повторному вычислению.
- Обработка больших объемов данных. Использование массивов в модуле листа будет эффективным, особенно если вы выполняете сложные операции над большими объемами данных.
Если изменения касаются только конкретных ячеек или определенной области, может иметь смысл использовать обработчик события Worksheet_Change в связке с массивами.
🔔 Помните! Важно следить за производительностью и учитывать ограничения этого метода.
🔶 Подводя итог...
Если ваш код предполагает обработку больших объемов данных рекомендуем сначала загрузить данные в массив, выполнить операции над массивом и затем записать обновленные значения обратно.
Этот подход особенно полезен, когда нужно многократно обрабатывать или изменять данные на листе, так как он снижает количество обращений к листу Excel, что улучшает производительность.
📝 Узнать больше о массивах
