Функция OFFSET в Excel

Функция OFFSET (СМЕЩ) в Excel возвращает ссылку на диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцы. СМЕЩ удобен в формулах, требующих динамического диапазона.

Что делает функция OFFSET в Excel?

Функция OFFSET (СМЕЩ) в Excel создает ссылочное смещение от заданной начальной точки.

Возвращаемое значение

Ссылка на ячейку.

Аргументы

  • reference — начальная точка, указанная как ссылка на ячейку или диапазон.
  • rows — количество строк для смещения ниже начальной ссылки.
  • cols — количество столбцов для смещения справа от начальной ссылки.
  • height — [необязательный] Высота возвращаемой ссылки в строках.
  • width — [необязательный] Ширина в столбцах возвращаемой ссылки.

Синтаксис

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

Примечания по использованию

Функция OFFSET возвращает динамический диапазон, построенный с пятью входными данными: (1) начальная точка, (2) смещение строки, (3) смещение столбца, (4) высота в строках, (5) ширина в столбцах.
OFFSET — это непостоянная функция , которая может вызвать проблемы с производительностью в больших или сложных листах.
Отправной точкой ( аргументом ссылки ) может быть одна ячейка или диапазон ячеек. Аргументы rows и cols — это количество ячеек, которые нужно «сместить» от начальной точки. Аргументы высоты и ширины являются необязательными и определяют размер создаваемого диапазона. Когда высота и ширина опущены, они по умолчанию равны высоте и ширине ссылки .

Например, для ссылки на C5, начинающейся с A1,  ссылка — это A1, строки — 4, а столбцы — 2:

=OFFSET(A1,4,2) // возвращает ссылку на C5

Чтобы сослаться на C1:C5 из A1,  ссылка — это A1, строки — 0,  столбцы — 2, высота — 5, а ширина — 1:

=OFFSET(A1,0,2,5,1) // возвращает ссылку на C1:C5

Примечание: ширину можно не указывать, так как по умолчанию она равна 1.

Часто можно увидеть, что OFFSET завернут в другую функцию, которая ожидает диапазон. Например, для СУММ C1:C5, начиная с A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Основная цель OFFSET — позволить формулам динамически адаптироваться к доступным данным или пользовательскому вводу. Функцию можно использовать для создания динамического именованного диапазона для диаграмм или сводных таблиц, чтобы гарантировать, что исходные данные всегда актуальны.

Примечание. В документации Excel указано, что высота и ширина не могут быть отрицательными, но с начала 1990-х отрицательные значения отлично работали. Функция OFFSET в Google Таблицах не допускает отрицательного значения аргументов высоты или ширины.

Примеры

В приведенных ниже примерах показано, как можно настроить OFFSET для возврата различных типов диапазонов. Эти экраны были сделаны с помощью Excel 365, поэтому OFFSET возвращает динамический массив , когда результатом является более одной ячейки.

В более старых версиях Excel вы можете использовать клавишу F9 для проверки результатов, возвращаемых функцией OFFSET.

Пример №1

На приведенном ниже экране мы используем OFFSET, чтобы вернуть третье значение (март) во втором столбце (запад). Формула в H4:

=OFFSET(B3,3,2) // вернет D6

Пример #2

На приведенном ниже экране мы используем OFFSET, чтобы вернуть последнее значение (июнь) в третьем столбце (север). Формула в H4:

=OFFSET(B3,6,3) // returns E9

Пример №3

Ниже мы используем OFFSET для возврата всех значений в третьем столбце (север). Формула в H4:

=OFFSET(B3,1,3,6) // returns E4:E9

Пример №4

Ниже мы используем OFFSET для возврата всех значений за май (пятая строка). Формула в H4:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Пример №5

Ниже мы используем OFFSET для возврата значений апреля, мая и июня для западного региона. Формула в H4:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Пример №6

Ниже мы используем OFFSET для возврата значений апреля, мая и июня для запада и севера. Формула в H4:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Примечания

  • OFFSET в Excel возвращает только ссылку, никакие ячейки не перемещаются.
  • И строки, и столбцы могут быть представлены как отрицательные числа, чтобы изменить их нормальное направление смещения — отрицательные столбцы смещены влево, а отрицательные строки смещены вверх.
  • СМЕЩ в экселе — это « изменчивая функция ». Изменчивые функции могут замедлять работу больших и сложных книг.
  • Отобразит #REF! значение ошибки, если смещение находится за пределами края рабочего листа.
  • Когда высота или ширина опущены, используются высота и ширина ссылки .
  • Функцию можно использовать с любой другой функцией, которая ожидает получить ссылку.
  • В документации Excel говорится, что высота и ширина не могут быть отрицательными, но отрицательные значения работают.
Автор А. Днепров

Обучаю основам работы с офисными программами.

Функция OFFSET в Excel
Как преобразовать формат телефонного номера в цифры в Excel?