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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= ПСТР ( A2 ; ПОИСК ( » » ; A2 ) + 1 ; ПОИСК ( » » ; A2 ; ПОИСК ( » » ; A2 ) + 1 ) — ПОИСК ( » » ; A2 ) — 1 )

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

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

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

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

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

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

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

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

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

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

= ЛЕВСИМВ ( ПСТР ( D2 ; НАЙТИ ( «^» ; D2 ) + 1 ; 50 ) ; НАЙТИ ( » » ; ПСТР ( D2 ; НАЙТИ ( «^» ; D2 ) + 1 ; 50 ) ) — 1 )

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

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

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

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

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

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

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

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

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

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

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

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

Литература

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

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

10 наиболее полезных функций при анализе данных в Excel — ExcelGuide: Про Excel и не только

• максимальные и минимальные значения;
• средние показатели;
• проценты;
• критерий Стьюдента и многое другое.

• Кроме того, в Excel отображаются различные текстовые сообщения, которые зависят непосредственно от результатов расчетов.
• Главным преимуществом программы является способность преобразовывать числовые значения и создавать альтернативные варианты, сценарии, в которых осуществляется моментальный расчет результатов.
• При этом необходимость вводить дополнительные данные и параметры отпадает.

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