Excel XLOOKUP против VLOOKUP: различия и преимущества
В этой статье мы рассмотрим различия между XLOOKUP, VLOOKUP и XLOOKUP, помогая вам выбрать правильную функцию для ваших задач анализа данных.
Если вы работаете с Excel, вы, вероятно, знаете, насколько полезна функция ВПР. Он позволяет вам искать значение в таблице и возвращать соответствующее значение из другого столбца. Но знаете ли вы, что существует более новая и мощная функция XLOOKUP, которая может еще больше? В этом уроке мы сравним эти две функции и посмотрим, чем они отличаются по синтаксису, функциональности и производительности.
VLOOKUP и XLOOKUP – сравнение синтаксиса
Подобно тому, как краеугольным камнем химии является периодическая таблица элементов, сердцем любой функции Excel является ее набор параметров. Давайте подробнее рассмотрим аргументы двух популярных функций поиска, чтобы разгадать их нюансы.
Синтаксис функции ВПР выглядит следующим образом:
ВПР(искомое_значение, table_array, col_index_num, [range_lookup])
Где:
- искомое_значение — значение для поиска.
- table_array — весь набор данных.
- col_index_num — номер столбца, из которого нужно получить значение.
- диапазон_поиска [optional] — указывает, следует ли найти приблизительное совпадение (по умолчанию — TRUE) или точное совпадение (FALSE).
А функция XLOOKUP имеет немного более сложную структуру:
XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])
Где:
- искомое_значение — значение для поиска.
- искомый_массив — диапазон, в котором нужно искать искомое значение.
- return_array — диапазон, из которого возвращается значение.
- if_not_found [optional] — значение, которое возвращается, если совпадение не найдено.
- режим_сопоставления [optional] – контролирует тип соответствия, например точное (0 — по умолчанию), точное совпадение или следующее меньшее (-1), точное совпадение или следующее большее (1), подстановочный знак (2).
- режим_поиска [optional] – указывает тип поиска, например, от первого до последнего (1 – по умолчанию), от последнего до первого (-1), двоичный поиск по возрастанию (2), двоичный поиск по убыванию (-2).
Более подробное объяснение аргументов см.:
Как видите, обеим функциям для выполнения своей работы требуется как минимум три параметра. Но XLOOKUP предоставляет больше дополнительных возможностей, что делает его более мощным и универсальным, чем его аналог VLOOKUP. Давайте разберем основные синтаксические различия в том, как они работают.
1. Где искать (массив поиска или массив таблиц)
ВПР заставляет пользователя указывать весь массив table_array, ища искомое значение исключительно в крайнем левом столбце. Столбец, из которого возвращается значение, определяется номером столбца в последующем аргументе.
XLOOKUP, напротив, позволяет указывать столбцы поиска и возврата отдельно. Такое разделение обеспечивает исключительную гибкость: столбец поиска можно расположить слева или справа от столбца возвращаемого значения.
2. Откуда вернуть значение (возвращаемый массив или номер столбца)
VLOOKUP использует жестко запрограммированный индексный номер для обозначения возвращаемого столбца, что создает уязвимость при изменении структуры массива поиска. Такие изменения, как добавление или удаление столбцов, могут легко нарушить вашу формулу.
С помощью XLOOKUP вы предоставляете ссылку на диапазон для возвращаемого массива, что позволяет лучше обрабатывать изменения в исходных данных. Более того, XLOOKUP может содержать несколько столбцов в возвращаемом массиве, для достижения этой функции требуется две или более разных формул VLOOKUP.
3. Как сопоставить (режим сопоставления и поиск по диапазону)
Аргумент range_lookup в VLOOKUP и match_mode в XLOOKUP определяют, ищет ли формула точное или приблизительное совпадение. Хотя обе функции имеют схожие возможности, XLOOKUP по умолчанию использует точное совпадение, предлагая более интуитивную настройку по умолчанию. Кроме того, XLOOKUP предоставляет дополнительную опцию для приблизительного совпадения, возвращающую следующее большее значение, без необходимости сортировки искомого массива, как это делает VLOOKUP.
Тип соответствия VLOOKUP XLOOKUP Точный FALSE или 0 0 или опущен (по умолчанию) Приблизительный (точный или ближайший к нему меньший) TRUE или 1 (по умолчанию)
Требуется, чтобы столбец поиска был отсортирован по возрастанию. -1 Приблизительное (точное или ближайшее большее значение) Н/Д 1 Подстановочный знак ЛОЖЬ (точное совпадение) 2
4. Как обрабатывать пропущенные значения (если они не найдены)
В тех случаях, когда искомое значение не найдено, ВПР может вернуть только стандартный #Н/Д! ошибка, которая потенциально может заставить вас усомниться в точности формулы.
Предоставляя аргумент if_not_found, XLOOKUP позволяет вам вернуть персонализированное сообщение, например: «Значение, которое вы ищете, не найдено в базе данных».
Проще говоря, в то время как VLOOKUP может сказать «Я не могу это найти» только с ошибкой, XLOOKUP позволяет вам отобразить что-то конкретное, если он не может найти то, что вы ищете. Например:
=XLOOKUP(F10, B4:B25, C4:C25, “К сожалению, ничего не найдено :(“)
5. Как искать (режим поиска)
ВПР всегда начинает поиск сверху и останавливается, когда находит первое совпадение.
XLOOKUP представляет аргумент search_mode, позволяющий пользователям выполнять поиск как от первого к последнему, так и от последнего к первому. Кроме того, он позволяет использовать методы двоичного поиска для более быстрого получения результатов в обширных наборах данных.
Например, в таблице ниже VLOOKUP может найти поставщика только для первого заказа данного товара, а XLOOKUP может сделать это как для первого, так и для последнего заказа. Просто установите search_mode равным 1 для первого совпадения и -1 для последнего совпадения:
=XLOOKUP(J4, F4:F25, G4:G25, , , -1)
Таким образом, XLOOKUP предоставляет больше возможностей выбора и контроля, позволяя нам указывать различные массивы для поиска и возвращаемых значений, а также различные параметры для режима сопоставления, режима поиска и обработки ошибок.
Разница между ВПР и XLOOKUP
Различия в синтаксисе VLOOKUP и XLOOKUP играют решающую роль в их производительности. Теперь давайте рассмотрим эти различия более подробно.
1. Положение столбца поиска (левый поиск)
В то время как VLOOKUP ограничен поиском только в первом столбце указанного table_array, XLOOKUP может искать значения в любом столбце, а не только в крайнем левом. Это означает, что XLOOKUP может легко выполнять двунаправленный поиск без необходимости перестановки данных.
Как вы можете заметить на снимке экрана ниже, VLOOKUP не может получить значение из столбца (Элемент), когда оно находится в правой части столбца поиска (Поставщик).
=ВПР(E4, A4:B25, 1, ЛОЖЬ)
XLOOKUP, с другой стороны, легко справляется с поиском слева:
=XПРОМ(E4, B4:B25, A4:A25)
2. Возврат значений из нескольких столбцов
ВПР может возвращать только одно значение за раз, что ограничивает его эффективность. Напротив, XLOOKUP может возвращать значения из нескольких столбцов за один раз. Это означает, что вы можете использовать одну формулу XLOOKUP вместо нескольких VLOOKUP.
Например, если вы хотите получить все сведения об определенном элементе, для использования ВПР требуются четыре отдельные формулы. Вы должны указать значения col_index_num в диапазоне от 2 (для региона в столбце B) до 5 (для поставщика в столбце E):
=ВПР($B$17, $A$4:$E$13, 2, ЛОЖЬ)
Благодаря поддержке динамических массивов XLOOKUP упрощает этот процесс, позволяя получить все данные с помощью всего одной формулы:
=XПРОМ(B17, A4:A13, B4:E13)
3. Поиск по нескольким критериям
XLOOKUP отлично справляется с поиском значений по нескольким критериям без необходимости использования дополнительных вспомогательных столбцов, в отличие от VLOOKUP. Практические примеры см.:
4. Поиск последнего вхождения
Чтобы найти последнее вхождение с помощью ВПР, вам потребуется изменить порядок исходных данных на обратный. Однако с помощью параметра search_mode XLOOKUP может получить последний экземпляр искомого значения без каких-либо дополнительных действий. Практические примеры см.:
5. Вставка или удаление столбцов
ВПР использует номер столбца статического индекса, что делает его уязвимым для проблем при обновлении источника данных или внесении изменений в порядок столбцов. XLOOKUP легко справляется с такими изменениями, гарантируя, что формула останется неизменной.
6. Ограничение размера искомого значения.
В VLOOKUP длина критериев поиска не может превышать 255 символов, иначе вы получите #VALUE! ошибка. Но XLOOKUP не накладывает ограничений на размер искомого значения. Итак, если ваш набор данных содержит очень длинные строки, XLOOKUP — это решение, позволяющее избежать ошибок.
7. Сортировка диапазона поиска
В то время как VLOOKUP требует сортировки по возрастанию для приблизительного поиска, XLOOKUP не имеет этого ограничения, обеспечивая адаптируемость в сценариях, где сортировка невозможна.
8. Универсальность в подборе
XLOOKUP выделяется своей универсальностью в параметрах сопоставления, предлагая различные варианты, такие как точные совпадения, два типа приблизительного сопоставления (следующее меньшее или следующее большее), совпадения с подстановочными знаками и даже двоичный поиск. Напротив, VLOOKUP имеет меньше возможностей, ограниченных точными совпадениями и следующими меньшими параметрами при приблизительном поиске.
9. Обработка ошибок
XLOOKUP лучше справляется с ошибками или пропущенными значениями. Вы можете решить, что он должен показывать, если не может найти запрошенное значение, чего не позволяет VLOOKUP.
10. Эффективность и скорость
XLOOKUP работает быстрее и эффективнее, поскольку он просматривает только то, что необходимо (массив поиска и возвращаемый массив), не тратя время и ресурсы на сканирование всей таблицы, как это делает VLOOKUP.
11. Простота использования
Хотя у VLOOKUP меньше аргументов, простота синтаксиса не обязательно означает простоту использования. Параметры XLOOKUP, такие как массив поиска и возвращаемый массив, предлагают более интуитивный подход по сравнению с массивом таблиц и номером индекса столбца VLOOKUP. Это делает XLOOKUP более удобным для пользователя, особенно для новичков.
12. Совместимость с версиями Excel
Это единственная область, где ВПР превосходит своего аналога 🙂 Хотя ВПР работает в любой версии Excel, XLOOKUP доступен только в Microsoft 365, Excel 2021 и Excel для Интернета.
Эти различия подчеркивают динамичный и эффективный характер XLOOKUP, что делает его отличным выбором по сравнению с VLOOKUP для различных задач поиска данных в Excel.
XLOOKUP против VLOOKUP – сводная таблица
В таблице ниже показаны основные различия между функциями XLOOKUP и VLOOKUP в Excel. Это краткое руководство, которое поможет вам понять, какой из них лучше подойдет для ваших задач.
Функция VLOOKUP Доступность XLOOKUP Все версии Excel 2021, Excel 365, Excel для Интернета Точное совпадение Да Да Приблизительное соответствие 1 вариант (следующий меньший размер) 2 параметра (следующий меньший размер или следующий больший размер) По умолчанию Приблизительное совпадение Точное соответствие Поиск по шаблону Да Да Двоичный поиск Нет Да Столбец подстановки необходимо отсортировать Для приблизительного совпадения Для бинарного поиска Ограничение на искомое значение 255 символов Нет Поиск слева Нет Да Горизонтальный поиск (по строкам) Нет Да Поиск в обратном порядке Нет Да Несколько критериев Сложно, со вспомогательным столбцом Проще, без вспомогательного столбца Возвращаемые значения из нескольких столбцов Нет Да Встроенная обработка ошибок Нет Да Добавление или удаление столбцов может привести к нарушению формулы Да Нет
XLOOKUP против HLOOKUP
В старых версиях Excel, если вы хотели найти данные в столбцах, вы использовали VLOOKUP, а для строк — HLOOKUP. Однако функция XLOOKUP может выполнять обе задачи! Давайте посмотрим, как это можно сравнить с HLOOKUP.
1. Поиск в любой строке
HLOOKUP может выполнять поиск только в верхней строке table_array, а XLOOKUP — в любой строке.
2. Поиск в обоих направлениях
HLOOKUP может выполнять поиск только слева направо, а XLOOKUP может выполнять поиск в обоих направлениях. Это похоже на двусторонний инструмент поиска.
3. Соответствие типов и значений по умолчанию
HLOOKUP по умолчанию использует приблизительное совпадение и требует, чтобы строка поиска была отсортирована по возрастанию. С другой стороны, XLOOKUP по умолчанию использует точное совпадение, что обычно и требуется в большинстве ситуаций.
Все остальные различия между XLOOKUP и VLOOKUP, о которых мы говорили, также применимы и к HLOOKUP. Таким образом, XLOOKUP похож на суперфункцию в Excel, которая может находить и возвращать значения из любой таблицы или диапазона с большей гибкостью, контролем и возможностями, чем обе старые функции поиска вместе взятые.
Подводя итог: хотя VLOOKUP уже много лет является надежным инструментом, теперь XLOOKUP предлагает более надежную и удобную альтернативу. Его способность выполнять как вертикальный, так и горизонтальный поиск, улучшенный синтаксис и улучшенная обработка ошибок делают его предпочтительным выбором для всех ваших поисков. Если у вас есть доступ к Excel 365 или 2021, вам обязательно стоит попробовать и посмотреть, как он может улучшить ваши таблицы Excel.
Рабочая тетрадь для скачивания
XLOOKUP vs. VLOOKUP – различия с примерами (файл .xlsx)
