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

вправо на 1 столбец.

• Высота — 9.

• Ширина — 2.

=СМЕЩ(A1;1;1;12;1)

• Начало — в ячейке A1.

• Отступ от нее:

вниз на 1 строку;

вправо на 1 столбец.

• Высота — 12.

• Ширина — 1.

СМЕЩ как аргумент других функций

СМЕЩ будет возвращать массив только в Microsoft 365 / Excel 2021, где появились динамические массивы. В прошлых версиях функция не работает самостоятельно, но это обычно и не нужно: функция формирует диапазон, который потом используется в других функциях как аргумент. Например, для расчета суммы или среднего. То есть мы не выводим диапазон в ячейке листа, а используем для дальнейших вычислений.

В этом примере мы вычисляем среднее значение чисел из диапазона, заданного не обычной ссылкой, а функцией СМЕЩ (цветом выделены ячейки, соответствующие заданным параметрам функции: высота 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.xlsx

Функция LET + ВПР.xlsx

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

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

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

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

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

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

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

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

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

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