Как использовать функцию Xlookup в Excel (с преимуществами и советами)
8 апреля 2022 г.
Многие люди используют формулы поиска Excel для отслеживания определенной информации в больших листах, содержащих данные, такие как оценки учащихся и финансовые отчеты. Изучение того, как использовать Xlookup, самый продвинутый инструмент поиска, может помочь вам сократить время, необходимое для поиска информации, и улучшить свои навыки работы с Excel. В этой статье мы обсудим, что такое функция Xlookup, как использовать Xlookup в Excel, преимущества использования этого инструмента, некоторые советы по его использованию в ваших собственных электронных таблицах и некоторые часто задаваемые вопросы об использовании этой функции.
Что такое Xlookup?
Xlookup — это формула, которую люди используют для поиска части данных в таблице или в вертикальных и горизонтальных диапазонах. Например, пользователь может найти конкретное имя в электронной таблице, введя номер телефона или идентификационный номер этого человека. Вы также можете сделать так, чтобы информация отображалась в несмежной ячейке. Если формула не может найти точное совпадение с введенной вами информацией, она находит ближайшее совпадение. Формула Xlookup:
=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])
Excel требует, чтобы пользователи заполнили первые три аргумента в формуле, в то время как вы можете не заполнять последние три. Минимум три аргумента предлагают дополнительные спецификации и требования к поиску. Вот список аргументов в этой формуле и объяснения того, что они означают:
Искомое_значение: это значение, которое вы ищете, например, имя или цена.
Lookup_array: это массив или диапазон, в котором ищет функция.
Return_array: это массив или диапазон, в котором функция ищет и возвращает значение.
[if_not_found]: это значение, которое функция может вернуть, если не находит искомое значение. Если вы не укажете этот аргумент, функция вернет ошибку #Н/Д.
[match_mode]: это спецификация того, какой тип соответствия вам нужен. 0 — точное совпадение, -1 — приблизительное совпадение со следующим меньшим значением, 1 — приблизительное совпадение со следующим большим значением и 2 — частичное совпадение с использованием подстановочных знаков.
[search_mode]: это спецификация того, как функция выполняет поиск в массиве поиска. 1 означает поиск в массиве сверху вниз, -1 выполняет поиск в массиве снизу вверх, 2 выполняет двоичный поиск данных в порядке возрастания и -2 выполняет двоичный поиск данных в порядке убывания.
Как использовать Xlookup в Excel
Следуйте этому списку шагов, если вы хотите использовать эту формулу и все ее аргументы в своей электронной таблице Excel:
Откройте книгу и выберите пустую ячейку рядом с вашими массивами данных.
Выберите строку формул в меню ленты.
Начните формулу, введя «=XLOOKUP(» в строке формул.
Вставьте критерии поиска, введя ссылку на определенную ячейку, например B3 или строку текста. Это означает, что формула ищет значение в этой ячейке или текст в книге.
Вставьте свой поисковый массив, введя диапазон ячеек или выбрав строку или столбец, которые, вероятно, содержат ваш поисковый запрос.
Вставьте возвращаемый массив, введя диапазон ячеек или выбрав строку или столбец, который вы хотите видеть в качестве результата. Вы можете закрыть скобки в этой формуле сейчас, если вы не хотите добавлять какие-либо дополнительные аргументы, или вы можете перейти к следующим шагам.
Добавьте пользовательское сообщение об ошибке, если функция не может вернуть точное совпадение. Введите числовое значение, например 1, текстовую строку в кавычках, логическую фразу или ссылку на ячейку.
Установите точный, приблизительный или подстановочный метод поиска, введя 1, -1, 2 или -2.
Определите режим поиска, введя 1, -1, 2 или -2.
Закройте скобки.
Преимущества использования Xlookup
Некоторые из наиболее распространенных преимуществ использования Xlookup включают поиск данных слева и справа от значения поиска и возврат нескольких результатов. Вот список других преимуществ, которыми вы можете воспользоваться при использовании этой функции:
Он может использовать точное совпадение в качестве режима поиска по умолчанию, в отличие от других функций поиска в Excel.
Он может искать как горизонтальные, так и вертикальные данные.
Он может выполнять обратный поиск.
Он может возвращать целые строки и столбцы данных вместо одного значения.
Он может использовать сложные критерии для работы с собственными массивами.
Советы по поиску
Вот список советов, которые могут помочь вам более эффективно использовать инструмент Xlookup в электронной таблице:
Используйте функцию Xlookup между книгами. Вы можете использовать эту функцию между открытыми книгами, чтобы сравнивать и находить данные с меньшими затратами времени и энергии.
Рассмотрите возможность использования бинарного поиска, чтобы сэкономить время. После сортировки данных рассмотрите возможность использования бинарного поиска, так как он использует меньше памяти в запросе. Это может помочь вам, если вы используете большие наборы данных, поскольку это может ускорить процесс запроса.
Наведите курсор на «=XLOOKUP(», чтобы просмотреть всю формулу. При этом появятся метки для всех аргументов формулы, что может помочь вам вспомнить, какие диапазоны данных следует вводить первыми.
Часто задаваемые вопросы об использовании Xlookup
Вот несколько ответов на распространенные вопросы о Xlookup:
Могу ли я использовать Xlookup в старых версиях Excel?
Вы не можете использовать Xlookup в старых версиях Excel. Эта функция является эксклюзивной для Office365, что означает, что люди с более старыми версиями приложения не могут ее использовать. Если вы используете версию Excel 2019 года и предыдущих лет, рассмотрите возможность обновления. Вы по-прежнему можете использовать такие функции, как Vlookup и Hlookup. Vlookup позволяет пользователям возвращать поисковые запросы для данных справа от значения поиска только в вертикальных массивах. Hlookup позволяет пользователям возвращать поисковые запросы для данных справа от значения поиска только в горизонтальных массивах.
Что делает поиск по подстановочным знакам?
Подстановочный поиск — это когда вы не указываете часть значения поиска и используете подстановочные знаки для оценки их значения. Вот список подстановочных знаков и то, что они символизируют:
? (вопросительный знак): неуказанное значение поиска — один символ. Например, “Ка?” может означать «Автомобиль» или «Кот».
* (звездочка): неуказанное значение поиска — любое количество символов. Например, «Re*» может означать «Красный», «Досягаемость» или «Ресторан».
~ (тильда): этот символ обозначает подстановочный знак в тексте. Например, если вы хотите найти точную фразу с подстановочным знаком, например «Число*», вы можете использовать «Число~*», чтобы найти именно эту фразу, а не слова, начинающиеся с этих букв.
Вот пример использования поиска по подстановочным знакам. Пользователь хочет найти значение для человека по имени Кейси, но не уверен, пишет ли этот человек начало своего имени с буквы «К» или «К». Пользователь мог ввести «?acey» в формулу Xlookup, чтобы найти имя этого человека. Вы также можете использовать подстановочные знаки с функциями Vlookup и Hlookup.
Могу ли я преобразовать формулу Xlookup в формулу Vlookup?
Вы можете преобразовать формулу Xlookup в формулу Vlookup, используя различные методы. Вот список шагов, которые могут помочь вам вручную изменить эту формулу, не изменяя значения в ней:
Замените «X» в начале формулы Xlookup на «V», чтобы она читалась как «VLOOKUP». Первый аргумент остается прежним.
Объедините первую часть второго аргумента с последней частью третьего аргумента. Например, второй аргумент «B1:10» и третий аргумент «C1:C10» объединяются, чтобы получить «B1:C10».
Подсчитайте количество столбцов между вторым и третьим аргументом исходной формулы и добавьте 1. Например, если есть разница в один столбец, вы должны ввести 2, чтобы создать третий аргумент в формуле ВПР.
Введите «FALSE» в качестве последнего аргумента.
Есть ли у Xlookup ограничения?
Функция Xlookup не имеет ограничений. Это означает, что вы можете использовать все 1 048 576 строк и 16 384 столбца книги. Вы можете извлечь выгоду из использования Xlookup для больших файлов, таких как рабочие листы с общекорпоративной информацией обо всех сотрудниках и высшем руководстве. Это также может работать с финансовыми записями за годы и аналогичными документами.
Обратите внимание, что компания, упомянутая в этой статье, не связана с компанией Indeed.