Как использовать функцию XLOOKUP в Excel

Что такое функция Xlookup в Excel ? Как использовать Xlookup в Excel ? Давайте выясним это вместе с LuckyTemplates.com!

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

Эта функция особенно полезна, когда вам приходится обрабатывать много данных в электронной таблице Excel. В Excel есть несколько типов функций ПРОСМОТР. XLOOKUP — один из них. Так что же такое функция XLOOKUP, что такое формула XLOOKUP и как ее использовать? Давайте выясним это вместе с LuckyTemplates.com!

Что нужно знать о функции XLOOKUP в Excel

• Что такое XLOOKUP?
  • Как получить доступ к функции XLOOKUP
  • Синтаксис функции ПРОСМОТР
• Преимущества и недостатки функции XLOOKUP в Excel
  • Преимущества функции XLOOKUP
  • Недостатки функции XLOOKUP
• Как использовать функцию XLOOKUP
  • Дополнительные индексные номера столбцов отсутствуют.
  • Точное совпадение по умолчанию
  • XLOOKUP можно найти слева.
  • Используйте XLOOKUP для поиска диапазонов.
  • XLOOKUP заменяет функцию HLOOKUP.
  • XLOOKUP можно просматривать снизу вверх.

Что такое функция ПРОСМОТР в Excel?

По сути, XLOOKUP заменяет старые функции, такие как HLOOKUP, VLOOKUP и LOOKUP. Он поддерживает поиск данных как по вертикали, так и по горизонтали.

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

Существует множество способов максимально эффективно использовать функцию XLOOKUP.

Как получить доступ к функции XLOOKUP

В настоящее время XLOOKUP доступен только пользователям программы Insiders. Любой, кто присоединяется к программе Insider, может получить доступ к новейшим функциям Excel по мере их появления. Microsoft скоро предоставит эту функцию всем пользователям Office 365. К сожалению, XLOOKUP недоступен для пользователей версий Office 2010, 2013, 2016 и 2019.

Если вы не используете пакет Microsoft 365 на своем компьютере, вам может потребоваться перейти на Microsoft 365, чтобы получить доступ к функциям XLOOKUP. А если вы используете Microsoft 365, вы увидите, что эта опция включена в Excel. XLOOKUP также доступен в Office 365 Online.

Синтаксис функции ПРОСМОТР

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

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

Функция XLOOKUP поддерживает до 6 аргументов, вот их значения.

1. искомое_значение (обязательно): значение, которое вы хотите найти.

2. искомый_массив (обязательно): Массив, в котором вы хотите найти искомое значение.

3. return_array (обязательно): Массив, из которого вы хотите получить и вернуть значения при обнаружении искомого значения.

4. [if_not_found] (необязательно): если совпадений не найдено, возвращается это значение.

5. [match_mode] (необязательно): этот аргумент позволяет указать тип соответствия, который вы ищете. Для его указания существуют разные значения:

• 0 — ищет точное совпадение, и значение должно точно совпадать со значением в Lookup_array. Это значение также устанавливается по умолчанию, если оно не указано.
• -1 — ищет точное совпадение и, как только оно найдено, возвращается к следующему меньшему значению.
• 1 — ищет точное совпадение и при обнаружении возвращает следующее большее значение.
• 2. Выполните частичное сопоставление с использованием подстановочных знаков, где *, ? и ~ имеет особое значение.

6. [search_mode] (необязательно): используется для указания режима поиска XLOOKUP в массиве_поиска. Существуют разные значения, чтобы указать одно и то же:

• 1 – Выполните поиск, начиная с первого элемента. Это значение устанавливается по умолчанию, если ничего не указано.
• -1 — выполняет обратный поиск, начиная с последнего элемента.
• 2. Выполните двоичный поиск в искомом_массиве , где данные необходимо отсортировать по возрастанию. Если данные не отсортированы, это может привести к ошибкам или неверным результатам.
• -2 — выполнить двоичный поиск в Lookup_array , где данные необходимо отсортировать по убыванию. Если данные не отсортированы, это может привести к ошибкам или неверным результатам.

Преимущества и недостатки функции XLOOKUP в Excel

XLOOKUP по-прежнему является более выгодной функцией по сравнению с VLOOKUP и INDEX/ATCH. Но у него есть и свои недостатки.

