Приветствуем, дорогие читатели! Знакома ли вам ситуация: вы открываете старый отчёт, видите формулу длиной в километр и понимаете, что проще переписать её заново, чем разобраться? Если да — эта статья для вас.
Сегодня мы говорим о функции LET — одном из самых мощных инструментов для оптимизации и отладки формул в современных версиях Excel.
Важно: Функция LET доступна в Excel 2021 и Excel 365. Если у вас более старая версия, вы не сможете ею воспользоваться.
Зачем нужна LET? Проблема "формул-монстров"
Представьте задачу: у нас есть таблица продаж (диапазон A2:A100). Нужно посчитать общую выручку и применить скидку 10%, если сумма больше 100 000 рублей.
Как обычно пишут формулу?
=ЕСЛИ(СУММ(A2:A100)>100000;СУММ(A2:A100)*0,9;СУММ(A2:A100))
В чем здесь проблема?
- Избыточность вычислений. Функция СУММ(A2:A100) выполняется три раза. Если данных много (например, 100 000 строк), Excel будет трижды перебирать одни и те же ячейки. Это замедляет работу файла.
- Сложность чтения. Глаз "спотыкается" об однотипные куски. Если в формуле потребуется изменить диапазон, это придется делать в трёх местах, что чревато ошибками.
- Отсутствие структуры. Формула плохо документирует сама себя. Непонятно, что мы считаем на промежуточных этапах.
Решение — LET
Функция LET позволяет присваивать имена (переменные) промежуточным расчётам и значениям. Синтаксис (для русскоязычной версии Excel) выглядит так:
=LET(Имя1; Значение1; [Имя2; Значение2]; ...; Вычисление)
Главное правило: последний аргумент — это то, что функция вернёт в ячейку.
Перепишем наш пример:
=LET(ОбщаяВыручка; СУММ(A2:A100); ЕСЛИ(ОбщаяВыручка>100000; ОбщаяВыручка*0,9; ОбщаяВыручка))
🔥 Что изменилось?
- Скорость: СУММ(A2:A100) вычисляется только один раз. Результат сохраняется в переменной ОбщаяВыручка, а затем просто подставляется в формулу.
- Читаемость: Формула теперь похожа на инструкцию: «Пусть ОбщаяВыручка равна сумме столбца. Если она больше 100 000, применить скидку, иначе оставить как есть».
- Удобство правки: Диапазон указан только один раз. Изменили его — и формула работает корректно во всех частях.
Пример 1. Расчет бонуса менеджера (Вложенная логика)
Задача: Менеджер получает 5% от продаж до 200 000 рублей и 10% от суммы превышения.
Как было бы без LET:
=ЕСЛИ(B2<=200000; B2*0,05; 200000*0,05 + (B2-200000)*0,1)
Формула короткая, но если мы захотим изменить порог (200 000) или процент, нам придется менять его в двух местах внутри ЕСЛИ. Это неудобно.
Как пишем с LET (профессиональный подход):
Мы вынесем все "настройки" в отдельные переменные в начале формулы.
=LET(Продажи; B2; Порог; 200000; ПроцентДоПорога; 0,05; ПроцентСверх; 0,1; БонусЗаПорог; Порог * ПроцентДоПорога; ЕСЛИ(Продажи <= Порог; Продажи * ПроцентДоПорога; БонусЗаПорог + (Продажи - Порог) * ПроцентСверх))
Согласитесь, даже если эта формула кажется длиннее, она намного понятнее.
- В самом начале мы видим "шапку" с входными данными: Порог, Процент.
- Легко изменить условия задачи. Нужно сделать порог 250 тыс.? Меняем одну цифру, а не копаемся в логике ЕСЛИ.
- Мы даже создали промежуточную переменную БонусЗаПорог, чтобы разделить логику на этапы.
Пример 2. Работа с датами и волатильность
Задача: Проверить, относится ли дата в ячейке A2 к текущему месяцу.
=LET(Сегодня; СЕГОДНЯ(); МесяцСейчас; МЕСЯЦ(Сегодня); ЕСЛИ(МЕСЯЦ(A2)=МесяцСейчас;"Да";"Нет"))
Важное примечание: Функция СЕГОДНЯ() — волатильная. Это значит, что она пересчитывается при любом изменении на листе. LET не делает её "спокойной". Однако, внутри одного пересчета этой конкретной формулы СЕГОДНЯ() вызовется строго один раз, и её значение будет использовано и для переменной Сегодня, и для вычисления месяца. Это защищает нас от случайных повторных вызовов одной и той же волатильной функции внутри формулы.
Пример 3. Продвинутый уровень: LET и динамические массивы
Этот пример показывает мощь LET в современных версиях Excel. Допустим, у нас есть список продаж с дубликатами, и мы хотим вывести уникальные значения и сразу посчитать их количество.
=LET(Данные; A2:A100; Уникальные; УНИК(Данные); ВЫБОР({1.2}; Уникальные; СЧЁТЕСЛИ(Данные; Уникальные)))
Разбор полётов:
- Данные — присвоили имя диапазону.
- Уникальные — сохранили результат функции УНИК (список уникальных значений).
- ВЫБОР({1.2}; ...) — использовали переменную Уникальные и сослались на исходные Данные, чтобы с помощью СЧЁТЕСЛИ посчитать напротив каждого уникального значения количество его повторений в исходном списке.
Этот пример показывает, как LET позволяет строить сложные, но при этом структурно понятные формулы, работающие с массивами данных.
Особенности и советы
- Синтаксис: В русском Excel разделителем аргументов служит точка с запятой (;). В английском — запятая (,). Имейте это в виду, если копируете формулы из иностранных источников.
- Правила именования:
Имя должно начинаться с буквы. Допустимы только буквы, цифры и символ подчеркивания (_). Пробелы и знаки препинания запрещены. Регистр букв не имеет значения (ВЫРУЧКА = выручка). - Локальность: Имена, созданные в LET, существуют только внутри этой формулы. Обратиться к переменной Продажи из другой ячейки невозможно.
- Масштабируемость: В одной формуле LET можно объявить до 126 пар "Имя-Значение". Это позволяет строить очень сложные вычисления, разбивая их на понятные шаги.
Заключение
Функция LET — это не просто "ещё одна новая функция". Это смена парадигмы мышления. Она заставляет нас думать структурно: разбивать задачу на этапы, давать им имена и собирать решение как конструктор.
Используйте LET, чтобы ваши коллеги (и вы сами через месяц) с легкостью понимали написанные вами формулы. Это верный знак высокого уровня мастерства работы в Excel!
Подписывайтесь на канал, впереди еще больше полезных инструментов для работы!