Как использовать XLookup в Excel

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

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

Но такие функции, как VLookup, имели определенные ограничения, поэтому Microsoft недавно представила функцию XLookup в Excel. Он обеспечивает более надежный метод поиска определенных значений в наборе данных.

Знание того, как его эффективно использовать, может значительно сократить время, затрачиваемое на анализ данных в Excel.

Что делает XLookup в Excel?

Преимущества XLookup в Excel

Обязательные аргументы XLookup

Необязательные аргументы XLookup

Как использовать XLookup в Excel

Лучшие практики для XLookup в Excel

Что делает XLookup в Excel?

Основная функциональность XLookup — это возможность поиска определенных значений в диапазоне или массиве данных. Когда вы запускаете функцию, соответствующее значение возвращается из другого диапазона или массива.

В конечном счете, это инструмент поиска для ваших наборов данных Excel.

В зависимости от ваших аргументов XLookup покажет вам первое или последнее значение в экземплярах с более чем одним совпадающим результатом.

Другие функции поиска, такие как VLookup (вертикальный поиск) или HLookup (горизонтальный поиск), имеют ограничения, такие как поиск только справа налево. Пользователям необходимо либо изменить порядок данных, либо найти сложные обходные пути, чтобы смягчить это.

XLookup — гораздо более гибкое решение, позволяющее получать частичные совпадения, использовать более одного критерия поиска и использовать вложенные запросы.

Ознакомьтесь с этим учебным пособием по XLookup Excel, чтобы увидеть его в действии:

Преимущества XLookup в Excel

Основным преимуществом XLookup в Excel является экономия времени. Например, вы можете получить результат поиска, не изменяя позиционирование данных.

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

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

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

Аргументы в пользу XLookup также проще, чем VLookup, что делает его быстрее и проще в использовании. Например, XLookup по умолчанию использует точное совпадение, в то время как вам нужно будет указать это в аргументе VLookup.

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

Обязательные аргументы XLookup

Ваша функция XLookup требует трех аргументов:

• Lookup_value: это значение, которое вы ищете в массиве.
• Lookup_array: это диапазон ячеек, в которых вы хотите отобразить возвращаемое значение.
• Return_array: это диапазон ячеек, в которых вы хотите, чтобы функция искала значение.

Таким образом, простая версия XLookup будет выглядеть так:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив)

Необязательные аргументы XLookup

Функция XLookup также имеет несколько необязательных аргументов, которые можно использовать для более сложных сценариев или для сужения области поиска:

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

С включенными необязательными аргументами функция XLookup выглядит следующим образом:

=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Как использовать XLookup в Excel

1. Откройте Excel и таблицу данных, для которой вы хотите использовать функцию XLookup.

2. Выберите ячейку, в которую вы хотите поместить формулу XLookup Excel.

3. Введите «=» в эту ячейку, а затем введите «XLookup». Нажмите на опцию «XLookup», которая появляется в раскрывающемся списке.

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

Это означает выбор диапазона ячеек для каждого аргумента и размещение запятой перед переходом к следующему аргументу.

5. Если вы используете необязательные аргументы, введите их после обязательных аргументов.

6. Когда ваши аргументы XLookup завершены, добавьте закрывающую скобку перед нажатием Enter.

7. Результаты вашего XLookup должны отображаться в ячейке, где вы ввели функцию.

В этом примере Excel XLookup мы использовали XLookup, чтобы узнать оценку конкретного учащегося. Итак, искомое_значение было именем студента («Рубен Пью»).

Lookup_array представлял собой список имен учащихся в столбце A. Наконец, lookup_return представлял собой список оценок учащихся в столбце C. С помощью этой формулы функция возвращала значение оценки ученика: C-.

Но как использовать XLookup с необязательными аргументами?

Допустим, используя тот же набор данных, что и выше, мы хотим найти уровень посещаемости для студента с фамилией «Смит».

Здесь мы говорим Excel вернуть сообщение «Не найдено», если он не может получить значение, указывающее, что студент «Смит» отсутствует в этом списке:

Теперь предположим, что мы хотим определить, была ли у кого-либо из студентов посещаемость 70%. Но мы также хотим знать следующий ближайший уровень посещаемости, если 70% не существует в наборе данных.

Мы будем использовать значение «1» в аргументе match_mode, чтобы найти следующий самый большой элемент, если 70 не существует:

Поскольку ни один студент не имеет посещаемости 70%, XLookup вернул «75», следующее по величине значение.

Лучшие практики для XLookup в Excel

Используйте описательные ссылки

Предпочтительно использовать описательные ссылки для используемых ячеек, а не общие диапазоны, такие как A1: A12.

Когда приходит время корректировать формулу (что вы часто делаете с такой функцией, как XLookup), описательные ссылки облегчают понимание того, для чего вы изначально использовали формулу.

Это также полезно, если вы передаете электронную таблицу новому пользователю, которому нужно быстро понять ссылки на формулы.

Используйте точное совпадение, где это возможно

Использование точного соответствия по умолчанию в формуле (в отличие от подстановочных знаков) помогает гарантировать, что вы не получите непредусмотренные значения в возвращаемом значении.

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

Сохраняйте диапазоны аргументов одного размера

При объединении ваших аргументов убедитесь, что вы используете одинаковое количество ячеек для массива return_array и look_up. В противном случае вы получите сообщение об ошибке, и Excel вернет в ячейке только #ЗНАЧ.

Проверьте свою формулу

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

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

Начиная

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

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