Преимущества функции XLOOKUP

• Челюсть работает как вертикально, так и горизонтально.
• Требуется только 3 аргумента вместо 4, как в VLOOKUP и INDEX MATCH.
• По умолчанию всегда используется точное совпадение.
• Частичный поиск можно выполнить с использованием подстановочных знаков.
• Поиск можно выполнять в порядке убывания.
• Используйте одну функцию вместо двух в INDEX MATCH.

Недостатки функции XLOOKUP

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

Преимущества использования функции XLOOKUP Excel

• XLOOKUP упрощает наиболее часто используемые формулы Excel и снижает вероятность ошибок . Вам просто нужно написать =XLOOKUP (данные для поиска, список, список результатов), и вы получите ответ или #N/A, если значение не найдено.
• Результаты поиска по умолчанию являются точными . Одним из недостатков VLOOKUP является то, что вам нужно указать FALSE в качестве последнего параметра, чтобы получить правильный результат. XLOOKUP исправляет это, выполняя точное совпадение по умолчанию. Если хотите, вы можете использовать параметр режима сопоставления, чтобы изменить поведение поиска.
• Четвертый параметр поддерживает случай, когда значение не найдено . В большинстве деловых ситуаций вам придется «обернуть» формулу поиска операторами IFERROR или IFNA, чтобы исключить ошибки. XLOOKUP предоставляет четвертый параметр, поэтому вы можете узнать желаемый результат по умолчанию, если значение не найдено.
• XLOOKUP предоставляет параметры выбора для поиска особых ситуаций . Вы можете выполнять поиск сверху или снизу, по специальным символам и использовать более быстрые варианты поиска отфильтрованных данных.

Как использовать функцию XLOOKUP

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

Это пример, который ищет точные результаты, но функция XLOOKUP требует только три типа информации.

На изображении ниже показан XLOOKUP с 5 аргументами, но для получения правильных результатов необходимы первые три аргумента. Итак, остановимся на них:

• Lookup_value : значение поиска.
• Lookup_array : диапазон поиска.
• Return_array : диапазон, содержащий возвращаемое значение.

В этом примере мы будем использовать формулу:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Теперь давайте рассмотрим некоторые преимущества XLOOKUP перед VLOOKUP.

Дополнительные индексные номера столбцов отсутствуют.

Третий аргумент VLOOKUP предназначен для указания количества столбцов информации, возвращаемой из диапазона таблицы. Это больше не проблема с XLOOKUP, поскольку функция позволяет пользователю выбирать диапазон возврата. (столбец F в этом примере).

И не забывайте, что XLOOKUP может видеть оставшиеся данные выбранной ячейки, в отличие от VLOOKUP.

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

Точное совпадение по умолчанию

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

XLOOKUP можно найти слева.

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

ВПР ограничен поиском в крайнем левом столбце таблицы и последующим возвратом из указанного количества столбцов справа.

В приведенном ниже примере нам нужно найти идентификатор (столбец E) и вернуть имя человека (столбец D).

Используйте формулу ниже:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Используйте XLOOKUP для поиска диапазонов.

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

На этот раз мы не ищем конкретное значение и нам нужно знать, в какой диапазон попадают значения в столбце B в столбце E, чтобы определить скидку.

У XLOOKUP есть необязательный четвертый аргумент (помните, что по умолчанию он имеет абсолютное совпадение), называемый match_mode.

Вы можете видеть, что XLOOKUP может находить приблизительные совпадения лучше, чем VLOOKUP.

Существует возможность найти ближайшее совпадение, меньшее (-1) или ближайшее совпадение, превышающее (1) значение поиска. Существует также возможность использовать подстановочные знаки (2), например ? или *. Этот параметр не включен по умолчанию, как в случае с ВПР.

Формула в этом примере возвращает ближайшее значение, меньшее искомого значения, если точное совпадение не найдено:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Однако в ячейке C7 произошла ошибка: скидку 0 % следовало вернуть, поскольку было потрачено только 64, что не соответствовало критериям получения скидки.

Еще одним преимуществом функции XLOOKUP является то, что она не требует диапазона поиска в порядке возрастания, как VLOOKUP.

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

