Условия тестирования
В 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) как можно чаще в своих таблицах! Она безоговорочный лидер по производительности и уделывает остальных конкурентов, как Бог черепаху!
Читайте также:
| 
0
