Calc VLOOKUP против XLOOKUP: выберите правильную формулу поиска

Используйте XLOOKUP для большинства новых операций поиска, если ваша рабочая книга будет открыта в LibreOffice 24.8 или более поздней версии; по умолчанию она выполняет точный поиск и позволяет выбирать отдельные диапазоны поиска и результатов. Используйте VLOOKUP, если вам нужна совместимость со старыми версиями Calc или если искомое значение находится в первом столбце компактной таблицы. Если вы сохраняете VLOOKUP, укажите ее четвертый аргумент как 0 или FALSE для точного поиска.

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

В чём заключается практическая разница?

Функция VLOOKUP выполняет поиск по первому столбцу прямоугольной таблицы и возвращает значение из пронумерованного столбца справа от него. Функция XLOOKUP разделяет диапазон поиска и диапазон возврата, поэтому возвращаемые значения могут находиться по обе стороны от ключей. Функция XLOOKUP также принимает пользовательское значение для отсутствующего совпадения и может выполнять поиск в обратном порядке.

Например, предположим, что ячейки A2:A5 содержат идентификаторы товаров, а ячейки B2:B5 — цены. В ячейке F2 находится искомый идентификатор. В Calc формула VLOOKUP для точного поиска выглядит следующим образом:

=VLOOKUP(F2;A2:B5;2;0)

Эквивалентная формула XLOOKUP выглядит следующим образом:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

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

В каких случаях следует использовать функцию XLOOKUP?

Выберите функцию XLOOKUP для новой рабочей книги, если вы знаете, что получатели используют LibreOffice 24.8 или более позднюю версию. Согласно официальной справочной странице XLOOKUP , эта функция стала доступна в LibreOffice 24.8 . Режим поиска по умолчанию — точный, поэтому отсутствие точного ключа не приводит к автоматическому возврату близкого значения.

Структура функции следующая =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]): . Первые три аргумента определяют ключ, одномерный диапазон для поиска и диапазон, из которого нужно вернуть результат. Четвертый аргумент, например, "Not found"заменяет обычный результат #N/A, если совпадение не найдено.

Возвращает значение из столбца слева.

Функция VLOOKUP не может вернуть значение слева от первого столбца таблицы. Функция XLOOKUP может, поскольку диапазоны поиска и результатов независимы. Например, если идентификаторы находятся в ячейках C2:C100, а названия отделов — в ячейках A2:A100, используйте:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Такая структура также позволяет избежать ненадежного столбцового индекса. Если кто-то вставляет столбец в таблицу, созданную с помощью функции VLOOKUP, жестко заданный индекс может указывать на другое поле. Явный диапазон результатов функции XLOOKUP упрощает проверку нужного возвращаемого столбца.

Возвращает самую последнюю соответствующую запись

Если присутствуют повторяющиеся ключи и вам нужно найти последнее совпадение, функция XLOOKUP поддерживает обратный поиск в своем режиме поиска. Для вертикального диапазона последний аргумент -1выполняет поиск от последнего к первому:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Эта функция возвращает результат, связанный с последним точным совпадением. Используйте её только в том случае, если порядок строк имеет определённое значение, например, самые новые записи отображаются последними. Если дубликаты следует рассматривать как ошибку, очистите или проверьте данные, а не выбирайте одно совпадение неявно.

В каких случаях функция VLOOKUP по-прежнему является лучшим выбором?

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

Для точного совпадения используется следующий шаблон =VLOOKUP(key;table;column_index;0): . Индекс отсчитывает столбцы от левого края выбранной таблицы: 1 означает ее первый столбец, 2 — второй. Ключ поиска должен находиться в этом первом столбце, а возвращаемое поле должно находиться справа от него.