Формула немедленно исправляет ошибку. У него нет проблем с наличием 0 в конце таблицы диапазонов.

XLOOKUP заменяет функцию HLOOKUP.

Как уже упоминалось, функция XLOOKUP также может заменить функцию HLOOKUP. Одна функция может заменить две функции, что может быть лучше?

Функция HLOOKUP ищет горизонтально и используется для поиска по строкам.

Эта функция не так известна, как VLOOKUP, но полезна в случаях, когда заголовок находится в столбце A, а данные — в строках 4 и 5, как в примере ниже.

XLOOKUP может выполнять поиск в обоих направлениях: вниз по столбцам и вдоль строк.

В этом примере формула используется для возврата стоимости продаж, связанной с именем в столбце A2. Он ищет имя в строке 4 и возвращает значение из строки 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP можно просматривать снизу вверх.

Обычно вам нужно просмотреть список сверху вниз, чтобы найти первое появление значения. У XLOOKUP есть пятый аргумент с именем search_mode. Этот аргумент позволяет перенаправить поиск, начиная с нижней части списка, чтобы найти последнее присутствие значения.

В приведенном ниже примере нам нужно найти уровень запасов каждого продукта в столбце A.

Таблица поиска расположена в порядке дат. Мы хотим вернуть уровень запасов по последней проверке (последнее появление идентификатора продукта).

Пятый аргумент функции XLOOKUP предоставляет четыре варианта. Здесь мы будем использовать опцию «Поиск от последнего к первому» .

Формула, используемая в этом примере:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

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

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

XLOOKUP — значительное улучшение функций поиска. Единственное ограничение функции XLOOKUP заключается в том, что она не имеет обратной совместимости. Если вы делитесь электронными таблицами с более ранними версиями Microsoft Office Suite, вы можете получить ошибки.

Однако вы можете получить доступ к XLOOKUP из Office 365 Online. XLOOKUP все еще находится в разработке, поэтому пользователям Excel потребуется некоторое время, чтобы освоить его.

Желаю вам успехов!

Разница между XLOOKUP и VLOOKUP

	ВПР	Xlookup
Точное совпадение по умолчанию	Н	Да
Возвращает значение справа от поиска	Да	Да
Возвращает значение слева от поиска	Н	Да
Поиск по строке	Н	Да
Возвращает более одного значения	Н	Да
Необходимо классифицировать столбец подстановки	Только приблизительные совпадения	Только двоичный поиск
Поиск сверху вниз	Да	Да
Поиск снизу вверх	Н	Да
Бинарный поиск	Н	Да
Настройте сообщение, если искомое значение не найдено	Н	Да
Поиск по подстановочному знаку	Да	Да
Точный поиск	Да	Да
Приблизительный поиск возвращает следующее меньшее значение.	Да	Да
Приблизительный поиск возвращает следующее большее значение.	Н	Да
Неверное значение может быть возвращено, если искомые значения не отсортированы.	Y - приблизительный	Н - приблизительный, Y - двоичный
Добавление нового столбца для поиска таблицы может нарушить формулу.	Да	Н

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

Хотя VLOOKUP имеет свои собственные возможности, он имеет более ограниченные возможности, чем XLOOKUP. Это делает XLOOKUP наиболее популярным выбором при необходимости обработки больших объемов данных. В результате он работает быстрее и имеет меньше ошибок.

• XLOOKUP не ограничивает диапазон данных : XLOOKUP более гибок, чем VLOOKUP. ВПР может искать значения, расположенные только в крайнем левом столбце таблицы, и возвращать значения из соответствующего столбца справа. Между тем, XLOOKUP требует лишь более простых шагов и может возвращать значения для любого столбца в любом направлении.
• Результатом XLOOKUP по умолчанию является наиболее точное значение .
• По умолчанию функция ВПР в Excel всегда находит приблизительные результаты . Однако если вам нужны точные результаты, вам следует ввести ЛОЖЬ в аргументе range_lookup.
• Функция XLOOKUP ищет искомое значение в определенном диапазоне и всегда по умолчанию использует точное значение. Если вы не можете его найти, вы можете отредактировать аргумент, чтобы найти приблизительное совпадение, которое меньше или больше искомого значения.

Другие причины:

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