XLOOKUP: вот что может XLOOKUP в Excel

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

XLOOKUP в Excel относится к семейству функций поиска и ссылок. Это одна из самых полезных функций в популярной программе электронных таблиц Microsoft. XLOOKUP — это самый простой способ поиска определенных записей данных в диапазоне ячеек. Записи в ранее определенном диапазоне ячеек отображаются в отформатированном виде. Звучит знакомо? Этот принцип также используется в VLOOKUP. Однако с помощью более гибкого Excel-XLOOKUP можно искать не только одну, но и несколько записей. Также возможен поиск значений по вертикали и горизонтали листа.

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

Прежде чем мы объясним синтаксис функции Excel XLOOKUP и как ее использовать на нескольких примерах, приведем краткий обзор наиболее распространенных вариантов ее применения:

• Поиск одной записи
• Поиск нескольких записей одновременно
• Поиск строк
• Поиск точного совпадения и следующих меньших или больших записей
• Использование символов подстановки в критерии поиска.

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

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

В нашем первом примере мы используем только три обязательных элемента «Критерий поиска», «Режим поиска» и «Возвращаемый массив». Содержимое нашей таблицы Excel находится в ячейках с B2 по D10, и в ней отображаются различные страны и соответствующие им коды городов. С помощью функции XLOOKUP мы хотим узнать, какой телефонный код имеет страна Венгрия. Результат должен быть отображен в области G2 (для этого просто выделите ячейку). Необходимая формула выглядит следующим образом:

F2 — критерий поиска, в данном случае «Венгрия». Режим поиска состоит из префиксного столбца и простирается от B2 до B10. Возвращаемый массив — это столбец, в котором должно быть найдено соответствующее значение, поэтому в примере он состоит из диапазона от D2 до D10. После нажатия клавиши Enter на экране появится искомый код региона.

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

Формула в нашем следующем примере содержит не только три упомянутых выше элемента синтаксиса, но и not_found. С помощью этого дополнения вы можете указать, какой текст должен быть выброшен в случае несоответствия. Для примера мы определим текст: «Invalid country» в качестве выходного текста и ищем страну «Portugal», которая не является частью нашего набора данных Excel.

В этом примере мы будем использовать необязательные аргументы not_found, match mode и search mode в дополнение к трем обязательным аргументам.

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

Для параметра not_found мы указали значение «0». Таким образом, в случае несовпадения вместо «#/NA» будет отображаться «0». Поскольку вы не ищете точного совпадения, для режима сравнения задано значение «1», что означает отображение следующего по величине элемента. Режим поиска также установлен на «1», чтобы Excel искал в таблице от первого до последнего элемента.

Наконец, мы используем вложенную функцию Excel XLOOKUP. Это дает возможность одновременно выполнять вертикальное и горизонтальное сопоставление — настоящая особенность XLOOKUP. В примере мы ищем продажи продавца с идентификатором «1002» в апреле месяце. Формула выглядит следующим образом: