Формулы Office 365/Excel 2016+
В этом уроке представлены три новые формулы, которые вы найдете в Office 365/Excel 2016+, которые называются XLOOKUP(), FILTER() и LET(). Еще один способ узнать о новых функциях и функциях в приложении — присоединиться к программе предварительной оценки Microsoft Office (см. в разделе справочных данных в конце этого модуля).
XLOOKUP()
XLOOKUP() — это новая, более эффективная версия функции VLOOKUP(). Она проще, быстрее и гибче.
Причины, по которым XLOOKUP() более оптимальна, чем VLOOKUP()
При поиске по столбцам и строкам возможности функций VLOOKUP() и HLOOKUP() объединяются для более полного поиска.
При поиске по столбцам слева шаблоны INDEX() MATCH() заменяются, что позволяет использовать сочетание, которое лучше подходит для поиска.
Формула более надежна в том плане, что ее выполнение не прерывается при добавлении или удалении столбцов.
XLOOKUP() включает синтаксис с тремя обязательными параметрами. По умолчанию функция выполняет точное соответствие.
Возможности XLOOKUP()
Возвращает значение из заданного столбца на основе значения в другом столбце.
Возвращает другое значение, если результат не найден.
Выполняет поиск сверху или снизу.
XLOOKUP() имеет шесть параметров, из которых три последних необязательные.
lookup_value: параметр, используемый для определения значения, которое требуется найти.
lookup_array: параметр Array, используемый для указания столбца, в котором нужно найти значение.
return_array: параметр array, используемый для определения столбца, из который будет возвращено значение.
if_not_found. Если совпадение не найдено, возвращается это необязательное значение.
match_mode: необязательный параметр для указания точного соответствия( сначала выше или ниже) или поиска с подстановочными знаками
search_mode. Укажите поиск сверху или снизу с помощью этого необязательного параметра.
В предыдущем примере набора данных обратите внимание на формулу XLOOKUP() справа в черном поле, где показаны возвращаемые результаты. Три примера отвечают на следующие вопросы.
Поиск продукта по идентификатору: формула, демонстрирующая поиск продукта с идентификатором продукта = 109, где результаты продукта находятся в столбце справа от столбца Код продукта.
Поиск города по ZIP. Пример формулы демонстрирует поиск города для ZIP = 21658, которые находятся в столбце слева от столбца ZIP.
Поиск последнего продукта по городу. Эта формула демонстрирует использование необязательных параметров "Результаты не найдены", если результаты не найдены, точное совпадение, а значение -1 указывает на поиск снизу вверх таблицы данных.
FILTER()
FILTER() — это новая функция массива. Добавление формулы в одну ячейку возвращает подмножество таблицы, а другие значения переносятся в другие ячейки в результатах. FILTER() возвращает строки данных и позволяет использовать несколько условий с помощью логики и/или.
Возможности FILTER()
Возвращает несколько результатов для одного значения поиска или нескольких.
Фильтрует данные без необходимости использовать синтаксис [refresh]{.underline}.
Можно вложить в другие функции Excel.
Следующие сведения описывают три параметра функции FILTER().
array: параметр, используемый для указания диапазона столбцов и строк для фильтрации.
include: параметр, используемый для предоставления условий правила фильтрации.
if_empty: необязательное значение параметра, возвращаемое, если ни строки не соответствуют условиям.
В предыдущем примере набора данных показана формула FILTER() в черном поле с возвращаемыми результатами. Обратите внимание, что вместо диапазона используется таблица. Рекомендуется всегда использовать таблицу, когда это возможно. В предыдущем примере применяется фильтр к таблице SalesTable, где Region = West, и возвращаются все совпадающие строки в результате.
В этом примере используется тот же набор данных, но к таблице применяются три фильтра. Формула фильтрует таблицу по следующим критериям. Для включения строки должны быть выполнены все критерии.
Product = Palma UM-01
Region = West
-Revenue = больше 1215,00 долл. США
В формуле используется функция умножения, поскольку логическое сравнение приведет к нулевому значению (0) для false или единице (1) для true. Если все условия имеют значение TRUE, то 1 * 1 * 1 = 1. Однако, если любое условие равно нулю (0) или false, то вся логика имеет значение false.
Звездочка (*) используется для условий И, а знак плюса (+) используется для условий ИЛИ.
LET()
Функция LET() обеспечивает значительную гибкость в сложных вычислениях и предоставляет более простой способ обработки различных частей формулы. Она сочетает в себе возможность хранения вычислений и значений, использующих переменные с собственным синтаксисом формулы Excel.
Переменные используются для присвоения имени значению или вычислению. Эти переменные используются для отзыва синтаксиса без необходимости повторной записи формулы. В функции можно определить до 126 различных переменных, но как минимум требуется наличие трех компонентов (переменная, значение переменной и вычисление). Кроме того, в функции LET() можно воспользоваться другими функциями массивов, такими как FILTER(). Следующий пример основан на примере использования функции FILTER() выше, но теперь с назначенными переменными.
На предыдущем снимке экрана числа от 1 до 4 являются переменными и определениями. Последняя инструкция — это вычисление, которое использует переменные.
ProductRange = диапазон столбцов продукта
Product = продукт, по которому выполняется фильтрация
RegionRange = диапазон столбцов региона
Регион = регион, по которому выполняется фильтрация
Filter = применение фильтра к таблице для Product и Region