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

В этом примере мы вычисляем среднее значение чисел из диапазона, заданного не обычной ссылкой, а функцией СМЕЩ (цветом выделены ячейки, соответствующие заданным параметрам функции: высота 12, ширина 1, начало в B2 — оно, начало, получено путем отступа на строку и на столбец от A1). Функция СМЕЩ тут выступает аргументом другой функции — СРЗНАЧ / AVERAGE, предоставляя ей диапазон для вычисления.

Параметры для СМЕЩ из ячеек

СМЕЩ с константами в качестве аргументов не имеет большого смысла — все примеры выше были приведены для демонстрации синтаксиса функции. На практике имеет смысл брать параметры из ячеек или вычислять с помощью других формул, чтобы была возможность формировать динамически изменяемый диапазон, не трогая саму формулу.

Например, в следующей функции СМЕЩ высота диапазона задается числом в ячейке F1.

И мы можем полученный диапазон использовать как аргумент другой функции, например СРЗНАЧ / AVERAGE.

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

Бывают ситуации, когда параметр для СМЕЩ задается не в ячейке и не меняется пользователем, а определяется расчетно.

Например, если нам нужно в течение года вычислять сумму нарастающего итога, чтобы сравнивать накопленный результат за прошедшие месяцы с аналогичным результатом прошлого года, можно отталкиваться от текущей даты и ее месяца, чтобы динамически задавать ширину диапазона.

Количество полных прошедших месяцев можно определить, например, так:

=МЕСЯЦ(СЕГОДНЯ())-1

(вычитаем из номера текущего месяца единицу).

А затем использовать полученное число в качестве параметра СМЕЩ, чтобы суммировать не все данные прошлого года, а столько месяцев, сколько прошло в текущем:

=СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1)

И просуммировать полученный диапазон:

=СУММ(СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1))

В такой ситуации стоит предусмотреть ситуацию, когда текущий месяц — январь. Это первый месяц, и часть нашей функции — МЕСЯЦ(СЕГОДНЯ())-1 — будет возвращать ноль, а вся конструкция — ошибку. Можно перехватить эту ситуацию с помощью функции ЕСЛИ и в случае января возвращать сумму за весь год:

=ЕСЛИ(МЕСЯЦ(СЕГОДНЯ())=1;

СУММ(B2:M2);

СУММ(СМЕЩ(B2;0;0;1;МЕСЯЦ(СЕГОДНЯ())-1)))

Функция LET

Файлы с примерами:

В ситуациях, когда в формуле приходится использовать какой-то промежуточный результат много раз, пригодится новая функция LET (Excel 2021 или Microsoft 365).

Синтаксис функции: несколько пар аргументов, в которых вы задаете в первом аргументе переменную, а во втором — выражение для нее.

Давайте посмотрим на совсем простой пример: зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET.

В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a.

Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз, или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.

Давайте посмотрим на пример. Допустим, у нас есть формула для поиска по названию товара и заголовку — функция ИНДЕКС / INDEX, аргументами которой выступают две функции ПОИСКПОЗ / MATCH. Мы рассматривали такой пример выше — см. «Двумерный поиск: ИНДЕКС + ПОИСКПОЗ».

С функцией LET ее можно переписать так:

=LET(Поиск_Товара;ПОИСКПОЗ([@Номенклатура];Прайс[Номенклатура];0);

Поиск_Заголовка;ПОИСКПОЗ(C$1;Прайс[#Заголовки];0);

ИНДЕКС(Прайс;Поиск_Товара;Поиск_Заголовка))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рассмотрим на простом примере с