Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Максимальная производительность при поиске и выделении (Select) большого массива несвязанных ячеек по условию - VBA
 
#1
04.03.2016 15:33:51
Всем привет! Стояла задача минимизировать время выполнения кода. Делюсь решением, которое получилось на основе алгоритма от ZVI, которые я использовал. По моим скромным знаниям и тестированию другие пути оказались не столь эффективны. В итоге в массиве из 250 тыс. ячеек и выделение 62 тыс. ячеек расположенных в случайном порядке занимает порядка 60-70 секунд.
Решения:
1. Объединение через Range() до 254 знаков
2. Запись адресов без $ - (A1)
3. Минимальное использование Union, для этого создается буферный массив для заполнения Range(), в размере 29 блоков (+текущий =максимально для Union), увеличение быстродействия до 2х раз.
4. Не создавать каждый раз новую строку, а использовать одну (предыдущую строку) как символьный массив, изменения в котором можно выполнять копированием фрагмента памяти с помощью Mid$(StrBuf, Ptr) = StrNew. Mid$() вместо Mid(), потому как Mid$() быстрее.
5. Поиск не по ячейкам, а по массиву (загруженному из range), для этих же целей Set a2.
6. Отключение экрана, событий и автопересчета
7. Открытый для меня новый факт: при переборе столбцы-строки, быстрее чем строки-столбцы, если структура данных вертикальная - от 20 до 70%

Где можно ещё полернуть? Возможно есть другие оригинальные решения?

Ещё раз повторюсь, основная идея от ZVI, за что ему огромное человеческое спасибо!
Изменено: bedvit - 04.03.2016 15:51:53
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#2
04.03.2016 15:36:55
Цитата
bedvit написал:
задача максимизировать время выполнения
- не путаете? Обычно все наоборот :D
Я сам - дурнее всякого примера! ...
 
 
 
#3
04.03.2016 15:40:38
KuklP, замечание принято, сообщение исправлено)
Дополню: комп хороший win+office x64, на слабых машинах время будет больше.
Изменено: bedvit - 04.03.2016 15:42:11
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#4
04.03.2016 15:51:49
Код
a = Range(Cells(1, 1), Cells(xEnd, yEnd))

следует учитывать, что не у всех 64-битная архитектура. На больших массивах эта строка вызовет ошибку переполнения памяти. И зависеть это будет напрямую от кол-ва доступной памяти, что в 32-битных системах критично - там не разгуляешься :)
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#5
04.03.2016 16:02:29
The_Prist, весь свой отдел перевел на х64, х32 уже не серьезно)) Но замечание дельное! хорошо бы понять максимальный массив для х32 (надо гуглить).
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#6
04.03.2016 16:52:31
Цитата
bedvit написал: хорошо бы понять максимальный массив
Цитата
The_Prist написал: И зависеть это будет напрямую от кол-ва доступной памяти
Поэтому все напрямую зависит от того, какие данные на листе. Если в каждой ячейке длинный текст - то это самое плохое, пожалуй. И таких ячеек потребуется куда меньше для переполнения, чем числовых данных.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#7
04.03.2016 17:02:14
The_Prist, логично. Выходит "хочешь обрабатывать большие массивы данных, ставь офис поновее и поразряднее"... что, вообщем-то логично. Ну или тогда перебирать по ячейкам, что в разы (а может и больше) медленнее...
Изменено: bedvit - 04.03.2016 17:04:07
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#8
04.03.2016 17:07:05
Цитата
bedvit написал: или тогда перебирать по ячейкам
Ну зачем же так...Можно же порционно в массивы загонять. Хоть и медленнее, чем все сразу - но быстрее, чем цикл по ячейкам.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#9
04.03.2016 17:11:58
The_Prist, согласен, можно и так)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#10
04.03.2016 18:18:07
bedvit, в чем смысл выделения как можно большего числа несмежных ячеек - спортивный интерес? ;)
Скопировать такой диапазон невозможно. Пользователю показать - вряд ли человек глазом найдет что-то нужное в этом узоре.
Если нужно очистить или закрасить эти ячейки - лучше делать это частями, скажем, по столбцам или по областям не более 10000 ячеек.
Я запустил код - опять получил ошибку. Вот содержимое Immediate:
Код
 2,75 
 1,328125
-- после останова по ошибке -- 
?x,y,selection.areas.count,selection.count
 4245          20            31610         47223 
