Функции и формулы

Как использовать функцию СМЕЩЕНИЯ (OFFSET) в Google Таблицах

Функция «СМЕЩЕНИЕ» в Google Таблицах используется недостаточно часто. Это происходит главным образом потому, что многие люди не понимают цели этой функции или того, как ее можно эффективно использовать. В этом руководстве мы поможем вам понять, что делает функция OFFSET, ее синтаксис, способы использования и несколько областей, в которых она может быть полезна.

Что делает функция смещения в Google Таблицах?

Функция СМЕЩЕНИЕ в основном возвращает ссылку на диапазон ячеек, которые находятся на указанное количество ячеек от данной ячейки. Другими словами, если вы хотите, чтобы Google Таблицы выбирали диапазон ячеек, который, скажем, находится в 5 строках, на 2 столбца от ячейки, вы можете использовать функцию СМЕЩЕНИЕ.

Синтаксис функции OFFSET

Синтаксис функции СМЕЩЕНИЕ (OFFSET) следующий:

OFFSET(reference, rows, cols, [height], [width])

Здесь,

  • reference — это ссылочная ячейка, на которой вы хотите основать смещение. Это может быть ссылка на ячейку или диапазон соседних ячеек.
  • rows — это количество строк, которое вы хотите сместить относительно ссылочной ячейки.
  • cols — это количество столбцов, которое вы хотите сместить относительно ссылочной ячейки.
  • height — это высота (количество строк), которую вы хотите иметь в возвращаемом диапазоне ячеек. Это необязательный параметр.
  • width — это ширина (количество столбцов), которую вы хотите иметь в возвращаемом диапазоне ячеек. Этот параметр также не является обязательным.

Итак, если вы хотите сослаться на ячейку, которая находится в 5 строках, на 3 столбца от ячейки A1 в наборе данных, показанном ниже, и вы хотите, чтобы возвращаемый диапазон составлял 3 строки в высоту и 2 строки в ширину, ваша формула СМЕЩЕНИЯ будет:

=OFFSET(A1,5,3,3,2)

Мы отметили возвращаемый диапазон ячеек красным прямоугольником на изображении ниже:

Примеры использования функции «СМЕЩЕНИЕ» в Google Таблицах

Чтобы понять, как использовать функцию СМЕЩЕНИЕ, давайте рассмотрим несколько простых примеров функции OFFSET с разными значениями параметров. Рассмотрим набор данных, показанный ниже:

Пример 1:

Если вы хотите получить значение на 2 строки и 3 столбца от ячейки A1, вы можете использовать следующую формулу:

=OFFSET(A1, 2, 3)

Обратите внимание, что мы не указали здесь высоту и значение столбца, поэтому по умолчанию предполагается, что и высота, и ширина равны 1, а функция возвращает одно значение — 893,21.

Пример 2:

Допустим, вам нужно последнее значение мая месяца, начиная с ячейки A1. В этом случае вы можете использовать формулу:

=OFFSET(A1, 5,4)

Пример 3:

Если вы хотите получить доступ к ячейке в том же столбце, но в другой строке, просто установите для второго параметра функции OFFSET значение 0. Например, если вы хотите получить доступ к третьему значению 2018 года, начиная с ячейки C1, вы можете используйте формулу:

=OFFSET(C1, 3,0)

Вот скриншот результатов применения рассмотренных до сих пор примеров:

Пример 4:

