Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Функция ПОСЛЕД / SEQUENCE
Дальше: Функция LET

Функция СМЕЩ / OFFSET

Файл с примерами: СМЕЩ.xlsx
Функция СМЕЩ / OFFSET позволяет формировать ссылку на диапазон, описываемый не точным адресом (A1:B10, например), а параметрами — первой ячейкой диапазона, отступом от нее, высотой и шириной.
Вообще говоря, любой диапазон в Excel можно задать тремя параметрами: первой (левой верхней) ячейкой, высотой и шириной. Например, упомянутый A1:B10 — это диапазон с первой ячейкой A1, шириной в 2 столбца и высотой в 10 строк. Зачем еще и отступ в функции СМЕЩ? Чтобы была возможность менять не только ширину и высоту столбца, но и первую ячейку диапазона (и все это — не меняя саму формулу, а только параметры функции СМЕЩ).
Функция СМЕЩ / OFFSET не новая — она есть во всех версиях Excel и в Google Таблицах.
Зачем вообще все это нужно, когда можно просто ссылаться на диапазон в привычной манере? Дело в том, что параметры функции СМЕЩ можно менять, например задавая их в отдельных ячейках или вычисляя с помощью других функций (например, задавая диапазон равным числу прошедших в текущем году месяцев, чтобы не менять формулу каждый месяц).
Аргументы функции СМЕЩ:
=СМЕЩ (ссылка; строка;столбец; высота;ширина)

 

 

Рассмотрим несколько примеров, как определенные параметры СМЕЩ соответствуют диапазону на листе:
=СМЕЩ(B1;0;0;9;1)
• Начало — в ячейке B1.
• Отступ от нее:

 

ноль по строкам;

 

ноль по столбцам.
• Высота — 9.
• Ширина — 1.

 

 

=СМЕЩ(A1;1;1;9;2)
• Начало — в ячейке A1.
• Отступ от нее:

 

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

 

вправо на 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)))
Назад: Функция ПОСЛЕД / SEQUENCE
Дальше: Функция LET