Функция LET в Excel с примерами формул
В этом руководстве представлена новая функция Excel LET, которая делает сложные формулы более понятными и ускоряет вычисления.
Если вы работаете с длинными формулами в Excel, то наверняка знакомы с идеей именованных диапазонов, которые упрощают чтение сложных формул. А теперь Microsoft делает шаг вперед и позволяет присваивать имена вычислениям и значениям прямо в формуле. Если в вашей формуле используется одно и то же выражение несколько раз, вы можете позволить Excel вычислить его только один раз, сохранить результат в формуле и повторно использовать столько раз, сколько необходимо. Звучит захватывающе, не так ли?
Функция ПУСК Excel
Функция LET в Excel позволяет присваивать имена результатам вычислений и определять переменные внутри формулы, чтобы формула выглядела понятнее и работала быстрее.
По сути, концепция такая же, как и при присвоении имен ячейкам, диапазонам и формулам в диспетчере имен. Что отличает функцию LET, так это то, что объявленные имена существуют только в рамках заданной формулы и больше нигде.
Синтаксис
LET имеет следующий синтаксис и аргументы:
ПУСТЬ (имя1, имя_значение1, [name2], [name_value2]…, расчет)
Где:
- Name1 (обязательно) — первое имя для назначения. Оно должно начинаться с буквы.
- Имя_значение1 (обязательно) — значение или вычисление, присвоенное имени1.
- Имя2/имя_значение2 (необязательно) — второе имя и значение.
- Расчет (обязательно) — расчет, использующий присвоенные имена и значения.
Функция может обрабатывать до 126 пар имя/значение.
Примечание. Microsoft использует несколько иную синтаксическую нотацию:
LET(имя1, имя_значение1, вычисление_или_имя2, [name_value2, calculation_or_name3…])
Лично я нахожу это немного запутанным, поэтому привожу его в более привычной форме. По сути, они говорят об одном и том же, но по-разному.
Доступность
Функция ПУСТЬ доступна только в следующих версиях Excel:
- Excel 365 (для Windows и Mac)
- Excel 2021 (для Windows и Mac)
- Excel для Интернета
Основная формула LET
Если у вас еще нет четкого представления о функции, пример формулы LET в ее простейшей форме поможет сфокусироваться на главном.
Рассмотрим это простое выражение: x+y. Здесь у нас есть 2 переменные, Икс а также у. Присвоим значение 2 Иксзначение от 5 до у и умножить одно на другое:
=ПУСТЬ(х, 2, у, 5, х*у)
Введите эту формулу в ячейку, и в результате она вернет 10.
Вместо значений переменные могут быть присвоены ссылкам на ячейки, например Икс до В2 и у до В3:
=ПУСТЬ(х, В2, у, В3, х * у)
Чтобы упростить построение формулы, уже объявленные имена отображаются в раскрывающемся списке IntelliSense точно так же, как имена, определенные в диспетчере имен.
Глядя на приведенную выше формулу, вы можете задаться вопросом, какой смысл все усложнять? Почему бы просто не сказать:
=В2*В3
В данном конкретном случае обычное умножение, конечно, проще. Цель состоит в том, чтобы уточнить понятие. Когда дело доходит до более сложных формул с повторяющимися вычислениями, функция LET приобретает совершенно новую степень полезности, как показано в другие примеры.
Преимущества
Если вы все еще сомневаетесь, что LET станет достойным дополнением к вашему набору инструментов Excel, рассмотрите следующие преимущества:
Упрощенные формулы. Присвоение описательных имен переменным и промежуточным вычислениям облегчает понимание того, что на самом деле делает формула. Вам больше не нужно выяснять внутреннюю логику каждого конкретного выражения или ссылки в формуле.
Более быстрые расчеты. Когда одно и то же выражение повторяется в формуле несколько раз, Excel вычисляет его несколько раз. Когда выражение упоминается по имени, Excel вычисляет его только один раз. В результате весь рабочий лист пересчитывается намного быстрее. Это положительное влияние на производительность особенно заметно в случае длинных формул и огромных наборов данных.
Простое обслуживание. Использование имен для повторяющихся вычислений не только делает формулы более компактными, но и упрощает их обновление. Когда необходимы корректировки, вы вносите только одно изменение вместо того, чтобы обновлять одни и те же выражения несколько раз, что экономит время и уменьшает количество человеческих ошибок.
Примечания по использованию
Чтобы сократить время обучения и предотвратить распространенные ошибки, следуйте этим простым рекомендациям:
- Формула LET должна иметь нечетное количество аргументов — одну или несколько пар имя/значение и вычисление. Последний аргумент всегда должен быть вычислением, возвращающим окончательный результат.
- В своих формулах придерживайтесь тех же правил именования, что и в менеджере имен, т.е. начинайте с буквы, без пробелов, без знаков препинания и т. д.
- Во избежание путаницы избегайте объявления имен, которые уже используются в диспетчере имен. Если одно и то же имя назначено внутри LET и определено в диспетчере имен, версия диспетчера имен будет проигнорирована.
- Имена, объявленные с помощью LET, допустимы только в рамках этой конкретной формулы.
Как использовать функцию ПУСТЬ в Excel – примеры формул
А теперь пришло время взглянуть на более реалистичные варианты использования и раскрыть всю мощь новой функции.
Пример 1. Формула LET для сокращения повторных вычислений
При написании многоуровневых формул часто бывает, что одно и то же выражение или вычисление используется более одного раза. Типичным примером являются вложенные операторы IF. В этом контексте давайте посмотрим, как LET может упростить ситуацию.
Предположим, у вас есть результаты студенческих экзаменов по трем разным предметам (столбцы B, C и D). Вы хотите найти среднее значение для каждого учащегося и оценить его, как показано в таблице справа:
Функция AVERAGE может легко вычислить среднее арифметическое для каждой строки:
СРЕДНЕЕ(B2:D2)
Затем вы создаете вложенный оператор IF на основе вышеуказанных критериев.
=ЕСЛИ(СРЕДНИЙ(B2:D2)>249, “Отлично”, ЕСЛИ(СРЕДНИЙ(B2:D2)>=200, “Хорошо”, ЕСЛИ(СРЕДНИЙ(B2:D2)>150, “Удовлетворительно”, “Плохо” )))
Проблема в том, что одна и та же функция AVERAGE повторяется три раза. Ну, может, это и не проблема, а ненужное усложнение, заставляющее Excel трижды выполнять одно и то же вычисление.
Чтобы оптимизировать формулу, мы можем присвоить имя функции СРЗНАЧ (скажем, среднее) и замените функцию этим «локальным» именем. Таким образом, среднее значение вычисляется только один раз, что помогает формуле работать быстрее:
=LET(среднее, СРЕДНЕЕ(B2:D2), ЕСЛИ(среднее>249, “Отлично”, ЕСЛИ(среднее>=200, “Хорошо”, ЕСЛИ(среднее>150, “Удовлетворительно”, “Плохо”))))
Введите формулу в E2, перетащите ее вниз через E10, и вы получите такой результат:
Представьте, что вы добавляете еще один экзамен (столбец E) в исходную таблицу. Естественно, вы хотите включить это в среднее значение. Без LET вам пришлось бы выполнять эту настройку в логическом тесте каждой функции ЕСЛИ. Будучи человеком, вы можете что-то упустить из виду и не правильно обновить все ссылки. С помощью LET вы делаете всего одно изменение:
=LET(среднее, СРЕДНЕЕ(B2:E2), ЕСЛИ(среднее>249, “Отлично”, ЕСЛИ(среднее>=200, “Хорошо”, ЕСЛИ(среднее>150, “Удовлетворительно”, “Плохо”))))
Пример 2. LET с несколькими именами
Вот еще один пример, показывающий, как функция LET может упростить создание сложных формул.
Допустим, у вас есть список полных имен (столбец А), из которого вы хотите извлечь отчества. Приведенная ниже формула прекрасно справляется со своей задачей, но на первый взгляд ее логика довольно неясна:
=СРЕДН(A2, ПОИСК(” “, A2) + 1, ПОИСК(” “, A2, ПОИСК(” “, A2) +1) – ПОИСК(” “, A2) – 1)
Чтобы обеспечить некоторый значимый контекст для себя и других пользователей, вы можете определить пару имен, таких как:
- ФИО – А2
- пространство – ПОИСК(” “, ФИО)
Очевидно, что в ячейке А2 содержится полное имя, которое нужно разбить, а функция ПОИСК возвращает позицию первого пробела в имени.
А затем мы заменяем ссылку на ячейку и функцию ПОИСК объявленными именами.
=LET(ФИО, A2, пробел, ПОИСК(” “, ФИО), MID(ФИО, пробел + 1, ПОИСК(” “, ФИО, пробел + 1) – пробел – 1))
Если вы знакомы с синтаксисом MID, формулу становится намного легче понять:
К позиции первого пробела вы добавляете 1, чтобы начать извлечение со следующего символа (пробел+1). Чтобы получить длину отчества (т.е. сколько символов извлечь из полного имени), вы определяете позицию второго пробела, вложив одну функцию ПОИСК в другую, затем находите разницу между позициями двух пробелов и вычитаете 1 из результата, чтобы исключить завершающий пробел (SEARCH(” “, full_name, пробел+1) – пробел -1)).
Для лучшей читабельности вы можете деформировать имена и вычисления на отдельные строки, например:
Пример 3. Функция LET с динамическими массивами
В предыдущих примерах функция LET работала с одной ячейкой, и мы скопировали формулу в ячейки ниже. Однако LET также может принимать массивы в качестве входных данных и создавать массивы в качестве выходных данных. Это возможно благодаря новому механизму вычислений Excel 365 и динамическим массивам.
Из приведенной ниже таблицы предположим, что вы хотите получить список экзаменов, которые должны быть сданы в следующем году. н дней, не считая сегодняшнего. Это можно сделать с помощью функции ФИЛЬТР с несколькими критериями:
ФИЛЬТР(данные, (даты>сегодня) * (даты<=сегодня+n), "Нет результатов")
Все, что вам нужно сделать, это определить соответствующие имена:
- данные – А2:С19
- даты – С2: С19
- Cегодня – CЕГОДНЯ()
- н – F3
После этого поместите имена и формулу FILTER внутрь LET:
=LET(данные, A2:C19, даты, C2:C19, сегодня, СЕГОДНЯ(), n, F3, ФИЛЬТР(данные, (даты>сегодня) * (даты<=сегодня+n), "Нет результатов"))
Результатом является массив записей, соответствующих заданным критериям:
Функция Excel LET не работает
Если вы не можете найти функцию ПУСТЬ в своем Excel или ваша формула приводит к ошибке, это может произойти по следующим причинам:
В моем Excel нет функции LET
Функция LET доступна только в эти версии Excel. Эта функция не имеет обратной совместимости и не будет отображаться в более ранних версиях Excel.
#ИМЯ? ошибка
Поскольку вероятность того, что кто-то может опечататься в имени функции, очень мала, проблема, скорее всего, связана с назначенными именами. Пожалуйста, убедитесь, что вы указали каждое имя, используемое в расчете, и все имена написаны правильно.
Вот как вы можете упростить и ускорить свои расчеты с помощью функции LET. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Примеры формул Excel LET (файл .xlsx)