?err.Description 
Method 'Union' of object '_Global' failed
Формирование строки можно еще ускорить, если копировать куски памяти с помощью RtlMoveMemory - поищите, примеров масса.
Получение адреса как addr = a2(x, y).Address(...) - неоптимально, метод Address довольно медленный. Лучше создать массив имен столбцов и получать адрес как
addr = arrColNames(y) & x
Кстати, чтобы не приклеивать каждый раз запятую, можно строки изначально заполнить запятыми: s(j) = String$(256, ",") вместо s(j) = Space(256)
Код, конечно, придется кое-где переделать.
 
 
 
#11
04.03.2016 18:19:32
по п.7 думаю это связано с работой Union, позволю себе процитировать (т.к. сам по этой тематике глубокими знаниями не обладаю):
Цитата
Казанский написал:
Почему Union работает медленно при большом количестве областей - он пытается соединить смежные диапазоны в одну область. НапримерКод?12?union(range("A1:A3"),range("C1:C5"),range("A4:A7")).Address $C$1:$C$5,$A$1:$A$7А если объединяются несколько диапазонов, каждый из которых состоит из многих областей, Union видимо пытается свести каждую область с каждой, и это очень долго.
из чего можно предположить, что в вертикальном массиве (мало столбцов, много строк) прои вертикальном "проходе" Union меньше сводит соседних областей, чем если "проходить" горизонтально. Мое предположение.
Изменено: bedvit - 04.03.2016 18:23:59
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#12
04.03.2016 18:33:24
Казанский, сейчас из спортивного интереса, но была и задача удаления лишних строк. Для удаления лишних ячеек, родилась эта. Массивы большие, решил отработать алгоритм. У вас наверное office х32? Попробуйте уменьшить массив (если интересен результат). За дополнения, Спасибо! Буду вносить корректировки.
Что бы зачистить массив (без удаления ячеек), есть очень быстрые алгоритмы (без select) , к примеру вот на нашем форуме. Кстати, ваш - мною отмечен как самый оригинальный.
Изменено: bedvit - 04.03.2016 18:40:18
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#13
11.03.2016 14:52:18
Итак, получил семикратный (и выше) прирост производительности на больших массивах на новом алгоритме. Алгоритм собственный. Итоги: обработка 500тыс. ячеек с выделением 125тыс.-34сек, 1млн. с выделением 250тыс.- 125 сек. (win7+office2010=x64). Отказался от Union. Изначально была идея пойти через именованные диапазоны и хотя в RefersTo:= влезало от 700 до 1280 знаков (почему так, вопрос), в отличии от range (где 255), идея не прошла. Пошел через SpecialCells. Приятным бонусом стало возможность обрабатывать тип данных (формулы, текст и т.д.), поменяв одну цифру (две) в коде.
Замечена одна особенность, в office2016х64 код выполняется в два раза дольше при прочих равных, неясно почему, кривые ручки майкросовт? Делюсь)
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#14
18.03.2016 16:58:58
Итак по SpecialCells: по собственным тестам ...Validation и ...FormatConditions работают быстрее чем другие
XlCellType constants. SpecialCells хорош, но не работает с защищенном листом, и убирает форматирование в примечаниях (выложенный алгоритм выше). Поэтому решил сегодня полернуть еще код. Вообщем то вышел вариант который на данный момент самый быстрый. без SpecialCells :) ничего не трогает, защита неважна. Обработка 250 тыс. ячеек с выделением 60 тыс.-5сек., т.е. в 2 раза быстрее чем выделение по SpecialCells. Кому интересно? :-)
Изменено: bedvit - 18.03.2016 17:03:21
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#15
18.03.2016 19:12:51
Ну, раз от желающих нет отбоя, допишу свой монолог.
Сделано к вышеописанному:
1. Отказался от сложного резервирования памяти (в данном случае слабо сказалось на производительности)
2. Ввел вместо Address (+ в карму за совет от Казанский) массив имен, прирост производительности закрыл с лихвой п.1
3. Максимально использовал функцию Union (все таки вернулся к ней)

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

P.S.Добавил проверку на размер массива и объект на IsEmpty.
Изменено: bedvit - 28.03.2016 11:30:30
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
#16
18.03.2016 21:47:56
К примеру, этот код выполняется 5 сек., из них Union 4,9 сек., остальное 0,1 сек.(Win7x64, Excel2016,2010x64). Итого 98% зависит от union. А чем больше Union сжирает за раз, тем быстрее выполняется код. На этой идее и строится данный алгоритм. Пробовал разными способами скармливать, остановился на этом.
«Бритва Оккама» или «Принцип Калашникова»?
 
 
 
Страницы: 1
Наверх