В теме Подсчёт совпадений... была поднята тема "что лучше при работе в VBA - использовать встроенные функции Excel или же взяв данные в массив обрабатывать их в массиве?" Тема была поднята не автором темы, а двумя желающими помочь форумчанами. Суть дискуссии состояла в выборе более быстрого варианта из следующих: [vba]
Код
' загоняем в словарь только нужное, а не все подряд... For Each c In .Columns(2).SpecialCells(xlCellTypeFormulas) Dict.Add c.Value, c.Row Next
[/vba] или [vba]
Код
'Снимаем с листа данные With Worksheets(conИсходныйЛист) Исх = .Range("A1:W" & .Cells(.Rows.Count, 3).End(xlUp).Row).Value End With
'Подготавливаем словарь для ускорения работы For i = 3 To UBound(Исх) If Исх(i, 3) <> "" Then 'Если в третьем столбце не пусто, то запоминаем номер строки СловарьСтрок.Add Исх(i, 3), i End If Next i
[/vba] Чтобы найти истину, были написаны тесты, сведённые в книгу Tests.xls (во вложении). В файле расположена исходная таблица данных на листе «ВОЙНАМИР» и модули в VBA-проекте:
0. Класс StopWatch - применяется для учёта времени, кажется в миллисекундах. В своей работе применяет функцию timeGetTime из "winmm.dll". Я обернул объявление функции макрокомандами, но тестировал только в Office 2007 x86 Windows 7 x64. 1. Module_ProgTest - тестирование скоростей выполнения программ. Тестируются варианты из модулей Заполнение, Заполнение2, Заполнение3, KSV_PPSS. Каждая программа выполняется 10 раз. Кнопка запуска теста вынесена на лист «ВОЙНАМИР»; 2. Module_DictFilling - тестирование скорости заполнения словаря по методfv KSV и Skif-F. Кнопка запуска теста вынесена на лист «ВОЙНАМИР»; 3. Module_RangeTest - на основании этого модуля можно увидеть, как VBA и Excel работают с диапазонами; 4. Module_TestCountA - сравнение скорости работы функции CountA и подсчёта строк в массиве; 5. KSV_PPSS - исходный вариант от KSV; 6. Заполнение - исходный вариант Skif-F; 7. Заполнение2 - доработанный вариант Skif-F с применением CountA; 8. Заполнение3 - доработанный вариант Skif-F с применением дополнительной переменной вместо UBound
В теме Подсчёт совпадений... была поднята тема "что лучше при работе в VBA - использовать встроенные функции Excel или же взяв данные в массив обрабатывать их в массиве?" Тема была поднята не автором темы, а двумя желающими помочь форумчанами. Суть дискуссии состояла в выборе более быстрого варианта из следующих: [vba]
Код
' загоняем в словарь только нужное, а не все подряд... For Each c In .Columns(2).SpecialCells(xlCellTypeFormulas) Dict.Add c.Value, c.Row Next
[/vba] или [vba]
Код
'Снимаем с листа данные With Worksheets(conИсходныйЛист) Исх = .Range("A1:W" & .Cells(.Rows.Count, 3).End(xlUp).Row).Value End With
'Подготавливаем словарь для ускорения работы For i = 3 To UBound(Исх) If Исх(i, 3) <> "" Then 'Если в третьем столбце не пусто, то запоминаем номер строки СловарьСтрок.Add Исх(i, 3), i End If Next i
[/vba] Чтобы найти истину, были написаны тесты, сведённые в книгу Tests.xls (во вложении). В файле расположена исходная таблица данных на листе «ВОЙНАМИР» и модули в VBA-проекте:
0. Класс StopWatch - применяется для учёта времени, кажется в миллисекундах. В своей работе применяет функцию timeGetTime из "winmm.dll". Я обернул объявление функции макрокомандами, но тестировал только в Office 2007 x86 Windows 7 x64. 1. Module_ProgTest - тестирование скоростей выполнения программ. Тестируются варианты из модулей Заполнение, Заполнение2, Заполнение3, KSV_PPSS. Каждая программа выполняется 10 раз. Кнопка запуска теста вынесена на лист «ВОЙНАМИР»; 2. Module_DictFilling - тестирование скорости заполнения словаря по методfv KSV и Skif-F. Кнопка запуска теста вынесена на лист «ВОЙНАМИР»; 3. Module_RangeTest - на основании этого модуля можно увидеть, как VBA и Excel работают с диапазонами; 4. Module_TestCountA - сравнение скорости работы функции CountA и подсчёта строк в массиве; 5. KSV_PPSS - исходный вариант от KSV; 6. Заполнение - исходный вариант Skif-F; 7. Заполнение2 - доработанный вариант Skif-F с применением CountA; 8. Заполнение3 - доработанный вариант Skif-F с применением дополнительной переменной вместо UBound
На основании вышеуказанных тестов можно сделать следующие выводы: 1. Циклы типа[vba]
Код
Dim c As Range For Each c In Range(...) ... Next
[/vba]жутко медленная вещь, поскольку при каждой итерации цикла заново создаётся объект типа Range, к которому обращается тело цикла (это уже многократно обсуждалось на просторах Интернета); 2. Интересная идея по применению SpecialCells() абсолютно нивелируется необходимостью многократных обращений к диапазонам; 3. Использование функции WorksheetFunction.CountA почему-то не повлияло на итоговую производительность программы при всех своих очевидных плюсах; 4. Функция UBound очень медленная. Поэтому в времякритичных частях кода лучше отслеживать размер массива через дополнительную переменную. [p.s.]Код тестировался в Windows 7, Excel 2007 x32. Будет интересно узнать выводы и наблюдения в других системах.
На основании вышеуказанных тестов можно сделать следующие выводы: 1. Циклы типа[vba]
Код
Dim c As Range For Each c In Range(...) ... Next
[/vba]жутко медленная вещь, поскольку при каждой итерации цикла заново создаётся объект типа Range, к которому обращается тело цикла (это уже многократно обсуждалось на просторах Интернета); 2. Интересная идея по применению SpecialCells() абсолютно нивелируется необходимостью многократных обращений к диапазонам; 3. Использование функции WorksheetFunction.CountA почему-то не повлияло на итоговую производительность программы при всех своих очевидных плюсах; 4. Функция UBound очень медленная. Поэтому в времякритичных частях кода лучше отслеживать размер массива через дополнительную переменную. [p.s.]Код тестировался в Windows 7, Excel 2007 x32. Будет интересно узнать выводы и наблюдения в других системах.Skif-F
Она используется в цикле. А когда я заменил её на переменную (модуль Заполнение3), то получил двукратное снижение времени, что сравнимо с полным отказом от неё (модуль Заполнение2)
Она используется в цикле. А когда я заменил её на переменную (модуль Заполнение3), то получил двукратное снижение времени, что сравнимо с полным отказом от неё (модуль Заполнение2)Skif-F
На основании вышеуказанных тестов можно сделать следующие выводы
честное слово - удивляюсь, как это у Вас получилось. в смысле - сделать выводы при таких результатах.
1) объем исходных данных слишком мал для более-менее адекватного теста. не спасает даже "десятикратность", т.к. всё равно мало, зато погрешностей, вызванных не разницей в алгоритмах, а накладными расходами на вызов функций и т.п. - прибавляется. 2) разброс результатов чудовищный. у меня при четырех-пяти запусках получилось от 68 "тиксов" до 141. причем в некоторых запусках вариант с заменой ubound на переменную был чуть быстрее, в некоторых - медленнее. что, имхо, ещё раз подтверждает вывод о невозможности делать серьезные "выводы" при таком тестировании.
все - имхо. не для спора.
win7 32, excel 2010 работает. само собой - одна из кнопок даёт ошибку, ну да бог с ней - вроде бы не нужна.
На основании вышеуказанных тестов можно сделать следующие выводы
честное слово - удивляюсь, как это у Вас получилось. в смысле - сделать выводы при таких результатах.
1) объем исходных данных слишком мал для более-менее адекватного теста. не спасает даже "десятикратность", т.к. всё равно мало, зато погрешностей, вызванных не разницей в алгоритмах, а накладными расходами на вызов функций и т.п. - прибавляется. 2) разброс результатов чудовищный. у меня при четырех-пяти запусках получилось от 68 "тиксов" до 141. причем в некоторых запусках вариант с заменой ubound на переменную был чуть быстрее, в некоторых - медленнее. что, имхо, ещё раз подтверждает вывод о невозможности делать серьезные "выводы" при таком тестировании.
Не наблюдал большого разброса результата, максимум процентов 30. Но при всём этом соотношение разных вариантов держится на похожем уровне. Например для теста программ: 64, 39, 39, 130 "тиков" (типовой результат)
Не наблюдал большого разброса результата, максимум процентов 30. Но при всём этом соотношение разных вариантов держится на похожем уровне. Например для теста программ: 64, 39, 39, 130 "тиков" (типовой результат)
На своих разработках пришел к выводу, что массивы однозначно быстрее диапазонов. Особенно если все сделать по уму без лишних циклов. А массивы в сочетании с словарем получается вообще бомба. Полностью согласен с ikki, для того чтобы по достоинству оценить их преимущества нужно работать с большими диапазонами (больше 500тыс строк). Когда-то я пользовался ВПР... сейчас практически исключил из повседневной работы формулы... все расчеты повседневных отчетов перевел на массивы+ словарь . Самый лучший эффект получается когда сначала считываешь ВСЕ данные сразу в разные массивы, а потом их там обрабатываешь... Преимущество на лицо... теперь за это же время можно, как минимум, выпить чашечку кофе, или написать пару - тройку сообщений на форуме
На своих разработках пришел к выводу, что массивы однозначно быстрее диапазонов. Особенно если все сделать по уму без лишних циклов. А массивы в сочетании с словарем получается вообще бомба. Полностью согласен с ikki, для того чтобы по достоинству оценить их преимущества нужно работать с большими диапазонами (больше 500тыс строк). Когда-то я пользовался ВПР... сейчас практически исключил из повседневной работы формулы... все расчеты повседневных отчетов перевел на массивы+ словарь . Самый лучший эффект получается когда сначала считываешь ВСЕ данные сразу в разные массивы, а потом их там обрабатываешь... Преимущество на лицо... теперь за это же время можно, как минимум, выпить чашечку кофе, или написать пару - тройку сообщений на форуме SLAVICK
Иногда все проще чем кажется с первого взгляда.
Сообщение отредактировал SLAVICK - Среда, 27.05.2015, 10:06
то, что массивы быстрее - это даже обсуждать странно ))
думаю, здесь важнее вопрос практического смысла. если кому-нибудь, в каких-нибудь задачах, гораздо быстрее записать свои действия макрорекодером (получив, само собой, "код с диапазонами"), чуть подправить его - и получить готовый макрос, чем продумывать и реализовывать алгоритм на массивах-словарях - то почему бы и нет? особенно если объемы не огромные и макрос запускается раз в месяц (или даже раз в день - короче, редко)
у такого кода тоже есть свои преимущества - он понятен большему числу людей )) а то, что супер-пупер код с массивами и словарями работает аж в семь раз быстрее, экономя целых сорок три миллисекунды за раз - это, конечно, греет душу. вот только писался такой код не за одну минуту, а за пять. ну а дальше - простая арифметика возврата инвестиций, вложенных во время разработки ))
то, что массивы быстрее - это даже обсуждать странно ))
думаю, здесь важнее вопрос практического смысла. если кому-нибудь, в каких-нибудь задачах, гораздо быстрее записать свои действия макрорекодером (получив, само собой, "код с диапазонами"), чуть подправить его - и получить готовый макрос, чем продумывать и реализовывать алгоритм на массивах-словарях - то почему бы и нет? особенно если объемы не огромные и макрос запускается раз в месяц (или даже раз в день - короче, редко)
у такого кода тоже есть свои преимущества - он понятен большему числу людей )) а то, что супер-пупер код с массивами и словарями работает аж в семь раз быстрее, экономя целых сорок три миллисекунды за раз - это, конечно, греет душу. вот только писался такой код не за одну минуту, а за пять. ну а дальше - простая арифметика возврата инвестиций, вложенных во время разработки ))ikki
помощь по Excel и VBA ikki@fxmail.ru, icq 592842413, skype alex.ikki
Может быть кому-нибудь поможет! В сумме (раз-, до-, про- и переработка) часов восемь-то ушло Зато сам кое-что новое узнал - про тот-же UBound, что не стоит его вставлять в цикл. А также то, что увеличение массива почти не влияет на скорость (как минимум на небольших массивах) - возможно VBA сразу выделяет память с запасом!
Может быть кому-нибудь поможет! В сумме (раз-, до-, про- и переработка) часов восемь-то ушло Зато сам кое-что новое узнал - про тот-же UBound, что не стоит его вставлять в цикл. А также то, что увеличение массива почти не влияет на скорость (как минимум на небольших массивах) - возможно VBA сразу выделяет память с запасом!Skif-F
Ну, про то, что не следует использовать for each при обработке больших наборов данных - исписаны тысячи страниц... Обращаю внимание - именно большого диапазона как источника данных для цикла. И вообще, "прямая" обработка данных в объектной модели (на листе) всегда проигрывает подходу "прочитать-обработать-записать результат" даже начиная с достаточно небольших объемов. Нечто похожее мы обсуждали и там.
А переменные (и массивы) в VBA всегда динамические, так что "скоростное переопределение" - это достоинства MemoryAllocator, а никак не хитрых алгоритмов
Ну, про то, что не следует использовать for each при обработке больших наборов данных - исписаны тысячи страниц... Обращаю внимание - именно большого диапазона как источника данных для цикла. И вообще, "прямая" обработка данных в объектной модели (на листе) всегда проигрывает подходу "прочитать-обработать-записать результат" даже начиная с достаточно небольших объемов. Нечто похожее мы обсуждали и там.
А переменные (и массивы) в VBA всегда динамические, так что "скоростное переопределение" - это достоинства MemoryAllocator, а никак не хитрых алгоритмовAndreTM
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
. Самый лучший эффект получается когда сначала считываешь ВСЕ данные сразу в разные массивы, а потом их там обрабатываешь... 
