Что такое XLOOKUP в Excel? Чем это лучше ВПР?

Семейство функций поиска Excel огромно. XLOOKUP в Excel — новейший член этого семейства. Некоторые из других важных членов этого семейства включают HLOOKUP, VLOOKUP, LOOKUP, INDEX + MATCH и т. д. Если вы используете Excel 2021 или Excel 365, вы можете использовать XLOOKUP вместо VLOOKUP. Эксперты Excel считают, что формула XLOOKUP удобна и проста в использовании.

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

Здесь мы прольем свет на XLOOKUP в Excel и формулу XLOOKUP .

Изучайте онлайн-курсы по науке о данных от лучших университетов мира. Участвуйте в программах Executive PG, Advanced Certificate Programs или Master Programs, чтобы ускорить свою карьеру.

Что такое XLOOKUP в Excel?

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

Формула XLOOKUP работает как настраиваемый инструмент поиска, который гибко подходит для различных типов требований к данным в настраиваемых электронных таблицах Excel. Это новая функция, доступная для пользователей Microsoft 365.

Изучите наши популярные курсы по науке о данных

Когда вы используете формулу XLOOKUP , вам просто нужно указать три основных параметра, а MS Excel может выполнить три другие функции:

• Значение, которое вы ищете
• Список, где вы найдете это значение
• Список, из которого вы хотите получить результат
• [необязательный] значение, если оно не найдено

Пример XLOOKUP для легкого понимания:

= XLOOKUP («Алиса», маркетинг[маркетолог], маркетинг[сетевой маркетинг])

Возвращает [Net Marketing] для Алисы, если имя есть в столбце [Marketing Person's].

При сравнении ВПР вам не нужно указывать номер_столбца или значение true/false для выполнения поиска.

Это означает, что XLOOKUP может искать результат в любом месте таблицы, а не только слева. Вам не нужно выбирать сложные формулы ВПР или сложные формулы ИНДЕКС+ПОИСКПОЗ.

Синтаксис формулы и функции XLOOKUP в MS Excel

Синтаксис формулы и функции XLOOKUP в MS Excel следующий:

XLOOKUP (значение, искомый_массив, возвращаемый_массив, [если не найдено], [режим_сопоставления], [режим_поиска])

Параметры или аргументы

value — это значение, которое вам нужно найти в lookup_array .

lookup_array — это диапазон ячеек или массив для поиска значения .

return_array — диапазон ячеек или массивов, из которых будет возвращено соответствующее значение на основе позиции значения в lookup_array .

if_not_found — необязательно. Возвращаемое значение, если совпадение не найдено. Если этот параметр опущен, функция выдаст ошибку #Н/Д (так же, как функции ГПР и ВПР).

match_mode — это необязательно. Это тип матча для выполнения. Это могут быть следующие значения:

search_mode — это необязательно. Это вид поиска, который вы должны выполнить. Это может быть одно из следующих значений:

Пошаговое руководство по использованию формулы XLOOKUP в MS Excel

Выполните следующие шаги, если вы хотите создать формулу с помощью функции XLOOKUP:

Шаг 1 – Выбор пустой ячейки

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

После того, как вы выберете ячейку, вы должны нажать строку формул на ленточной панели, чтобы вы могли начать писать и редактировать по мере необходимости. Когда вы видите, что курсор на ленте мигает, это означает, что ячейка активна, и вы можете начать редактирование. Теперь вы можете вставить новую формулу XLOOKUP .

Шаг 2 – Определение критериев поиска (lookup)

Вы должны следовать определенной структуре и синтаксису при использовании новой формулы с помощью функции XLOOKUP. Вы должны начать с ввода формулы. Начните вводить =XLOOKUP( на панели ленты. Теперь введите критерии поиска . Это информация, которую вы ищете.

XLOOKUP поддерживает текстовые и числовые строки. Но большинство пользователей указывают ссылку на ячейку. Таким образом, вы можете изменить критерии поиска в соответствии со своими предпочтениями, не изменяя напрямую формулу XLOOKUP . По умолчанию XLOOKUP попытается найти точное совпадение.

Шаг 3 — Определение набора данных с помощью lookup_array и return_array

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

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

Шаг 4 (необязательно) — Добавление пользовательского сообщения об ошибке с помощью not_found

Если вы хотите найти точное совпадение с искомым значением, но его нет в диапазоне искомого_массива, Excel отобразит ответ об ошибке #NA . Чтобы избежать этой проблемы, необходимо вставить необязательный аргумент not_found в формулу XLOOKUP . Аргумент not_found может быть текстовой строкой в ​​кавычках («не найдено»), числовым значением, ссылкой на ячейку или логическим значением (истина или ложь).

Шаг 5 (необязательно) — добавление приблизительных, точных или подстановочных совпадений при поиске с использованием match_mode

XLOOKUP в Excel по умолчанию предназначен для поиска точных совпадений. Но это можно изменить, добавив в формулу XLOOKUP необязательный аргумент match_mode . В качестве подстановочного знака это самое близкое и первое приближение. Если вы хотите изменить порядок поиска, вам нужно добавить аргумент search_mode на следующем шаге.

Шаг 6 (необязательно) — определение порядка возврата значения с помощью search_mode

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

Причины, по которым XLOOKUP в Excel лучше, чем VLOOKUP

Есть много причин, по которым формула XLOOKUP лучше, чем VLOOKUP. Вот некоторые из них:

1. XLOOKUP по умолчанию пытается найти точное совпадение

В VLOOKUP вы должны указать FALSE в качестве последнего параметра для получения правильного результата. Однако в XLOOKUP по умолчанию можно найти точное совпадение. Используйте параметр режима соответствия для изменения поведения поиска.

2. XLOOKUP делает формулу динамической

В MS Excel формула XLOOKUP является динамичной и простой. Самое главное, формула менее подвержена ошибкам. Вам просто нужно написать =XLOOKUP( и вы получите результат. Если значение не найдено, вы получите ошибку #N/A.

3. В XLOOKUP в Excel есть необязательные параметры

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

4. XLOOKUP легко набирать

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

5. XLOOKUP в Excel возвращает ссылку в качестве вывода

Формула XLOOKUP возвращает ссылку в качестве вывода, а не значение. Для обычных пользователей это может быть неважно, но для пользователей Pro-Excel это формула, которая возвращает ссылки. Это означает, что XLOOKUP можно комбинировать с другими формулами разными способами.

Читайте наши популярные статьи о науке о данных

Вывод

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

Присоединяйтесь к классу с сертификационной программой upGrad по аналитике данных

Карьера в области аналитики данных — это выгодная возможность в современной отрасли. Аналитики данных работают с огромными объемами данных для извлечения важной информации. Чтобы отточить свои навыки в области анализа данных, вы должны присоединиться к сертификационной программе по анализу данных upGrad . При поддержке Fullstack Academy вы получите сертификат Калифорнийского технологического института по анализу данных по завершении курса. В ходе курса вы сможете освоить навыки визуализации данных, Python и SQL, что проложит вам путь к тому, чтобы стать отличным аналитиком данных.