Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В итоговом вычислении в функции ИНДЕКС не будет большого числа скобок и точек с запятой — только имена; сначала — имя таблицы, как и раньше (Прайс), а далее — имена переменных, которые вычисляются на предыдущем шаге. В данном примере у нас нет ничего, что вычисляется несколько раз в формуле, мы лишь делаем ее более читабельной с помощью LET.

В следующем случае у нас есть фрагмент формулы, который вычисляется несколько раз, — это функция ВПР, возвращающая число остатков с другого листа. Логика формулы следующая: «Если заказано больше товаров, чем остатков на складе, то возвращается текст “Не хватает” с недостающим количеством (Заказ — Остаток). Если остатков хватает, то возвращаются текст “Останется на складе” и разница между остатками и заказанным количеством».

Получается довольно громоздкая формула, где трижды повторяется функция ВПР (то есть остаток на складе, который мы ищем этой функцией на другом листе), — она выделена серым в формуле:

=ЕСЛИ([@Заказ]>ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0);

"Не хватает "&([@Заказ]-ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0));

"Останется на складе: "&(ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0)-[@Заказ]))

И если присвоить ей имя с помощью LET и дальше обращаться в формуле по этому имени — будет гораздо компактнее (и важно даже не количество символов в формуле, а простота восприятия):

=LET(Остаток;ВПР([@Номенклатура];Прайс[[Номенклатура]: [Остатки]];3;0); ЕСЛИ([@Заказ]>Остаток; "Не хватает "&([@Заказ]-Остаток); "Останется на складе: "&(Остаток-[@Заказ])))

Итак, преимущества LET:

• легче воспринимать формулу, особенно спустя несколько недель или месяцев после того, как вы ее сочинили; или если с вашей формулой имеют дело другие люди — тут ввод переменных и обращение к ним по имени тоже может сделать чтение формулы более простым;

• вычисление быстрее работает (так как промежуточное выражение вычисляется только один раз).

В Google Таблицах функция LET появилась как раз в процессе написания этой книги — в феврале 2023-го.

Функция LAMBDA и вспомогательные функции

Файл с примером: LAMBDA.xlsx

Если раньше в Excel нужно было создавать собственные пользовательские функции с помощью макросов (VBA), то теперь можно делать это функцией, не залезая в код, если в вашей версии появилась новая (можно сказать, революционная) функция LAMBDA. А появилась она в Microsoft 365 в 2020–2021 годах (в зависимости от пакета обновлений), ее нет в «коробочной» версии Excel 2021.

Функция LAMBDA появилась и в Google Таблицах в 2022 году.

Синтаксис у нее такой:

=LAMBDA([переменная]; …; [переменная]; формула)

Переменных может и не быть (хотя тогда LAMBDA не имеет особого смысла, можно просто присвоить имени Excel формулу без аргументов и вызывать ее по этому имени), может быть одна или несколько. В конце последним аргументом всегда будет формула с этими переменными.

Рассмотрим на простом примере с отклонением «факт — план». Допустим, план в столбце B, факт в столбце C. Обычная формула будет выглядеть так:

=C2 / B2 — 1

А в случае с LAMBDA мы указываем переменные и формулу в общем случае:

=LAMBDA(план; факт; факт / план — 1)

После чего можно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции, например “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую, можно создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).

Вся мощь LAMBDA раскрывается с ее вспомогательными функциями, которые позволяют обрабатывать все значения в массиве или все строки/столбцы в массиве и применять к ним одно и то же вычисление. То есть теперь можно посчитать, например, среднее значение по каждой строке одной формулой. Или собирать данные с разных листов одной формулой, притом что этот список листов будет меняться. Давайте рассмотрим такие примеры применения LAMBDA со вспомогательными функциями.

ФУНКЦИЯ MAP: ОБРАБАТЫВАЕМ КАЖДЫЙ ЭЛЕМЕНТ МАССИВА

Файл с примером: LAMBDA.xlsx