ДИНАМИЧЕСКИЕ ДИАПАЗОНЫ — OFFSET (СМЕЩ)
С помощью OFFSET можно обработать любой диапазон листа с любой шириной и высотой, причем каждый параметр может меняться в зависимости от каких-либо условий. Так что это одна из самых полезных и гибких формул Google Таблиц.
Синтаксис функции:
=OFFSET(адрес ячейки; число строк; число столбцов; [высота]; [ширина])
адрес ячейки — это левая верхняя ячейка того диапазона, который вы хотите вернуть с помощью формулы;
число строк — отступ вниз (или вверх при отрицательном аргументе) от ячейки, заданной в предыдущем аргументе;
число столбцов — отступ вправо (или влево при отрицательном аргументе) от ячейки, заданной в первом аргументе;
высота — высота итогового диапазона;
ширина — его ширина.
Формула кажется сложной, но несколько примеров помогут ее понять.
Итак, следующая формула задает диапазон A1:A9.
=OFFSET(A1;0;0;9;1)
=СМЕЩ(A1;0;0;9;1)
(Диапазон, начинающийся в ячейке A1, без отступов от нее, с высотой 9 и шириной 1.)
А если мы добавим отступ по столбцам и строкам (на 1 строку и на 1 столбец) и увеличим ширину до трех, получим диапазон B2:D10.
=OFFSET(A1;1;1;9;3)
=СМЕЩ(A1;1;1;9;3)
(Диапазон, начинающийся в B2 (на строку ниже A1 и на столбец правее), с высотой 9 и шириной 3.)
Как же сделать диапазон динамическим? Ведь простой диапазон B1:B9 мы можем задать традиционным образом.
Рассмотрим пример, в котором суммируем продажи за первые N месяцев, где N — параметр, который будет вводиться в ячейку:
Диапазон, включающий в себя данные по продажам за N месяцев, характеризуется следующими параметрами: он начинается в ячейке B2, у него ширина 1 и высота N. Введем эти параметры в OFFSET:
OFFSET(B2;0;0;E1;1)
СМЕЩ(B2;0;0;E1;1)
Далее эту функцию мы будем использовать в качестве аргумента SUM, чтобы получить сумму ячеек динамического диапазона:
=SUM(OFFSET(B2;0;0;E1;1))
=СУММ(СМЕЩ(B2;0;0;E1;1))
Теперь мы сможем менять количество месяцев в ячейке E1 и получать сумму за соответствующее количество месяцев:
А если мы хотим считать сумму за N месяцев, начиная с M-го месяца?
Тогда аргументом число_строк в функции OFFSET будет параметр M, мы лишь вычтем из него единицу, чтобы начинать с М-й строки, а не M + 1):
А сумма за последние N месяцев?
В таком случае диапазон будет начинаться, как и прежде, с B2 (первого месяца). Дальше мы рассчитаем, сколько строк нужно отступить от B2:
COUNT(B2:B)-E1
СЧЁТ(B2:B)-E1
COUNT(B2:B) возвращает количество чисел в столбце B, из которого мы вычитаем N — то, за сколько последних месяцев нам нужны данные.
Например, в столбце B 12 строк с данными и нам нужна сумма последних 4 месяцев. Этот фрагмент функции вернет 8 — именно на столько строк мы отступим от первой и будем суммировать с 9-й по 12-ю строки (последние 4 месяца, как и нужно).
Высота диапазона — E1 — количество месяцев N.
Формула целиком:
=SUM(OFFSET(B2;COUNT(B2:B)-E1;0;E1;1))
=СУММ(СМЕЩ(B2;СЧЁТ(B2:B)-E1;0;E1;1))
ДИНАМИЧЕСКАЯ ДИАГРАММА (СПАРКЛАЙН)
С помощью OFFSET (СМЕЩ) можно создать и динамический спарклайн — график, который будет изменяться при изменении параметров.
Для примера воспользуемся диапазоном, сформированным в предыдущем примере — с последними N месяцами продаж.
OFFSET(B2;COUNT(B2:B)-E1;0;E1;1)
СМЕЩ(B2;СЧЁТ(B2:B)-E1;0;E1;1)
Используем его как аргумент функции SPARKLINE (подробнее о ней смотрите в ).
=SPARKLINE(OFFSET(B2;COUNT(B2:B)-E1;0;E1;1);{"charttype" \ "column"})
=SPARKLINE(СМЕЩ(B2;СЧЁТ(B2:B)-E1;0;E1;1);{"charttype" \ "column"})
Теперь в ячейке с этой формулой будет отображаться диаграмма с динамикой продаж за последние N месяцев, где N берется из ячейки E1:
Поменяем число в ячейке E1:
Диаграмма изменится автоматически.
Еще один пример: диаграмма, у которой задается и протяженность (за сколько месяцев отображать данные), и точка отсчета (с какого месяца начинать отображение данных).
Диапазон будет задан иным образом: в качестве смещения по строкам используется месяц старта (из ячейки).
OFFSET(B2;E2-1;0;E1;1)
СМЕЩ(B2;E2-1;0;E1;1)
E2 — это ячейка с номером месяца, с которого нужно начать отображение.
Из нее мы вычитаем единицу (так как первую строку занимает название столбца):
Итак, мы получили настраиваемую диаграмму. Изменим параметры:
