Упрощение формул Excel путем именования фрагментов с помощью функции LET

Функция LET появилась в версии Office 365 (по подписке) и будет доступна, начиная с версии Office 2021. Функция LET позволяет присваивать имена фрагментам формулы, а затем использовать эти имена в вычислениях. Формулы становятся короче и лучше читаемы. Это похоже на использование имен ячеек, диапазонов и формул в диспетчере имен. Но имена, присвоенные функцией LET, действуют только в пределах указанной формулы, и нигде больше. При использовании в русском Office имя функции сохраняется (не переводится на русский язык).

Синтаксис

Рис. 1. Синтаксис функции LET

Скачать заметку в формате Word или pdf, примеры в формате Excel

Более строго, синтаксис функции LET:

Имя1 – обязательный аргумент, должен начинаться с буквы или нижнего подчеркивания. Значение_имени1 – обязательный аргумент, содержит значение для Имя1. имя2/значение_имени2 – необязательные аргументы; всего может быть не более 126 таких пар. Вычисление – обязательный аргумент, формула, использующая имена.

В простейшем случае:

Рис. 2. Возведение в степень с помощью функции LET

Внутри функции LET определено имя х и его значение 3, а также имя y со значением 4. С этими именами выполняется действие x^y. Этот пример поясняет базовые возможности функции LET. Для подобных простых вычислений использование LET не оправдано. Поэтому обратимся к более сложным формулам, в которых использование LET будет полезным.

Функция LET устраняет дублирование фрагментов формулы

Допустим, вам нужно вывести все рабочие дни между двумя датами, хранящимися в ячейках листа. Функция LET присвоит имя исходному массиву дат, а затем использует это имя в функциях ФИЛЬТР() и ДЕНЬНЕД():

Если вы не знакомы с функциями динамических массивов, вам для начала сюда))

Рис. 3. Упрощение формулы за счет именования ее фрагмента

Первый аргумент функции LET – даты – вводит имя внутри формулы. Второй аргумент функции LET задает массив дат: ПОСЛЕД(C5-C4+1;;C4). Этот массив включает на одно значение больше, чем разность в ячейках С5 и С4, начинается с С4 с шагом 1.

Третий аргумент функции LET осуществляет вычисления с массивом дат: ФИЛЬТР(даты;ДЕНЬНЕД(даты;2)<6). А именно, фильтрует массив дат, оставляя только даты, для которых ДЕНЬНЕД() меньше 6, т.е., исключает выходные дни.

Без функции LET формула могла выглядеть так:

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

Функция LET дает три существенных преимущества

Простота. Использование имен для повторяющихся фрагментов упрощает формулы и облегчает их редактирование. Если требуется, вы вносите одно изменение, а не несколько. Это быстрее и снижает вероятность ошибки.

Ясность. Использование говорящих имен облегчает понимание того, как работает формула. Человеку, написавшему формулу и использующему её, гораздо проще дешифровать внутреннюю структуру записи.

Производительность. Удаление дублирования сокращает время вычислений. Это незаметно в обычных книгах Excel, но очень важно в моделях с миллионами записей.

Использование LET во вложенных конструкциях ЕСЛИ

Допустим у вас есть результаты трех тестов по трем отдельным предметам (столбцы В, C и D). Вам нужно рассчитать среднее значение для каждого человека и выставить оценку в соответствии с критерием в правой таблице:

Рис. 4. Результаты тестов для оценки

Без функции LET вы, скорее всего, выставите оценку с помощью вложенных ЕСЛИ:

Рис. 5. Оценка с помощью функций ЕСЛИ

Проблема в том, что СРЗНАЧ(B2:D2) рассчитывается трижды. Чтобы сделать формулу более эффективной (и наглядной) мы можем присвоить среднему значению имя:

Рис. 6. Выставление оценки с использованием LET

Если вам понадобится добавить еще один экзамен, то вы поправите формулу лишь в одном месте:

Рис. 7. Добавление еще одного экзамена требует лишь одного изменения в формуле

Без LET вам пришлось бы вносить изменения в трех местах.

Использование нескольких имен в функции LET

Допустим у вас есть ФИО, и нужно извлечь имя. Следующая формула справляется с этой задачей, но понять, как она работает совсем не просто:

Рис. 8. Извлечение имени из ФИО

Чтобы работа формулы стала более понятной, можно ввести несколько имен:

• ФИО = А2
• пробел1 = ПОИСК(" ";A2)
• пробел2 = ПОИСК(" ";A2;ПОИСК(" ";A2)+1)

Теперь формула стала намного понятнее:

Рис. 9. Та же формула, но с использованием имен

Функция LET и динамические массивы

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

Предположим, в одной таблице у вас есть график экзаменов (см. рис. 10, диапазон А2:С21), а во второй таблице вы хотите вывести только ту часть графика, которая приходится на n ближайших дней, не включая сегодняшний. Да к тому же отсортировать список по фамилиям по алфавиту. Определите имена внутри функции LET, и используйте функции динамических массивов СОРТ и ФИЛЬТР. Результатом будет массив записей в диапазоне Е6:G10.

Рис. 10. Использование LET с динамическими массивами

Поскольку даты экзаменов я задал в ячейке С2 случайной функцией СЛМАССИВ, нажимая F9 вы будете изменять даты в С2:С21. Соответственно будет меняться таблица, начинающаяся в Е6.

Извлечение предпоследнего слова из текстовой строки

Допустим у вас есть серия фраз, из которых нужно извлечь предпоследнее слово. В принципе понятно, как найти решение, но также ясно, что оно будет не простым, и основано на целом ряде шагов. Можно, например:

• сжать пробелы (вдруг между какими-то словами используется более одного пробела);
• подсчитать длину исходной фразы;
• длину фразы с удаленными пробелами;
• узнать количество слов во фразе;
• заменить пробел перед предпоследним словом на знак карет ^;
• выделить часть фразы после карет ^;
• найти позицию пробела между оставшимися двумя словами;
• выделить левое слово.

Рис. 11. Найти предпоследнее слово в пять шагов

Поскольку часто хотят получить ответ в одной ячейке, указанные выше пять формул можно объединить в одну. Для этого начните с формулы в ячейке F2:

Вместо ссылки на Е2 дважды подставьте формулу из Е2 (без знака равно). Получится:

Теперь выделите формулу в D2 и подставьте ее содержимое четыре раза вместо ссылки на D2. Если вы дойдете до конца, то получите формулу, ссылающуюся только на А2:

Рис. 12. Объединенная формула в одной ячейке

Вот именно в таких задачах выгода от использования функции LET становится очевидной. Для начала вы дадите имя переменной для формулы из B2 (см. рис. 11), затем, используя это первое имя, поименуете формулу в ячейке С3, и т.д., двигаясь теперь слева направо. Вы можете провести подготовительную работу в таблице…

Рис. 13. Имена и значения

… а затем подставить эти имена и формулы в функцию LET:

Рис. 14. Функция LET облегчает написание и понимание формулы

Чтобы формула с LET лучше читалась, используйте Alt+Enter для перехода к новой строке в строке формул после каждой пары аргументов.

Расчет ответа в игре Быки и коровы

Кратко напомню правила. Играют двое. Каждый задумывает и записывает тайное 4-значное число с неповторяющимися цифрами (ноль может быть первым). Игрок, который начинает игру по жребию, делает попытку отгадать число. Попытка — это 4-значное число с неповторяющимися цифрами, сообщаемое противнику в виде вопроса. Противник говорит в ответ, сколько цифр угадано с совпадением их позиций в тайном числе и сколько угадано без совпадения. Например: задумано тайное число 3219; попытка (вопрос) 2310; результат (ответ): один «бык» (цифра 1 из вопроса входит в тайное число и стоит на своем месте) и две «коровы» (цифры 2 и 3 из вопроса входят в тайное число, но стоят не на своем месте). Ответ сообщается в виде 2-значного числа. В нашем примере ответ – 12 (один «бык», две «коровы»). Игроки делают попытки по очереди. Побеждает тот, кто первым получит на свой вопрос ответ 40.

Около 10 лет назад я написал заметку на эту тему и для нахождения ответов использовал довольно сложную формулу:

Рис. 15. Формула для получения ответа в игре Быки и коровы

Тогда я использовал механизм объединения нескольких формул в ряде ячеек в одну сложную формулу. Промежуточные вычисления у меня не сохранились, так что сейчас мне было бы довольно непросто понять, как работает формула. За 10 лет изменился Excel и мои знания формул, так что сегодня эту задачу я бы решал иначе.

Итак, нам нужно сравнить два 4-значных числа, и подсчитать, сколько цифр совпадает в них с точностью до позиции и сколько просто совпадает:

Рис. 16. Формула для получения ответа в игре Быки и коровы с использованием LET

Первые четыре переменные извлекают цифры из проверяемого числа. Вторые четыре переменные – из числа-вопроса. Имя Бык возвращает количество совпадающих цифр с точностью до позиции. Кор1, … Кор4 возвращают число совпадений на разных позициях. Вычисление выводит ответ. Вуаля!

Литература

Baxter Floyd. Excel 2021. A Complete Guide About Excel 2021 With All-in-One Approach For Beginners

Bill Jelen. MrExcel 2021 – Unmasking Excel. Holy Macro! Books. 2021

Разработка оптимальной стратегии игры «Быки и коровы» на основе теории информации