Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше?

Функции XLOOKUP и VLOOKUP в Excel используются для быстрого анализа больших наборов данных. ВПР — это функция, которая используется в Excel уже много лет. Однако есть более новая функция, которую вы должны использовать, которая называется XLOOKUP.

Оба имеют преимущества и ограничения. Мы здесь, чтобы представить различия и помочь вам решить, какой из них больше подходит для вашего аналитического проекта. Проверять Множество полезных приемов Microsoft Excel, которые поразят вашего босса.

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Как работает ВПР?

ВПР означает вертикальный поиск. Это функция Excel, которая выполняет поиск элементов в таблице или диапазоне по строке из набора данных, а затем возвращает уникальное значение из другого столбца в той же строке. Синтаксис функции ВПР следующий:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Давайте посмотрим на пример. Допустим, у вас есть список моделей смартфонов в вашем наборе данных, и вам нужно получить определенную информацию о нем.

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Вы можете использовать функцию ВПР, чтобы получить дополнительную информацию об их процессорах и ценах.

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Вот подробное руководство о Как использовать функцию ВПР в Excel Извлекает конкретную информацию из набора данных. Хотя ВПР является одной из лучших функций Excel и может помочь вам упростить большие наборы данных, у нее есть некоторые ограничения. Например, для работы функции данные должны быть расположены в порядке возрастания.

Как работает XLOOKUP?

XLOOKUP позволяет выполнять горизонтальный и вертикальный поиск, чтобы найти значение в электронной таблице. Функция в основном ищет массив. Если совпадение найдено, возвращается соответствующий элемент из второго диапазона данных (return_array).

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Он более эффективен, чем другие параметры поиска, поскольку по умолчанию он содержит точное совпадение. Вы можете использовать необязательные аргументы в функции ([match_mode]), чтобы указать тип необходимых вам значений широкого соответствия — точное совпадение, более низкое или более высокое значение суффикса или совпадение с подстановочным знаком.

Их можно использовать для различных целей, включая финансовый анализ, маркетинг, анализ продаж и т. д. Синтаксис функции XLOOKUP следующий:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Следующее руководство познакомит вас с основами и поможет понять, как использовать функцию XLOOKUP в электронной таблице. Эти две функции требуют как минимум трех аргументов.

Ключевые различия между XLOOKUP и VLOOKUP

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Хотя между XLOOKUP и VLOOKUP есть много общего, важно понимать различия.

1. Индексный номер столбца

При запуске ВПР вам нужно будет перейти к исходной таблице, чтобы проверить и указать точный номер индекса столбца.

Функция ВПР требует, чтобы параметр номера столбца возвращал правильные результаты. Использование неправильного номера индекса столбца может привести к другому результату на листе или данных либо в виде неправильного значения, либо в виде ошибки «#ССЫЛКА».

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Допустим, в приведенном выше примере вам нужно извлечь сведения о процессоре устройства Apple iPhone 12. Мы будем использовать второй набор данных для всех примеров.

Соответственно нам нужно ввести искомое значение, например, Apple iPhone 12 в графу J , который оказался крайним левым столбцом в нашем примере набора данных, соответствующим аргументу table_array в функции (столбцы J и K). Значение должно быть извлечено из второго столбца в table_array, т. е. столбца поиска K, поэтому номер индекса столбца будет равен 2.

XLOOKUP — это динамическая функция, которая инвертирует аргумент номера столбца, что означает, что вам не нужно беспокоиться о подсчете количества столбцов в таблице. Вам нужно будет только указать столбцы поиска, что сэкономит вам много времени и сил.

2. Долговечность и гибкость

После создания модели вы сможете быстро обновить исходную таблицу новыми данными, не беспокоясь о сбое функции. При добавлении или перемещении столбцов с функцией ВПР вам придется переформатировать всю функцию.

XLOOKUP — более мощная функция, позволяющая добавлять или удалять столбцы, не беспокоясь об ошибках. Просто убедитесь, что вы не вставляете столбец или строку вне массива поиска, и в этом случае функция вернет ошибку.

3. Пользовательские сообщения об ошибках

XLOOKUP удобен в использовании. Это позволяет вам ввести пользовательское текстовое сообщение об ошибке в аргументе [if_not_found], которое появится, если искомое значение не будет найдено.

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

ВПР не предлагает подобных возможностей. Возможно, вам придется использовать другие функции, такие как ЕСЛИОШИБКА, в качестве обходного пути, если совпадений не найдено. Проверять Основные причины отказаться от Excel и начать использовать Google Sheets.

Почему XLOOKUP лучше, чем VLOOKUP?

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

