Введение
Многие Excel‑пользователи ожидают, что новая функция XLOOKUP() будет быстрее устаревшего VLOOKUP(), особенно при массовых подстановках в больших книгах. Однако на практике замена всех VLOOKUP() на XLOOKUP() в одной реальной книге объёмом 6 МБ привела к тому, что время пересчёта выросло с ~2 секунд до ~4,5 секунд.
В статье мы разберём факты, экспериментальные данные и типичные сценарии, в которых XLOOKUP() действительно может превосходить VLOOKUP() по производительности. Сохранены приведённые примеры, числа и ключевые термины (XLOOKUP(), VLOOKUP(), Excel, Microsoft 365, binary search и т.д.).
Проблема и экспериментальные данные
Автор заметил замедление на большой книге (6 МБ) после массовой замены VLOOKUP() на XLOOKUP(). До замены время пересчёта было около 2 секунд, после — примерно 4,5 секунды. Это побудило провести контрольный тест в небольшой тестовой книге.
В тесте столбец B содержал последовательность чисел от 1 до 1 048 576, а столбец C = SQRT(B) (от 1 до чуть более 1000). Искали значение 1 000 000 в столбце B и возвращали соответствующее значение из C (1000). XLOOKUP() показал 0,04–0,05 секунды, а VLOOKUP() — 0,00 секунды в этом конкретном тесте.
При этом в сети встречаются противоречивые утверждения: одни источники утверждают, что XLOOKUP() быстрее, другие — что VLOOKUP() быстрее. Поэтому нужно рассмотреть, в каких ситуациях какая функция имеет преимущество.
Общее мнение: гибкость против простоты и скорости
Общее мнение сводится к тому, что XLOOKUP() более функционален и гибок, но не всегда быстрее. VLOOKUP() имеет меньше опций и по умолчанию использует приблизительное соответствие (approximate match), что может ускорять поиск, но снижает точность и гибкость.
XLOOKUP() по умолчанию использует точное соответствие (exact match), что обычно более правильный выбор, и предоставляет дополнительные опции для настройки поведения. Таким образом выбор между XLOOKUP() и VLOOKUP() — это компромисс между скоростью в простых сценариях и функциональностью при более сложных требованиях.
Когда XLOOKUP() быстрее VLOOKUP()
1. Обратные (right‑to‑left) и большие наборы данных
VLOOKUP() не умеет нативно делать обратный поиск (справа налево) и для этого часто используют комбинацию INDEX+MATCH, что усложняет расчёт. XLOOKUP() поддерживает обратный поиск напрямую, что упрощает формулы и может повышать эффективность в больших наборах данных.
Для больших таблиц с множеством отдельных обращений к одной и той же паре столбцов XLOOKUP() может работать быстрее благодаря тому, что он оперирует только с нужными столбцами вместо всей таблицы.
2. Поиск по одному столбцу (single‑column matching)
XLOOKUP() загружает в память только столбец поиска и столбец возврата, тогда как VLOOKUP() обрабатывает весь указанный диапазон таблицы. Это особенно заметно, когда таблица имеет много столбцов, но нужно вернуть значение только из одного.
Пример (большой набор данных):
=XLOOKUP("Order123", Orders[ID], Orders[Amount]) 'Обрабатывает только два столбца
=VLOOKUP("Order123", Orders, 5, FALSE) 'Обрабатывает все столбцы диапазона Orders
3. Бинарный поиск (binary search) для сортированных данных
XLOOKUP() поддерживает режим бинарного поиска (search_mode=2 или -2) при условии, что данные заранее отсортированы. Бинарный поиск делит диапазон пополам и выполняет логарифмическое число сравнений, что значительно быстрее линейного поиска.
XLOOKUP() в режиме бинарного поиска может работать более чем вдвое быстрее по сравнению с обычным режимом XLOOKUP(). Для отсортированных данных это делает XLOOKUP() быстрее, чем VLOOKUP() в большинстве случаев.
4. Динамические массивы и избежание повторных вычислений
XLOOKUP() нативно работает с динамическими массивами, что позволяет возвращать и обрабатывать массивы значений без дополнительных функций. VLOOKUP() в подобных задачах требует дополнительных приёмов (например, FILTER), что может увеличивать вычислительную нагрузку.
При массовом применении формул, использующих динамические массивы, XLOOKUP() может снизить повторные вычисления и тем самым улучшить общую производительность книги.
Ключевые замечания и вывод
XLOOKUP() предлагает больше возможностей и инструментов для настройки (точный поиск по умолчанию, обратный поиск, режим бинарного поиска, поддержка динамических массивов). В некоторых ситуациях, например при сортированных данных с включённым бинарным поиском или при поиске только по двум столбцам в очень широкой таблице, XLOOKUP() будет быстрее VLOOKUP().
Однако VLOOKUP() остаётся в ряде простых сценариев быстрее из‑за меньшего количества опций и использования по умолчанию приблизительного соответствия. Поэтому выбор между XLOOKUP() и VLOOKUP() следует делать, учитывая не только скорость, но и точность, гибкость и структуру данных. Используется Microsoft 365 в оригинальном примере, где доступны все возможности XLOOKUP().
Источники
Релевантные материалы и тесты были приведены в исходных источниках:
Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?
https://trumpexcel.com/excel-functions/vlookup-vs-xlookup/
