Функция 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.
Функция ПУСК Excel

Вместо значений переменные могут быть присвоены ссылкам на ячейки, например Икс до В2 и у до В3:

=ПУСТЬ(х, В2, у, В3, х * у)
Основная формула LET

Чтобы упростить построение формулы, уже объявленные имена отображаются в раскрывающемся списке IntelliSense точно так же, как имена, определенные в диспетчере имен.
Написание формулы LET в Excel

Глядя на приведенную выше формулу, вы можете задаться вопросом, какой смысл все усложнять? Почему бы просто не сказать:

=В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, и вы получите такой результат:

Формула LET с вложенными ЕСЛИ

Представьте, что вы добавляете еще один экзамен (столбец E) в исходную таблицу. Естественно, вы хотите включить это в среднее значение. Без LET вам пришлось бы выполнять эту настройку в логическом тесте каждой функции ЕСЛИ. Будучи человеком, вы можете что-то упустить из виду и не правильно обновить все ссылки. С помощью LET вы делаете всего одно изменение:

=LET(среднее, СРЕДНЕЕ(B2:E2), ЕСЛИ(среднее>249, «Отлично», ЕСЛИ(среднее>=200, «Хорошо», ЕСЛИ(среднее>150, «Удовлетворительно», «Плохо»))))
Обновление формулы LET

Пример 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)).

Для лучшей читабельности вы можете деформировать имена и вычисления на отдельные строки, например:
LET формула с несколькими именами

Пример 3. Функция LET с динамическими массивами

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

Из приведенной ниже таблицы предположим, что вы хотите получить список экзаменов, которые должны быть сданы в следующем году. н дней, не считая сегодняшнего. Это можно сделать с помощью функции ФИЛЬТР с несколькими критериями:

ФИЛЬТР(данные, (даты>сегодня) * (даты<=сегодня+n), "Нет результатов")

Все, что вам нужно сделать, это определить соответствующие имена:

  • данные — А2:С19
  • даты — С2: С19
  • Cегодня — CЕГОДНЯ()
  • н — F3

После этого поместите имена и формулу FILTER внутрь LET:

=LET(данные, A2:C19, даты, C2:C19, сегодня, СЕГОДНЯ(), n, F3, ФИЛЬТР(данные, (даты>сегодня) * (даты<=сегодня+n), "Нет результатов"))

Результатом является массив записей, соответствующих заданным критериям:
Использование функции LET с динамическими массивами

Функция Excel LET не работает

Если вы не можете найти функцию ПУСТЬ в своем Excel или ваша формула приводит к ошибке, это может произойти по следующим причинам:

В моем Excel нет функции LET

Функция LET доступна только в эти версии Excel. Эта функция не имеет обратной совместимости и не будет отображаться в более ранних версиях Excel.

#ИМЯ? ошибка

Поскольку вероятность того, что кто-то может опечататься в имени функции, очень мала, проблема, скорее всего, связана с назначенными именами. Пожалуйста, убедитесь, что вы указали каждое имя, используемое в расчете, и все имена написаны правильно.

Вот как вы можете упростить и ускорить свои расчеты с помощью функции LET. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Практическая рабочая тетрадь для скачивания

Примеры формул Excel LET (файл .xlsx)

Вас также могут заинтересовать:

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *