Функция OFFSET (СМЕЩ) в Google Таблицах

preview

Мощнейшая функция СМЕЩ (OFFSET) позволяет решать много задач в Excel и Google Таблицах. В Excel, например, можно создавать динамические диаграммы, в которых меняется масштаб и точка  отсчета. А в Google Таблицах — получать из диапазона только определенную часть данных в зависимости от выполнения определенного условия. Например, получать из списка всех книг — как вышедших в продажу, так и готовящихся, только те, что еще не вышли. Причем тот список, что вы получите, будет обновляться автоматически при обновлении исходных данных. Пример — в этой короткой статье. Конечно, его можно применить в любой отрасли, а не только с книгами 🙂

Допустим, у вас есть список книг, часть из которых уже вышла (данные о продажах в примере вымышленные), часть — в редакции:

1

Задача — на другом листе (вкладке) или просто в другом месте сформировать автоматически меняющийся список только тех книг, которые еще не вышли (со статусом «В редакции»). Для этого и пригодится функция OFFSET. Ее синтаксис:

=OFFSET (адрес_ячейки;число_строк;число_столбцов;[высота];[ширина]).

Адрес ячейки — это левая верхняя ячейка того диапазона, который вы хотите вернуть с помощью формулы.

Число_строк — отступ от ячейки вниз (или вверх при отрицательном аргументе), заданной в предыдущем аргументе;

Число_столбцов — отступ от ячейки вправо (или влево при отрицательном аргументе), заданной в первом аргументе;

Высота — высота итогового диапазона;

Ширина — его ширина.

Формула кажется сложной, но несколько примеров помогут ее понять.

Итак, следующая формула задает диапазон A1:A9.

=OFFSET(A1;0;0;9;1 ) — диапазон, начинающийся в A1, без отступов от нее, с высотой 9 и шириной 1.

2

А если мы добавим отступ по столбцам и строкам (на 1 строку и на 1 столбец) и увеличим ширину до трех, получим диапазон

=OFFSET(A1;1;1;9;3 ) — диапазон, начинающийся в B2 (на строку ниже A1 и на столбец правее) с высотой 9 и шириной 3.

3

А как же сделать диапазон динамическим? Как получить только строки по книгам, находящимся в редакции?

Идем по порядку: очевидно, точкой отсчета может быть ячейка A1 (от нее будем уходить вниз до первой книги, которая еще не в работе), а ширина диапазона — 4 (столько у нас столбцов с данными). Отступать по столбцам вправо/влево смысла нет — ширина диапазона фиксированная, отступ равен нулю. Осталось два неизвестных значения 🙂

=OFFSET(A1;?;0;?;4 )

Раз мы хотим получить данные по книгам, которые находятся в редакции — то количество строк должно совпадать с количеством таких книг, верно? Его можно вычислить с помощью функции COUNTIF (СЧЕТЕСЛИ в Excel):

=COUNTIF(D:D;»В редакции»)

Формула подсчитывает количество вхождения фразы «В редакции» в столбце D.

А какой должен быть отступ по строкам? Мы должны найти, где начинаются книги «В редакции». Это можно сделать с помощью функции MATCH (ПОИСКПОЗ в Excel):

=MATCH(«В редакции»;D:D;0)

Эта формула определяет, в какой строке впервые в столбце D встречается фраза «В редакции». В нашем примере это 14. Но так как функция OFFSET начинает отсчет с ячейки A1, нам нужно из 14 вычесть единицу: отступив на 13 строк от A1, мы и попадем в четырнадцатую строку — ту, где начинается список книг в редакции.

Вот так это выглядит вместе:

=OFFSET(A1;MATCH(«В редакции»;D:D;0)-1;0;COUNTIF(D:D;»В редакции»);4)

 

4

Весь сыр-бор, конечно, ради автоматического обновления полученного списка при изменении исходного:

1

с помощью OFFSET можно скопировать любой диапазон листа с любой шириной и высотой, причем каждый параметр можно менять в зависимости от каких-либо условий. Так что это одна из самых полезных и гибких функций Google Таблиц.

Например, можно суммировать данные за N месяцев, меняя N в какой-то ячейке.
= SUM(OFFSET(A1;0;0;N;1))
Такая формула будет суммировать N ячеек, начиная с A1. N нужно брать какой-нибудь ячейки.

Рубрики:
Комментарии

Отправить ответ

Я так понимаю, функция будет работать, если данные отсортированы (в примере) по столбцу статус. И она возвращает те строки, которые после найденного совпадения (последнее «в редакции»)?

В данном случае, как мне кажется, проще решить задачу через использование функции filter. При этом предварительная сортировка не потребуется.

Темы, на которые я пишу: