Что такое функция XLOOKUP и для чего она нужна в Excel?

С тех пор как Microsoft выпустила свой офисный пакет в 1990 году и до сегодняшнего дня, список внесенных улучшений был очень длинным. Одним из последних, которые пошли на улучшение пользовательского опыта дочерней программы расчета, было введение функции поиска. В этом посте мы увидим Что такое XLOOKUP и для чего он нужен в Excel.

Эта функция была впервые интегрирована в бета-фазе в августе 2019 г. В настоящее время она доступна только пользователям Microsoft 365. Как мы покажем вам далее, это очень полезный инструмент для тех, кто работает в Excel с большими объемами данных.

Что такое XLOOKUP?

В Excel функция xlookup относится к группе поисковых и справочных функций. С момента своего внедрения он стал одним из наиболее часто используемых пользователями инструментов благодаря своей эффективности и простоте использования.

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

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

Связанная статья:

Поиск в Excel: все хитрости

Синтаксис функции XLOOKUP

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

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

Параметры, соответствующие каждому из значений этой формулы, следующие:

• искомое_значение: искомая запись, которая может состоять из текста, цифр или даже заполнителей.
• search_array: относится к диапазону или матрице, в которой Excel должен искать нужную информацию.
• возвращенный_массив: относится к диапазону или матрице, в которой мы хотим, чтобы Excel возвращал нужную информацию.

Эти три параметра являются основными. Есть также другие необязательные параметры, которые мы можем использовать в зависимости от каждого случая:

• Если_это_не_найдено: Если Excel не находит допустимого совпадения, он может вернуть текст или значение, которое мы предоставили ранее (если этого не сделать, Excel просто отобразит сообщение «#Н/Д»).
• Match_Mode: тип соответствия можно указать в режиме сравнения.
• режим_поиска: Вы можете активировать бинарный поиск или определить, где поиск должен начинаться и заканчиваться, используя значение «1» для первого элемента и значение «-1» для последнего.

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

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

Использование только необходимых параметров

Во-первых, самый простой случай, в котором нам нужно используются только три обязательные спецификации «искомое_значение», «искомый_массив» и «возвращаемый_массив».

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

=БУСКАРХ(F6;B6:B17;D6:D17)

Искомое значение — «Гальван» (ячейка F6). Искомая матрица — это столбец имен (B6:B17), а возвращаемая матрица — это столбец, в котором соответствуют высоты каждого из них (D6:D17). Вам просто нужно нажать клавишу «Enter», чтобы появился результат, который мы ищем.

Добавление двух возвращаемых значений

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

=БУСКАРХ(F6;B6:B17;C6:D17)

Добавление параметра «si_no_se_encuentra»

Давайте теперь представим, что в том же примере, что и в двух предыдущих случаях, мы вводим в поиск имя игрока, которого нет в списке. Это когда вы можете указать текст ответа через параметр «si_no_se_encuentra». Выбранный выходной текст «Неверная спецификация», хотя любое другое сообщение, которое нам покажется подходящим, также будет действительным. Соответствующий синтаксис будет таким:

=XLOOKUP(F6;B6:B17;C6:D17; «Неверная спецификация»)

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