Недавно в одной из тем боковичком вылезло обсуждение использования функций листа в коде VBA. Соответственно возник вопрос - я всегда считал, что для стандартных процедур встроенные функции быстрее, чем их симуляция в VBA.
Провел несколько тестов. Сформировал случайным образом 5000 чисел и 5000 текстовых строк (на повторы не проверял) Набросал простенький макрос поиска номера вхождения в диапазон 4-мя способами: 1. Поиск через WorksheetFunction.Match 2. Поиск через Range.Find(...).Row 3. Поиск через перебор ячеек в диапазоне (результаты тут не привожу, они минимум в 15-20 раз хуже) 4. Поиск через перебор массива. Использовал Timer() - не самое точное вычисление, но особенно глубоко залезать не хотелось. Таймер сбрасывался после каждой процедуры поиска. Для теста считал суммарное время поиска, среднее и максимальное (для одного прохода) - по каждому методу.
Итого в тесте 5000 раз ищем некое случайное число (в диапазоне от 1 до 10000) или текст (аналогичное случайное число плюс абракадабра) среди неотсортированного массива из 5000 таким же образом созданных случайных чисел или текстов. Число/текст для поиска каждый раз формируется рандомом, диапазон для поиска постоянный.
Тест 1. Ищем число среди чисел. Для функций Range.Find и Match используем в качестве аргумента диапазона переменную типа Range Тест 2. Ищем число среди чисел. Используем в виде аргумента Range напрямую, без переменной
Тест 3. Перебор ячеек через индекс строки.
Еще медленнее. Ищем только методом перебора ячеек через индекс строки (If Cells(i, 2) = Arg1 Then...) Sum: For Loop Cells 131,195313 Average: For Loop Cells 0,026234 Maximum: For Loop Cells 0,109375
В общем, с перебором ячеек всё понятно. Больше на него время не тратим.
Тест 4. Ищем текст
Итого (в секундах):
Поиск числа 1
Поиск числа 2
Поиск текста
Среднее
Sum:
Match
0,628906
0,734375
1,285156
0,882813
For Loop Array
2,429688
2,179688
3,640625
2,750000
Range.Find
8,976563
9,054688
7,210938
8,414063
Average:
Match
0,00013
0,000147
0,000257
0,000139
For Loop Array
0,00049
0,000436
0,000728
0,000404
Range.Find
0,001795
0,001811
0,001442
0,001682
Maximum:
Match
0,003906
0,046875
0,003906
0,018229
For Loop Array
0,046875
0,058594
0,070313
0,058594
Range.Find
0,074219
0,054688
0,062500
0,063802
В итоге при поиске чисел Match быстрее, чем Find в 12 раз - если в качестве аргумента задавать непосредственно Range, примерно в 14 раз - если в качестве аргумента задавать переменную типа Range. По сравнению с перебором массива Match при поиске числа быстрее соответственно в 4 или в 3 раза. При поиске текста Match в 2,8 раз быстрее перебора массива и в 5,6 раз быстрее, чем Find
Я думаю, на сортированном массиве при нечетком поиске результат был бы еще круче. Может быть, тест не самый чистый, но, как говорится, комментарии излишни. Как резюме - не всякий метод работает быстрее при реализации в VBA, особенно в том, что касается поиска. Я думаю, что какой-нибудь хитрый метод при поиске в массиве (что бы это могло быть?) может сработать быстрее перебора, но на несортированном рандоме - вряд ли удастся добыть устойчиво лучший вариант.
Максим, спасибо за интересные выкладки. На забугорских форумах часто читал о пользе find в сравнении с циклом по перебору объекта Range. Так и думал, что find - лучший в этом деле. В принципе не ошибался, ибо речи о match никогда не шло. Другое дело, что match тоже не лучший вариант. Если занести данные в вариативный массив, то поиск через Loop будет еще быстрее.
Все_просто, For Loop Array - это и есть Loop по массиву Variant. Он на втором месте после Match ))) Но есть различия - данная проверка показывает результаты именно по поиску позиции в массиве. Поиск подстроки, например, даст другие результаты для Match, а для Find и Loop результаты практически неизменны будут
Да я написал, а потом уже дочитал. Вот аналогичные выкладки (другая методика) с забугорского сайта. Там при эксперименте задекларировали АПИ микротаймера (это с точностью до 1/1000000 секунды). Соответственно цифры даны в миллисекундах.
ну если так называется массив, в котором могут находится различные типы данных --------------- Ни когда не слышал такого определения Тогда я не совсем согласен, что поиск через Loop будет еще быстрее. Вообще поиск очень специфическая штука, и всегда зависит от конкретных задач.
Попробовал. Добавил еще тестов. Результаты интересные. Компьютер был другой, плюс для замера времени вместо Timer(), который часто дает 0 вместо времени при очень быстрых результатах, использовал более точную функцию MicroTimer(), которую утащил по ссылке от Все_просто, так что сравнивать конкретные показатели неправильно, но соотношения - можно.
Начальные тесты на таких условиях: - Случайным образом генерируется столбец чисел (5000 или 50000 строк), путем добавления к таким же случайным числам абракадабры создается столбец текста. В дальнейшем в процессе тестов исходные данные не меняются. - Диапазон для поиска передается в функцию как переменная типа Range до собственно цикла поиска - Диапазон для поиска передается в цикл перебора массива как переменная типа Variant до собственно цикла поиска То есть, переменные диапазонов заранее определены и присвоены.
1. 5000 циклов по 5.000 строк (рандомные числа до 5000) При объявлении Dim rng1 As Variant Match быстрее Find примерно в 2 раза, быстрее массива в 16,5 раз при поиске числа Match быстрее Find примерно в 1,5 раза, быстрее массива в 16,5 раз при поиске текста При объявлении Dim rng1() As Variant Match быстрее Find примерно в 2 раза, быстрее массива в 9,7 раз при поиске числа Match быстрее Find примерно в 1,5 раза, быстрее массива в 9,8 раз при поиске текста 2. 5000 циклов по 5.000 строк (рандом расширен до 100000) При объявлении Dim rng1 As Variant Match быстрее Find примерно в 2 раза, быстрее массива в 16,5 раз при поиске числа Match быстрее Find примерно в 1,5 раза, быстрее массива в 16,5 раз при поиске текста При объявлении Dim rng1() As Variant Match быстрее Find примерно в 2 раза, быстрее массива в 9,7 раз при поиске числа Match быстрее Find примерно в 1,5 раза, быстрее массива в 9,8 раз при поиске текста
Увеличиваем диапазоны поиска до 50000 строк, рандом расширяем до 100.000 3. 5000 циклов по 50.000 строк При объявлении Dim rng1 As Variant Match быстрее Find примерно в 2,3 раза, медленнее массива в 2 раза при поиске числа Match быстрее Find примерно в 1,8 раза, медленнее массива в 4 раз при поиске текста При объявлении Dim rng1() As Variant Match быстрее Find примерно в 2,3 раза, медленнее массива в 4 раза при поиске числа Match быстрее Find примерно в 1,8 раза, медленнее массива в 5 раз при поиске текста
Теперь делаем финт ушами: для функций вместо переменных задаем диапазоны напрямую, по типу: Match(Arg1, Range(...),0), Range(....).Find Переменную типа Variant присваиваем внутри цикла. Таким образом, каждый раз считается время не просто на поиск внутри заранее заданных диапазонов, а подсчитываем скорость разового поиска - "взять диапазон с листа" + "найти значение" Тут картина однозначная:
4. 5000 циклов по 5.000 строк При объявлении Dim rng1 As Variant Match быстрее Find примерно в 3 раза, быстрее массива в 24 раза при поиске числа Match быстрее Find примерно в 2 раза, быстрее массива в 22 раз при поиске текста При объявлении Dim rng1() As Variant Match быстрее Find примерно в 3 раза, быстрее массива в 23 раза при поиске числа Match быстрее Find примерно в 2 раза, быстрее массива в 21 раз при поиске текста 5. 5000 циклов по 50.000 строк При объявлении Dim rng1 As Variant Match быстрее Find примерно в 2,5 раза, быстрее массива в 48 раза при поиске числа Match быстрее Find примерно в 1,8 раза, быстрее массива в 47 раз при поиске текста При объявлении Dim rng1() As Variant Match быстрее Find примерно в 2,7 раза, быстрее массива в 82 раза при поиске числа Match быстрее Find примерно в 1,9 раза, быстрее массива в 80 раз при поиске текста
Результаты немного странные, на первый взгяд - почему при rng1() скорость забора данных в массив с листа резко упала? Почти в 2 раза. При этом скорость собственно обработки массивов объявленных как rng1() As Variant почти в 1,7 раз выше, чем массивов "без скобочек"
Получается, что преимущество того или иного метода поиска зависит в первую очередь от 2 факторов: размера диапазона и особенностей кода. Для одноразового поиска в больших и не очень массивах данных лучше всё же Match. При многократном обращении массивы типа MyVar() несомненно выигрывают. И, интересный момент, они выигрывают еще больше, если в рассмотрение попадает больший диапазон. Что, в общем-то, странно, объяснить я это пока не смог. Наверное из-за того, что нужный элемент находится сравнительно быстрее, по сравнению с алгоритмами Match и Find. Но это уже к вопросу о случайности исходных данных.
Максим Зеленский пишет: Наверное из-за того, что нужный элемент находится сравнительно быстрее, по сравнению с алгоритмами Match и Find
Нет. По сути алгоритмы там одинаковые - все идут циклом и выходят при первом совпадении. А перебор в массиве быстрее потому что при поиске в массиве обращение к элементам идет через память, т.к. сам массив - это ссылка на ячейку в памяти. А обращение к ОЗУ ой какое быстрое. Find - это все же доп.обращение к объектной модели Excel, что кушает время на создание вызова элемента COM. Тоже самое и с Match. Но алгоритмы там все равно быстрее, т.к. реализованы на С++, который быстрее VBA работает. Перебор же ячеек на листе без массив происходит дольше всего по уже понятным и описанным выше причинам - каждое обращение к ячейке из VBA - это обращение к модели COM Excel. Использование скобок при объявлении массива дает выигрыш во времени при объявлении переменной как массива - VBA заранее знает, что это массив и уже не тратит время на переопределение типа при присвоении значения. Если же объявлять как Variant - то сначала время тратится на само объявление, а потом на переопределение типа.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Да, но почему такое несоразмерное изменение происходит? Условно говоря, среднее время на 1 поиск Match при увеличении размера исходных данных практически не вырастает, ну процентов на 10 (в сообщении этого нет, но в файле измерения есть), в то время как у массива () время падает раз в 50.
Навскидку: массив в 50000 элементов в памяти занимает гораздо больше места, чем 5000. Следовательно Вы крадете у ОЗУ основной источник для проведения вычислений, в том числе внутри массива. В случае с Match - Вы просто даете ссылку на уже созданный ранее объект, который и так присутствует в памяти(как диапазон на листе) и время тратиться только на обращение к COM. И памяти доступно больше, т.к. Excel может задействовать больше ресурсов, чем VBA. Документального подтверждения данных слов у меня нет - основано только на личном опыте и почерпнутых знаний на этом и других форумах. Поэтому могу ошибаться.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Я думаю так - если искать нужно один раз, и одно значение - то что find, что match - особой разницы нет, миллисекунду не заметите. Если же в одном диапазоне нужно искать "5000 циклов по 5.000 строк" - то быстрее взять диапазон в массив, затем загнать всё в словарь, а уж затем 5000 раз смотреть в словарь, где там что лежит. И никакого поиска
В словаре искать не нужно - просто берём и всё Ты ведь обычно не ищешь куда только что положил вещь? Конечно если память хорошая Вообще конечно вопрос формулировки - но ведь часто словарь и используется в основном чтоб потом больше не искать - иначе можно обойтись циклом в цикле или поиском, без словаря. А так сразу запомнили где что лежит, потом когда нужно - сразу берём.
внутри словаря всё равно производится поиск. другое дело - что не прямым перебором т.е. за счёт усложнения структуры данных и алгоритмов. что при небольшом объеме данных выигрыша может и не дать - накладные расходы съедят.
фрилансер Excel, VBA - контакты в профиле "Совершенствоваться не обязательно. Выживание — дело добровольное." Э.Деминг
Если честно, всегда считал что поиск по массиву намного быстрее т.к. он весь в памяти, а не к листу обращаемся + там еще всегда нужно пути полные прописывать книга\лист и т.д., а массив он в памяти работай с ним (бери что хочешь, записуй что хочешь, он и динамичный может быть, едиственное что столбцы\строки наоборот местами, что не привычно по началу), потом освободи память, а тут таааакой прорыв - спасибо за пост!
Автоматизация приложений, разработка ботов, парсинг сайтов, поиск информации и многое другое на языках : Delphi, C++, VBA. Информация в профиле.