Функция XLOOKUP в Microsoft Excel — это эффективный инструмент поиска, который позволяет находить определенные значения в диапазоне ячеек. Он заменяет предыдущие функции поиска, такие как VLOOKUP, и устраняет многие их ограничения. В этой статье представлено пошаговое руководство по эффективному использованию XLOOKUP.
Что такое функция XLOOKUP и как она работает?
Excel предоставляет несколько инструментов для поиска по данным, включая встроенный инструмент поиска и выбора и правила условного форматирования. Однако эти характеристики не имеют большого значения для вашей электронной таблицы и не упрощают использование данных в других формулах. В таких случаях особенно полезны функции поиска, такие как XLOOKUP.
Зачем нам нужны функции ПРОСМОТРА
При анализе данных часто возникают случаи, когда вам необходимо выполнить поиск по данным, используя пользовательские критерии поиска, которые регулярно меняются. Аналитики данных часто используют запросы, которые можно легко настроить для получения конкретных результатов из набора данных.
ВПР и ГПР — ограничения и решения
Диапазон традиционных функций поиска, таких как HLOOKUP (горизонтальный поиск) и VLOOKUP (вертикальный поиск), ограничен. Среди других ограничений им необходимо найти решения для поиска данных слева или справа от значений. Чтобы обойти эти ограничения, XLOOKUP позволяет искать данные в строках и столбцах как слева, так и справа от искомого значения.
Решение — XLOOKUP
XLOOKUP может обрабатывать приблизительные совпадения, множественные значения поиска, вложенные запросы, пользовательские сообщения об ошибках и многое другое. Он доступен только для подписчиков Microsoft 365, но быстро изменил правила игры как для базового, так и для сложного поиска данных.
Как работает функция XLOOKUP в Microsoft Excel?
Чтобы объяснить функцию XLOOKUP, давайте воспользуемся примером. Представьте себе электронную таблицу Excel, содержащую список сотрудников со столбцами для идентификатора сотрудника, имени, адреса электронной почты и должности.
Подобный набор данных можно использовать в качестве каталога с возможностью поиска в Excel с помощью формулы XLOOKUP. Значением поиска для этой формулы может быть любое из значений в этих столбцах.
Базовый синтаксис XLOOKUP
Основной синтаксис функции XLOOKUP:
- искомое_значение:
Значение, которое вы хотите найти.
- искомый_массив:
Диапазон или массив для поиска.
- return_array:
Диапазон или массив, из которого возвращается результат.
- [if_not_found]:(Необязательно) Значение, возвращаемое, если совпадение не найдено.
Пример сценария
Рассмотрим электронную таблицу Excel с четырьмя столбцами: идентификатор сотрудника, имя, адрес электронной почты и должность.
Вы хотите найти адрес электронной почты сотрудника с идентификатором 103. Вот как это сделать с помощью XLOOKUP:
- Выберите пустую ячейку
Чтобы вставить функцию XLOOKUP, начните с открытия электронной таблицы Excel и выбора пустой ячейки. Щелкните панель формул на ленте, чтобы начать редактирование.
- Определите критерии поиска
Начните вводить формулу на панели ленты:
- искомое_значение: 103
- искомый_массив: A2
(диапазон, содержащий идентификаторы сотрудников)
- return_array: C2
(диапазон, содержащий адреса электронной почты)
Эта формула будет искать идентификатор 103 в диапазоне A2.
и верните соответствующее письмо из диапазона C2
, то есть «jim@example.com».
Необязательные аргументы
- не найдено:
Если значение не найдено, вы можете указать собственное сообщение или значение.
- match_mode: Укажите тип соответствия.
- search_mode: Укажите порядок поиска.
Расширенные возможности XLOOKUP
XLOOKUP предлагает несколько расширенных функций:
1. Возврат нескольких столбцов
XLOOKUP может возвращать несколько столбцов данных. Предположим, вам нужен и адрес электронной почты, и должность для данного сотрудника:
Эта формула возвращает массив {"jim@example.com", "Designer"}.
2. Вложенный XLOOKUP
Вы можете вкладывать функции XLOOKUP для сложных запросов. Например, поиск должности сотрудника по его электронной почте:
Эта формула сначала находит идентификатор сотрудника по электронной почте, а затем находит должность по этому идентификатору.
Что следует учитывать перед использованием XLOOKUP
- Расположение данных:
XLOOKUP может искать данные, расположенные как по горизонтали, так и по вертикали.
- Обработка ошибок:
Используйте аргумент not_found для корректной обработки несовпадающих результатов.
- Размеры диапазона:
Во избежание ошибок убедитесь, что искомый_массив и возвращаемый_массив имеют одинаковый размер.
- Совместимость:
XLOOKUP доступен только в Microsoft 365 и более поздних версиях Excel.
Пошаговое руководство по использованию XLOOKUP в Excel
Выполните следующие шаги, чтобы создать формулу с помощью XLOOKUP:
1. Выберите пустую ячейку:
Откройте таблицу Excel и выберите пустую ячейку.
2. Введите формулу:
Начните вводить =XLOOKUP( в строке формул.
3. Вставить критерии поиска:
Укажите искомое значение, массив поиска и возвращаемый массив.
4. Добавьте необязательные аргументы:
При необходимости включите not_found, match_mode и search_mode.
5. Нажмите Ввод:
Закройте формулу закрывающей скобкой и нажмите Enter, чтобы увидеть результат.
Последние мысли
Функция XLOOKUP в Excel — универсальный и эффективный инструмент для анализа данных. По сравнению со стандартными операциями поиска он обеспечивает большую гибкость, упрощает сложные запросы и повышает эффективность работы по управлению данными. Вы можете в полной мере воспользоваться возможностями Excel и значительно улучшить свои навыки анализа данных, научившись использовать XLOOKUP. XLOOKUP — жизненно важный инструмент для любого пользователя Excel или аналитика данных, независимо от того, работают ли они с базовыми списками или сложными наборами данных.