Четвертый аргумент имеет значение. Если вы его опустите или установите значение TRUE, функция VLOOKUP будет использовать приблизительное сопоставление и ожидать, что первый столбец будет отсортирован в порядке возрастания. Такое поведение может быть полезно для диапазонов, таких как налоговые ставки, где каждая строка обозначает нижнюю границу. Оно опасно для идентификаторов, имен и несортированных списков. Для них укажите 0 или FALSE.

Как соотносятся процессы сопоставления и ошибки?

• Точное совпадение: XLOOKUP по умолчанию использует точное совпадение. Для гарантированного точного поиска VLOOKUP требует, чтобы четвертый аргумент был установлен в значение 0 или FALSE.
• Отсутствует ключ: функция XLOOKUP может возвращать выбранное сообщение или значение в качестве четвертого аргумента. Функция VLOOKUP обычно возвращает #N/A; оберните ее в IFNA, если вам нужно пользовательское сообщение.
• Приблизительное совпадение: Оба метода поддерживают сопоставление по диапазону, но данные и выбранный режим должны соответствовать заданному правилу. Отсортируйте данные по мере необходимости и проверьте граничные случаи.
• Направление поиска: функция XLOOKUP может выполнять поиск снизу вверх, используя аргумент search-mode. Функция VLOOKUP возвращает первую совпадающую строку при обычном поиске сверху вниз.

Чтобы задать пользовательский ответ для отсутствующего ключа с помощью функции VLOOKUP, используйте ` =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found").`. Это сохранит видимой настройку точного совпадения и обработает только ошибку недоступности. Избегайте заключения формулы в общий обработчик ошибок, если вы не хотите также скрыть несвязанные проблемы, такие как недопустимая ссылка на диапазон.

Что следует проверить перед тем, как поделиться рабочей книгой?

В справке LibreOffice указано, что функция XLOOKUP доступна начиная с версии 24.8. Также отмечается, что XLOOKUP не является частью стандарта OpenFormula OpenDocument версии 1.3, и указывается пространство имен функций Microsoft для обеспечения совместимости. Это означает, что формат файла сам по себе не гарантирует, что каждое старое или стороннее приложение для работы с электронными таблицами сможет вычислить эту функцию. Для смешанных программных сред протестируйте рабочую книгу в самом старом целевом приложении или используйте проверенную формулу, например, VLOOKUP с точным совпадением, если её структура подходит.

Также обратите внимание на документированное условие для формул массивов: если результирующий массив XLOOKUP представляет собой диапазон ячеек, функция должна быть введена как формула массива. Приведенные выше простые примеры возвращают одно соответствующее значение. Если вам нужна формула, возвращающая несколько ячеек, обратитесь к справке для конкретной версии и проверьте, как целевое приложение обрабатывает результат массива.

Как можно проверить результат?

1. Проверьте существующий ключ и сравните возвращаемое значение с исходной строкой.
2. Проверьте несуществующий ключ. Убедитесь, что функция XLOOKUP отображает выбранный вами резервный вариант или что функция VLOOKUP возвращает ожидаемое сообщение #N/A или IFNA.
3. Для точного поиска с помощью функции VLOOKUP убедитесь, что последний аргумент равен 0 или FALSE. Если вы намеренно используете приблизительное совпадение, проверьте порядок сортировки и значения непосредственно выше, на и ниже границы.
4. Для функции XLOOKUP с возвращаемым столбцом слева или для обратного поиска проверьте ключ с известным результатом и, если обнаружены дубликаты, подтвердите, какое именно совпадение следует вернуть.
5. Сохраните и откройте рабочую книгу в самой старой версии Calc или другой программе для работы с электронными таблицами, которую используют ваши получатели. Правильный результат на вашем компьютере не доказывает, что более старая версия поддерживает ту же функцию.

Для большинства новых таблиц Calc функция XLOOKUP предлагает более понятные диапазоны и более безопасные значения по умолчанию для точного совпадения. Используйте VLOOKUP, когда важна обратная совместимость или когда расположение элементов в первом столбце справа уже подходит, и явно укажите режим поиска.