Несмотря на использование ВПР, его функциональность более ограничена, чем XLOOKUP. Это делает XLOOKUP предпочтительным выбором для работы с большими наборами данных. Результаты быстрее, а также менее подвержены ошибкам. Давайте разберемся, почему XLOOKUP работает лучше, чем VLOOKUP.

1. XLOOKUP не ограничивается определенными диапазонами данных

XLOOKUP более гибкий, чем VLOOKUP, который может искать значения только в крайнем левом столбце таблицы, и возвращать значения из соответствующих столбцов справа, как мы видели в примере выше. Напротив, модель XLOOKUP требует более простых шагов и может возвращать значения для любого столбца в любом направлении.

2. XLOOKUP по умолчанию использует точное совпадение

По умолчанию функция ВПР Excel предполагает, что найдено приблизительное совпадение. Однако, если для вашего рабочего листа требуется точное совпадение, вы должны ввести «FALSE» в аргументе range_lookup.

Функция XLOOKUP ищет значение поиска в указанном диапазоне, которое по умолчанию является точным совпадением. Если точное совпадение найти не удается, можно изменить аргумент, чтобы найти приблизительное совпадение, которое может быть меньше или больше искомого значения.

Для этого вы можете установить для дополнительного аргумента [match_mode] значение 0 = точное совпадение (по умолчанию), -1 = следующее или наименьшее точное совпадение, 1 = точное или следующее наибольшее совпадение или 2 = совпадение с подстановочным знаком.

Сравнение XLOOKUP и VLOOKUP: какая функция Excel лучше? - Отзывы

3. XLOOKUP может обрабатывать вставки или удаления столбцов.

ВПР — отличная функция для поиска определенных данных из электронной таблицы или другой таблицы. Однако они обеспечивают постоянное масштабирование — если вам нужно обновить источник данных, внести изменения или переставить столбцы, функция вылетит, и вы пропустите ВПР.

С другой стороны, XLOOKUP — более динамичная метрика, которая может обрабатывать изменения столбцов и возвращать точные результаты поиска. Он также поставляется с более конкретными параметрами, такими как массив поиска и массив возврата, вместо аргумента массива таблицы и номера индекса столбца в ВПР.

4. XLOOKUP выполняет вертикальный и горизонтальный поиск

XLOOKUP может находить данные как в горизонтальной, так и в вертикальной матрицах, в отличие от VLOOKUP, который может выполнять только вертикальный поиск данных.

5. XLOOKUP обеспечивает более точное совпадение

Если вы ищете элемент, который ближе, чем вам хотелось бы, XLOOKUP даст вам более точные результаты. Что дает более точные результаты и меньше ложных срабатываний.

Однако с функцией ВПР, если вы хотите найти приблизительное совпадение из электронной таблицы, функция вернет только следующее наименьшее значение ссылки на совпадение. Но XLOOKUP позволяет выполнять более точное приблизительное сопоставление и также возвращать следующее наибольшее или наименьшее значение из таблицы поиска.

6. XLOOKUP ТРЕБУЕТСЯ МЕНЬШЕ ССЫЛОК НА ЯЧЕЙКИ

При использовании XLOOKUP все, что вам нужно, это определить два конкретных диапазона данных — массив или диапазон, из которого вы хотите найти значение (lookup_array), и массив/диапазон, из которого вы хотите получить значение (return_array).

С другой стороны, ВПР требует, чтобы вы указали весь диапазон данных, используя параметр table_array. Это может быть проблемой со сложными наборами данных, потому что, если электронная таблица большая, может быть сложно переупорядочить или выбрать весь диапазон ячеек, из которых вы хотите вернуть значение. Кроме того, чтобы вернуть приблизительное совпадение с помощью функции ВПР, данные в таблице всегда должны быть отсортированы в порядке возрастания.

VLOOKUP или XLOOKUP: какая функция вам подходит?

Итак, XLOOKUP лучше, чем VLOOKUP? При прямом сравнении ответ должен быть положительным. Однако это может быть не так просто для всех пользователей. Например, вы можете предпочесть более старую функцию, если вы особенно хорошо с ней справляетесь, или когда вы найдете применение для конкретной функции, которое подходит именно вам.

Для больших наборов данных XLOOKUP, вероятно, является лучшим выбором для вас из-за его расширенной функциональности. Функция ВПР имеет множество ограничений, которые затрудняют ее использование для сложных данных, и, как следствие, функция КСПР обладает большей гибкостью, что делает ее лучшим выбором для большинства пользователей электронных таблиц. Теперь вы можете просмотреть Навыки Word и Excel, необходимые для успешной работы в офисе.

Источник
Перейти к верхней кнопке