Функция 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

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

 

  1. Anton Bolbat says:

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

    • Ренат Шагабутдинов says:

      Да, без сортировки работать не будет. Функция в примере возвращает абсолютно все строки из исходного списка, в которых стоит статус “В редакции”. Кстати, если поменять в формуле “В редакции” на “вышла”, получим автоматом список уже вышедших книг. Формула будет работать и в случае наличия трех и более параметров, лишь бы данные были отсортированы.

      • Евгений says:

        недавно узнал о элитнейшей функции гугл-таблиц “Query”, с помощью которой можно построить запрос к массиву, если нужно выбрать все книги “в редакции”, например, то формула будет выглядеть так: query(a1:d50;”select A where D = ‘в редакции’ “) условия по-разному можно комбинировать, в общем очень полезная штуковина, в экселе это реализовано с помощью формул массива, но далеко не так удобно. салют 🙂

Оставьте комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

CAPTCHA image
*