Что вам нужно знать о XLOOKUP
С помощью XVERWEIS Microsoft предлагает своим пользователям Excel новую возможность быстрого и простого поиска в таблицах и оценки данных. Изначально эта функция была доступна только участникам на этапе тестирования, но с начала года также доступна для версий Microsoft 365 для Windows и Mac.
По своей сути XLOOKUP - это более удобная версия VLOOKUP и HLOOKUP, дополненная дополнительными практическими приложениями. Таким образом, его задача также заключается в поиске в таблицах, но это уже отличается от предыдущих ссылок. Фильтрация контента больше не привязана к предписанному курсу, но теперь может запускаться справа налево, сверху вниз и наоборот. Это нововведение предлагает большое преимущество, заключающееся в том, что таблицы Excel теперь можно разрабатывать по своему усмотрению и больше не привязаны к техническим характеристикам. Благодаря новым параметрам стали доступны дополнительные опции, которых раньше не было. Полезность простирается от мелких деталей до заметных упрощений в использовании. Окончательный эффект XLOOKUP полностью зависит от используемых параметров.
Простые параметры XLOOKUP
Для простого использования XLOOKUP требуется всего три параметра. Эти:
- Критерий поиска
- Матрица поиска
- Матрица возврата
Вновь завоеванная свобода в первую очередь связана с разделением матриц поиска и возврата. В то время как функции VLOOKUP и HLOOKUP по-прежнему требовали от пользователя выбора всей матрицы для процесса поиска, XLOOKUP позволяет ему разделить известное и искомое значение. Это означает, что один столбец поиска или теперь также строка поиска определяется как требуемая матрица, в которой находится выбранный критерий поиска, а другая становится областью возврата желаемого результата. Новая формула выглядит следующим образом:
= XLOOKUP (критерий поиска; матрица поиска; матрица возврата)
Если, например, ежемесячная зарплата г-на Вагнера может быть взята из таблицы Excel для сотрудников, тогда важны два столбца: первый, в котором перечислены все сотрудники по именам, и другой, в который вводится зарплата сотрудников. Поскольку критерием поиска является имя, соответствующий столбец поиска выбирается как матрица. С другой стороны, возвращаемый результат - это зарплата, поэтому соответствующий столбец здесь делается матрицей возврата.
Этот пример показывает, что новая формула делает излишним предыдущее обозначение определенного индекса столбца в одной большой матрице. Это экономит дополнительную детальную работу и предотвращает ненужные ошибки.
Однако, если неизвестно, называется ли сотрудник, которого вы ищете, «Вагнер» или «Вегнер», может помочь новое дополнение к критерию поиска. Любое количество неизвестных символов можно пропустить, вставив звездочку (*). В этом случае имеет смысл быстро изменить критерий поиска на «* gner», чтобы добраться до нужного пункта назначения. Однако, если таблица заполнена многими похожими именами, так что коллега г-жа Стегнер непреднамеренно выводится, потому что ее имя также содержит комбинацию букв, которую вы ищете, то поиск можно еще более уточнить. Здесь в игру вступает вопросительный знак (?), Поскольку он позволяет пользователю заменить только один символ. Таким образом, критерий поиска заполняется "W? Gner".
Но что происходит, когда вам нужно искать звездочку или вопросительный знак? В этом случае XLOOKUP имеет дополнительную тильду (~), с помощью которой становится ясно, что имеется в виду не функция критерия поиска, а содержимое матрицы поиска. Следовательно, двойная тильда как критерий поиска (~~) также позволяет искать одиночную тильду в матрице поиска (~).
Полные параметры
Кроме того, XVERWEIS предлагает другие функции, которые вступают в игру, как только эти три дополнительных параметра используются по желанию:
- If_not_ найдено
- Режим сравнения
- Режим поиска
"Если_не_ найдено"
Помимо трех параметров критерия поиска, матрицы поиска и матрицы возврата, новый XVERWEIS имеет еще три параметра, которые предлагают пользователю множество преимуществ. Одна из них - «If_not_ found», которая действует как интегрированная функция «если-ошибка».
С помощью этой функции XLOOKUP позволяет избежать общей проблемы с предыдущими ссылками: если результат поиска не может быть найден, пока отображается только значение загадочной ошибки ("#NV"). Благодаря новому параметру теперь можно дать имя этой ошибке и, таким образом, упростить ее классификацию, заменив параметр, занимающий место, выбранным вами словом, заключенным в кавычки. Вместо автоматического значения ошибки Excel может указать, что результат «не найден» или что произошла «ошибка ввода». Принимая во внимание всю информацию, формула XVERWEIS выглядит так:
= XLOOKUP (критерий поиска; матрица поиска; матрица возврата; если_не_ найдено)
Режим сравнения
Другой параметр - это режим сравнения, который позволяет при необходимости расширить область поиска значений. Первоначально VLOOKUP и HLOOKUP знали только совпадения или ошибки. Однако XLOOKUP может гибко реагировать и, в случае несуществующего результата, альтернативно использовать максимально близкое значение, чтобы не просто отображать ошибку пользователю, а вместо этого рекомендовать альтернативу. Например, если вы ищете счет на сумму 1500 евро, который не удалось найти, то для параметра режима сравнения можно использовать значение -1, чтобы вместо этого отобразить следующий более низкий результат. Может оказаться, что с самого начала счет был всего 1450 евро. Эту информацию можно было получить только с помощью предыдущих ссылок на промежуточных этапах. И наоборот, значение 1 можно использовать для получения следующего большего результата.
Эта функция особенно полезна, когда значение известно лишь приблизительно. Таким образом можно сузить рамку, чтобы, несмотря ни на что, было легче найти требуемый результат. Кроме того, содержимое таблицы больше не нужно сортировать в порядке возрастания, как в случае с функцией VLOOKUP, поскольку XLOOKUP может найти следующее значимое значение даже без помощи пользователя. Это также дает дополнительную свободу в индивидуализации таблиц.
Однако, если эти нововведения не требуются, значение 0 можно просто использовать, чтобы продолжать получать только точные результаты, как обычно. С помощью общего заполнителя формула раскрывается следующим образом:
= XLOOKUP (критерий поиска; матрица поиска; матрица возврата; if_not_ found; режим сравнения)
Режим поиска
Последний параметр снова показывает простейшее и, пожалуй, самое поразительное улучшение по сравнению с функцией ВПР, поскольку он позволяет настраивать направление истории поиска впервые. При вставленном значении 1 вы можете искать совпадения сверху вниз, а при значении -1 все идет вверх дном. Кроме того, двоичный поиск по возрастанию может быть инициирован значением 2, а двоичный поиск по убыванию - значением -2.
Хотя этот параметр поначалу не кажется особенно впечатляющим, он может регулярно давать положительный эффект в сочетании с расширенным критерием поиска. Поскольку, если матрица поиска содержит критерий поиска дважды (например, два сотрудника с одинаковой фамилией), то по умолчанию выдается возвращаемое значение, которое хронологически находится в более ранней позиции. Однако, если направление истории поиска меняется на противоположное, происходит противоположный эффект, и ранее скрытое значение становится видимым. Однако этот параметр также полезен, если он используется для временного контроля. Поскольку, если поиск сверху вниз дает другой результат, чем поиск снизу вверх, это может означать, что могла возникнуть ошибка приложения, которую теперь можно исправить на ранней стадии. Принимая во внимание это примечание, окончательная формула XLOOKUP выглядит так:
= XLOOKUP (критерий поиска; матрица поиска; матрица возврата; if_not_ found; режим сравнения; режим поиска)
Стоит ли переходить с VLOOKUP на XLOOKUP?
В заключение остается сказать, что с введением XLOOKUP Microsoft предоставила пользователям Excel новый способ поиска и анализа, который может служить различным целям и прост в использовании. Таким образом выполняется все, от быстрого поиска до конкретных требований к выходным данным. Таким образом, XLOOKUP явно опережает другие ссылки, поскольку он предлагает явные преимущества даже в функциях, которые он унаследовал от этого. Это видно из того факта, что здесь две ссылочные функции объединены и, среди прочего, дополнены интегрированной ошибкой if.
Любой, кто до сих пор полностью удовлетворен ВПР или ГПР и не заинтересован в том, чтобы привыкнуть к новой формуле, может с чистой совестью придерживаться испытанного и испытанного. Для всех, кто хочет сделать свой подход более динамичным и несложным, XVERWEIS - долгожданное нововведение. Новые параметры, безусловно, можно изменять и комбинировать таким образом, чтобы следующее использование Excel было заметно удобнее.