Если вы хотите получить доступ к последним 2 ячейкам, начиная с ячейки A1 (ячейки, соответствующие июню и июлю 2019 и 2020 годов, вы можете использовать формулу:

=OFFSET(A1,6,3,2,2)

Здесь мы указали ширину и высоту возвращаемой ссылки как 2. В ответ мы получаем массив 2 x 2.

Если вы обернете это другой функцией, вы сможете выполнять дальнейшие операции с этим диапазоном ячеек.

Например, вы можете обернуть функцию СУММ вокруг этой функции СМЕЩЕНИЕ, чтобы получить сумму значений в возвращаемом диапазоне:

=SUM(OFFSET(A1,6,3,2,2))

Обратите внимание, что функция СМЕЩЕНИЕ просто извлекает диапазон значений. Таким образом, любые операции, выполняемые с возвращенными ссылками на ячейки, не вносят никаких изменений в исходные значения по этим адресам ячеек.

Зачем использовать функцию смещения в Google Таблицах?

Возникает очевидный вопрос: зачем нам функция OFFSET, если мы можем просто ссылаться на ячейки напрямую, используя их ссылки на ячейки?

Что ж, хотя прямые ссылки на ячейки отлично работают в большинстве случаев, могут быть моменты, когда вам может потребоваться получить динамический доступ к определенным диапазонам ячеек.

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

Давайте посмотрим на некоторые области, в которых функция СМЕЩЕНИЕ может применяться на практике:

Использование функции OFFSET в Google Таблицах для получения динамического доступа к диапазонам ячеек

Рассмотрим набор данных ниже:

Функция СУММ в правой части последней строки учитывает все значения за июль с 2017 по 2020 годы.

Теперь посмотрим, что произойдет, если мы попытаемся добавить новый столбец до 2017 года:

Изменение не отражается в сумме, поскольку не учитывает этот новый столбец, который был добавлен. В сумме по-прежнему учитывается только диапазон от C8 до F8. Это означает, что каждый раз, когда мы добавляем новый столбец слева от первого столбца, нам нужно явно изменять диапазон в функции СУММ.

Это может стать непрактичным, если вам нужно часто добавлять новый столбец. Чтобы обойти эту проблему, мы можем обернуть функцию СУММ вокруг функции СМЕЩЕНИЕ следующим образом:

=SUM(OFFSET(A8,0,1):F8)

Приведенная выше формула находит сумму значений из диапазона, начиная с 1 столбца справа от ячейки A8 до ячейки F8. Это означает, что независимо от того, сколько столбцов вставлено после A8, диапазон для этой функции SUM всегда будет начинаться со столбца справа от A8.

Точно так же давайте рассмотрим другую ситуацию, когда вы всегда хотите найти среднее значение за последние N месяцев 2020 года. Это значение N будет указано в другой ячейке, скажем, в ячейке B10. Давайте посмотрим, как мы можем убедиться, что независимо от того, добавлена ​​или удалена строка, мы всегда получаем среднее значение за последние N месяцев.

=AVERAGE(OFFSET(F1,COUNTA(F:F)-B10,0,B10,1))

Таким образом, использование функции СМЕЩЕНИЕ может быть действительно полезным, когда диапазоны ячеек, к которым вы хотите получить доступ, являются динамическими.

Использование смещения, когда адрес ячейки ранее не известен

Допустим, в следующем наборе данных вы хотите найти среднее значение, соответствующее значению месяца в ячейке B10. В этом случае будет невозможно предоставить прямые ссылки на ячейки внутри функции СРЕДНЕЕ, поскольку диапазоны ячеек будут продолжать изменяться в зависимости от значения месяца, указанного в ячейке B10.

В таких случаях может оказаться весьма полезной функция СМЕЩЕНИЕ. Чтобы найти среднее значение динамически, мы можем использовать следующую формулу:

=AVERAGE(OFFSET(A1,MATCH(B10,A2:A8,0),1,1,5))

Объяснение формулы

Функция MATCH в приведенной выше формуле находит индекс ячейки между A2 и A8, которая содержит значение в ячейке B10. Если значение в B10 — Mar, функция MATCH вернет здесь индекс 3.

Теперь функция OFFSET принимает это значение индекса и возвращает ссылку на диапазон ячеек, который начинается на 3 строки ниже ячейки A1 и 1 столбец справа от нее. Диапазон состоит из 1 строки и 5 столбцов, поэтому в основном функция AVERAGE рассматривает диапазон ячеек, отмеченных в красной заштрихованной рамке на изображении, показанном ниже:

Теперь вы всегда будете получать среднее значение за месяц, указанный в ячейке B11.

Важные моменты, которые следует помнить

Перед тем как закончить, вот несколько моментов, о которых следует помнить при использовании функции СМЕЩЕНИЕ в Google Таблицах:

  • Функция  OFFSET (СМЕЩЕНИЕ) возвращает # ССЫЛКУ! ошибка, если запрошенное смещение выходит за пределы края листа, поэтому всегда проверяйте, правильно ли вы рассчитываете значения смещения строки и столбца.
  • Если какая-либо из ваших строк, столбцов, высоты или ширины имеет формулы, возвращающие нечисловое значение, вы получите #VALUE! ошибка.
  • Функция СМЕЩЕНИЕ — это «непостоянная функция», что означает, что она пересчитывается каждый раз, когда в вашем листе происходит изменение. Итак, если на вашем листе слишком много функций OFFSET, это может в конечном итоге замедлить его.

В этом уроке мы рассмотрели, как вы можете использовать функцию СМЕЩЕНИЕ в Google Таблицах, чтобы получить более динамический доступ к ячейкам на вашем листе. Мы надеемся, что эта информация была полезна для вас.

Вас могут заинтересовать следующие статьи: