Функция 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 нужно брать какой-нибудь ячейки.

Рубрики:
Комментарии
2 Цепочка комментария
2 Ответы по цепочке
0 Последователи
 
Популярнейший комментарий
Цепочка актуального комментария
4 Авторы комментариев
Anton Bolbat

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

Korvelle

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

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