Главная » Статьи » Excel » Формулы рабочего листа

Производительность функций извлечения данных

Условия тестирования

В Excel есть несколько формул для извлечения данных. Я задался вопросом, какая из них самая быстрая. Я подготовил таблицу из 50 000 строк и 11 столбцов. Первый столбец - число от 1 до 50000. Остальные столбцы - случайные 10-символьные текстовые константы (например, "NTULGZCSED"). Таким образом, в качестве таблицы, из которой будут извлекаться данные, используется массив, состоящий из 550 000 элементов. Эту таблицу будем называть справочником. Поскольку и эта таблица, и следующая оформлены в файле, как "умные", то она получила идентификатор REF.

В качестве таблицы для тестирования производительности формул использовался массив из 5000 формул (1000 строк и 11 столбцов, из которых один содержит константу, которая является индексом для поиска в справочной таблице). Эту таблицу будем называть тестовой. Умная таблица называется PERF.

Для извлечения данных использовались:

  • ВПР (VLOOKUP)
  • ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH)
  • ПРОСМОТР (LOOKUP)
  • СМЕЩ+ПОИСКПОЗ (OFFSET+MATCH)

Результаты

Получены очень интересные результаты:

Формула Результат Комментарии


=ВПР(PERF[@[KEY]:[KEY]];REF[#Данные];СТОЛБЕЦ();0)

=VLOOKUP(PERF[@[KEY]:[KEY]];REF[#Data];COLUMN();0)
2730 ms ms - миллисекунды. Измерения проводились при помощи функции Win32 API GetTickCount, которая имеет погрешность 10 ms. Таблица REF была отсортирована по полю KEY.
Результат, который показала ВПР вполне рядовой и похож на остальных конкурентов, кроме нашего лидера. Данное время в 56 (!) раз хуже результата, продемонстрированного функцией ПРОСМОТР (LOOKUP).

=ИНДЕКС(REF[#Данные];ПОИСКПОЗ(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];0);СТОЛБЕЦ())

=INDEX(REF[#Data];MATCH(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];0);COLUMN())
2715 ms Результаты почти полностью аналогичны функции ВПР. В 58 раз хуже лидера.


=ПРОСМОТР(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];REF[COL1])

=LOOKUP(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];REF[COL1])
50 ms Лидер измерений. Честно говоря, я был очень удивлён такому результату, так как Microsoft относит данную функцию к разряду устаревших и оставленных только для совместимости с Lotus 1-2-3. Для корректной работы функция требует, чтобы таблица REF была отсортирована по полю KEY. Однако результаты, которые демонстрирует эта функция, позволяют простить ей ВСЁ. Ближайший конкурент в 56 раз медленее. В это трудно поверить, но я несколько раз проверил и не вижу никакой ошибки в своих измерениях.


=СМЕЩ(REF[[#Заголовки];[KEY]:[KEY]];ПОИСКПОЗ(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];0);СТОЛБЕЦ()-1;1;1)

=OFFSET(REF[[#Headers];[KEY]:[KEY]];MATCH(PERF[@[KEY]:[KEY]];REF[[KEY]:[KEY]];0);COLUMN()-1;1;1)
2730 ms Функция СМЕЩ (OFFSET) все клеймят тем, что она очень медленная. Наверное, это связано с тем, что её часто применяют для динамического определения именованного диапазона. В этом случае она слишком часто пересчитывается. Однако, в жизни эта функция оказалась на уровне функций ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что тоже для меня явилось неожиданным. В 58 раз хуже лидера.

Конструкции вида "PERF[@[KEY]:[KEY]]" или "REF[[KEY]:[KEY]]" являются абсолютными структурированными ссылками умных таблиц. То есть они не меняются при протягивании формулы. Не пугайтесь.

Код для измерения времени:


Скачать

Тестовый файл можно выкачать тут. Осторожно, - 13 Мб!

Выводы

В качестве вывода можно сказать только одно - используйте функцию ПРОСМОТР (LOOKUP) как можно чаще в своих таблицах! Она безоговорочный лидер по производительности и уделывает остальных конкурентов, как Бог черепаху!


Читайте также:

Категория: Формулы рабочего листа | Добавил: dsb75 (18.06.2014) | Автор: Батьянов Денис E W
Просмотров: 2823 | Комментарии: 15 | Теги: VLOOKUP, ПОИСКПОЗ, Match, просмотр, Lookup, Index, впр, СМЕЩ, Индекс, Offset | Рейтинг: 5.0/2
Всего комментариев: 15
15 antycapral   (16.10.2014 10:54)
http://perfect-excel.ru/forum/7-19-1#70 Создал тему , можете посмотреть почему криво работает ПРОСМОТР!

14 antycapral   (16.10.2014 10:54)
http://perfect-excel.ru/forum/7-19-1#70

9 MCH   (06.10.2014 21:11)
Цитата
В это трудно поверить, но я несколько раз проверил и не вижу никакой ошибки в своих измерениях.

В приложенных примерах у ВПР и ПОИСКПОЗ указан интервальный просмотр равный нулю, при этом ищется точное совпадение в неотсортированных массивах, при этом ПРОСМОТР нуждается в сортировке исходного массива. Может в этом секрет быстродействия?
Сделал свой тест по поиску в сортированном массиве без указания интервального просмотра в ВПР и ПОИСКПОЗ, особых преимуществ у ПРОСМОТР не увидел
https://yadi.sk/d/8JaRvXwZbr4oV

файл по ссылке:

0
10 dsb75   (06.10.2014 22:55)

Цитата
Сделал свой тест по поиску в сортированном массиве без указания интервального просмотра в ВПР и ПОИСКПОЗ, особых преимуществ у ПРОСМОТР не увидел

Миша, вот эту мысль не понял. Пример пока ваш не смотрел, но если мы не указываем параметр интервального просмотра, то он принимается по дефолту равным TRUE. Он нам зачем такой?

11 MCH   (06.10.2014 23:06)
Цитата
Он нам зачем такой?

Это я к тому, что ПРОСМОТР работает так же, как ВПР(...;ИСТИНА), если массив не отсортирован, то останавливается при нахождении первого значения большего искомого.
Поэтому сравнивать ПРОСМОТР с ВПР и ИНДЕКС/ПОИСКПОЗ нужно в равных условиях, когда они работают одинаково: на отсортированном массиве данных и без интервального просмотра

0
13 dsb75   (06.10.2014 23:09)
Вы правы. Всё сходится. Спасибо за ценные замечания!

0
12 dsb75   (06.10.2014 23:07)

Код
Может в этом секрет быстродействия?

Я думаю, что вы правы, а столь большая разница по скорости, возможно, объясняется тем, что Excel, вычисляя 10 ВПР в одной строке, делает это 1 раз для первого ВПР, а остальные 9 пользуются результатами уже проделанной работы. Возможно это работает через механизм кэширования вычислений, который по-любому должен быть.

4 antycapral   (24.09.2014 15:56)
Ждите тестов ))))

0
5 dsb75   (24.09.2014 22:37)
Ждём-ждём smile

6 antycapral   (25.09.2014 10:07)
Замечания
  • Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

из справки ....... В моих расчетах много ошибок, ВПР при этом все правильно считает.....

0
7 dsb75   (25.09.2014 10:12)
Сергей, да, массив для LOOKUP надо предварительно отсортировать. Это условие для её успешного использования.
Вы какую-то ошибку нашли у меня сверх этого?

8 antycapral   (29.09.2014 11:58)
Надо на форм перейти и там с примерами поиграться ..

1 Ольга   (03.07.2014 12:44)
пошла переделывать файл, который построен на Index+Match и пересчитывается 15-20 минут..

0
2 dsb75   (03.07.2014 13:05)
smile

0
3 dsb75   (03.07.2014 13:11)
Оля, расскажи потом, чего удастся добиться в итоге!

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика