В ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз, пригодится новая функция LET. Она есть в Excel 2021/2024/365 и Google Таблицах.
Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. И затем вычисление с использованием этих переменных.
Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET:
Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее. И затем вычисление с использованием этих переменных.
Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET:
В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a:
Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.
Давайте посмотрим на пример. Допустим, у нас есть формула для поиска по названию товара и заголовку — функция ИНДЕКС / INDEX, аргументами которой выступают две функции ПОИСКПОЗ / MATCH.
Давайте посмотрим на пример. Допустим, у нас есть формула для поиска по названию товара и заголовку — функция ИНДЕКС / INDEX, аргументами которой выступают две функции ПОИСКПОЗ / MATCH.
С функцией LET ее можно переписать так:
=LET (Поиск_Товара;ПОИСКПОЗ ([@Номенклатура];Прайс[Номенклатура];0);
Поиск_Заголовка;ПОИСКПОЗ (C$ 1;Прайс[#Заголовки];0);
ИНДЕКС (Прайс;Поиск_Товара;Поиск_Заголовка))
В итоговом вычислении в функции ИНДЕКС не будет большого числа скобок и точек с запятой — только имена; сначала — имя таблицы, как и раньше (Прайс), а далее — имена переменных, которые вычисляются на предыдущем шаге. В данном примере у нас нет ничего, что вычисляется несколько раз в формуле, мы лишь делаем ее более читабельной с помощью LET.
В следующем случае у нас есть фрагмент формулы, который вычисляется несколько — это функция ВПР, возвращающая число остатков с другого листа. Логика формулы следующая:
Если заказано больше товаров, чем остатков на складе, то возвращается текст «Не хватает» с недостающим количеством (Заказ — Остаток). Если остатков хватает, то возвращается текст «Останется на складе» и разница между остатками и заказанным количеством.
Получается довольно громоздкая формула, где трижды повторяется функция ВПР (то есть остаток на складе, который мы ищем этой функцией на другом листе) — она выделена серым в формуле:
=ЕСЛИ ([@Заказ]>ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0);
«Не хватает «&([@Заказ]-ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0));
"Останется на складе: «&(ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0)-[@Заказ]))
=LET (Поиск_Товара;ПОИСКПОЗ ([@Номенклатура];Прайс[Номенклатура];0);
Поиск_Заголовка;ПОИСКПОЗ (C$ 1;Прайс[#Заголовки];0);
ИНДЕКС (Прайс;Поиск_Товара;Поиск_Заголовка))
В итоговом вычислении в функции ИНДЕКС не будет большого числа скобок и точек с запятой — только имена; сначала — имя таблицы, как и раньше (Прайс), а далее — имена переменных, которые вычисляются на предыдущем шаге. В данном примере у нас нет ничего, что вычисляется несколько раз в формуле, мы лишь делаем ее более читабельной с помощью LET.
В следующем случае у нас есть фрагмент формулы, который вычисляется несколько — это функция ВПР, возвращающая число остатков с другого листа. Логика формулы следующая:
Если заказано больше товаров, чем остатков на складе, то возвращается текст «Не хватает» с недостающим количеством (Заказ — Остаток). Если остатков хватает, то возвращается текст «Останется на складе» и разница между остатками и заказанным количеством.
Получается довольно громоздкая формула, где трижды повторяется функция ВПР (то есть остаток на складе, который мы ищем этой функцией на другом листе) — она выделена серым в формуле:
=ЕСЛИ ([@Заказ]>ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0);
«Не хватает «&([@Заказ]-ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0));
"Останется на складе: «&(ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0)-[@Заказ]))
И если присвоить ей имя с помощью LET и дальше обращаться в формуле по этому имени — будет гораздо компактнее (и важно даже не количество символов в формуле, а простота восприятия):
=LET (Остаток;
ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0);
ЕСЛИ ([@Заказ]>Остаток; «Не хватает «&([@Заказ]-Остаток);
«Останется на складе: «&(Остаток-[@Заказ])))
=LET (Остаток;
ВПР ([@Номенклатура];Прайс[[Номенклатура]:[Остатки]];3;0);
ЕСЛИ ([@Заказ]>Остаток; «Не хватает «&([@Заказ]-Остаток);
«Останется на складе: «&(Остаток-[@Заказ])))
Комментарий в формуле
Еще LET можно использовать для создания комментариев. Задаем переменную (с любым названием) и присваиваем ей текстовое значение. А в вычислении не используем!
=LET (переменная; «комментарий»; [другие переменные для вычислений]; …; вычисление)
До появления LET использовали редко — скорее в качестве прикола — функцию Ч / N для комментариев.
Функция с очень коротким названием превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.
Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата.
Например:
=E2*15% + Ч ("Вычисляем комиссию менеджера как 15% от суммы сделки")
Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет.
Еще LET можно использовать для создания комментариев. Задаем переменную (с любым названием) и присваиваем ей текстовое значение. А в вычислении не используем!
=LET (переменная; «комментарий»; [другие переменные для вычислений]; …; вычисление)
До появления LET использовали редко — скорее в качестве прикола — функцию Ч / N для комментариев.
Функция с очень коротким названием превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.
Последним и можно воспользоваться, если очень хочется добавить в формулу текст без искажения результата.
Например:
=E2*15% + Ч ("Вычисляем комиссию менеджера как 15% от суммы сделки")
Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри функции текст есть, а к результату эта часть ничего не добавляет.
Итак, преимущества LET:
- Легче воспринимать формулу. Особенно спустя несколько недель или месяцев после того, как вы ее сочинили ;) Или если с вашей формулой имеют дело другие люди — тут ввод переменных и обращение к ним по имени тоже может сделать чтение формулы более простым.
- Вычисление быстрее работает (так как промежуточное выражение вычисляется только один раз).
- Экзотика, но почему нет: можно добавить комментарий к формуле.
