Содержание

Упрощение формул 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 возвращают число совпадений на разных позициях. Вычисление выводит ответ. Вуаля!

Литература

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

[expert_bq id=»1570″]Для этого перед С2 в алгоритме прописываем знак , указать его необходимо как перед буквой, так и перед цифрой в обозначении. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если слагаемых, которые идут в одной строке (столбце), довольно много, например 10, формулу можно упростить, указав диапазон координат. В итоге формула будет выглядеть следующим образом: =СУММ(А1:А10). В качестве ячейки для вывода искомого результата, выберем ячейку В6. В итоге у нас получится результат, показанный на скриншоте.

Заполнение сложных формул в excel. Создание сложных формул в Microsoft Excel

= («знак равно») – равенство, равно. Обычно отвечает за начало формулы;
+ («плюс») – сложение. Может складывать как числа, так и выражения;
— («минус») – вычитание;
* («звёздочка») – умножение;
/ («наклонная вправо») – делит;
^ («циркумфлекс») – возводит в степень.

Excel — это сетка ячеек, а столбцы идут слева направо, каждая назначена на букву, а строки пронумерованы. Каждая ячейка является пересечением строки и столбца. Например, ячейка, где пересекаются столбцы A и строка 3, называется A3.

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции