Формулы Office 365/Excel 2016+

Завершено

В этом уроке рассматриваются три новые формулы, которые можно найти в Office 365/Excel 2016+ — XLOOKUP(), FILTER() и LET(). Еще один способ узнать о новых функциях и возможностях, которые были добавлены в приложение, — присоединиться к Программе предварительной оценки Microsoft Office (см. ссылку в конце этого модуля).

XLOOKUP()

XLOOKUP() — это новая, более эффективная версия функции VLOOKUP(). Она проще, быстрее и гибче.

Причины, по которым XLOOKUP() более оптимальна, чем VLOOKUP()

  • При поиске по столбцам и строкам возможности функций VLOOKUP() и HLOOKUP() объединяются для более полного поиска.

  • При поиске по столбцам слева шаблоны INDEX() MATCH() заменяются, что позволяет использовать сочетание, которое лучше подходит для поиска.

  • Формула более надежна в том плане, что ее выполнение не прерывается при добавлении или удалении столбцов.

XLOOKUP() включает синтаксис с тремя обязательными параметрами. По умолчанию функция выполняет точное соответствие.

Снимок экрана строки формул Excel с синтаксисом функции XLOOKUP()

Возможности XLOOKUP()

  • Возвращает значение из заданного столбца на основе значения в другом столбце.

  • Возвращает другое значение, если результат не найден.

  • Выполняет поиск сверху или снизу.

XLOOKUP() имеет шесть параметров, из которых три последних необязательные.

  • lookup_value — параметр, используемый для определения искомого значения.

  • lookup_array — параметр массива, используемый для указания столбца для поиска значения.

  • return_array — параметр массива, используемый для определения столбца, из которого следует возвратить значение.

  • if_not_found — если совпадения не найдены, возвращается это необязательное значение.

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

  • search_mode — необязательный параметр для указания направления поиска: сверху или снизу.

Снимок экрана с примерами функции XLOOKUP()

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

  • Find Product by ID — формула, демонстрирующая поиск продукта по идентификатору продукта = 109, где результаты найдены в столбце справа от столбца Product ID.

  • Find City by ZIP — формула демонстрирует поиск города по индексу = 21658, где результаты найдены в столбце слева от столбца ZIP.

  • Find last Product by City — данная формула демонстрирует использование необязательных параметров. Возвращается No Results found, если результаты не найдены, точное совпадение и –1, что означает поиск снизу вверх в таблице данных.

FILTER()

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

Возможности FILTER()

  • Возвращает несколько результатов для одного значения поиска или нескольких.

  • Фильтрует данные без необходимости использовать синтаксис [refresh]{.underline}.

  • Можно вложить в другие функции Excel.

Следующие сведения описывают три параметра функции FILTER().

  • array — параметр, используемый для указания диапазона столбцов и строк для фильтрации.

  • include — параметр, используемый для предоставления критериев правила фильтрации.

  • if_empty — необязательный параметр значения, возвращаемого, если ни одна строка не соответствует условиям.

Снимок экрана: пример функции FILTER() с одним результатом

В предыдущем примере набора данных показана формула FILTER() в черном поле с возвращаемыми результатами. Обратите внимание, что вместо диапазона используется таблица. Рекомендуется всегда использовать таблицу, когда это возможно. В предыдущем примере применяется фильтр к таблице SalesTable, где Region = West, и возвращаются все совпадающие строки в результате.

Снимок экрана: пример функции FILTER() с несколькими результатами

В этом примере используется тот же набор данных, но к таблице применяются три фильтра. Формула будет фильтровать таблицу по следующим критериям (для включения строки необходимо соблюдение всех критериев).

  • Product = Palma UM-01

  • Region = West

  • Revenue = свыше 1215,00 долл. США

В формуле используется функция умножения, поскольку логическое сравнение приведет к нулевому значению (0) для false или единице (1) для true. Если все условия имеют значение TRUE, то 1 * 1 * 1 = 1. Однако, если любое условие равно нулю (0) или false, то вся логика имеет значение false.

Звездочка (*) используется для условий И, а знак плюса (+) используется для условий ИЛИ.

LET()

Функция LET() обеспечивает значительную гибкость в сложных вычислениях и предоставляет более простой способ обработки различных частей формулы. Она сочетает в себе возможность хранения вычислений и значений, использующих переменные с собственным синтаксисом формулы Excel.

Схема синтаксиса функции LET()

Переменные используются для присвоения имени значению или вычислению. Эти переменные используются для отзыва синтаксиса без необходимости повторной записи формулы. В функции можно определить до 126 различных переменных, но необходимо иметь как минимум три компонента (переменная, значение переменной, вычисление). Кроме того, в функции LET() можно воспользоваться другими функциями массивов, такими как FILTER(). Следующий пример основан на примере использования функции FILTER() выше, но теперь с назначенными переменными.

Снимок экрана: пример использования функции LET()

На предыдущем снимке экрана числа от 1 до 4 являются переменными и определениями. Последняя инструкция — это вычисление, которое использует переменные.

  • ProductRange = диапазон столбцов продукта

  • Product = продукт для применения фильтра

  • RegionRange = диапазон столбцов региона

  • Region = регион для применения фильтра

  • Filter = применение фильтра к таблице для Product и Region